Business Intelligence Basics – Dimension and Fact

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.

Fact1

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.

Factdim1

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.

factdim2

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.

factdim3

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.

2 thoughts on “Business Intelligence Basics – Dimension and Fact

  1. Hi chandraish, this is pinal. I am enable to connect Datasource in Tableau. I used the same path still shows me an error regarding location.

    please guide me.

    thank you.

    • Pinal:
      Is the data source error occurring when you are trying to open the downloaded workbook or when you are trying to create a new one.
      If you are getting error while opening the existing workbook that may be because data source is not in the “data source” folder.
      If you are creating a new twb, then you should just locate the excel file.
      Let me know if this resolves the issue.

Leave a Reply

Your email address will not be published. Required fields are marked *