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 of analytic data in large organizations is fragmented. Despite their best intentions, CIOs are struggling to deliver consistent data that provides a single view across the enterprise. CIOs who seek this so-called single version of the truth must feel like they are playing an endless game of  Whack-a-Mole every time they stamp out a renegade analytic silo, another pops up elsewhere.

Consequences of Proliferation. The problems that result from this rampant proliferation are two-fold. First, executives become frustrated because they can't get the data they need to assess the performance of their company. They get apoplectic when managers spend more time in meetings arguing about whose data is right rather than developing 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 structures are wasteful. When deployed as physically distinct systems with separate hardware, storage, software licenses, data feeds, and staff, these systems can drive up data warehousing costs by 30 to 50 percent, according to some data warehousing managers. Consolidating these structures can save organizations millions of dollars a year and deliver a quick return on investment (ROI).

Remedies for Proliferation

The Disease Is the Cure. Ironically, the cure for the proliferation of analytic silos is to create another analytic structure. To stamp out analytic silos, organizations need to implement an enterprise data warehouse (EDW) that provides sustenance for all past, present, and future analytical structures. The key is to plant the EDW deeply enough and broadly enough within the information architecture and corporate culture that it becomes the de facto analytic structure within the organization.

The EDW only works, however, if the organization comes together to hammer out definitions and rules for commonly used terms and calculations, such as net margin or sale or profit. Standardizing the meaning of shared data elements often referred to as metadata is often more challenging than consolidating the actual physical structures. Once this exercise is completed, the EDW becomes the repository for shared data, rules, definitions, and other metadata used by multiple analytic applications.

Trends in Analytic Consolidation

A Big Job. Consolidating duplicate data and redundant analytic structures is not for the faint of heart. That's because most organizations have dozens of analytic structures that they would like to consolidate. As mentioned above, organizations have an average of two data warehouses, six independent data marts, 4.5 ODSs, and28.5 spreadmarts left to consolidate. To date, they've only consolidated about one-third of all the structures in their environment. But acquisitions, 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 of data 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 is safe to say that the smaller the structure, the more likely it is to proliferate and the harder it is to consolidate.

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

Project Duration

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

Illustration 2. Data warehouses take the longest time to consolidate, approximately 9.75 months. Based on 209 respondents who have completed a consolidation project.

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

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's not the driver. Although we expect to reduce costs by 30 percent or more, what is really driving this project is the need to comply with the Basel Accord and improve the bank's risk analysis 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 warehouses or migrate them to a new environment. Similarly, less than one-third calculate the ROI of migrating analytic structures to a new environment.

Average Costs and Projected ROI. However, TDWI research shows that consolidation projects 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 migrate it. It costs $1.597 million a year to maintain a data warehouse and $1.171 million to migrate it. (See illustration 3.)

Illustration 3. Data warehouses cost more to support and migrate to a consolidated environment than independent data marts. Based on 150 respondents who have already consolidated analytic structures.

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

Illustration 4. The average ROI for consolidation projects is $3.34 million. Based on 150 respondents.

The most promising candidates for consolidation are data marts that run on different platforms and are managed by separate IT staffs. Consolidating these independent data marts can save companies more money than those that run 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 a standardized architecture. Generally, once an organization has lived through the 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 same source of information and getting consistent reporting and interpretations," says Russ Vaughn, senior vice president of data warehousing at Bank of America. "Once something leaves our hands, we can't guarantee the validity of the information."

Eight Migration Options. Through interviews with dozens of organizations that have consolidated analytic silos, we have defined eight migration strategies. Some organizations only use one strategy; others adopt different strategies at various phases in the migration to a consolidated environment; and others are forced to switch strategies as business events change. The eight consolidation 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 or merging existing ones.
3. Designate and Evolve. Designate an existing data warehouse or mart as the corporate 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 reference repository.

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 organizational structures or strategies. (See Table 1.) For instance, the "start from scratch" strategy is best used when two equally sized companies merge. The "designate and evolve" strategy is best used when a bigger company acquires a smaller one. Rehosting is used by companies looking for quick cost savings, while synchronization is good for large companies with lots of operational applications 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	Medium	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 consolidation strategies.

The distributed strategies are good interim solutions while the organization implements a centralized strategy. However, the conformed mart strategy can also be deployed in a centralized fashion if all the marts are logical schema within a single instance of a central database.

We also discovered a correlation between the level of metadata integration required and the speed at which the strategy can be deployed. It's no surprise that the strategies that are quickest to deploy-rehosting, mart of marts, and distributed query-involve the lowest level of metadata integration. Conversely, those strategies requiring the highest levels of metadata integration-especially "start from scratch"-are the most time consuming to deploy, often taking several 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, technical expertise, 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 enterprise data warehouse (EDW) to deliver a single version of truth and reduce overhead costs.

A Long Way to Go. But organizations still have a long way to go. They've consolidated one-third of their analytic structures on average and have dozens more. 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. Organizations that consolidate multiple analytic structures make $3.34 million on their investments in about two years. In addition, there are eight proven migration strategies that organizations can use as a guide. The migration approach an organization uses 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 consolidation project is to gain top management commitment, and put together a project plan, team, and tools that help you migrate incrementally to your target environment. It's critical to assess your current environment so you can prioritize efforts and gain momentum for the project.

"I try to keep it simple," says Vaughn of Bank of America. "You need 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-day commitment list."

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

Comments