Data Warehouse and Mart Consolidation to a Single Platform

With all the discussion of expanding information environments, it could be easy to think that consolidation efforts are no longer desired.  With expanding software federation capabilities, the continued historically relatively low cost of storage and the plethora of new projects, shouldn’t corporations be less concerned about consolidation and more concerned about casting caution to the wind and just “build, build, build” information projects?

Well, if you are in information management in a large corporation, you already know this is not the case.  Nor should it be.  Consolidation is almost synonymous with architecture these days.  Despite a decade’s worth of consistent consolidation projects and much more than that of data warehousing as a discipline, fragmented information environments continue to plague organizations.

The reason for the plague is two-fold.  First, there are true cost efficiencies to be gained by reducing redundant hardware, software and personnel cost.  Second, environments with multiple (for example) customer data stores, unless they are using an enterprise master data management approach, seldom represent that customer consistently.  Consolidation eliminates redundancy in the environment cost as well as bringing organizations to shared views, which streamline processes.  This also streamlines development by reducing build time associated with attaining the needed data.

Master data management (MDM) is a form of consolidation.  MDM provides common, enterprise data to subscribing systems, eliminating their need to create the data independently as well as the potential they would form that data differently and cause process challenges later.  However, MDM will not usually provide material cost savings from an infrastructure perspective due to the low quantity (but high quality) of information that it manages. 

For cost savings, there is true data warehousing.  Most companies are still on the journey to enterprise data warehousing.  Truthfully, that is all it is – a journey.  It’s a worthy journey, but nonetheless, a never—ending journey towards an enterprise single version of the truth.  Even in best-practice winning environments with strong sponsorship and corporate understanding, independent data marts pop up. 

Whether it’s data marts, data warehouses or a combination that need consolidation, the energy starts with creating an architecture vision.  The architecture vision is then plotted along timelines that include quarterly deliverables.  While the consolidation itself could be viewed as a project, it is seldom viewed as a vessel for funding.  Rather, business deliverables remain the focus of information management funding, while consolidation efforts, which generate longer-term ROI, seldom receive independent funding until a budget crisis or a major business opportunity is missed due to redundant data.  Regardless, funding will be necessary towards consolidation, which generates longer-term ROI and enables a scalable foundation for the future.  Consolidation projects require vision and none should be attempted without a shared vision with the business.

One approach that is not consolidation is to simply re-platform.  While replatforming is often advised, consolidation refers to the bringing together of former disparate systems.  Many replatforming efforts become consolidation efforts when the organization becomes more comfortable with the new platform and decides to take advantage of its new features, namely scalability.  Another type of project that is not only consolidation at the business intelligence layer, not the data layer, is a virtual form of consolidation, which enables federated query (across multiple systems).  These are much less costly projects and usually only cover up the real problems – disparate data, disparate data models and non-scalable data platforms.  Turning to consolidation, there are two approaches.

One is often an outgrowth of replatforming and that is moving multiple databases onto a common platform.  That platform may or may not exist prior to the consolidation.  It is not unusual (I’ve done it many times) to pick an existing platform/system that is either or both of: a good platform for the future and having much of the interesting data for the consolidated system. 

A second form of consolidation is rearchitecting.  Rearchitecting is the process of merging database designs and therefore the data acquisition strategy for the data as well. Rearchitecting may involve picking the best model components from various models and/or it may involve more zero-based approaches, starting from scratch, that use requirements as the basis for the new model.

There are several patterns or methodologies that emerge in successful consolidations.  Successful consolidation efforts, unlike data warehouse journeys, do complete and the result is cost efficiencies, internal efficiencies and system efficiencies. 

In environments without a true standout data warehouse, a new data warehouse can be built which assumes the functionality and data of the non-integrated data marts that exist.  Often, this is the case when operational databases have played the inappropriate roles of analytics and reporting.  Snippets of existing models can be reused for the data warehouse, but often new data modeling must occur. 

Other environments, when there are similar subject areas modeled in different places, will pick a winner among the existing data marts and warehouses and begin to move other marts and warehouses over. This happens often when the data mart layer has got out of control and marts have become the assumed need for all new efforts.  A form of this is when cube growth is overrunning the environment, and the budget.  This often means the go-forward platform must be expanded and usually means modeling additions to accommodate the new data.

Regardless as to whether the data warehouse is a new or an existing platform, and regardless of whether the consolidation is of 2 large warehouses or 50 small data marts, some elements of the approach is necessary for success in my experience:

• Minimize the data outages to the users and the operational systems impact
• Make sure the platform is scalable to handle the expanded usage
• Expect and plan for cultural resistance
• Get that top-down, executive level support
• Fix real, measurable problems – the consolidation has its own ROI
• Starve the pre-consolidated marts of attention and resources
• Take some time to rearchitect; have a sound data model