In Search of a Single Version of Truth

• 11/03/04

By Wayne Eckerson, Director of Research, TDWI

Drivers of Analytic Consolidation

The one word that best describes the state ofanalytic data in large organizations is fragmented.Despite their best intentions, CIOs are struggling todeliver consistent data that provides a single viewacross the enterprise. CIOs who seek this so-calledsingle version of the truth? must feel like they are playingan endless game of Whack-a-Mole every timethey stamp out a renegade analytic silo, another popsup elsewhere.

Consequences of Proliferation. The problems thatresult from this rampant proliferation are twofold. First,executives become frustrated because they can't getthe data they need to assess the performance of theircompany. They get apoplectic when managers spend more time in meetings arguing about whose data is right rather thandeveloping strategies and plans to achieve corporate goals.Without a consistent view of performance across the enterprise,executives know they can't do a good job running the company.

Second, these redundant, non-integrated analytic structuresare wasteful. When deployed as physically distinct systems withseparate hardware, storage, software licenses, data feeds, andstaff, these systems can drive up data warehousing costs by 30to 50 percent, according to some data warehousing managers.Consolidating these structures can save organizations millions ofdollars a year and deliver a quick return on investment (ROI).

Remedies for Proliferation

The Disease Is the Cure. Ironically, the cure for the proliferationof analytic silos is to create another analytic structure.To stamp out analytic silos, organizations need toimplement an enterprise data warehouse (EDW) thatprovides sustenance for all past, present, and futureanalytical structures. The key is to plant the EDW deeplyenough and broadly enough within the informationarchitecture and corporate culture that it becomes thede facto analytic structure within the organization.

The EDW only works, however, if the organizationcomes together to hammer out definitions and rules forcommonly used terms and calculations, such as netmargin or sale or profit. Standardizing the meaning of shared data elements often referred to as metadata is oftenmore challenging than consolidating the actual physical structures.Once this exercise is completed, the EDW becomes the repositoryfor shared data, rules, definitions, and other metadata used by multipleanalytic applications.

Trends in Analytic Consolidation

A Big Job. Consolidating duplicate data and redundant analyticstructures is not for the faint of heart. That?s because most organizationshave dozens of analytic structures that they would like toconsolidate. As mentioned above, organizations have an average oftwo data warehouses, six independent data marts, 4.5 ODSs, and28.5 spreadmarts left to consolidate. To date, they've only consolidatedabout one-third of all the structures in their environment. Butacquisitions, mergers, and reorganizations make this number a perpetually moving target. (See illustration 1.)

Illustration 1. Organizations have only consolidated about a third of the analytic structures that exist in their organizations. Based on 521 respondents.

On average, organizations have consolidated a slightly higher percentage ofdata warehouses (42 percent) and a slightly lower percentage of spreadmarts(22 percent). This makes sense given the relative numbers of these structures.In fact, itÂfssafe to say that the smaller the structure, the more likely it is to proliferateand the harder it is to consolidate.

Every week during our assessment phase, I discovered a new spreadsheetthat was a source for customers or contacts. In the end, I found more than 23files in spreadsheets, Foxpro, Act, Goldmine, and Paradox databases that containedcustomer information,Âh says Wanda Black, director of information resourcemanagement at a privately held manufacturing firm.

Project Duration

Time to Consolidate. Not surprisingly, the most complex analytic structurestake the most time to consolidate and the simplest structures take the leasttime. Our survey shows that data warehouses take an average of 9.75 monthsto consolidate (the average of planned and completed consolidation), followedby operational data stores at 6.82 months, data marts at 6.02 months, andspreadmarts at 5.69 months. (See illustration 2.)

Illustration 2. Data warehouses take thelongest time toconsolidate, approximately 9.75 months. Based on 209 respondents who havecompleted a consolidation project.

Twenty-Three Years of Hard Labor. If we multiply the number of analytic structuresthat organizations plan to consolidate (see Illustration 1) by the averagetime to consolidate each type of structure, then it will take organizations277 months or23 years to complete their projects if done sequentially! Obviously, organizations need to consolidate multiple structuresin parallel or implement a true EDW that can replace all warehouses, marts,and spreadmarts at once or more than likely over a period of severalyears.

Cost Justification

Although reducing costs is an important reason to consolidate analytic silos,it usually takes a backseat to more strategic concerns.

"We did some ROI analysis, but thatÂfs not the driver. Although weexpect to reduce costs by 30 percent or more, what is really driving this projectis the need to comply with the Basel Accord and improve the bank's riskanalysis capabilities," says the lead architect at an Australian bank.

According to our survey, less than a third of organizations analyze what it costs to support independent data marts or data warehousesor migrate them to a new environment. Similarly, less than one-third calculatethe ROI of migrating analytic structures to a new environment.

Average Costs and Projected ROI. However, TDWI research shows that consolidationprojects deliver a hefty payback on the investment. On average, it costs$614,000 a year to maintain an independent data mart and $544,000 to migrateit. It costs $1.597 million a year to maintain a data warehouse and $1.171 millionto migrate it. (See illustration 3.)

Illustration 3. Data warehouses cost moreto support andmigrate to a consolidated environment than independent data marts. Basedon 150 respondents who have already consolidated analytic structures.

Among organizations that calculated ROI for their projects, the average ROIwas $3.34 million over 2.1 years. (See illustration 4.) Our estimates matchthose generated by Alstom Power, which is now building an EDW to consolidatenumerous analytic structures, including a data warehouse, a reporting repository,two departmental data marts, and numerous Access databases. Alstom expectsa $3.5 million payback over 2.8 years from its project, according to MichaelSykes, U.S. manager of data warehousing at the company.

