DATA TRAVELING - Guilherme Joaquim's Blog

View Original

Pro tips to optimize Power BI data modeling: automating manual tasks

Guilherme Joaquim

Context

Recently, I built a semantic model in Power BI that had multiple columns in my fact table. I didn’t want to write simple DAX measures (such as SUM, MAX, AVERAGE, MEDIAN…) manually for each column, change the data format, add a description and so on… if you ever came across this tedious task before, you know what I am talking about. This is just an example. Today I want to share some ideas and techniques that I use in my daily work to automize such manual tasks.

The main purpose of this post is to show you some possible ways to make our life easier during data model developement in Power BI.

In this post we are going to talk about the following topics:

  • Create multiple DAX measures using Tabular Editor 2

  • How to format all your DAX measures at once

  • Create a robust date table for your model

  • Update Power BI metadata simultaneously

  • Check referential integrity violation in your data model

  • Data model documentation

  • Parameterizing source connections

  • Best Practice Analyzer (BPA) to improve your model’s performance

  • Quickly check DAX measure dependencies

Tip 1: Create multiple DAX measures using Tabular Editor 2

A very common approach when writing DAX measures is to write basic DAX measures (SUM, MAX, MIN…) first and then use these measures in your future measures. This is called measure branching.

How to create DAX measures for SUM, MAX, MIN, AVERAGE and MEDIAN at once, including data format and description of the measure?

You can do this using a free external tool called Tabular Editor 2. See below the screenshot from Tabular Editor 2:

You can find the complete C# script used below. You can copy the C# script below in your Tabular Editor.

If you interested in more C# scripts, check the Tabular Editor documentation here or check the Github from Bernat Agulló Roselló here or this Github from PowerBI-tips here.

Tip 2: How to format all your DAX measures at once?

Imagine you got a data model from someone and the DAX measures are not properly formatted. It would be a tedious task to manually format them. There are 2 ways that I know and use to do this task.

Option 1: Use the external tool Bravo to format all your DAX measures

This option uses the external tool Bravo developed by the SQLBI team. Here you can choose how you would like to format your DAX measures:

See below an example in action transforming an unformatted DAX measure into a formatted one. If you are wondering how Bravo does the formatting, it sends the measures to the DAX Formatter service (also developed by the SQLBI team).

Option 2: Use C# scripts inside of Tabular Editor to format all your DAX measures

If your organization does not allow you to install Bravo, then we could use Tabular Editor to do this task. Just for your information, it is possible to download the zip file from Tabular Editor 2 and use it without having admin rights.

See below the C# script to format all your DAX measures and add a break line all at once:

The C# script above also uses DAX Formatter to format the DAX measures. For more information about it check the Tabular Editor documentation. 

Tip 3: Create a robust date table for your model

A good date table plays a very important role in your semantic model. For example, simplifying time intelligence DAX measures, being able to do time intelligence analysis based on fiscal year and so on. There are many ways to create a date table. Assuming you don’t have a date table in your data warehouse, there are 2 ways that I personally like to create my date table:

Option 1: Write the date table in Power Query with M code

  • I really like the date table written in M code by Melissa de Korte and this is the one I almost always use.

See the M code below. For more details refer to this blog post from Enterprise DNA.

Option 2: Use external tool Bravo to create the date table

  • Another option is to create a date table using the external tool Bravo developed by the SQLBI team. I think this a good option if you want to do a quick analysis. However, I still prefer the date table from the M code above

Tip 4: Update Power BI metadata simultaneously

Imagine the following scenarios as an example:

  • You have your date table and also other date columns in your data model. You want to change the data type format to “short date” automatically

  • You want to set the “summarize by” to “none” for all columns in your data model

The two examples above can be easily performed using an external tool called Metadata Mechanic Pro developed by Greg Deckler. See below the screenshot from Metadata Mechanic Pro, where I set all date types to “short date” and aggregation to “none” for all columns:

Tip 5: Check referential integrity violation in your data model

One thing that I always do during data model development is to check for referential integrity violation. Referential integrity violation means that foreign key values in a fact table are missing from the dimension table. One example would be having foreign keys as null values in the fact table causing a referential integrity violation. For more details refer to this article from Kimball Group.

Option 1: Use the external tool DAX Studio

This option is useful when you only want to check during development and you don’t need to dynamically check it for every semantic model refresh (if you want to dynamically check, see option 2).

How to check it using DAX Studio:

  • I already wrote a LinkedIn post describing how to check for referential integrity violation. Please check this post here.

Option 2: Dynamically check for referential integrity

Another option is to leverage DAX measures to check for referential integrity violation. Here credit to Bernat Agulló Roselló. He wrote about it in his blog. So, there is no point of repeating here again. Check his blog post here where he describes in great detail how to do it.

Tip 6: Data model documentation

Model documentation is a topic that might be forgotten or left behind. But this is an important topic, especially if you want to share your semantic models with other people. One option to document your data model is to use the external tool Model Documenter developed by Marc Lelijveld.

This external tool generates a Power BI report with a lot of information:

Tip 7: Parameterizing source connections

There are many use cases for using parameters in Power Query. One of them is to create a parameter to change the data source dynamically. For example, you work with SQL Server as data source and often need to switch between environments (such as moving between development and production). Doing that you don’t need to change the data source connection for every table manually, because you can use the parameter to do it all at once.

I already wrote a LinkedIn post describing how to create a parameter for your data source in Power Query. Please check this post here.

Tip 8: Best Practice Analyzer (BPA) to improve your model’s performance

Before publishing your data model, it is a good idea to check if there are any issues within the model. This check can help us, especially when working with large data models.

To run this check, you have to first run a C# script in Tabular Editor. There are already many resources out there explaining how to do it. Therefore, there is no point repeating it here.

See some resources below:

Tip 9: Quickly check DAX measure dependencies

For this one, you need to have at least the November 2023 version of Power BI Desktop. With this version, Microsoft released a new feature called DAX Query View inside of Power BI Desktop. I personally really like this feature, because we can perform some things that were only possible using DAX Studio, such as performing a DAX query without leaving Power BI Desktop.

When working with large models with a lot of DAX measures, it can become quite hard to find DAX measure dependency. Well, right now we can perform this task easily in Power BI Desktop without using any external tool :)

See below the screenshot with an example:

For more details about this new feature, I highly recommend to watch the YouTube video from Marco Russo here where he really dive into this feature comparing with DAX Studio as well.

Another great resource is the article from Zoe Douglas from Microsoft here.

Conclusion

In this post we talked about some ideas and techniques to automate manual tasks and to optimize your data model development in Power BI. Of course, there are definitely many other ways to automate such tasks. The purpose of this post is to show you some possibilities.

Do you know any other technique or idea to make our life easier during data model development? Please leave a comment below or on my LinkedIn post here.

Thanks for reading!

Last updated on December 13, 2023