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.