1. Importing Data
Importing the data refers to loading the tables needed for analysis into Power BI desktop. Power BI comes with various data connectors to access data from different data sources. For a step by step guide on this: https://powerbitraining.com.au/data-model-common-issues-and-solution-power-bi-training-in-sydney-power-bi-training-in-melbourne/2. Data Preparation
Data Preparation is cleaning and transforming of raw data into a format which is more useful for analysis. For a step by step guide on this: https://powerbitraining.com.au/data-model-common-issues-and-solution-power-bi-training-in-sydney-power-bi-training-in-melbourne/3. Creating Date Table
Date table is one of the most important table in any data model. Date Table can be created using the DAX functions such as CALENDAR() and CALENDARAUTO(). For a step by step guide on this: https://powerbitraining.com.au/data-model-common-issues-and-solution-3-power-bi-training-in-sydney-power-bi-training-in-melbourne/4. Setting Model Design
Model design is created by connecting the isolated data tables. These tables have primary key for the identification and these keys act as foreign keys for value identifiers in the other tables. For a step by step guide on this issue: https://powerbitraining.com.au/data-model-common-issues-and-solution-4-power-bi-training-in-sydney-power-bi-training-in-melbourne/5. Creating Surrogate Key
Primary and foreign keys act as row identifiers in any table and are used to link tables. Sometimes the primary key is missing and it becomes impossible to link two isolated table. The missing key is called Surrogate key and can be added in the missing table with just a few clicks. For a step by step guide on this: https://powerbitraining.com.au/data-model-common-issues-and-solution-5-power-bi-training-in-sydney-power-bi-training-in-melbourne/6. Merging Queries
Merging queries involves combining two data tables based on a common column. This column should have similar values in the both tables. For a step by step guide on this: https://powerbitraining.com.au/data-model-common-issues-and-solution-6-power-bi-training-in-sydney-power-bi-training-in-melbourne/7. Creating Calculated Columns
Calculated Columns are created to provide you with insights related to your data set and make data visualization easy and meaningful. For a step by step guide on this: https://powerbitraining.com.au/data-model-common-issues-and-solution-7-power-bi-training-in-sydney-power-bi-training-in-melbourne/8. Flattening the Product Table
Flattening of the table refers to linking the tables attached to the ends of the schema. In data terminology it means changing the snow flake to star schema because star schema is the most preferred data model by industry experts. Flattening of Product table is important due to two reasons. Firstly, it helps to organizes the data model and secondly, hierarchy can only be created in the same table of the data model. For a step by step guide on this: https://powerbitraining.com.au/data-model-common-issues-and-solution-8-power-bi-training-in-sydney-power-bi-training-in-melbourne/9. Creating Product Hierarchy
Power BI allows you to create Product Hierarchy for columns present in the same table. For a step by step guide on this: https://powerbitraining.com.au/data-model-common-issues-and-solution-9-power-bi-training-in-sydney-power-bi-training-in-melbourne/10. Active vs. Inactive Relationship
The Sales table contains two date fields, Order Date and Ship Date. We have already configured Order Date with the Date Table. To analyze Sales in context to the shipping date, the shipping date must have an active relationship with the date table. For a step by step guide on this: https://powerbitraining.com.au/data-model-common-issues-and-solution-10-power-bi-training-in-sydney-power-bi-training-in-melbourne/11. Working with Role Playing Dimensions
Role Playing Dimensions in a data model are dimensions that are used multiple times and for different purposes. As an example, the Date in our data model is a Role Playing dimension. It is used to signify Order Date as well as the Ship Date. However, only one active relationship can exist between two tables and designing measures to use inactive relationships is not preferred. For a step by step guide on this: https://powerbitraining.com.au/data-model-common-issues-and-solution-11-power-bi-training-in-sydney-power-bi-training-in-melbourne/12. Adding Multiple Fact Tables
Now you will learn how to use multiple fact tables in a data model. At this point, we will introduce a Sales Target table. A monthly goal of Product Type is the target quantity of products to be sold in that month. To Add this Higher Level of detail: 1. Load the Sales Target
2. Click on Month in the Sales Target Table and drag the cursor to Date column in the Date Table.
A one-to-many relationship is created. This is because for a given month, different targets are set for each product table.
3. Click on Product Type in the Sales Target Table and drag to the Product Table.
A many-to-many relationship is created. Each product is assigned multiple targets, one for each month.
4. Click on Table in the Visualization Pane.
5. Drag and drop the Date from the Date Table to Values.
6. Drag and drop the Product Type from the Product Table to the Values.
7. Drag the Quantity Sold from the Sales Table to the Values.
8. Drag and drop the Target Quantity from the Sales Target Table to the Values.
9. Press Ctrl+C to copy the Visual.
10. Press Ctrl+V to Paste the visual.
11. In the new table, Remove Product Type and Add Product from the Product Table.
Two table visuals have been created. Each of them shows a different granularity level. As the target has been defined for each Product Type and not Product, the values in the second visual is not incorrect however it is misleading. In the first visual we have 4 rows for January 1st, 2019. Whereas, in the second visual, we have 8 rows for the same date.
This issue will be address in the next blog.
Conclusion:
It is often considered impossible to have more than one fact table in any data model. However, some business problems cannot be solved without multiple fact tables. One of such problems is adding a monthly target table. This blog provides a step by step guide on this problem.
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.
