The first and foremost concept you need to understand in Power BI is the difference between the Fact and Dimension table. Relationships are defined between tables through primary and foreign keys. Relationships between tables are formed when you have primary and foreign keys in common between different tables.
| Fact Tables (Foreign Key) | Dimension Tables (Primary Key) |
|---|---|
| It represents the facts/attributes/metrics in a table. In general, Sales and Order tables are fact tables. | It represents dimensions of the fact table. In general, Calendar table is the dimension table which would act as a role-playing dimension. |
| Fact tables contain observational data. | Dimension tables contain information about specific entities within the data. |
| It is connected to the dimension table by foreign key | It is connected to fact table by primary key |
| Foreign keys are referencing rows in a dimension table. | Primary keys are columns that identify each unique, non-null data row. |
| Fact tables contains label level data either in numerical or text format which represents the lowest level of information which needs to be analyzed and reported. | Dimension table contains detailed data usually in text format which can be filtered or represented. |
| Fact table does not contain a hierarchy | Dimension table contains hierarchies |
| Fact tables grows vertically with more number of records. Sales and Order tables. | Dimension table grows horizontally and data is to be analyzed. Location, Time, branch tables. |
| Typically there are less number of fact tables in data set. | Typically there are more number of dimension tables in data set. |
| For example, 1) Location ID, 2) Order Date, 3) Item ID which are unique records referencing to the Dimension table. Additionally, Qty sold, Amt sold & sales price etc are part of the fact table. | For example 1) Location could contain, Location ID, country, pin code, state, city, 2) Time table could contain Order date, month, year, quarter 3) Category table can contain Item ID, Item name, Brand etc |
Hope you found the post informative. Your valuable feedback, question, or comments about this post are always welcome by leaving me message on contact form is truly appreciated.
Discover more from LR Virtual Classroom
Subscribe to get the latest posts sent to your email.
LR Virtual Classroom 