In our last Power BI training in Sydney, we discussed the Best Data Modeling Practices. Here’s a quick recap of what we discussed. In this blog, we will walk through a list of some good practices that you must follow to create a good data model in Microsoft Power BI.
- Ask the right questions before building model
Before building a data model, one must be clear about the questions that the model will address. This is particularly important because it directly decides the tables that will be imported into the model.
- Get clear on your outcome and what you want to measure
One must be clear on the outcomes expected once the complete BI process is executed. Once the data is imported, DAX calculations are performed on the data to get the required insight. This is only possible if the outcome is clear.
- Bring only the selective data into the model
Excessive data tables increase the processing overhead and are likely to create loops in the model.
- Rename fields/columns and tables appropriately
Renaming fields and columns is necessary to avoid confusion amongst similar fields and increase the readability of the report.
- Check Data types
Power BI guesses the data types based on the top 1000 rows so it is important to check the data types both in the query and the model. For numeric data types, it is a good practice the set the decimal digits to ensure that the values are correctly rounded off in the model. Numerical data types are set according to the level of precision required.
- Check if the categories are correctly assigned and other defaults such as summarization, sort by another column are correctly configured. Finally, check the format as well.
Categories are especially important for geographical data. (Blog #)
- Don’t flatten the data set. Bi-directional filters make it as one big table
- Go for the star
Star schema is the most widely preferred schema. (Blog #)
- Use single direction when multiple fact tables are there
Bi directional filtering is avoided because Power BI’s engine treats both the table as single.
- Use calculated tables to reduce ambiguity
Some useful functions include:
| Function | Description |
| DISTINCT | Returns table by removing the duplicate rows from another table. |
| UNION | Returns table containing all the rows from each of the table expressions. |
| INTERSECT | Returns table containing common rows from each of the table expressions. |
| EQUAL | Creates a clone of the table referred in the expression. |
| CROSSJOIN | Returns table containing combination of all values from each of the table in the expressions |
| NATURALINNERJOIN | Returns a table that consists of the tables are joined on common columns (by name) in the two tables used in the expression. |
| FILTER | Returns a table that represents a subset of another table or expression. |
| SUMMARIZE | Returns a summary table for the requested totals over a set of groups. |
| CALENDER | Returns a table with a single column named “Date” that contains a continuous set of dates. |
| CALENDERAUTO | Returns a table containing date automatically configured to the first and last dates in the data set. |
- Always have a separate date table
Date tables are necessary to add a dimension of Date in the model. Some common functions to create date table and column are listed below.
| Function | Description |
| YEAR | Retrieves value of year from the Date Field. |
| MONTH | Retrieves value of month from the Date Field. |
| DAY | Retrieves value of Day of Month from the Date Field. |
| WEEKDAY | Retrieves value of Day of Week from the Date Field. |
| FORMAT | Formats the date results. |
- Use relevant DAX Functions to model relationships to meet the changing data updates
In a live environment, data tends to change over time. Therefore, it is essential to cater for the future changes in the data model. This can be done using relevant DAX functions.
| Function | Description |
| RELATED | Retrieves value from the table at 1 side of relationship i.e., the dimension table. |
| RELATEDTABLE | Retrieves rows from the table at * side of relationship i.e., the fact table |
| USERELATIONSHIP | Forces the use of a particular relationship. |
| CROSSFILTER | Modifies the filter behavior to single, both or none |
| TREATAS | Applies the result of a table expression as a filter to columns from unrelated table. |
| PATH | Changes parent-child relationships into columns because Power BI does not support recursive relationships. |
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