Illustration 4. The average ROI for consolidationprojectsis $3.34 million. Based on 150 respondents.

The most promising candidates for consolidation are data marts that run ondifferent platforms and are managed by separate IT staffs. Consolidatingthese independent data marts can save companies more money than those thatrun on the same platform and are managed by the same IT staff. Of course,as we have seen, saving money is not the prime incentive for consolidating marts;the key driver is delivering consistent information to the enterprise.

Architectural Approaches to Consolidation

End-State Architectures

Once an organization has made a commitment to consolidating analytic structures,it needs to plan how to migrate from its current chaotic environment to astandardized architecture. Generally, once an organization has lived throughthe pain of analytic silos, it is adamant about centralizing as much data,resources, and infrastructure as it possibly can.

"A centralized environment ensures that people are looking at the samesource of information and getting consistent reporting and interpretations," saysRuss Vaughn, senior vice president of data warehousing at Bank of America. "Oncesomething leaves our hands, we canÂft guarantee the validity of the information."

Eight Migration Options. Through interviews with dozens of organizationsthat have consolidated analytic silos, we have defined eight migrationstrategies. Some organizations only use one strategy; others adopt differentstrategies at various phases in the migration to a consolidated environment;and others are forced to switch strategies as business events change. The eightconsolidation strategies in a nutshell are:

Physical Strategies

1. Rehost. Move existing analytic structures onto a single platform.

Centralized Strategies

2. Start from Scratch. Build a new data warehouse instead of designating ormerging existing ones.
3. Designate and Evolve. Designate an existing data warehouse or mart as thecorporate standard and migrate other structures to it, either immediately or over time.
4. Backfill. Implement a staging area/warehouse behind
   existing data marts to consolidate extracts and data for marts to pull from.
5. Synchronize. Synchronize remote operational data stores from a central referencerepository.

Distributed Strategies

6. Conform Data Marts. Conform the data models of existing
   data marts by standardizing shared dimensions.
7. Create a Mart of Marts. Create an enterprise view across data marts by extracting data from them to create a new superset data mart.
8. Distributed Query. Create an enterprise view by querying multiple marts and reconciling results on the fly.

Some consolidation strategies are best suited for different organizationalstructures or strategies. (See Table 1.) For instance, the "start fromscratch" strategy is best used when two equally sized companies merge.The "designate and evolve" strategy is best used when a bigger companyacquires a smaller one. Rehosting is used by companies looking for quick costsavings, while synchronization is good for large companies with lots of operationalapplications that need to share the same reference data.

STRATEGY		Organizational Structure & Strategy	Speed to Deploy	Metadata Integration	Hardware Savings
1.	Rehost	Centralized - Fast focus on quick cost savings	None	High	High
2.	Start from Scratch	Centralized - Merger of equals	Slow	High	High - Eventually
3.	Designate & Evolve	Centralized - Merger of unequals	Medium	Moderate	High - Eventually
4.	Backfill	Decentralized - But central IT sells shared infrastructure	Nedium	Moderate	Extra costs for DW
5.	Synchronize	Centralized - Master data management	Slow	Moderate	Extra costs for hum
6.	Conformed Data Marts	Decentralized - But with enterprise view	Medium	Moderate	None
7.	Create a Mart of Marts	Decentralized - But top execs need single view	Fast	Minimal	Extra costs for data mart
8.	Distributed Query	Decentralized - Quick fix	Fast	Minimal	None

Table 1: Summary of characteristics of the eight consolidationstrategies.

The distributed strategies are good interim solutions while the organizationimplements a centralized strategy. However, the conformed mart strategy canalso be deployed in a centralized fashion if all the marts are logical schemawithin a single instance of a central database.

We also discovered a correlation between the level of metadata integrationrequired and the speed at which the strategy can be deployed. It's nosurprise that the strategies that are quickest to deploy-rehosting, martof marts, and distributed query-involve the lowest level of metadata integration.Conversely, those strategies requiring the highest levels of metadata integration-especially "startfrom scratch"-are the most time consuming to deploy, often takingseveral years to complete.

Ultimately, the approach an organization uses to migrate to an environment that delivers standardized information depends on many factors, including its culture, organizational structure, technicalexpertise, funding, and available time to accomplish the migration.

Conclusion

Today, organizations are plagued by the proliferation of analytic silos,which make it difficult to deliver consistent information across the enterprise.Organizations are now starting to consolidate these silos into an enterprisedata warehouse (EDW) to deliver a single version of truth and reduce overheadcosts.

A Long Way to Go. But organizations still have a long way to go. They'veconsolidated one-third of their analytic structures on average and have dozensmore. Since it takes between 4 and 10 months to consolidate analytic structures,these consolidation projects can last years.

The good news is that the ROI for these projects is impressive. Organizationsthat consolidate multiple analytic structures make $3.34 million on their investmentsin about two years. In addition, there are eight proven migration strategiesthat organizations can use as a guide. The migration approach an organizationuses depends on many factors, including its culture, organizational structure,technical expertise, funding, and available time to accomplish the migration.

Critical Success Factors. The key to a successful consolidationproject is to gain top management commitment, and put together a project plan,team, and tools that help you migrate incrementallyto your target environment. It's critical to assess your current environmentso you can prioritize efforts and gain momentum for the project.

"I try to keep it simple," says Vaughn of Bank of America. "Youneed to know what steps one, two, and three are but you can't focus on step two until you finish step one. I stay on a 60-daycommitment list."

This article originally appeared in the 11/1/2004 issue of TDWI.

Comments