Dimensional Modelling

Dimensional modelling , a very important technique for designing data marts. It makes use of two types of tables- facts and dimensions. A fact table consists of “measures of a subject that is modelled for analysis” says Kimball. The fact tables are usually thin and long. Dimensions carry the descriptive attributes related to the subject mentioned in the fact table. The dimension tables are fat and short.

This kind of modelling makes the database very simple and understandable. Dimensional databases can be conceived as a database cube of three or four dimensions where anyone can access a slice of the database along anyone of its dimensions.

Example:

Considering a simple business which sells products in different locations and one would like to evaluate the performance of sales in a particular location over a period of time , it becomes very handy when you have modelled your database in a “cube” format. This would have time, products and location as the dimensions. The intersections of the cube will correspond to a particular measure describing a combination of product, location and time.

There are two types of dimensional model:

Star schema :  The name describes the structure of the model because it looks like a star with one central table around which a set of other tables are displayed. The central table is the fact and the surrounding tables are the dimensions.

Snowflakes schema : It is similar to start schema except for the fact that the dimensions are normalized into multiple related tables. The dimension tables are elaborate, having multiple levels of relationships and the child table has multiple parent tables.