Tabular Editor and the Best Practice Analyser for Power BI

Finally I get a chance to have a look at the Best Practice Analyse in Tabular Editor

https://powerbi.microsoft.com/en-ca/blog/best-practice-rules-to-improve-your-models-performance/

I am so excited about this because we are looking at ways to improve and speed up the reviewing process on all the datasets being created.

Currently our Power BI Power Users go in to review and take a lot of time checking out the models, processing time, DAX Measures, etc etc.

This is great but if there are ways to speed up that process so we can review even more models then I am in.

So lets get started.

DAX Studio

I always go to DAX Studio connected to my model to get a look at the model Summary before I do any updates. Advanced Tab – View Metrics

Tabular Editor

With a dataset open in Power BI Desktop I open up Tabular Editor from External Tools

And then go to Advanced Scripting

And now I am ready to load in the Best Practice Rules from GitHub.

https://github.com/microsoft/Analysis-Services/tree/master/BestPracticeRules

I go for the Automated Set up approach and run the following code

I believe that these rules get updated so when you run it again your rules will get updated.

Then after running the code I create a custom action over the code by clicking the green + Button

And you now have it in Samples

I can then Close and Open tabular Editor again. (I am doing it with a model already open but you can open it separately and connect to a model.

Then I go to Tools and Best Practice Analyser

And here is where it gets interesting. It brings up a list of Best practice rules that have been worked on by Microsoft experts for a long time. I really want to get a good look at where they are at at 15/10/2021

At this point its fair to say that this has been incredibly easy to do and very useful so far.

Of course the rules come up very specific to my model. 172 objects are in violation of 17 best practice rules. Lets have a look at some of them

[Performance] Do not use Floating Point data Type

My Latitude and Longitude are in Violation here so I should be going back to the model and checking that I cant change these items and not get issues

Some fixes can be taken from best practice analyser.

In this case I am selecting generate fix Script

And pasting to Advanced Scripting window to have a look at it

I can run this and it will immediately update my data model. However it should be said that Tabular Editor requires the XMLA endpoint to allow both read/write access. This setting is controlled by your capacity admin.

In the Power BI Admin Portal

If you don’t have this enabled you need to check with your Admin. At worst you can gather all these recommendations and implement them manually in Power BI.

If you can use XMLA endpoints you can also simply choose apply fix but I prefer to see the fix before implementing.

[Performance] Set isAvailableInMDX to false on None-Attribute columns (6 Objects)

I wasn’t aware of this and all my Keys within the dimensions are flagged as issues here. Lets have a look at that in more detail.

This is rather more specific to Analysis Services. You can stop attribute hierarchies from being build on columns that don’t need them.

Basically all attributes have this set to on. It allows a column on a table to be used on a row or column axis of a visual so it can for example filter a measure. the important thing to note here is that these are structures only used queries tabular models (Analysis Services) IN MDX. Power BI always runs DAX queries so its not really specific to out Power BI Model. We can ignore this rule

And hit Show ignored if you want them back

[DAX Expressions] No two measures should have the same definitions

I have two measures in my DAX. I can right click on each to go to object. and I can then look at the expression

CALCULATE(DISTINCTCOUNT(fact[AccountID]),
FILTER(Accounts, Accounts[Name]<>"NA"))
CALCULATE(DISTINCTCOUNT(fact[AccountID]),
FILTER(Accounts, Accounts[Name]<>"NA"))

It goes to show that these things do happen. You can’t apply a fix script here. Its a case of finding out what uses these two items and ensuring we only use one. So the Question is here, is there an easy way of answering this question?

[DAX Expressions] Use the DIVIDE Function for Division

5 of my objects aren’t using the DIVIDE Function created especially for this. I clearly need to remember that I have a habit of doing this.

There is no fix script for this but I can go to object and to the Expression Editor

I have recreated as a proper DAX Function and you can simply hit the tick to update the code in your model.

[DAX Expressions] Filter Column Values with the Correct Syntax

I am unsure what this means so go to Manage Best Practice Rules in the top corner of the screen

Hit Edit Rule

And you can get to a description of the rule

Instead of using this pattern FILTER(‘Table’,’Table'[Column]=”Value”) for the filter parameters of a CALCULATE or CALCULATETABLE function, use one of the options below. As far as whether to use the KEEPFILTERS function, see the second reference link below.

Option 1: KEEPFILTERS(‘Table'[Column]=”Value”)
Option 2: ‘Table'[Column]=”Value”

Reference: https://docs.microsoft.com/power-bi/guidance/dax-avoid-avoid-filter-as-filter-argument
Reference: https://www.sqlbi.com/articles/using-keepfilters-in-dax/

This is fantastic. I can even go to the document reference to get more information. When I go to the object I can see that I have done this

CALCULATE([Number of Records],FILTER(Records, RecordType[Type]="Test"))

And immediately its clearly to me. CALCULATE is supposed to be fast for one implicit Filter. you use FILTER when its a little more complex and is slower. Although this works I have absolutely no need to use the filter.

CALCULATE([Number of Records],Records[Type]="Test")

And there you go. A faster bit of DAX. Current opinion of this tool. I am really quite taken with this. Its a bit of a gamechanger.

[Maintenance] Visible objects with no description

I have lots of these and absolutely. Each one should have a good description against it. I hadn’t even thought of that.

There are quite a few more rules that I have violated. I wont go through them all here but I am really excited to see how I can do so much more to streamline my model. this is absolutely fantastic.

Additional Script Rules

Note that there are several rules which require running an additional script. And these steps are specific to the Vertipaq Analyser which was added to DAX Studio. We can use the Vertipaq analyser combined with the best practice analyser

https://www.elegantbi.com/post/vertipaqintabulareditor

After reading the documentation above I took the script and ran it in Advanced Editor. I also saved it as a sample just in case.

This should create Annotations that you can see in Tabular Editor when you look at the objects but I cant see anything in my model. this was because you need to go to File – Preferences and Allow Unsupported Power BI features.

Now I can see Annotations

We are basically saying here that in Power BI desktop you can’t create Annotations. This can only be done within the external tool so there is a possibility that this could cause issues. From the documentation it does seem that this is fine to do, but you could now do other things that could cause problems in future. One to think about.

So, We have run report that basically creates the same stats as the Vertipaq Analyser in DAX Studio. How do we use it with Best Practice Analyser?

Avoid bi-directional relationships against high-cardinality columns

We need to create the rule over the script

Add a New rule. I’m simply Following the documentation at this point so lets get to the end of the rule creation

Its important to set the Applies to

If you don’t specify the Applies to you will get an error message (See image below)

I save the new Rule into the Rules for the Local user. But a question here is that, If I rerun the Advanced Scripting Code Sample again to get any new changes, will this wipe of the item created?

To test I go back to Power BI and Change a join to bi directional

And then go back to Tabular Editor and Update the Change Rerun the Best Practice Analyser and I am shown my one bi directional join as a best practice violation. this allows the user to say if its valid

Or if I should do something about it. I will do this in Power BI and rerun

Large tables should be partitioned

When you run the script for Avoid Bi Directional Relationships you also Create the Vertipaq rule for this best practice.

Frustratingly the documentation gets a little confusing at this point. the only rule that has a screen shot to show how to create it is Bi Directional functionality.

I’m making the assumption then that you don’t need to set up the rule and It should automatically appear. However my model isn’t big enough for this to be violated.

Is it a rule then? Got to ToolsManage PBA Rules .

Yes, there it is, I’m just not violating it. I was asked to look at this documentation for more information at this point but I am finding it a little confusing.

https://github.com/microsoft/Analysis-Services/tree/master/BestPracticeRules

Reduce usage of long-length columns with high cardinality

Again for this one, When you run the script for Avoid Bi Directional Relationships you also Create the Vertipaq rule for this best practice.

But you also have to run another script (again I saved as a sample)

Is this a rule then? Got to ToolsManage PBA Rules

This is good, the rule is there but I am not causing any violations to happen

Split date and time

In the above gitHub information you are told to run another script for this which I saved as a sample.

Best Practice Analyser was run again and it appears as a violation. However it is erroring

I know Split date and time is incredibly important to keep the models as speedy as possible so I would like to resolve this problem.

Rerunning the Scripts

Its been some time and we want to review another model. We also think it might be a good idea to rerun the original script in case the rules have changed.

Open another model in Desktop. then open Tabular Editor. Because I have enabled experimental Power BI features I get this warning.

Because we have already run the code we can simply open up best practice analyser to get the results. But what if we want to update for possible changes?

It would actually be good to have a way to know if there have been changes made to the rules?

Rerun the script. close and reopen like last time

Tools – Manage BPA Rules – Rules for the Local User

The additional rules are still there which is great. Lets run the analyser. 367 objects are in violation of 22 best practice rules.

Lots more to do then and all to make the model better and more efficient.

Conclusion

The First section of this just works and I’m incredibly pleased with it and I think it will really help.

The Vertipaq Analyser against Best Practice Analyser references are more difficult to follow but it seems to be that there are three extra scripts to run and one of the rules actually needs to be set up. the other rules are automatically created.

Obviously you have to allow for XMLA Endpoints in order to update your model using Tabular Editor so there are some things to think about here.

Having to set Allow Unsupported Power BI features is a worry for the Vertipac Analyser rules. Having gone through the process to try and figure out how it would sit in our review process I think you could do the following

Power BI – reporting on Items NOT in a filter

We are working on a report to look at Power BI activities and objects at the moment.

Lets have a look at the model

This is a test model with a date dimension (When the Activity occured)

  • A Report dimension (Details of the report used in the activity)
  • A Dataset dimension (Details about the dataset used in the activity)
  • And a Fact table of Activity metrics

We have a star schema with single direction joins to the fact. Power BI as we know loves a star.

Lets have a look at my sample data

Date

Just a sample of the date table

DataSet

Report

Fact

DataSet 3 and report 4 have never been part of an activity (Just for this exercise 01/01/2000 is our fake date because there is no activity)

The above logic is easy to do (Which items have never been used) but what happens when you want to look at the business question

Give me all the reports NOT used in September

As we can see from the metrics table report 2 and 4 were not used in September

So how do we create DAX that will allow us to look at these kind of Business Questions?

Base DAX

We want some base DAX measures that we can then build on top of

# reports = DISTINCTCOUNT(ActivityMetrics[ReportKey])
# Datasets = DISTINCTCOUNT(ActivityMetrics[DatasetKey])

And for the reports we never use we can either use the measure above with a filter

Or explicitly add a filter into a new measure

# Reports Never Used = CALCULATE([# reports],DimReport[neverusedFlag]=true)
# Datasets Never Used = CALCULATE([# Datasets],DimDataset[neverusedFlag]=true) 

Notice that I set Edit Interactions to off between the date slicer and the two cards because the cards are simply telling me how many reports and dashboards have never been used

So this part of the process is all fine.

Our Next Business Question is

Show me the reports and datasets used in September by date

This is another straight forward request

And both interact with the Date Filter.

And now to the question

Show me the reports and datasets NOT used in September

So we use our date slicer and we want to see items outside of that date slicer. We need to create a more complex measure to do what we need

Is Not in the Date Filter Flag = 
// assumes a slicer on DimDate[ActivityDateKey] and there is an implicit filter on the visual 
VAR VActivityDates = SELECTCOLUMNS(DimDate,"Dates", DimDate[DateKey]) 
VAR VDates = CALENDAR(MINX(DimDate,DimDate[DateKey]),MAXX(DimDate,DimDate[DateKey]))
RETURN IF(COUNTROWS(INTERSECT(VActivityDates,VDates)),0,1) 
// if no matching rows, return 1

Lets break the above down a little to understand what is happening

SELECTCOLUMNS

VAR VActivityDates = SELECTCOLUMNS(DimDate,”Dates”, DimDate[DateKey])

First of all create a variable. SELECTCOLUMNS

“Adds calculated columns to the given table or table expression.”

We start with the table they come from. then we give the name to the column in SELECTCOLUMNS “ColumnName”, Next comes the expression which in this case is the column reference.

CALENDAR

VAR VDates = CALENDAR(MINX(DimDate,DimDate[DateKey]),MAXX(DimDate,DimDate[DateKey]))

Next comes the VDates Variable. Calendar Returns a table with a single column named “Date” and it contains a contiguous set of dates. We need to provide the range of dates. In this case MINX finds the earliest date and MAXX finds the Maximum date in our date dimension

Now this should work with our slicer. In this case Min 01/09/2021 Max 30/09/2021

INTERSECT

INTERSECT(VActivityDates,VDates)

And now we get to the measure that will be RETURNED

“INTERSECT A table that contains all the rows in table_expression1 that are also in table_expression2”

COUNTROWS

COUNTROWS simply counts the number of rows within a table or an expression

IF

RETURN IF(COUNTROWS(INTERSECT(VActivityDates,VDates)),0,1)

So here we can see the intersect and if working together. If there are rows counted (True – There are rows then 0 else 1.

Still Struggling to understand? Me too. Lets draw it

Here is where I am a little confused. I’m not sure which part of the DAX works with the slicer. SELECTCOLUMNS or CALENDAR MINX MAXX? I’m making the assumption that CALENDAR takes the min and max of the slicer but its incredibly difficult to test so if anyone has any more insight on this that would be fantastic.

And we can now look at everything together in a report

We have used Is Not =1 in the ‘Is not in the Date Filter’ as out value and its always ever 0 or 1

But now we are asked another business question

How MANY reports did we not view in the current date slicer?

with the measure as it stands. It needs the Report Name or the Data set name to provide details

We can’t use what we have in a card because we cant add the implicit filter (Report Name etc).

So How can we do this one?

We can solve it using variables

Total Reports not in date Filter = 
//Clear all filters which are applied to the specified table.ALLCROSSFILTERED
var VAllReports =CALCULATE([# reports],ALLCROSSFILTERED(DimDate))
//Total within the date filtervar 
VInFilter = [# reports]
//All reports - Reports inside the slicer
Return VAllReports-VInFilter

Using ALLCROSSFILTERED brings back the total reports 4 as it removes all the filters

then we get back the total of reports in the date slicer and finally take in filter away from all the reports.

Out # reports is a measure made from distinct report IDs already. We now have everything we need to create these reports thanks to DAX.

Create your website with WordPress.com
Get started