8. Data Warehouse Architecture

Data warehouses are almost universally designed as multidimensional databases, or, in relational database terms, as star schemas. The problem with both of these terms is that they are overly technical terms to the targeted users of business intelligence. This is unfortunate because the underlying concepts that they represent are relatively easy to understand for the technically naïve and very empowering for those willing to grasp their essence.

In an attempt to make the architecture of the standard data warehouse comprehensible to a wider audience, I submit the following:

1. A data warehouse is not really a multidimensional structure in any true geometric sense. Rather, it is simply a collection of facts wherein each fact is associated with a set of objects that related to it. True, it can be implemented as an array of many dimensions, but more generally, it is just a collection of facts and their material associations.

2. The star schema is a pattern of database design that has a core table of “facts,” or events that are historically recorded with their relations to business objects. Each category of business object (i.e. customer, product, business unit) is stored in a separate table that is connected to the fact table and is referred to as a dimension. The dimensional tables have no geometric significance, but rather, they represent collections of objects to which the facts relate to. Although a star schema can be thought of as a dimensional space, with each category of objects representing a dimension, it can also be simply thought of as a representation of historical events with each event being defined by a set of factors, each of which is represented by one of the dimensional tables.

3. Per the previous two observations, although the star schema is represented diagrammatically as a star-like structure of tables, with the fact table in the center surrounded by tables that represent the factors (dimensions) which define the facts, these same diagrams can be represented, with equal accuracy, as a table that is simply related to a set of factors. Above are two alternative diagrams for the same data warehouse. The implementation of the data warehouse remains the same, only the geometry of the graphic has changed. As shown in the above diagram, a star schema can be viewed as a table of facts and a set of tables that are used to factor the facts.

5. Virtual Data and BI

Knowledge is one of the scarcest of all resources.

Thomas Sowell

Virtual data is the reason why the star schema architecture of the BI data warehouse is such a powerful means of producing new information. Virtual data is the information that is produced on demand rather than stored in a database (see previous post). As the power of the computer creates a greater demand for virtual data, the star schema will become a greater factor in maintaining a company’s competitive advantage.

The star schema data warehouse stores primitive data, representing individual events or transactions, in a form that relates those events to the real-world objects that are they are related to. Each dimension of the star schema’s “multidimensional” architecture represents one of these real-world objects. Customers, business units, dates, and financial accounts are examples of typical dimensions stored in an effective data warehouse.

The many dimensions of a company’s data warehouse allow the user to summarize the events of the company’s history by the real-world objects that the company is related to. The company’s revenue can be summarized by business unit, customer type, product, geographical location, or any other factor that the company has deemed relevant to its analysis.

The factors (real-world objects) can be combined and correlated and the number of combinations and correlations are nearly infinite in number based upon the arithmetic of combinations (a later post).

As the typical twenty-first century business becomes increasingly information-based, its data warehouse will become a more critical factor in its ability to generate new information. The data warehouse will be able to produce more information because information will become increasingly virtual, produced on demand from the primitive atoms that represent the grist for the company’s analytical mill.

See Banking the Past.

4. Virtual Data

The smallest operations can now afford financial control programs that account
for their finances with greater speed and sophistication that even the largest
corporations could have achieved through their production hierarchies a few
decades ago.
James Dale Davidson and Lord William Rees-Mogg

Business intelligence will become increasingly based upon “virtual data” as we proceed into the twenty-first century. Virtual data is the information that is produced by a computer from more primitive data stored in the computers’ databases.

Examples of powerful information that will soon be virtual data are the monetary amounts that move and shake our financial markets. Earnings, income, and liquidity are the critical numbers that give us a measure of the success and economic viability of a company. These numbers, typically represented as a few data points for each quarter of a year of business, are really the sums of millions of individual transactions that the company has incurred during that time period. They have been traditionally computed and stored each quarter and become the primary financial data of the company as the records of the transactions themselves are relegated to the information background.

However, these pieces of summary data will become virtual, or produced on demand, rather than primary data, for the simple reason that the production and reproduction of the data is extremely cheap and accurate with the advent of the modern computer.

The product of arithmetic operations, particularly where large volumes of data are concerned, has historically been turned into stored data because of the cost involved in manually performing these operations. However, the arithmetic that humans have produced laboriously and erratically can now be performed perfectly and effortlessly by a computer.

The inexpensiveness of performing arithmetic on a computer is the essential factor in determining when information becomes virtual as opposed to being kept as stored data. It is just a matter of fundamental economics – if it costs nothing to reproduce the data, it has no value as stored information and can just as well be reproduced on demand.

This virtualization of critical data is the key behind the increasing power of business intelligence and the star schema architecture that defines a data warehouse. The arithmetic sums that are produced by an OLAP data warehouse are produced on the fly from underlying primitive information (typically, atomic financial transactions). By “virtualizing” these arithmetic sums from the underlying atomic data, we are able to use the same underlying atomic data to produce other arithmetic sums. This is basically how we use a data warehouse: we slice the data along combinations of dimensions to produce, for example, not only earnings, but earnings by business unit, store type, or customer demographics.

We are now in the era of the ubiquitous computation engine and the cost of performing computations is approaching zero, minimizing the need to store and save the result and maximizing the value of primitive atomic data. See Banking the Past.