Executive Summary:
When designing a data warehouse, it’s important to consider the business and design factors associated with the project, first. This article walks you through the business considerations and data warehouse design best practices you should keep in mind when designing and creating a SQL Server data warehouse. And don’t forget to get buy-in from both the top-level executives and the business users—the last thing you want to do is build a data warehouse that doesn’t meet your users’ needs.
|
It’s often said that the best way to learn is from your mistakes, but mistakes made in a data warehouse/business intelligence (BI) environment tend to be very expensive and possibly career-killers. Building a data warehouse isn’t a simple task; it takes a village to build one out of which you can extract viable information.
A data warehouse and the operations that build and maintain it are a combination of business considerations and design best practices. It can be a huge challenge to balance these requirements and still be able to deliver valuable content to users at the end of the day. Make no mistake—not everyone is suited to work in a data warehouse environment, but the work is stimulating and the rewards are satisfying. Here are some of the lessons I’ve learned from designing data warehouses and BI environments.
Get Buy-in from Business and Technical Groups
When designing a data warehouse, business alignment must come first because if the product that’s delivered at the end of the day doesn’t reflect the goals and purposes of the enterprise or meet users’ needs, then the data warehouse is a failure. To give your data warehouse/BI project any chance of success you need to get buy-in from the business and technical sides of your organization, from the top-level executives to the front-line worker bees. Don’t believe that “if you build it they will come.” An IT-driven, IT-sponsored, IT-centric data warehouse/BI project is leaving out the most important facet of a BI solution—alignment to business needs and requirements. Involving business decision makers throughout the project ensures that the data warehouse will deliver meaningful, useful information to business users.
At the beginning of your data warehouse project, establish a steering committee that’s composed of both business decision makers and technical decision makers, and consider establishing a BI competency group that reports to a non-technical C-level manager, such as the COO or CFO. Use these people as a resource throughout the lifetime of the project and even once the system is in production.
Maintain Data Integrity
When designing your data warehouse environment, it’s important to minimize data replication in the table design (by using conformed dimensions, for example) and data inconsistency by vetting source data and adhering to Master Data Management (MDM) standards. (For more information about MDM, see "Master Data Management," January 2007, InstantDoc ID 94193, or "Master Data Management Challenges," www.sqlmag.com, InstantDoc ID 97881.)
Poor data quality is one of the biggest hurdles when it comes to data warehouse adoption rates. You can do the following to mitigate poor data quality:
- Identify which business units own specific data and are responsible for its quality.
- Appoint a data steward in each business unit and include “data quality” in the job description.
- Make sure your extraction, transformation, and loading (ETL) routines are scouring for data quality problems and either correcting the data before it’s loaded into the warehouse or blocking it entirely.
- Audit and verify data as necessary by setting up back-end routines that periodically reconcile warehouse data with source data, taking into account any inaccuracies that are inherent to the source data store.
- Determine what level of data quality is considered to be “good enough” by involving the business units and finding out what their data analysis needs are. A business unit that’s using only highly summarized data might not need data to be cleansed to the same level as a unit that’s drilling down into the detail level.
Provide User-Friendly Interfaces
Strive for user-friendly interfaces because a data warehouse without users is a failure, and a BI system that’s too complicated to learn and incorporate into everyday use is a disaster. Providing highly summarized visual information to the business decision makers is one of the things that every data warehouse/BI system must be able to do. Dashboards, which are highly visual real-time reflections of operational activities, and balanced scorecards, which are non-real-time reports that blend the operational, marketing, developmental, and financial aspects of an organization, are both highly summarized and symbolic of a data warehouse. However, without an overarching strategy, dashboards and balanced scorecards can easily become flashy facades for silo-specific “islands of influence” that aren’t connected to enterprise objectives.