What is STAR Schema

Recently a student in my training class asked me, is it enough to know QlikView. One of my  friend who is  reasonably knowledgeable in QlikView also asked me the same question. He is struggling to meet client’s requirement. His question was the same ,is it not enough to just know QlikView. My response to them was, it is a “Fact” that you know QlikView but what are your “dimensions”. They both had a blank expression on their faces and asked what do I mean by “dimension” and that was the key and answer to their problems.

QlikView is a tool that helps you perform your tasks easily. Knowing just QlikView is like saying that knowing how to operate a car will make you a great driver. To become a successful QlikView solution provider you should be good in Business Intelligence concepts and procedures.

What is a 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 $1Million 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 that 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 does’nt 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 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.

QlikView is just a tool but to implement it in a right way you should have good knowledge of data warehousing concepts and a grasp of business requirements.