Case Study Teradata Data Mart Consolidation Return on Investment at GST
Professor Robert J. J. Sweeney Sweeney,, Wright State University University Robert J. Davis Davis,, Teradat eradata, a, a divis division ion of NCR Professor Mark Mark Jeffery, Jeffery, Kellogg School of Management
Case Study Teradata Data Mart Consolidation Return on Investment at GST
The telecommunications company was having a tough year with the stock price down 35%. Overview
persuaded? He also wondered how best to quell
Robert Davis had just finished a meeting with
Richards’ concerns about organizational
Mark Johnson and Jeff Richards the CFO and
change and moving to a Teradata architecture?
State University and
CIO of GST Inc.The telecommunications com-
Fortunately, Johnson and Richards had provid-
Robert J. Davis of
pany was having a tough year with the stock
ed a detailed breakdown of their costs for the
price down 35% and Johnson was looking for
existing systems.
Professor Robert J. Sweeney of Wright
Teradata, a division
ways to significantly reduce costs. Davis
of NCR prepared
worked for Teradata and Richards had request-
GST INC.
ed he come in to talk with the CFO about
Located in the southeast, GST operates
streamlining their investment in technology.
in the highly competitive telecommunications
Davis had suggested data mart consolidation
industry. With 13 million customers in
as a potential solution.
11 states, 28,000 employees and annual sales
this case study in collaboration with Professor Mark Jeffery
exceeding $5 billion for the most recent
from Northwestern
The idea of consolidating systems seemed like
year, GST was positioning itself to become an
University's
an easy win, but Johnson was not impressed.
industry leader through its commitment
He wanted to see hard numbers “before he
to product innovation and personalized
Kellogg School of
invested a dime.” Richards was
customer service.
Management as
not as skeptical but he was concerned about
the basis for class discussion rather than to illustrate
the move to a non-standard infrastructure,
GST began in 1903 as Greater Southern
what he would do w ith the technical resources
Telephone, the region’s third largest incumbent
potentially displaced by this
local exchange carrier (ILEC). Over the years,
new system, user training,and related
Greater Southern has changed its name to GST,
organizational change issues.
extended its reach as a competitive local exchange carrier (CLEC), and now
effectiveness of management. Some
Davis walked out of the GST corporate
provides a complete menu of state-of-the-art
headquarters towards his car.Johnson had
telecommunications services to its ever-
facts within the case
really harped on the need for a realistic ROI
expanding array of business and residential
have been altered
analysis before he committed any upfront capi-
customers; each customer has a unique
tal to the project. Davis needed his team
need for which GST has cultivated a unique
to put together an ROI analysis that would
relationship. The service menu includes data
clearly demonstrate how the Teradata
and voice transmission capabilities such as
solution could help GST and impact their
broadband data services and Internet access
bottom line. He wondered how much capital
delivered over a digital network.
for reasons of confidentiality.
would be required to fund the consolidation and if Johnson and Richards could be
EB-3105
PAGE 2 OF 13
Case Study ROI for a Customer Relationship Management Initiative at GST
A. Organization Chart for GST Inc. Mary Gros CEO
Tom Webster COO
Mark Johnson CFO
Jeff Richards CIO
Stacy Hoyle VP Region #1
Daniel Wymer CAO
Jill Newburg VP Region #2
Barb Young General Counsel
Dominique Arnold VP Region #3
Cheik Daddah Investor Relations
Jeff Shoemacher VP Region #4
Erica Kolks Marketing
Meghan McCormick VP Region #5
Nichole Knell Industry Relations
Raveen Rajavama VP Region #6
Karine Hatti Human Resources
Jean Secrist VP Region #7
1a
B. Organization Chart for GST Inc. Region #4 Jeff Shoemacher CEO VP Region #4
Fall Ainina CFO
Rebecca Koop CIO
Susan Lightle CAO
Bud Baker ILEC
Paula Saunders CLEC
Cathy Kempf Internet Services
Michael Edwards Data Services
Joe Castellano CustomerRelations
1b As the business evolved technologically
The organization of each GST geographic
and geographically, GST adopted a
region includes a regional vice president serv-
TERADATA Teradata is a division of NCR Corporation, and
decentralized model by region. The
ing as the CEO of the business unit, a regional
is a leading provider of enterprise data ware-
corporate level leadership team includes
CFO, a regional CIO who also reports to the
housing technology and solutions.
the President and CEO, the COO,and
corporate CIO, a CAO and several product
NCR has a storied history dating back
fifteen vice presidents; seven are regional
managers. Exhibit 1b represents the organiza-
to its inception in 1884. In that year, John
VPs while the other eight include the Chief
tion chart for GST Region 4.
H. Patterson purchased the National
Financial Officer, Chief Accounting Officer,
Manufacturing Company, maker of the first
Chief Information Officer,Senior VP
Mary Gros, CEO, had requested a set of income
mechanical cash registers, and renamed it
for Investor Relations,VP for Human
statements reporting MIS expenses separate
National Cash Register Company.
Resources,VP for Marketing,VP for Industry
from Cost of Goods Sold. She noted the
Relations, and the General Counsel.The high-
increase in IT expense each year, both in
Extending from mechanical cash registers,
level corporate organization chart is provided
dollars terms and as a percentage of revenue,
NCR evolved into an innovative supplier
in Exhibit 1a.
and charged Johnson with finding ways to
of advanced Point of Sale Solutions, the world-
cut costs. Exhibit 2 contains the comparative
wide leader in sales and shipment of
income statements for the past three years
Automated Teller Machines (ATMs), and data
for Region 4.
EB-3105
PAGE 3 OF 13
Case Study ROI for a Customer Relationship Management Initiative at GST The data warehouse systems conComparative Income Statements GST Inc.—Region #4 2001
nect with customer mainframes
2000
1999
and operational systems to “siphon off”pertinent detailed data from
Revenue*
$319,904
$280,289
$252,437
Costs and expenses, excluding MIS and depreciation
$107,406
$106,539
$108,037
MIS
95,971
75,678
58,536
and timely analysis and action.
Depreciation and amortization
55,824
45,605
39,832
This integrated decision support
$60,703
$52,467
$46,032
3,733
2,524
2,973
Operating Income (Loss) Interest and dividend income Interest expense Other income, net Income (loss) before income taxes Provision (benefit) for income taxes Net income (loss)
silos into a large database, where
(21,790)
(15,939)
the data can be queried for effective
system is called an Enterprise-wide Data Warehouse (EDW).
(13,417)
698
531
326
$43,344
$39,583
$32,914
20,911
18,833
15,333
$22,433
$20,750
$17,581
The primary elements of Teradata’s value proposition are:
2 * All numbers are in units of thousands.
warehousing solutions. In 1974, the company
upon customer information previously locked
officially changed it name to NCR Corporation.
in isolated data silos. Exhibit 3 is a schematic
Today, NCR has a global reach with annual
view of isolated data silos in a typical large
revenues of $6 billion and approximately
corporation such as GST.
Proven Performance Customer References Teradata customers include many successful global companies such as: Wal-Mart, Bank of
America, 3M,SBC,Delta Airlines,Whirlpool, Belgacom, Harrah’s Entertainment, Royal Bank of Canada, Procter & Gamble,AT&T, Travelocity, and Merck Medco.
32,000 employees. In 1991, AT&T invested $7.4 Billion to acquire NCR and effectively established the
Multiple Views and Silos of Data in a Large Corporation such as GST Inc.
unit as their computer systems division. Behaviors Purchase History
That same year, NCR purchased Teradata Corporation for their advanced enterprise data warehousing technology.NCR became an independent company again in 1997 as a result of the restructuring of AT&T into three distinct companies: AT&T, Lucent Technologies and NCR. Teradata, founded in 1984,was based upon the mission of providing high-performance commercially viable data warehouse technology and solutions.Data warehouse technology enables large corporations to analyze and act
EB-3105
PAGE 4 OF 13
Margins Resources
Customers Demographics Preferences Attitudes
Enterprise Profits Inventory Growth
Channels
Payment Availability
Partners
New Entrants Competitors .com's Products & Services
Quality Delivery Growth
Marketing
3
Case Study ROI for a Customer Relationship Management Initiative at GST Scalability Scalability is the ability to support more users over time. For an EDW, scalability has multiple dimensions: hardware, support of user connectivity, and from a database perspective the ability to support ever increasing expectations for complex as well as ad hoc query performance. Demands on a data warehouse increase exponentially as data and user volumes grow, update frequencies increase, and the operational feeder systems multiply.The Teradata solution has demonstrated scalability. Support for High User Concurrency One of the sure signs of a successful data warehouse is when more and more business users want access to it. In some environments, this demand presents a dilemma: Do you accept all users and suffer performance degradation that leads to diminished warehouse effectiveness and user attrition? Or do you restrict data warehouse access to a limited number of users, resulting in sufficient warehouse performance but reduced overall business value? The Teradata solution uses massively parallel processing so that many users can access the system simultaneously without loss of performance.
BACKGROUND ON DATA WAREHOUSE TECHNOLOGY
A schematic diagram of a typical data ware-
too costly and as a result, data marts
house is shown in Exhibit 4a. The typical flow
have proliferated. Data marts are smaller
of data to information is as follows: operational
repositories of information that are for a
data is generated through customer transac-
specific business unit or process. Exhibit 4b
tions. Data is then transformed into a consis-
is a schematic of a company similar to
tent format into storage for later use. The
GST that does not have a centralized data
appropriate information is extrac ted and
warehouse,but instead has a series of
imported for summarization. The summariza-
isolated data marts.
tion might involve comparing sales across time, across products, and by profit margins.
As independent systems, data marts are
Similarly, data can be summarized by cus-
often considered less expensive to operate.
tomer, across time, and across products by
This is only true if one ignores many of the
profit margin. Finally, the summarized data is
hidden costs associated with data marts. In a
presented as information for use in future
2001 Gartner report, it was determined that
business decisions.
data marts were 70% more expensive to operative per subject area than a comparable
The storage component of the data flow is
data warehouse.
the subject of data warehousing.In most decentralized business environments,
Data marts are usually constructed for an
data warehouses have been considered
individual user/business unit because of the
Data Mart and Data Warehouse Architectures
IT Users
Operational Data
Data Transformation
Enterprise Warehouse & Management
Data Transformation
Data Marts
Business users accessing disparate data marts
A data warehouse is not a product but rather a process. Data warehouses are environments that allow business users to transform vast amounts of data into useful information
Business Users
Schematic of a typical data warehouse architecture
4a
efficiently and accurately, enabling companies to “get to know the customer.”
EB-3105
PAGE 5 OF 13
Alex Payne, Marketing Specialist, Teradata, a division of NCR and Chiek Daddah, Senior Business Analyst, Teradata, a division of NCR
Case Study ROI for a Customer Relationship Management Initiative at GST difficulty of obtaining data consensus across the organization.Data marts often become isolated
Data Mart and Data Warehouse Architectures
data silos. This is primarily because business users tend to want to t inker with the system and
IT Users
customize it to their specific business division Operational Data
needs. As the number of users (tinkerers) grows, the effectiveness of the mart deteriorates.
Data Transformation
Different users with differing information needs might customize the mart to their unique needs.
Data Marts
This customization makes it v irtually impossible to share information across the organization.
Business users accessing disparate data marts
Finally, changing the data mart is often slow – programmers often wait until a large number
Business Users
of changes are received before they alter the data mart code.
Architecture comprising of isolated data marts and no centralized data warehouse.
4b
The data warehouse architecture Exhibit 4a is an improvement over the data mart environment
Alex Payne, Marketing Specialist, Teradata, a division of NCR and Chiek Daddah, Senior Business Analyst, Teradata, a division of NCR
Exhibit 4b because it allows business users across the organization access to a single set of data. The data warehouse is more readily adaptable to Data Mart and Data Warehouse Architectures
change as user needs change,and is generally free from the tinkering that tends to be endemic to data marts. Furthermore, data warehouses are
External Data
cost effective because they eliminate redundancy
Type 1 Data Mart
in staffing as well as information. Data integration is essential to the development
ODS
Warehouse
Type 2 Data Mart
of a single view of the enterprise. However, even with integrated data, companies achieve maximum success if the integrated data is
Independent Data Source
available to all business units in a useful form
Type 3 Data Mart
that is both cost-effective and accurate. Enterprise data warehouses can be seen as a n important step in this direction.
Data Sources
Information Users
Hybrid data mart/data warehouse architecture
4c Alex Payne, Marketing Specialist, Teradata, a division of NCR and Chiek Daddah, Senior Business Analyst, Teradata, a division of NCR
EB-3105
PAGE 6 OF 13
Case Study ROI for a Customer Relationship Management Initiative at GST Like most companies, GST organizes data by function: customer data,partner data, competitor data, and finally enterprise data. A schematic of this configuration is given in
Data Mart and Data Warehouse Architectures
IT Users
Exhibit 3. Partitioning data along these lines obscures many business
Operational Data
relationships that could be more cost Data Transformation
effective and more profitable.
BACKGROUND ON DATA MART SYSTEMS
The typical data mart environment usually includes independent data marts, dependent data marts and/or hybrid data marts. In an independent mart (Exhibit 4b), transactional data is collected, transformed and then stored in data marts. These data are then shared with the business users. Eliminating data redundancy, guaranteeing data synchronization and capturing data latency are difficult to achieve let alone manage in a data mart environment.
Enterprise Warehouse & Management
Business Users
Architecture of an Enterprise Data Warehouse (EDW). The system consolidates all data marts into a single enterprise-wide database. Users then query the database directly, instead of querying disparate data marts.
4d Alex Payne, Marketing Specialist, Teradata, a division of NCR and Chiek Daddah, Senior Business Analyst, Teradata, a division of NCR
Data marts operated separate from the
(MPP) to process many user queries
business users can create data management
simultaneously. The database at the core
problems down stream. For example,
of the Teradata EDW system has much higher
business users obtaining data will create
performance than competitors such as IBM or
internal systems to consolidate the data and
Oracle, and this high performance means that
Dependent data marts (Exhibit 4a) receive
to analyze the data.A simple change in the
individual data marts can be eliminated.
data from a data warehouse before the data is
way the data is reported from the mart, say for
shared with the business users. Transactional
example, from weekly information to daily
With the new architecture, shown in Exhibit
data is again collected and transformed and
information will obviously alter the way the
4d, all data is housed in a single place giving
the information is stored in a data warehouse.
data is interpreted. Unless the business users
business users access to a single view of the
From here the information flows to data marts.
are vigilant about keeping pace with the changes,
enterprise and more specifically,t he customer.
Similar to an independent data mart environ-
decisions could be made using faulty data.
Data synchronization is assured since any
ment, redundancy, synchronization and latency
changes in the way the data is collected at
are problems in a dependent environment.
the transactional level flows directly and
The third environment is the hybrid data mart system,shown schematically in Exhibit 4c.
ENTERPRISE DATA WAREHOUSE ARCHITECTURE
Hybrid systems incorporate features of
immediately to the business users. Unlocking the information content of the data
both independent and dependent data mart
The architecture for an enterprise data
(data latency) is facilitated since the data is
environments. In addition, the hybrid
warehouse (EDW) is shown schematically
accessible at a more granular level. Finally, data
environment incorporates the data problems
in Exhibit 4d. The Teradata EDW database
redundancy is eliminated since the business
associated with data marts.
incorporates massive parallel processing
users have access to a single source for data.
EB-3105
PAGE 7 OF 13
2
Note that marketing research studies may provide additional insights into what constitutes a reasonable percentage increase in value.
3
This is where some thought will have to be given as to what marketing actions will be taken.
Case Study ROI for a Customer Relationship Management Initiative at GST Potential costs that are either eliminated or reduced from Exhibit 4b include administration costs, systems maintenance costs,data movement
The Teradata Enterprise Data Warehouse (EDW) Physical Architecture Pilot Footprint
costs and data synchronization costs. Simply stated, data redundancy leads to staff redundancy, and eliminating disparate data marts can reduce the staff count. The actual Teradata system configuration is shown schematically in Exhibit 5a. The bottom cabinets in the exhibit represent disk arrays. The disk array can be comprised of either 18GB drives (1.4 terabytes of data) or 36 GB drives
(2) SMP Nodes per cabinet (4) Intel CPUs per Node
(2.8 terabytes of data.): Disk arrays can be clustered
cabinets. Each cabinet has two nodes comprised
SMC
SMC
SMC
BYNET
BYNET
BYNET
BYNET
BYNET
BYNET
BYNET
SMP
SMP
SMP
SMP
SMP
SMP
SMP
SMP
UPS
UPS
UPS
UPS
UPS
UPS
UPS
UPS
UPS
UPS
UPS
UPS
SMC
Disk Options
Up to 256 Cabinets Up to 2,048 Intel CPUs
SMC
Disk Array (40 Disks)
Up to 100's Terabytes
Disk Array (40 Disks)
18GB Drives (1.4TB) or
Disk Array (40 Disks)
Disk Array (40 Disks)
Height–77" Width–22" per Disk Array
36GB Drives (2.8TB)
together to support 100s of terabytes of data. The middle section of Exhibit 5a contains node
SMC
BYNET
AWS
The Teradata EDW architecture consists of 2 – 512 processing nodes (each node consists of four high performing Intel based CPUs —this is called a symmetric multi processor (SMP) node with disk scalability up to 100s of terabytes via highly available, hot-pluggable, Redundant Array of Independent Disks (RAID) for data storage. Nodes can be aded in pairs to map to the processing requirements of each configuration. Disk options exist with Teradata sourcing RAID configurations from EMC 2 and LSI Logic. The GST data mart consolidation pilot system would be approximately 20% of the complete EDW, and is shown schematically in the dashed box.
5a
of 4-Intel processors. In addition, nodes are interconnected via Teradata’s BYNET. The processing cabinets are designed for resiliency with uninterrupted power supply units in each cabinet. Up to The Teradata BYNET
256 cabinets (equaling 512 nodes) can be configured as a single massively parallel processing (MPP) system.As of January 2002,a total of 2,048 Intel CPUs
BYNET
could be configured in a complete Teradata EDW. The top portion of Exhibit 5a presents the adminis-
NODE
Cliques
(4) CPUs in a Node Up to 512 Nodes
Grouping of 4 Nodes Redundancy in case of Node failure
tration work station (AWS). This is a standalone UNIX or Windows based workstation that is the primary operations interface for MPP systems. The AWS provides a single, graphical view of the system. Not shown are the thousands of end users with access to the system. Finally, the dotted line containing three cabinets (or six nodes) is the footprint for the proposed GST pilot program. Exhibit 5b demonstrates the proprietary competitive advantage of the Teradata EDW architecture. Up to 512 nodes, each node contains four CPU’s, can be
EB-3105
PAGE 8 OF 13
RAID Redundant Array of Indepent Disks Terabytes of Data
The BYNET is a hi gh-speed interconnect that is optimized for parallel processing with the Teradata Relational Database Management System. More specifically, two BYNETs are configured with every Teradata MPP (Massively Parallel Processing) System for redundancy, high performance and scalability. These BYNETs are uniquely designed to provide simultaneous, bi-directional traffic (messages) between the : ¥ Processing Nodes ¥ Parsing Engine (PE-checks the SQL statement, access rights and invokes action), ¥ and the Access Module Processors (AMPs) for effective data retrieval and disk management. The BYNET is the key design feature that enables support for many concurrent users and maximum system throughput.
5b
Case Study ROI for a Customer Relationship Management Initiative at GST access to the same data – this structure is both less costly and
GST Average Annual Salary Data
more consistent.The improvements in efficiency and consistency are
System Administrator
$130,000
value-added by the data mart
Data Base Analyst
$110,000
consolidation.
COSTS OF THE GST DATA MART ENVIRONMENT
ETL Programmer
$80,000
Query Programmer
$70,000
Network Administrator
$80,000
Support Staff
$40,000
Benefits
Susan Lightle, CAO of Region 4 was asked to identify the costs associated
40% of salary
Expected Inflation Rate: Salary and Benefits
4%
7
with the data marts. She offered the following information – Each Oracle data mart requires one system administrator,two data base
GST System Staffing Requirements, Maintenance, and Support Costs
analysts,two ETL programmers, three query programmers, one
GST Individual Data Marts Oracle 8I
IBM DB2
Best Case
Most Likely
Worst Case
System Administrator
1
1
1
1
1
addition, non-personnel support
Data Base Analyst
2
3
6
8
9
costs for each Oracle system was
ETL Programmer
2
2
3
4
8
approximately $1,000,000 for the
Query Programmer
3
3
8
10
15
Network Administrator
1
1
0
0
0
Support Staff
2
2
2
3
4
network administrator, and two people working as support staff. In
next year. This did not include $80,000 per year per mart for maintenance and upgrades.
Staff / System
Teradata EDW
Maintenance per node
An IBM data mart required one system administrator, three data
Non-personnel support costs
$80,000/yr
$110,000/yr
$1,000,000/yr $1,800,000/yr
10% of HW and software list price per yr $125,000/month after the data marts are decommissioned
base analysts,two ETL programmers, three query programmers, one network
number and type of GST employees required for
reductions. The most likely scenario for
administrator, and two people working as
each Oracle and IBM data mart, see Exhibit 8.
staffing the proposed enterprise data
8
support staff. Non-personnel support costs
warehouse is one system administrator, eight
for the IBM system was $1,800,000 per year.
data base analysts,four ETL programmers,
Maintenance and upgrades for the IBM mart total $110,000 per year.
COSTS FOR THE TERADATA SOLUTION
ten query programmers, and three individuals serving as support staff. Exhibit 8 also summarizes the best, worst, and expected
Lightle gave Davis GST employee salary and
The staffing requirement for the Teradata
case scenarios for staffing the new Teradata
benefits information, see Exhibit 7. She also
system depends, in part, on how GST
system.The exact probabilities for the
gave Davis a summary breakdown of the
management decides to handle the personnel
GST staffing changes were not known,
EB-3105
PAGE 10 OF 13
Case Study ROI for a Customer Relationship Management Initiative at GST however the GST team urged Bob to use Teradata Cost Sheet
20%-60%-20% as the probabilities for the
Hardware and Software
staffing scenarios best case, most likely Item
case and worst cases, respectively. The list prices associated with the acquisition of the data warehouse are included in Exhibit
1st Node
2nd Node
3rd Node
4th Node
5th Node
Hardware
$175,000
$225,000
$200,000
$200,000
$720,000
Software
$90,000
$190,000
$190,000
$190,000
$500,000
Training and Professional Services Costs for the Teradata Solution
9. The consolidation of the five data marts will
Expense
Year 1
Year 2
Year 3
require five nodes.Although the first four
Training
See Exhibit 11: $15,000 per month starting in May
$15,000 per month
-$0-
nodes are sold as individual units, nodes beyond the fourth are only sold in pairs.The
Consulting
prices quoted in Exhibit 9 are per node. The proposed system (nodes, software, and disks)
See Exhibit 11: $125,000 per month $125,000 per month $125,000 per month after implementation Data Storage Disk Costs
would be depreciated using the MACRS 5-year class life schedule assuming the mid-year
$650,000 (For 2.8TBytes of data)
convention. The total cost for disks is estimated
9
Adapted from Steven Weber, Pricing Director, Teradata, a division of NCR
as $650,000. Maintenance/upgrades for the
ments of the total enterprise (including the
nodes and software is 10% of the list price. Professional services costs (business
requirements associated with the remaining
Finally, the first year non-personnel support
consulting) for the three years of the pilot
45 data marts). In addition, Davis was
costs for the Teradata warehouse, once the
study are quoted at $125,000 per m onth
suggesting GST begin work on the development
system is operational, is projected to be
once the implementation project is complete.
of customer relationship management programs
$1,500,000 (paid in monthly installments.)
Exhibit 11 gives the detailed break down of the
that would be possible with the more complete
professional service costs during the estimated
view of the customer. The professional services
12-month implementation schedule.
costs in years 2 and 3 were associated with
On behalf of Teradata, Davis can offer an installed price for nodes and software at 30%
the design of the data warehouse under a full-
off the list price.In addition, Teradata is willing
Consulting costs decline dramatically after
consolidation EDW scenario and for the
to provide a $400,000 equipment credit against
the first year because GST was being urged to
development of CRM programs.
the purchase price if GST commits to the
purchase the hardware and re-architect the
consolidation pilot study.However,the disks
data structure at the beginning of the process,
Training costs, separate from business
for the data storage would not be eligible for
which front-loads the consulting fees. This was
consulting,would be $15,000 per month for
the 30% discount.
an alternative to acquiring a node, migrating
the first two years – see Exhibit 11 for the start
Summary GST financial assumptions
the data, and re-architecting the system
date of the training.Some of these costs were
sequentially. Davis was convinced the
related to training the existing employees on
former was in the b est long-term interest
the new system as well as training dislocated
Required return for project investments
14%
of GST. Davis was also encouraging GST
existing employees for other internal positions.
Corporate Tax Rate
38%
to engage Teradata’s team of consultants
Training would commence once the data marts
Inflation Rate: Non-personnel costs
5%
to commence work on the development
are loaded into the warehouse. For this ROI
Inflation Rate: Personnel costs
4%
of a logical data model to address a
analysis,the training costs would be expensed
holistic look at the information require-
as incurred.
10
EB-3105
PAGE 11 OF 13
Case Study ROI for a Customer Relationship Management Initiative at GST IMPLEMENTATION PROJECT Data Mart Consolidation Project Baseline
Exhibit 11 is a high-level schematic
1st Quarter
of the proposed data mart consolida-
Jan
tion implementation project.Phase 1
3 wks
Feb
2nd Quarter
Mar
Apr
May
Jun
3rd Quarter Jul
Aug
6 wks
take approximately 2 weeks. Although
Migrate Datamart 2
4 wks
much of this work is done as part of
Migrate Datamart 3
the proposal, many details of the
4 wks
Oct
Nov
1st Quarter
Dec
Jan
Feb
Phase 1: Data Capture and Planning •Understand the data structure in each mart •Identify ETL processes •Specify amount and frequency of updates •Scope amount of data
Data Capture and Planning
– data capture and planning should
Sep
4th Quarter
Migrate Datamart 4
4 wks
existing system must be understood
Migrate Datamart 5
prior to data migration. Phase 2 –
6 wks Datamart Testing
Phase 2: Data Migration •Forklift data from marts into data warehouse •Transfer scripts, C Programs and PL/SQL •Migrate 3rd party Applications •Test data marts
moving data to the Teradata system will involve between 3 and 4 weeks per data mart (15 to 20 weeks for 5 data marts.) This represents the
16 wks Engineer EDW
8 wks Test EDW
physical migration of the data, tables,
Phase 3 & 4: Enterprise Data Warehouse Architecture Pilot Study •Develop logical model •Testing
and processes highlighted in Phase 1. After the fifth data mart had been migrated, all the original data and many applications would be again available to the end-users and the data marts could be retired. However, once all the data a nd applications were copied to the warehouse, GST required a 6-week test and validation
Data Mart Consolidation Project Budgeted Cost of Work of Schedule Expenses Professional Services
Jan
Feb
Mar
Apr
$220
$255
$270
$290
May $290
Jun
Jul
Aug
Sep
Oct
Nov
Dec
$290
$270
$270
$270
$270
$270
$270
$15
$15
$15
$15
$15
$15
$125
$125
$125
$125
$125
$125
Training Non-personnel Support
process be conducted to guarantee that, from the user’s perspective, the warehouse was identical to the
All dollar amounts are in thousands. Professional services costs include: Data capture and planning data migration, scope of complete EDW–consolidating the remaining 45 data marts, and scope of future CRM applicaions. Training costs include: Training existing employees on Teradata system, and training dislocated employees on other internal systems. Non-personnel support costs include: Travel, subscriptions, overhead allocation, etc.
original data mart. Bob believed the
11
first test phase would be complete, and the data marts could be retired,
Source: Alex Payne, Marketing Specialist, Teradata, division of NCR and Cheik Daddah, Senior Business Analyst, Teradata, a division of NCR
as soon as May 1 or it could take as long as September 1. However, it was most likely that the data marts will be decommissioned on July 1.
Phase 3 – model design, re-architect model, and update will take 3 to 4 months. Although the end-users have access to the data and tables, it was during Phase 3 that the
EB-3105
PAGE 12 OF 13
enterprise re-architecture will eliminate the redundant systems producing significant performance improvements. Testing represents the final phase, Phase 4, before the warehouse was fully operational. Bob was rather certain that Phases 3 and 4 will take a total of six months to complete.
The complete transition from data marts to an enterprise data warehouse was expected to take twelve months to achieve. Phases 1 and 2 could be accomplished more efficiently or take longer that expected. In total, the transition could take as few as ten months or as long as 14 months. For each month the project goes over or under the 12 month
Case Study ROI for a Customer Relationship Management Initiative at GST base-line the professional service implementation cost would increase or decrease by approximately $270,000. Davis had experienced 9 similar data mart consolidation projects. Of these,2 had come in under time at 10 months, 3 had taken 12 months,and 4 projects had run over to the full 14 months.
of 38%, expected an inflation rate for nonpersonnel support costs of 5% annually,and expects salaries to increase 4% per year across-the-board. In addition, GST was considering retaining one Oracle mart for an internal training program. These data are summarized in Exhibit 10.
ANALYSIS Following are some questions to consider with your analysis: • What is the project ROI and the pay back period? • Of the best,worst, and expected case
Davis wanted to make sure his team calculated best, worst, and most-likely cases for the project ROI, and were realistic in their numbers. The existing data marts and the enterprise data warehouse would be operated simultaneously until the fifth data mart has b een successfully moved.Hence, following the base-line plan, by early June the original data marts could be decommissioned. However, GST required the data marts would continue to operate until July 1 during the data mart test phase (see Exhibit 11) to ensure the data and application validation were completed.
Davis was in contact with Johnson, and they concurred that the analysis of the pilot study should be conducted utilizing a three-year investment horizon. The three-year horizon begins with the start of Phase 1 and runs for 36 months. Phase 1 would commence on the first day of January 2002.
which should you present to GST? • How much upfront capital is needed for this project, and what financing options would you recommend? • How would you recommend dealing with Richards personnel concerns? • If you were Johnson and Richards, would you move forward with the consolidation project?
BUSINESS IMPACT MODELING TEAM Davis planned to give this ROI problem to the
ADDITIONAL DATA
Business Impact Modeling Group at Teradata. He wanted to make sure they would be thorough
GST used a weighted average cost of capital (WACC) of 14%, had a tax rate
enough to calculate best, worse, and a mostlikely case for the project ROI,a nd be realistic in their numbers.As members of the team, help Davis make a recommendation to GST.
© 2002 by Mark Jeffery. No part of this publication may be reproduced, stored in a retrieval system, used in a spreadsheet, or transmitted in any for by means electronic, mechanical, photocopying, or otherwise - without the permission of Mark Jeffery. Teradata is a registered trademark and WorldMark is a trademark of NCR Corporation. All other brand and product names appearing in this release are registered trademarks or trademarks of their respective holders. NCR continually improves products as new technologies and components become available. NCR therefore, reserves the right to change specifications without prior notice. All features, functions and operations described herein may not be marketed in all parts of the world. Consult your NCR representative for further information. © 2002 NCR Corporation www.teradata.com
EB-3105
Dayton, OH U.S.A.
www.kellogg.nwu.edu
PAGE 13 OF 13
Produced in U.S.A.
All rights reserved.
