My Background in Data Warehousing
I have been involved in the creation, development, and maintenance of seven data warehouses through the years- one of them before I even knew about the term "data warehousing"! I have built them with SAS and Oracle, SAS alone, Informatica and Oracle, Oracle alone, and SQL Server.
I have also visited many companies as an adviser, consultant and user of their data warehouse. In these many visits, I have seen some successes and many failures. Often, the failures could have been prevented with some key guiding principles.
Data Warehousing or Enterprise Data Integration?
Data warehousing is now known by a new buzz word, Enterprise Data Integration. In fact, SAS recently renamed SAS ETL Studio as SAS Data Integration Studio (they also added some new features around the EDI area, one new feature was around continual data acquisition so that near real time data feeds are available in the data warehouse.) Another great part of SAS EDI is SAS Data Quality, this should be a consideration throughout the entire process, but I won't directly comment about data quality in this post. Since most people still use the term data warehousing, so I will keep the popular terminology over the analysts and even SAS.
What Does it Take to Build a Successful Data Warehouse?
There are many factors required to bring success to a data warehousing effort: quality data warehousing tools, the right storage system for the data, qualified technical people, business support, and a strong vision rooted in current and future business needs. However, I have rarely seen data warehouses fail due to a lack of tools or competent technical teams. Instead, poor strategy and management are the typical landmines disabling many data warehouse initiatives.
100% of the Strategic Vision, 80% Tactical Commitment, Just 20% of the Work!
My work with many companies has yielded several keys to a successful data warehouse effort:
- Long-term vision- intense collaboration between the technical architect, the sponsors and the key super users (lead analysts, statisticians, BI team) of the data warehouse is critical to paint a long-term vision of data warehouse scope, purpose and long-term analytic and reporting needs.This step is absolutely vital to long-term success as it paints a road-map for the technical team to consider as they build the key components of the data warehouse. Without this work, you may have some early wins only to find that key long-term needs weren't considered in the design of the data model or in the extraction, transformation, and loading jobs developed by the team. Data warehouses that fail due to a lack of a long-term road map (100% Strategic Vision) are usually easy to identify- they result in a "scrap and rebuild" effort that is disruptive, disheartening and a huge waste of effort and money.Finally, the Strategic Vision road map must be in a form digestible, understandable, and agreeable to the technical and super user community. You don't want reams of technical language nor a document so light on technical details that it could read like an advertisement. I know this isn't easy, but it will make your life so much easier all around for data warehousing projects!
- A short-term plan for development success (80% of the Tactical Commitment)- once the Strategic Vision is developed is key to delivery of tangible results that inform the technical team rather quickly on the many issues that always pop up when designing a data warehouse. If your architect plans wisely, you can often balance critical business needs from the Strategic Vision balanced with "easy wins" from the overall roadmap and break up 80% of the long-term project into 3-4 major iterations.Even when you have delivered these iterations, there will still be 20% of the road map unfulfilled, but 80% delivered! Of greater importance, you will have repeatedly delivered success in smaller chunks in moderate time frames (typically 3-9 months each) -and- your team and the super users will have valuable access to the data and will see critical areas needing refinement in each iteration and with the overall Long-Term Vision! The Long-Term Vision will change over time, this is healthy! Iterations of delivery should not change very much once development has started except to correct clear oversights in development or user requirements.
- Okay, 20% of the work sounds a bit unrealistic, but it is a good maxim to follow in data warehousing- work in smaller "bites" to help refine your data model, ETL work and to allow the user community the opportunity to enjoy your success and offer constructive feedback on their needs.Breaking up the work so that easy and critical pieces are in each phase is an ideal, but it is even more important is to ensure that you don't extend any development iteration to more than 6 or 9 months. This is absolutely vital to maintain user and executive support. It is also a big positive for your technical team's confidence to see their work playing the vital role of helping the business and creating buzz and excitement!
While I don't claim this to be an exhaustive set of keys to success, I can claim it is a set of principles that I have seen in every data warehouse success I have been involved in. Best of luck!