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.
Getting Started
For this chapter, we will disable the auto detection of relationships by Power BI. This is done to create an environment where you can dive deeply into model creation. Disabling relationship detection is done purely to enhance concept building and is not preferred in ideal conditions.
To disable the auto detection of relationship by Power BI:
- Click on File Tab in the Ribbon.
- Click on Options and Settings.
- Click on Options.
A pop-up menu appears.
4. Click on Data Load in the Current File Section.
5. Uncheck all the options in the Relationship Section.
6. Click ok.
Importing Data
The Importing Data phase of a data modeling involved procuring the right tables that can answer the business question at hand.
To Gather Data for Data Model:
- Click on the check box at the left of Product Sheet.
It consists of three columns addressing the product details, i.e., Product Code, Product and Product Type. Each Product has a distinct Product Code and falls under only one Product Type.
- Click on the box at the left of Product Line Prod Type Sheet.
Each Product Type is a subcategory of Product Line. This sheet records the subcategory to category mapping.
- Click on the box at the left of Product List Sheet.
Product list contains the details of each Product and the list price associated per unit.
- Click on the box at the left of Sales Person Per Product Type Sheet.
This sheet contains details of Sales Person associated with the Sales. There exists a many to many relationships between the sales person and the product.
- Click on the box at the left of Sales Sheet.
Sales sheet contains transactional Data for the Sales.
- Click Load.
Now you have five queries loaded in your Query Editor. At this stage your data model should contain five un connected tables as shown in the image below.
Conclusion:
Data model consists of the right number of tables with the right relationship between them. A typical data model consists of a fact table and a dimension table. In this blog post, the concept of data model is introduced and the first two stages in the creation of a perfect data model have been discussed. This blog is from the archive focused on various data modeling problems and solutions.
