cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Koushikrish
Helper I
Helper I

Question regarding IsAvailableforMDX property on a Model.

Hello,

 

Just to provide an overview before proceeding further, we are a BI team who have recently switched to Tabular Editor as our main Dev tool for PBI model development.

While browing the capabilities available on TE3, we came across this article about the IsAvailableforMDX property :

 

https://blog.crossjoin.co.uk/2018/07/02/isavailableinmdx-ssas-tabular/

 

This property is enabled by default on PBI Tabular models which in turn allows automatic creation on Attribute Heirarchies on the tables/columns.

 

We tested this on one of our test data models by disabling this property on all columns of 4 Dim tables and 1 Fact table. The results are below:

 

Before:

Koushikrish_2-1642620102270.png

 

After:

Koushikrish_4-1642620258808.png

 

 

 

As you can see, disabling this property reduced the over table and column size of these 4 tables. 

The 'Hier Size' (size of heirarchy) went down to 0 on all these tables that helped bring down the overall table and column size for all 5 tables. This is understandable as we had disabled the IsAvailableforMDX, eventually avoiding the model create any attribute heirarchies.

 

But as we can also see, the 'Dict Size' (size of Data Dictionary) also came down for all tables (esp. for the Dim tables), which helped in bringing down overall table/column size. We have been trying to understand how disabling the IsAvailableforMDX property brings down the DD size as it doesn't correlate to the heirarchy creation.

 

Can anyone advise on how and why the Data Dictionary size reduced and what actually changed? Please let us know if you need more details.

 

2 ACCEPTED SOLUTIONS
Daryl-Lynch-Bzy
Super User
Super User

@Koushikrish 
This setting will impact Excel connections to the Power BI Analysis Service Tabular model.  Excel relies on MDX to generate query against the DataSet.  If this is set to False, excel users will not be able to select the column in a pivot.   It is the equivalent to hiding a column, but it will also remove the search index hence the size reduction.  It is recommended to use for Measure columns (i.e. things you will aggregate by; not group by).

@marcorusso explain this in the video round the 40 min mark.  Optimizing Power BI model size and memory by using DAX Studio metrics and VertiPaq Analyzer by Marco...

View solution in original post

The dictionary size shouldn't change, but you should make sure you did your test in the same conditions. FOr example, you might have run a full refresh after changing the IsAvailableInMDX property and this removes any unused entry from the dictionary - which could include unused entries in other conditions, such as (but not limited to) an incremental refresh.

Try to run a full refresh of the whole database with the two settings and then compare what you see at that point.
If you would see a significant difference in the dictionary at that point, it would be strange and deserve more investigation.

View solution in original post

4 REPLIES 4

You can rebuild the dictionary by using Defragment instead of Refresh - Calculate does not reset the dictionary.

See RefreshType Enum (Microsoft.AnalysisServices.Tabular) | Microsoft Docs

The dictionary size shouldn't change, but you should make sure you did your test in the same conditions. FOr example, you might have run a full refresh after changing the IsAvailableInMDX property and this removes any unused entry from the dictionary - which could include unused entries in other conditions, such as (but not limited to) an incremental refresh.

Try to run a full refresh of the whole database with the two settings and then compare what you see at that point.
If you would see a significant difference in the dictionary at that point, it would be strange and deserve more investigation.

Hi Marco,

 

Thanks for the reply. I am doing the following steps now:

 

1. Set the property back to True.

2. Refresh the model.

3. Run Vertipaq analyzer.

 

I will repeat the same with the property set to False and compare the results.

Regarding the refresh : Does it actually have to be a 'Full Refresh' of the model (it takes over 3 hours for this entire model to refresh)? Considering that we are just going to recalculate the dictionary after the updates wouldn't a 'Calculate' refresh of the model suffice? Please advise.

Daryl-Lynch-Bzy
Super User
Super User

@Koushikrish 
This setting will impact Excel connections to the Power BI Analysis Service Tabular model.  Excel relies on MDX to generate query against the DataSet.  If this is set to False, excel users will not be able to select the column in a pivot.   It is the equivalent to hiding a column, but it will also remove the search index hence the size reduction.  It is recommended to use for Measure columns (i.e. things you will aggregate by; not group by).

@marcorusso explain this in the video round the 40 min mark.  Optimizing Power BI model size and memory by using DAX Studio metrics and VertiPaq Analyzer by Marco...

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Difinity Conference

Difinity Conference 2022

Difinity is the largest Microsoft Data, AI, Power BI, Power Platform and Business Applications Conference in New Zealand.

European Share Point Conference

European Share Point Conference

This conference returns live and in-person in Copenhagen this 28 November-01 December with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Top Solution Authors