Best Data Modeling Practices for your data model using Power BI

You are currently viewing Best Data Modeling Practices for your data model using Power BI

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:

DISTINCTReturns table by removing the duplicate rows from another table.
UNIONReturns table containing all the rows from each of the table expressions.
INTERSECTReturns table containing common rows from each of the table expressions.
EQUALCreates a clone of the table referred in the expression.
CROSSJOINReturns table containing combination of all values from each of the table in the expressions
NATURALINNERJOINReturns a table that consists of the tables are joined on common columns (by name) in the two tables used in the expression.
FILTERReturns a table that represents a subset of another table or expression.
SUMMARIZEReturns a summary table for the requested totals over a set of groups.
CALENDERReturns a table with a single column named “Date” that contains a continuous set of dates.
CALENDERAUTOReturns 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.

YEARRetrieves value of year from the Date Field.
MONTHRetrieves value of month from the Date Field.
DAYRetrieves value of Day of Month from the Date Field.
WEEKDAYRetrieves value of Day of Week from the Date Field.
FORMATFormats 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.

RELATEDRetrieves value from the table at 1 side of relationship i.e., the dimension table.
RELATEDTABLERetrieves rows from the table at * side of relationship i.e., the fact table
USERELATIONSHIPForces the use of a particular relationship.
CROSSFILTERModifies the filter behavior to single, both or none
TREATASApplies the result of a table expression as a filter to columns from unrelated table.
PATHChanges 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

Leave a Reply