In this blog post we discuss how Tabular Editor can help you with improving your data models. Every Power BI developer needs to learn the essential skill of data modeling. The way that your data is structured has an effect on the efficiency and quality of your reports.
However, it can be hard to evaluate your data model. Luckily, Tabular Editor is able to help you with that task.
What is Tabular Editor and how does it work?
Tabular editor is used for data modeling and managing measures and calculated columns in Power BI and Analysis Services data models. This editor is extremely useful for the development and validation of data models.
There are currently two versions of the software. Version 2 is the free version, version 3 a commercial version extending V2 with additional features.
But how does Tabular Editor validate these models? When using Power BI, your metadata is stored in a BIM file. Tabular Editor can access, validate and modify the structures in this file ensuring the optimization of your models.
In order to validate something, there of course have to be criteria. Tabular Editor has attached a file in their Github with a list of best practices for data modeling that have been created by various members in the Power BI community.
This article is focused on data model optimization and improvement. Tabular Analyzer has many more functionalities that are useful that are not covered in this blog.
How can you use Tabular Editor V2?
The first thing we need to do is installing the software package of Tabular from their GitHub page. Once the installation is complete, you can open your Power BI report and navigate towards the External tools section. There you will see the Tabular Editor logo.
Click on the icon and your model will be opened in the Tabular Editor UI.
How do we validate a Power BI Data model?
Tabular Editor provides a Best Practice Analyzer that can evaluate data models. How does this work? Tabular Editor and the Power BI community have created a set of rules for data model development. Within the Tabular Editor you can find the Best Practice Analyzer that will evaluate your model based on the predefined set of rules.
The data modeling rules are stored on the GitHub of the Tabular Editor. Go to the GitHub page, download the ZIP file. Go to your downloads, and extract all.
In order to use these rules we have to import them into the best practice analyzer inside Tabular Editor. Go to the Tabular Editor, hover over tools, and Manage BPA Rules.
Within this UI you will see the option to include a local rule file.
Now you are able to import the BPARules-standard.json from the Best Practices folder we downloaded from GitHub.
If you click on the link you will see all the rules that were imported to the Tabular Editor.
Beware, you need to manually save your work in the Tabular Editor. You can do this by clicking on file and then save.
Let’s validate our model
We have our tool, and we have the rules, let’s validate our model. To illustrate the functionality, I have created a data model with a obvious few flaws.
- The ID column in the facts table is not unique. It contains duplicate IDs.
- The relationship between DimUser and Facts is bi-directional (both). This is not best practice.
- DimUser and Facts are linked with a text column, and not an integer.
In Power BI we click on external tools and select Tabular Editor. In this UI we hover over tools, and select the Best Practice Analyzer.
When I open the analyzer, I see a lot of things that can be improved in my model. For example, I need to avoid CamelCase on visible measures and tables.
Let’s improve this. I go into my Tabular Editor, to my tables, and I rename the Tables.
Now let’s save the file and go back to the performance analyzer. You will see that these issues are now gone.
Solving all of my modeling issues
Now you can start with solving all modeling issues and reach a model that follows Power BI best practices!
