Dimensional data modeling is used for offline analysis of data and is associated with data warehousing. There are two elements to real world modeling.

The level of abstraction in case of real time transactions which is also called on-line transaction processing is very high and granularity low. On the other hand, data warehousing demands a higher level of granularity which is required for extracting intelligence from a database. This is called dimensional data modeling. In this model, the information is extracted from database offline. For example, a sales transaction may have many dimensions. Sales data, price, quantity and discount are different aspects which may be captured by a dimensional data model.

The same data is stored differently for on-line transaction processing and for data warehousing purposes. Why should the same data be represented in different forms? It should be appreciated that in any information processing system we are dealing with different levels of abstraction. Data extraction and business intelligence require a higher level of information procession. This would generally not be possible in real time.

As regards the actual implementation of dimensional data model, it consists of fact tables and lookup tables. Fact tables connect to lookup tables. There may be many fact tables which are not related to each other. Each of these fact tables would have their respective lookup or attribute tables.

There are two ways in which a dimensional data model is represented. The star schema has a fact table at the center of the star with the attribute or lookup tables forming the different arms of the star. There can be many fact tables and associated lookup tables in a single dimensional data model.

The other implementation is called the snow flakes schema. One can imagine this to be a snow flake with each star point breaking up into numerous stars. This results in lower memory requirements and also improves granularity.

Dimensional data models are important for extracting business intelligence from databases. It forms an important concept in data warehousing. Its applications are many and particularly in sales and marketing. Tracking consumer preferences and providing useful insights into their behavior is the ultimate aim of creating dimensional data model. Implementation can vary depending upon the quality of granularity or detail which may be required from a particular business transaction. Essentially it is an offline model which compliments the on-line transaction processing model which is used for real time or on-line transactions.

By Odalys E. Anton, VP Web/Application Development

GreenCode Technologies, Inc.

(954) 840-8068