What is a dimension/dimension table

In Data warehousing, dimension table contains the textual or descriptive attribute of the data.

For example customer dimension will contain details about customer’s name,address,phone number etc. Dimensions are used to slice and dice the data i.e. filter and group the data.Dimension table also help you by looking at data with “By” attribute i.e. say, if the Total sales of the company is $1 Million then using Customer dimension you can look at the Total sales “By” Customer or “By” Time. A dimension table has a primary key column, also called Dimension ID/Dim Id. This column uniquely identifies each dimension row. The dimension table is associated with a fact table using this key.

What is a Fact table

Fact Table contains the measureable attribute of the data.It contains measurable data that can be analyzed by Dimension tables. Fact tables contain the foreign keys of the associated Dimension tables.

Let us see a real life example of dimension and fact tables.

USA population according to Census  is 316,251,315 millions. So this is a ‘fact’  as this is a measureable attribute.

but this doesn’t give us all the info as we would like to know what this population is on year by year basis. So to know that we will add a Time dimension. This will give you an ability to analyze Population year by year.

This is good that we have Year by Year break down of Population , but we should also know how this population is divided by Gender. How many males and females comprise this population. All we have to do is to have one more dimension for Gender.

So now we can have population by Year and by gender. Using this structure we can know how many Males and females we have in our population. Now we should also be interested in knowing other attributes like how this population is spread across different states or how many different Races are their in our population.

We can get all the required information using dimensions and facts. Using this above structure you can get all the information regarding USA population.  Once you have the right data model then dashboard is just a visualization.

If you  look closely, this structure looks like a ‘Star’ with fact in between and dimensions surrounding it. This is a structure known as a Star Schema. Star schema is very useful in dimensional modeling and dashboarding.