Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Resident Rockstar
Resident Rockstar

@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
Resident Rockstar
Resident Rockstar

@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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors