Make your own free website on
Venky's World
Facts & Dimensions
About Me
Favorite Links
Contact Me
Why I Started This?
Welcome to the DW World
How to Start Isolating data
Grain is the Grail
Facts & Dimensions
Oh Yeah, Its ETL Time
SCD, is this Table/Column dependent
Handling Error Loads

Facts & Dimensions

 Facts & Dimensions form the heart of a data warehouse. Facts are the metrics that business users would use for making business decisions. Generally, facts are mere numbers. The facts cannot be used without their dimensions. Dimensions are those attributes that qualify facts. They give structure to the facts. Dimensions give different views of the facts. In our example of employee expenses, the employee expense forms a fact. The Dimensions like department, employee, and location qualify it. This was mentioned so as to give an idea of what facts are. Facts are like skeletons of a body. Skin forms the dimensions. The dimensions give structure to the facts. 
            The design of a DW depends on identifying facts & dimensions. Of course, this must be done only after identifying the granularity. Generally, the relation between the facts & dimensions is many to one. Facts form a part of a table called Fact table. Dimensions form a part of Dimension table. Fact table keeps on growing in size. The growth of Dimensions is generally slower. This can be understood as from the previous example. The employee expenses would keep on occurring each & every day/month. All these have to be tracked. But the department tables would grow very slowly. This is because the Department dimension will grow only when a new department is added. This might happen only once or twice in a year. Since the fact tables keep on growing in size very rapidly, it is desires that the data redundancy is reduced to the max. Hence the fact tables are normalized to the maximum extent. Whereas the Dimension tables are de-normalized since their growth would be very less. 
         The facts & Dimension tables are linked by means of key called surrogate keys. Each fact table would have a column surrogate key that would have a corresponding key in the dimension tables. The Surrogate keys are used since they are easier to use. We could have used the business keys itself, but the problem in using that is a person using the data would not be knowing the business keys. One important thing to note is that higher the number of dimensions lower is the grain. There might be exceptions to this.

Check out

Attitude,Not Aptitude, Determines Altitude