DataOps Principle #13: Simplicity

We lacked consistency… a senior developer tells me this after he reviewed some of the Power BI datasets we were building. We had tests, we were versioning, so progress had been made, but the naming conventions of tables and measures varied slightly. If data analysts/customers were going to use our datasets we didn’t want them looking at two different datasets and seeing different names (DateDim, DimDate, Date Dimension, etc.) for the same dimension. And for the senior staff, we didn’t want to spend hours applying good design with manual reviews. In short, we wanted predictability, standardization and a simple way to enforce that.

DataOps Principle #13: We believe that continuous attention to technical excellence and good design enhances agility; likewise, simplicity–the art of maximizing the amount of work not done–is essential.

However, introducing simplicity when it comes to applying consistent design with datasets does have its challenges in Power BI:

The Challenges

1) Power BI is flexible - We can name our tables, measures, columns anything we want. As a developer, I like flexibility because it allows for creativity and the ability to adjust to customer needs. As a manager though, the thought of the development team building models for the same customer but have them look completely different makes me break out in hives. And it is further distressing that I must ask senior staff and myself to waste time (time not delivering value to customers) enforcing standardization through manual reviews and subsequent rework with updates to datasets.

2) There are no built-in linters - Never heard of a linter? A linter performs static analysis on your code to look for bad coding style/practices, forcing you and your team to keep a consistent code style. There are linters for many programming languages, but Power BI inherently lacks a linter.

Best Practices

First and foremost, I’m standing on the shoulder of a giant here. Daniel Otykier, the author of Tabular Editor, and his blog series around Analysis Services allowed these best practices to be viable in Power BI.

1) Leverage the Best Practice Analyzer in Tabular Editor. Version 2.0 of Tabular Editor is free to use, and the best practice analyzer leverages a JSON file to define rules on DAX expressions, Error Prevention, Formatting, Maintenance, Naming Conventions and more. And in the spirit of simplicity, others have already created JSON templates with predefined best practices such as Microsoft or Daniel Otykier.

2) Just like I suggested that your team use DAX Studio to test Power BI on their local machine, your team can use Tabular Editor to run your Power BI dataset through the Best Practice Analyzer. That way you start conditioning your team to check for issues that go against best practices prior to committing the Power BI dataset to Git and fix those issues.

Example of Best Practice Analyzer and, in this case, the model has many issues

3) Incorporate the Best Practice Analyzer in your continuous integration pipeline. Now I made it simple here, because I took the pipeline template from Part 5 and added a step that runs the Best Practice Analyzer to Github. The pipeline now behaves like this:

Updated Pipeline with Best Practice Analyzer in Step 4

If warnings (as indicated with Severity Level 2 in the JSON file) are encountered, the build doesn’t fail but you will see the warnings listed in Azure DevOps. An example of a warning is leaving key columns visible in the model.

Example of warnings from the Best Practice Analyzer in Azure DevOps

Errors that are encountered will fail the build, and Azure DevOps will list those errors. An example of an error would be to use a variable/measure in the denominator for division instead of using the DIVIDE function which handles the divide by zero issue.

Example of an error from the Best Practice Analyzer in Azure DevOps

Let me know what you think on LinkedIn or Twitter, and stayed turned for my next article that will cover best practices in embracing change with Power BI. It won’t be so technical this time (fans of Azure DevOps and automation will have to wait a couple more weeks…)