Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld online is next week! REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

What is the difference between a fact table and a dimension table ?

Hi Everyone,

I often see QlikView documentation referring to fact tables and dimension tables. Are the two things different ? If so, how can I tell them apart ? What do they each do ?

Please don't refer me to the QlikView Reference Manual as I find it confusing.

Thanks

MV

1 Solution

Accepted Solutions
JonnyPoole
Employee
Employee

In QlikView there is no hard and fast property or rule that defines this for all scenarios. It is definitely a 'thing' in the world of data modelling and it does translate into QlikView's associative model.

In your data model (to view it go to file -> Table viewer) you will see 1 or more tables.  ( If you have 1 table then consider it a fact table. )  The tables with numeric values that you use for chart expressions can be thought of as  your fact table .   You can have more than one fact table in a data model , but often with this scenario in QlikView it is common to merge the 2 fact tables into one big table using joins or table concatenation. Fact tables are usually quite granular. They often have a date or time field in them for example. The individual rows in a fact table are called 'facts'

Examples of fact tables are :

- a list of  sales orders (including Sale amount)

- a list of trades in the stock market

- a list of employee movements (status changes)

- a list of expense items

A table without a numeric field (that is, without a numeric field that will be used in an expression) you can think of as Dimension table. They usually provide additional descriptive information about the facts in a fact table. The fields in a dimension table are often used as chart dimensions, filters for list boxes / multiboxes etc and search boxes

Examples of dimension tables are:

- Customer  ( The customers name, address, location etc..)

- Product ( The product's name , The product grouping, its color etc...)

- Employee ( Tenure , department etc..)

A common way to organize a data model is a star schema because it minimized the amount of rows and columns in a data model and still store all the infortmation.   Its called a start because the middle is the fact table, and the fact is joined to more than one dimension table. Visually to show the links clearly between the fact table and all the dimension tables is to arrange the dimensions in a circle around the fact to look like a 'star' . Thats pretty much it.

If you had a list of sales orders as a fact. Each row or fact may have a customer ID, a Sales Rep ID, a product ID etc..  And then you would have 3 dimension tables. A customer table with customer ID, a Sales Rep table with Sales rep ID and a product table with product ID.  it doesn't have to be ID, just something that links the tables together.

This is important because the user can now question the fact table from a customer standpoint , a rep performance standpoint or a product standpoint. They can analyze the data from any angle and get their answer quickly and have the ability to drill down to details and answer the next question. Its the key thing to turn your qlikview applications from a visual  dashboards to an application that can provide root cause analysis and answers to unexpected questions.

one step further: a 'snowflake' is a star schema but when the dimension tables themselves have 1 or more extra dimensional table dangling off them. For example a customer table may have a list of customers and their addresses and cities, but a secondary table may have all the cities + their countries and regions.  The Customer is the dimension table joined to the fact table, while the  Customer Location table is 'snow flaked' off the Customer dimension table and it does not join directly to the fact table.

help ?

View solution in original post

11 Replies
sivarajs
Specialist II
Specialist II

When comes to data modelling, we have fact and dimension tables.

Fact- Stores transactional data ( e.g how many purchased a product today from a website(sales))- Measures will be there(sum,count etc). This table keeps changing whenever the sales is happening.

Dimension - Stores high level data (Customer table - customer name , country) .This type of data you wont get in fact table. Change in data will not be often.

maleksafa
Specialist
Specialist

fact table come at the heart of the star schema, where the fact table is the main table including all transactions, operations etc... and the dimension tables that are linked to this fact table contain the attributes and grouping of the fact table, like country, calendar, brands etc...

ideally you will have a star schema with 1 to n relationship between the fact and dimensions tables.

Not applicable
Author

How does QV know if a table is a fact table or a dimension table ?

Am I able to tell the difference ?

Not applicable
Author

Hi,

Yes you can Dimension table having the description of data it may be nummber and text

but fact table contains only numeric data

ogster1974
Master II
Master II

QlikView itself doesn't know the difference between a table of facts and a table of dimensions only you can decide how to structure your data.  For QlikView to perform from a speed and usability point of view as the guys have already stated you should try to model your data in a star or snowflake schema. 

Usually a single fact (transaction) table with reference data held in dimension tables linked to this fact table. an example might be a table of Orders (facts) linked to Customers (Dimension) just be careful to give your IDs unique names as QlikView loves to turn ambiguous joins into synthetic keys.  

sivarajs
Specialist II
Specialist II

Qlikview basically works with star schema.

As soon you load the table in qlikview, it will link based on the common fields between the tables. It does not know its fact or dimension table ( as per my knowledge).

In order to achieve the star model, we need to design the data model. When designing we can specify the dimension and fact on our own. Link table concept comes here.

JonnyPoole
Employee
Employee

In QlikView there is no hard and fast property or rule that defines this for all scenarios. It is definitely a 'thing' in the world of data modelling and it does translate into QlikView's associative model.

In your data model (to view it go to file -> Table viewer) you will see 1 or more tables.  ( If you have 1 table then consider it a fact table. )  The tables with numeric values that you use for chart expressions can be thought of as  your fact table .   You can have more than one fact table in a data model , but often with this scenario in QlikView it is common to merge the 2 fact tables into one big table using joins or table concatenation. Fact tables are usually quite granular. They often have a date or time field in them for example. The individual rows in a fact table are called 'facts'

Examples of fact tables are :

- a list of  sales orders (including Sale amount)

- a list of trades in the stock market

- a list of employee movements (status changes)

- a list of expense items

A table without a numeric field (that is, without a numeric field that will be used in an expression) you can think of as Dimension table. They usually provide additional descriptive information about the facts in a fact table. The fields in a dimension table are often used as chart dimensions, filters for list boxes / multiboxes etc and search boxes

Examples of dimension tables are:

- Customer  ( The customers name, address, location etc..)

- Product ( The product's name , The product grouping, its color etc...)

- Employee ( Tenure , department etc..)

A common way to organize a data model is a star schema because it minimized the amount of rows and columns in a data model and still store all the infortmation.   Its called a start because the middle is the fact table, and the fact is joined to more than one dimension table. Visually to show the links clearly between the fact table and all the dimension tables is to arrange the dimensions in a circle around the fact to look like a 'star' . Thats pretty much it.

If you had a list of sales orders as a fact. Each row or fact may have a customer ID, a Sales Rep ID, a product ID etc..  And then you would have 3 dimension tables. A customer table with customer ID, a Sales Rep table with Sales rep ID and a product table with product ID.  it doesn't have to be ID, just something that links the tables together.

This is important because the user can now question the fact table from a customer standpoint , a rep performance standpoint or a product standpoint. They can analyze the data from any angle and get their answer quickly and have the ability to drill down to details and answer the next question. Its the key thing to turn your qlikview applications from a visual  dashboards to an application that can provide root cause analysis and answers to unexpected questions.

one step further: a 'snowflake' is a star schema but when the dimension tables themselves have 1 or more extra dimensional table dangling off them. For example a customer table may have a list of customers and their addresses and cities, but a secondary table may have all the cities + their countries and regions.  The Customer is the dimension table joined to the fact table, while the  Customer Location table is 'snow flaked' off the Customer dimension table and it does not join directly to the fact table.

help ?

Not applicable
Author

Excellent. What a fantastic answer. That's really helped me understand the concept of data modelling in QlikView. Thanks very much.

MV