So what is a data model?
Yikes, a data model sounds confusing? Don’t worry, a data model is just a series of tables connected by relationships. Those relationships define how the data sources are connected with one another.
Taking the time to chose the data and granularity you actually need, will give you a much faster model that will enable better visualization. Fortunately, Power BI has some tools to help us create an easy to use data model.
In this section we will cover:
- Star schema
- Design a data model
- Develop a data model
- Create measures using DAX formulas
- Optimize model performance
A popular method to design your data model is using the star schema. In a star schema, a fact table is surrounded by dimension tables, as per the visual below. You are then probably thinking what is a fact and a dimension table?
A fact table contains observations or events, such as sales orders or inventory counts. A dimension table has the details of the fact table such as products, employees and site locations. Dimension tables are connected to fact tables using keys e.g. ProductID or SiteID. Dimension tables are also used to filter or group fact tables in visualizations.
Fact tables are usually much larger than dimension tables as they have detailed data at a transactional level. Dimension tables are usually much smaller and are not updated as frequently e.g. the number of factory locations will be a finite set that does not change often.
Another schema you may come across is the snowflake schema. A snowflake schema is an extension of the star schema. In a snowflake schema, the dimension tables divide themselves into more than one table, a process called normalization. That creates the snowflake pattern.
Design a data model
Let’s start by importing 3 tables from the file ‘Adventureworks2020.xls’. To download this file and follow along, please use the below file link:
To import an excel file, go to the Home ribbon and select Get Data -> Excel. Next select the 3 tables product, sales and salesorders. As always, select transform data to go into Power Query to edit the data
The first thing we are going to do is configure the table column properties. Open the sales table and use shift to highlight unit price through to sales amount columns. Then in the transform tab select ‘Fixed Decimal Number.’ Then select ‘Replace Current Step’.
On the ‘Unit Price Discount’ column, select the left corner icon and change it to % Percentage in the drop down. Again select ‘replace current’.
Open the product table and change the ‘Standard Cost’ and ‘List Price’ to fixed decimal number. Once you are done, go to the Home ribbon and click ‘Close & Apply’ to return to Power BI Desktop.