Power BI

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:

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

Back to list

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *