This type of decision support environment serves every business unit in the organization; it is not as easy as building a standalone system for one individual user or one department. It also cannot be built in one big bang - it must evolve and grow over time. Some of the typical data warehousing questions are depicted in Figures 1 and 2.
Data warehouse projects are time-consuming for various reasons:
Changes to scope. This can include staff, budget, technology, business representatives and sponsors, all of which can severely impact the success of the project. “Scope creep” can be one of the largest roadblocks in completing a long-term project. At the outset, certain requirements need to be established, defining the information that needs to be provided. After a week or so, more requests tend to pop up: “Would you please do this too? I really need this information!” The requirements keep expanding, and the scope of the project keeps stretching, resulting in scope creep (see Figure 3). Data warehouse projects are very prone to scope creep because these projects try to provide data to many users from numerous departments within the organization.
Data quality. Source data quality can be one of the biggest challenges for all data warehouse projects. Before the data warehouse initiative, data was limited to the view of one line of business, and the data was never reconciled with other views in the organization. As a result, data integration and data cleansing steps require a significant percentage of time out of the project schedule.
Extract, transform and load design. ETL is the most complicated process of a data warehouse project. The extract stage requires extracting data from the source; the transform stage requires transforming data from its original format to fit into the format to be stored in the data warehouse; and the load stage is when you actually load the transformed data into the data warehouse.
ETL processing time frames, or data staging windows, are typically small. Because the source data is often of poor quality, a lot of time is required to run the transformation and cleansing programs. Finishing the ETL process within a designated time frame can pose a challenge to most organizations. In general, the time requirement necessary to build a data warehouse is the biggest problem for any organization. And one of the big reasons is the long and winding ETL process (see Figures 4, 5 and 6).
The Difficulty of Meeting User Demands
Two other factors that bog down and complicate data warehouse projects are data integration and data presentation. The users in a business are a diverse group with diverse needs. As a result, the data they want comes from many disparate sources, such as order entry, sales, marketing, human resources and finance, as well as from various electronic documents, printed documents or multimedia. (I’ll address unstructured data sources in a future column.)
Business users want up-to-date information from their online transaction processing systems. Figure 7 illustrates the various systems from which organizations retrieve data. All of these disparate data sources have to be integrated.
Determining user needs can be ambiguous. Often, users don’t know what they want in advance - only after they see it do they recognize the need. What’s more, user requirements for information delivery tend to be dictated by whatever technology they are using at home or on the job, and as technology changes, the tools change. These days, users engage with user-friendly websites when ordering merchandise online, banking online or even ordering food online. As a result, they expect the technology at their jobs to be equally accessible. This means the solutions have to be intuitive and should require no training. After all, no user training is required for consumers to order books from Amazon, clothes from Land’s End or food from FreshDirect. Why should there be a need for training at work? Indeed, if too many bells and whistles are added to the tools, they become too complex and inaccessible.
