SQLServerCentral Article

Problems In Building a Data Warehouse



I saw someone posting a thread in the data warehouse forum at SQLServerCentral.com that their upper management asked the SQL Server DBA of that company to build a data warehouse and they expected to see a prototype in a month. The DBA did not know anything about data warehouses; neither did any SQL developers in the company. None of them knew Analysis Services or how to build DTS packages. The DBA asked if anyone could suggest which front-end and back-end tool to use.

It was liked someone buying a car, but the person did not know how to drive or where they wanted to go. Apparently the management did not give any direction to the poor DBA and did not understand what it took to build a data warehouse. They thought buying a BI tool would solve all their problems. This is the common perception of most companies have when they are going to build a data warehouse.

The Biggest Problem in Building a Data Warehouse

Many companies that have data warehouses are using BI tools for ETL (Extraction, Transformation, and Loading) and reporting. They also have a data warehouse team containing a DBA, a data architect, a business analyst and SQL developers. However they also know the most important thing to build a data warehouse is not choosing a BI tool, it is identifying the data warehouse requirements.

Most companies have the misconception that building a data warehouse is ITs responsibility. The data warehouse is used to store business data that the business users need; the IT team does not know what the business users want. Business users understand the needs, direction, and strategy of the company and they are the ones who can define what should be in the data warehouse and how it will be used. Sometimes it is difficult for business users to identify what data they need, so they would just ask for everything. Of course the IT department will not give the users everything, so they select the data what they think the business users need and the data warehouse is built with limited usage.

The biggest obstacle is the business users and the IT department needs to communicate with them and determine what the requirements really are. In a company all the departments are inter-related, but somehow it is difficult for the business users to talk to the IT department. Then later on the business will complain they never get anything they want from the IT department. One of the business analyst's responsibilities is trying to gather information from the business users, understand what they need and then communicate that information to the IT department.

Another obstacle is that the same term can be interpreted differently for different departments in the organization. Terms liked 'total sales' for example, as defined by the Sales department that sales have been committed by their customers, while the Finance department may have defined it as only those sales that have been paid. While gathering information and requirements from the business users, the data warehouse project team needs to help the users to reach one definition of a term.

Rome was not built in one day and neither was the data warehouse. It is important to identify the priority of what information is important and critical to the business. This will help the IT department to build the data warehouse incrementally and effectively providing valuable data for the users.

Everyone agrees data quality is important, and poor data quality is not just the IT department's problem. The business users need to identify critical business rules and audit the data to make sure the data warehouse contains the right data. If problem occurs, the IT team needs to review the procedures; the data base design and analyze the data.


When a company decides to build a data warehouse, the IT developers may get excited about learning the new toolset and new technology while the business users may think building the data warehouse is not in their job description. They are too busy with their daily work. In reality, the IT department and the business departments are all responsible for project. Both groups must identify requirements and provide quality data that represents the organizational needs. They need to work together to build a successful data warehouse that will be used and help drive the strategic direction of the company.


4 (3)




4 (3)