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

Calculation groups with Tabular Editor for Power BI

Calculation groups look like a game changer, but are they? These have been available for Analysis Services tabular models for a while. Now Tabular editor Lets you build calculation groups for Power BI.

Taking one of our current pbix files as an example.

There are metrics for number of events, Financial metrics, timing metrics. Each one has to be added to Year to Date, Last month, last Year, Month to date etc metrics.

The same measures are repeated over and over again for the different metrics in the report.

And adding metrics in Power BI desktop isn’t a fast process. Its actually quite fiddly so adding the first lot of DAX required always takes a lot of time.

The Solution

As from July 2020 you can reach tabular editor Via External tools

  • You must have them installed at the right version for this to work.
  • You must enable Store datasets using enhanced metadata format in Options > Preview features

You are taken straight to the model you are connected to

Right click on Tables. Create New and Calculation Group

Time Intelligence

The best example to look at is time intelligence

This example is renamed to time Intelligence. What has been created is essentially a single column table.

the rows are based on all the calculation items that I already have in this model.

I initially recreate all the metrics from source as SUMs so they can be used within measures. Along with this I may have other base calculations like Distinct Count of IDs to make a number of events. Minimum, Maximum, Average etc.

right click Calculation items and choose new calculation item

In this example, go back to the original pbix and copy a time intelligence DAX measure

YTD Sales = TOTALYTD([Total Sales],DimDate[FullDateAlternateKey])

Total Sales is a measure created with SUM() using the Sales Amount metric from the data source

The measure is replaced with the scalar function SELECTEDMEASURE()

Instead of having to recreate this DAX over all your ID’s where necessary you only need to do it once

Scalar function – a function that returns one single value

The base DAX in this data set can now be added into Tabular Editor

You can imagine starting a prototype model with DAX for a specific metric. Then recreating as calculation groups so you can add all your other metrics without having to copy and paste the same measures over and over again

Tabular Editor currently has no intellisense so you wouldn’t write and test your DAX in here. this would be done in Desktop. If you wrote incorrect DAX in here and Saved to Power BI, you would then see an error icon in Tabular Editor.

Adding more time Intelligence from the original report

The rows are based on all the calculation items that are already have in this example model.

However the DAX needed to be amended because previously the Year to date measure was used to create variance (Two separate measures). We need to be able to give each measure the underlying measure by using variables in order to use them successfully.

So for example

YTD Sales Var % = DIVIDE([YTD Sales Var],[LY YTD Revenue])

Note that YTD Sales var and LY YTD Revenue Measures have already been created over the original year to date measure. We cant have this so we need to move to

YTD Sales Var % = VAR CY = [Total Sales] 
VAR TYTD = TOTALYTD([Total Sales],DimDate[FullDateAlternateKey]) 
VAR LYTD = CALCULATE(
                   TOTALYTD([Total Sales],DimDate[FullDateAlternateKey])
           ,SAMEPERIODLASTYEAR(DimDate[FullDateAlternateKey])) 
VAR VYTD = TOTALYTD([Total Sales],DimDate[FullDateAlternateKey])-
          CALCULATE(
                   TOTALYTD([Total Sales],DimDate[FullDateAlternateKey]),
          SAMEPERIODLASTYEAR(DimDate[FullDateAlternateKey])) 
RETURN DIVIDE(VYTD,LYTD)

Within this measure we call the base measure and create the variance% by calling variables.

Now these time Intelligence measures can be created in Tabular Editor

current
YTD
Last YTD
YTD Var

Notice that you cant use another variable within a variable. So our Total year to date has to be re added within the last year to date measure.

If we were doing this without calculation groups in mind we would simply reference the year to date measure we had created

YTD Var %

Again, Notice how we cant reuse a variable within a variable. So LTYD needs the full DAX inside the SAMEPERIODLASTYEAR DAX.

And Year to Date variance requires both last year to date and total year to date. this makes your DAX more complex than simply using a measure already set up.

In the above instance I could take out TYTD Variable but honestly. Im leaving it there as a reminder of what the DAX is for Year to Date

YoY Var %

LY
YoY

QTD

As you can see, each measure uses one base measure

And for the percentages I added format String Expression “0.00%”

Save the calculation group into the main desktop file

Lets see the calculated measure group in action using a matrix

Name from the time Intelligence group has been added to columns on the matrix.

Year and Month are in Rows

Total Sales, the base measure has been added to values

Name has also been added to a Slicer so you can turn off those you aren’t interested in.

