Monday, July 12, 2010

What is difference between Fact table and dimension table?

Dimension table

Fact table

It provides the context /descriptive information for fact table measurements.

It provides measurement of an enterprise.

Structure of Dimension - Surrogate key, one or more other fields that compose the natural key and set of Attributes.

Structure of Fact Table - foreign key, Degenerated Dimension and Measurements.

Size of Dimension Table is smaller than Fact Table.

Size of Fact Table is larger than Dimension Table.

In a schema more no of dimensions tables are presented than Fact tables.

In a schema less no of Fact tables are presented than Dimension tables.

Values of fields are in text representation.

Values of the fields always in integer form.

We can load the dimension table directly

We can't load the fact table first. So to load the fact table we need to load the dimension table first. Also while loading the fact table we will make a lookup on the dimension table cause the fact table contains the measures/facts & the foreign keys which are primary keys in the dimension tables surrounded to that fact table.

1 comment:


All Rights Reserved @ Raju Das