1. Importing Data
Importing data refers to procuring the data tables into Power BI desktop or service. This step involves creating a connection with the data base to access the data. 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 involves ensuring that the data is capable of delivering value. In this phase, the raw data is manipulated to extract useful information from it. 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 important for temporal analysis. The date table is created using the CALENDER() function or the CALENDERAUTO() function. Both of these functions have different input parameters and produce different results. 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
The model design consists of creating relationship between the data tables procured for the task at hand. The tables are linked on the basis of primary and foreign keys. If these keys are absent then the table cannot be linked to another table. 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
Surrogate Key is the unique identifier in any table. It is important to create a relationship between the two tables. This key acts as a primary key in one table and foreign key in the other. 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 referred data model by industry experts. Modeling. Flattening of Product table is important due to two reasons. Firstly, it helps to organizes the data model and secondly, hierarchy can 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. 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. To work with Role Playing Dimension: 1. Click on Modeling Tab in the Ribbon. 2. Click on New Table. 3. Type the following DAX expression in the Formula Bar.
A clone for the Date table is created.
4 Rename the Month Column to Shipping Month.
5. Rename the Year Column to Shipping Year.
6. Rename the Name of Month Column to Name of Shipping Month.
7. Click on Model Tab in the Left
8. Click on the inactive realtionship between Ship Date and Date. Click Delete.
9. Click on Ship Date in the Sales table and drag the cursor to Ship Date Table.
10. Click on the line showing active relationship between the Ship Date in the Sales Table and the Shipping Date table.
11. Set the cardinality to one-to-many and the cross filter to single.
12. Click on Shipping Date under the Properties Pan. Type Filter by Ship Date in the Description.
Filter by Shipping Date appears in the tool tip of the Shipping Date Table.
Conclusion:
A perfect star schema consists of a central fact table surrounded by multiple dimension tables. These tables should ideally have only one relationship with the fact table but this does not happen practically due to the existence of role playing dimensions. An example of role playing dimension is the date which can occur in the Sales table as Order date or shipping date, etc. In this blog post, the concept of role playing dimensions is covered.
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.
