The Business-Accessible Data Warehouse

by Michael Schmitz

November 2004

This article introduces a new data warehouse architecture that focuses on business accessibility to information and utilizes a newer, more efficient and flexible data architecture. We will discuss the need for a new architecture and the implications of ‘business driven’ and ‘business accessible’.

The need for a new architecture

Why is it time for a new direction in data warehousing? A data warehouse is no longer a non-volatile, historical repository of difficult-to-access detail data with business access primarily limited to summarized data distributed via data marts. A data warehouse is now often near-real-time and always updated with dimensional change, thus providing the context of transaction or event history and should facilitate business access to both detail and summarized data through the same tool and data structures. With the information explosion, companies cannot afford to store data in multiple formats or process the same data multiple times. Figure 1 depicts the older architecture with its multiple repositories and ETL(extract, transformation, and load) functions.

Figure 1: Older data warehouse architecture

Business access to data in this architecture only takes place at the far right, and the same data is stored in multiple repositories in different structures. To access detail data the business person must drill down from the data marts to the warehouse and must deal with different (often more complex) data structures. This approach is outdated, inefficient, takes too long to deliver value to the business, and costs way too much in development time and processing costs. Keeping volumes of data in multiple structures and distributing it between them takes time and money. There needs to be a single data repository that stores data in a fashion to support business access while providing flexibility and maintainability.

Magic? No, actually very simple. Construct a single-image database, encompassing detail, analytical, and summary structures transparent to the user or tool. Store the largest volumes normalized and the smaller volumes with their hierarchies de-normalized. This provides maintainability and flexibility while still rendering the data structures accessible to the business person and the database optimizers, providing high performance at a low cost. Figure 2 depicts the business-accessible data warehouse architecture.

Figure 2: Business-Accessible Data Warehouse Architecture

The emphasis here is on the single-image data store that contains detail, analytical, and summary data, all accessible transparently to the business user through their tool of choice.

It is critical, even in the implementation of this new architecture, that your efforts are business driven.

Business Driven

Business-driven data warehousing implies that business initiatives drive the building of the data warehouse rather than IT initiatives. It is trite to bring up the fallacy of the old concept of ‘build it and they will come’, but it still occurs much too often. One problem with this approach is that the architectural decisions are often made from a “how to build it” perspective rather than a “how to use it” one. I encounter countless clients where IT’s objections to making the data warehouse more usable by the business people is that they either don’t have the time to build in this usability or that the processing to accomplish this will take too long. The cold, hard truth is that if this is the case, it shouldn’t be built at all. The good news is that this doesn’t have to be the case. By using proven methods and templates and a newer, more efficient architecture, you have the time to build it right and the processing can be accomplished efficiently, probably faster than the entire process took before.

Business driven also implies that the data warehouse’s primary design goal is business accessibility. Business accessibility does not mean that we can forget about data validity, flexibility, and maintainability, but that we must provide all of this while at the same time providing business accessibility.

Business Accessibility

Business accessibility implies several things. It implies that information in the warehouse must be stored such a way that it is directly accessible by business people using their tools of choice and that access does not require specialized IT skills. The data structures must be legible to and understood by business people and reflect the business. Database objects must be named according to business terminology and defined in business terms so that no ambiguity exists. Accessibility also implies that response times to requests are rapid and predictable.

Another face of accessibility is time to delivery. Obviously, if Information has not been implemented in the data warehouse, it is not accessible. Most data warehousing efforts have a serious backlog of important corporate information that, if accessible, in the data warehouse, could help make business decisions that aid the corporate bottom line. It is my contention that the use of older methods and inefficient architecture results in much slower times to delivery than what can be accomplished using a new, streamlined data architecture.

Achieving business accessibility is not necessarily easy. Achieving simplicity is a fine art that takes concentrated effort. This is one reason why it is so important to have the data warehouse built upon a solid architectural foundation.

In conclusion the new architecture focuses on business accessibility to information and how to provide it efficiently and cost effectively. The information explosion has buried most businesses under tons of detailed information that is either not accessible or very expensive to provide access to it. The answer to the problems of providing business access to large volumes of data lies not in a silver bullet, but in taking what we have learned over the past 17 years of data warehousing and applying it intelligently to the problem. This is what we call the Business Accessible Data Warehouse Architecture.