Organizations are now dealing with the challenges of working with multiple versions of their data warehouse/business intelligence (DW/BI) environment, continuing to use previous versions while building out their new environment.

They have been through generations of their data warehouse program with multiple architectures, including multiple schemas and databases. Some of these environments may not have been fully implemented, utilized, or well documented but became critical to the business community.

What should you do when your organization begins the journey to build and implement a redesigned DW/BI environment? Can the previous environment live in harmony with these new environments or should it be scheduled for imminent retirement?

There will be new tables for the new DW/BI environment with possibly new levels of detail and different aggregates. There may be different data sources. What should the business users do? How do they utilize both the previous and new DW/BI environments—or should they? What approach should you take to help your user community down a temporary but nonetheless challenging road?

The previous DW/BI environment often has more subjects populated utilizing more sources than the new environment will have when the first release is implemented. There may be hundreds of legacy reports and queries users have built and depend on that provide insights into their business. The users have learned the pieces of data that are questionable and know the work-arounds or data plugs they need to complete before distributing these reports.

When implemented, the new DW/BI environment will have more complete data. It will deliver more snapshots and historical data. It will also provide a complete data dictionary with business definitions and examples of each piece of data, along with better audit controls not part of the previous environment.

There are numerous options for organizations to take, but all require analysis and discussion with business users to develop a road map for implementing the new environment quickly and accurately. Here are the three steps you can take to build that road map.

Step 1. Evaluate both DW/BI environments

A good starting point is to analyze the DW/BI environments by understanding and comparing the differences in architecture. Create a subject and source map for both environments that shows the type of data in each and makes the differences in available data clearly visible.

Include the detail and summary levels and how data is aggregated, the triggers for creating new records, and if and how changes and deletions are identified, captured, and processed in these environments.

This analysis will help determine if tables from the previous and new DW/BI environments can be joined together accurately or if a set of bridge tables can be built to help merge data from both environments. This should be a temporary measure used for a short period of time until the new DW/BI environment has the same subjects and sources as the previous one, fulfilling the needs of the business community. It may be decided that the two environments are so different they need to remain separate.

Step 2. Evaluate existing reports and queries

Understand how your previous DW/BI environment is being used by your business community. Start by building an inventory of reports that are currently used in the previous environment, providing information about the purpose, responsible user, distribution, frequency, main subject, and priority or ranking of the report. It is helpful to understand the lineage of data from the source through the tables into the query tool, reports, and queries.

Through this report analysis, many of the reports and queries may be marked for retirement. Some may be removed because they duplicate other reports; some can be modified slightly by adding parameters to the report to provide more flexibility; and some may be incorrect and should not be used by the community because of data issues.

This analysis will help identify the minimum requirements of the business community and help the DW/BI team understand what needs to occur next in the DW/BI road map.

Step 3. Build the road map

Finally, plan and build the road map with the right steps to get the new DW/BI environment into production and make it a useful tool for the business community. The plan needs to clearly delineate which environment to use for specific needs. It may include building bridge tables to enable some users to use tables or sets of tables from both environments or, perhaps, building a temporary user interface that accesses both environments accurately and when appropriate. It may entail rolling out this new environment for some users and not for others based on the type of reports and queries each group needs. Make this period of transition user friendly and easy to understand.

The plan should include the next subjects and capabilities the new DW/BI environment will provide, along with a planned schedule to help the users understand how it will be built out and when they will need to rebuild reports. The DW/BI project team may need to consider helping the users redesign and create their reports in the new environment, so together they can create as much reusability as possible. The plan should also encompass a time frame for retirement of the previous DW/BI environment (e.g., by sets of tables, subjects, or sources) along with corresponding reports and queries.

Summary

It’s time to build out your road map—your plan and schedule—by defining the way to help the business community get the reports they need from the previous DW/BI environment and by defining the way your enterprise moves to the new environment. As your business community begins to see the advantages of your new environment, they will want to speed down that road even faster.

To ensure your new DW/BI environment is a success, work with your business community to get down that bumpy road as smoothly and quickly as possible. The journey will be worth it.

Patty Haines is founder of Chimney Rock Information Solutions, a company specializing in data warehousing and data quality.

TDWI Onsite Education: Let TDWI Onsite Education partner with you on this journey! TDWI Onsite helps you develop the skills to build the right foundation with the essentials that are fundamental to BI success. We bring the training directly to you—our instructors travel to your location and train your team. Explore the listing of TDWI Onsite courses and start building your foundation today.