Data Modeling is a part of our every Power BI training in Australia. In Power BI Basic course, only the basic concepts related to data modeling are covered whereas Power BI Advanced course is more focused towards data modeling and data transformation. Therefore, understanding what a Data Model is entirely important for both beginners and intermediate level users. This blog is dedicated to understanding what is a data model in Power BI? What is not a Data Model in Power BI? And what are the main components of a Data Model.
Here’s a quick walkthrough for best Practices in Data Modeling in Power BI: https://powerbitraining.com.au/best-data-modeling-practices-power-bi/
What is Data Model in Microsoft Power BI?
Data Model refers to the abstract model that demonstrates the logical structure of data and the relationships that exist in the data. In Microsoft Power BI, Data Model refers to everything that is loaded from query.
A data model consists of Two or more tables related to each other. For example, Let’s consider the data model in the image. The sales table in the center is related to each of the other tables, i.e., Products, Customers, Territory and Calendar.
Flat File is NOT a Data Model
Flat files are not considered as data models because they contain denormalized data. As the data volumes grow and the models becomes complex, it becomes inefficient to design measures on a single flat file. Hence flat files turn out to be non-optimal solution for higher data volumes.
Tables in a Data Model
A typical data model consists of:
Fact Table / Data Table
If we record a value of an event or something that happened, it’s called a fact and the complete record is known data table in the BI world. Fact tables record measurements or metrics for a specific event. They are usually number heavy, thin (less no. of columns) and long (large no. of rows). They also contain foreign keys to relate to dimensional data sets.
Dimension Table / Lookup Table:
Dimension or lookup table have the additional information. Dimension tables are text heavy. They usually have a relatively small number of records compared to fact tables, but each record may have a very large number of attributes to describe the fact data. Dimension tables have unique identifiers called primary keys
Fact Table vs. Dimension Table
In a Data model, a fact and dimension table are linked by many to one relationship. Dimension table exist on the 1 side of relation and the fact tables exist on many side of the relationship. Both of these tables have a common column that constitutes their relationship. In the dimension table, this column is the primary therefore holds uniquely identified values. Whereas, in the fact table, this column acts a foreign key therefore holds duplicates.
The table below provides a comparison between fact table and a dimension table.
Conclusion:
Data modeling Is an important concept. Creating a right data model increases the efficiency of your report and provides cleaner analysis. Data Model refers to everything that is loaded into the query editor of Microsoft Power BI. Data model contains the right number of tables with the right relationship between them. In this blog post, the concept of data model is introduced and some misconceptions related to data model have been discussed. The concept of Fact table and the dimension table has also been explored.
Learning Power BI from Scratch? Get a complete, in-depth introduction to Microsoft Power BI tool by attending our Power BI Basic Training Course.
More Checkouts for Power BI Training in Australia