To me, the big win of calculation groups is being able to create one measure and using it across multiple measures instead of creating the same measure over and over again.

And quickly switch to quantity by adding that measure to value

Issues with using calculation groups

  • formatting each item is much more difficult in tabular editor because you have to know the code. There is no help for creating these expressions.
  • Similarly, you cant develop DAX Here, you need to do it first within power BI Desktop
  • Your DAX has to become much more complex, rather than creating a measure and then referencing this measure within another measure. Like Year to Date and Last Year to Date

You cant create the matrix with Months in columns

This is using the measures created in the data set. As you can see, the Months are shown as columns and the measures are also displayed

You cant do this with column groups.

Lets have a look at some visuals already created with time Intelligence to see if we can recreate

Variance Waterfall chart

This is an example of a waterfall variance chart for year to date. If we didn’t have measures outside of a calculation group, could we still pull off the exact same solution?

Table: No you cant recreate the table. You would have to move to a matrix

With a filter on name to the measures you are interested in. It doesn’t appear that you can turn off grand totals though so we have some information there that we don’t need. the Total column.

So, ok but not great

Waterfall visual; there doesn’t appear to be any way you can recreate this. so to create my variance visuals there has to be a measure that can be used.

Conclusion

Calculation groups seem to only for for one visual. the Matrix. Where you can add the base measure to values and the Name to columns.

Even the matrix cant be fully configured to how it would normally be shown with months in columns and the measures underneath.

In order to use time intelligence for all other visuals, there still needs to be measures in your model to use.

It would be a great idea if you could fully get rid of your duplicated measures but you cant.

Because of this, I still don’t have the results I need to spend time creating calculation groups.

Other people seem to be seriously excited about this though so maybe I am missing something. Please let me know if I am, I would be very interested to get another point of view

Power BI July 2020 Mini Blog – Launch External Tools

This is part of the Semantic modelling for Power BI Programme and its a big one. Very exciting

The first thing you should see is External Tools on your ribbon (Making sure you are on the Jusy 2020 release of Desktop)

In the first instance, this doesn’t appear to be avilable. Why is this missing?

This is because you may not have the latest releases of the External tools.

These tools are:

  • ALM toolkit
  • DAX Studio
  • Tabular Editor

Currently, I only have DAX Studio but this isnt enough to give me the External tools Menu item? Whats going on?

DAX Studio

My Current release is

Going to DAX studio there is a new version

https://daxstudio.org/

this is the release you need for Power BI. Click on the installer to download the new release (Power BI Has been closed down during this process)

Do you want to run this file? Click Run

Once installed open up Power BI

External Tools is now an option

However DAX Studio is greyed out

go into Preview Features and make sure Store datasets using enhanced metadata format is turned on.

Check the External Tools Folder

C:\Program Files (x86)\Common Files\Microsoft Shared\Power BI Desktop\External Tools

I now have the DAX Studio JSON file in the External tools folder

ALM Toolkit

http://alm-toolkit.com/

After installing, take another look at your external template Folder

Tabular Editor

https://tabulareditor.com/

Again, a new JSON file is added to the folder

So now we have all three tools connected into Power BI. Lets have a look at what they can do for you. the following is a few basics from each tool

DAX Studio

With a Power BI pbix file open. go to External tools and click on DAX Studio. It will automatically connect to the data set you have open

Go to Advanced and View metrics

This is a great way of using the Vertipaq Analyser to look at Tables, Columns, relationships, partitions and a Summary of your data model

You get to see which items have low and high cardinality. What items are taking up too much space in your model and really helps you get a feel for what you can do to make your model better

ALM Toolkit

The ALM toolkit comes from the Analysis Services space. Due to Power BI encompassing an Enterprise architecture along with self service, we can now start to use these community tools with Power BI datasets.

Its a schema comparison tool for your datasets.

Use it many scenarios such as incremental Refreshing, metadata only deployment (Now available for Premium and Pro)

This is another big hitter and requires a much more in depth look

Tabular Editor

Again, tabular Editor will connect to the tabular database you have open within your pbix file.

This is an excellent tool to help with quickly modifying tabular models.

If you do anything in here, like add measures into a folder, you will see the change straight away in Power BI Desktop.

You can also test and create measures in here which can offer a much easier way to do this than within power BI Desktop.

basically, using this tool you can automate some of the more tedious tasks and save lots of time.

Like DAX Studio. You can also analyse your entire model to get a feel any improvements you can make.

There are some great videos out there on how to make the most out of this tool and if you use Power BI heavily this is a must have.

Create your website with WordPress.com
Get started