Fact and Dimension Tables

There are so many articles regarding this topic. If you are up to a job title that is related to data warehousing, mark my words, you will definitely look for this topic. In my case, I will explain simply as I can which is short & sweet.

What is a Fact Table?

Fact Table is the table that contains the business numbers in a data warehouse. There are some cases in which is the numbers are in the Dimension Tables too. This table contains all the primary keys in the Dimension Tables as foreign keys.

We can do aggregation on these Fact Tables.

What is a Dimension Table?

Dimension Tables are where all descriptive entities stores that describe the numbers in the Fact Table. In other words, the numbers in the Fact Table are described by the entities of the Dimension Tables.

We can do filtering, grouping and labeling functions on these Dimension Tables.

In data warehousing, we have to do some drilling through and slicing & dicing to get some results we need. For that, these Fact and Dimension Tables (Dimensional Modeling) are very useful.

Let’s take the following diagram of a retail shop which contains huge amount of data and maintains branches all over the county as an example.

Image source

Item, Time, Branch and Location tables are the Dimension Tables for the Sales Fact Table.

If we take data only from the Sales Fact Table, it is useless. Because it only contains only numerics like quantity and amount.

If we want to query more meaningful data, we have to use those Dimension Tables.

We can retrieve the sold quantity of a particular item sold in a particular branch which is located in a particular location in a given particular time period. That is Slice & Dice.

Also, we can Dilling Through the data like annually, half-yearly, quarterly and monthly.

That all from here for now. Go & get Mr. Google busy on your searches in data. The data is amazing!