What are the Different Types of Data Warehouse Systems?

There are two main data warehouse systems; normalized and dimensional. In a normalized structure, data is limited to a simple presentation of factual information. There is no context or background to the data beyond what the user is willing to correlate. In a dimensional system, the information comes in a context of other facts that show what the data is as a whole. In this case, there is a wealth of information available, whether you want it or not.

The two main data warehouse systems are the two extremes. In most data warehouses, a middle ground is reached between these two. The actual descriptions are for the purest form of the style, even though that is rarely encountered.

Normalized data is the easier of the two data warehouse systems to implement and manipulate. In this style, information is whittled down to individual facts without any connections to other data. For instance, a product’s serial number and the product’s name are put together with no additional information. The information is available to any user that may want it, but they have to do the work to make it mean anything.

In order to make sense of information in normalized data warehouse systems, the user collects connected information to string together an entire picture. In order to find a client’s phone number, the information above may be connected with a serial number and an account number of the person that purchased the item. Then the account number and name could be located. Finally, the name and phone number are found. Each of these steps is a separate database query put forth by the user to collect information.

Dimensional data is the exact opposite. In general, these data warehouse systems are the easiest for humans to use, but the most difficult to change or manipulate. When information is collected, everything is combined into one big ball of data. Instead of a serial and product number, an entire purchasing invoice would go in all at the same time.

If a user were to look for a phone number in a dimensional database, the process would be different. The serial number would yield an entire history for that client, names and dates of everything ever purchased and any service calls or returns. In addition, every address and phone number the client has ever used would be directly available as well. The picture is very complete, but perhaps so complete that the information required is difficult to find.