Following on from my post the other week about the new RowsetSerializationLimit server property, I thought it would be a good idea to write about why the new IsAvailableInMDX property (announced in the same blog post) is so important. In fact, I would say that everyone using Analysis Services Tabular 2017 (CU7 or higher) or Azure Analysis Services should spend some time investigating it because the potential benefits in terms of reduced memory usage and faster processing times are significant, especially for larger models.
First of all, what does it actually do? As the blog post says, it allows you to stop attribute hierarchies from being built on columns when you don’t need them. But what are attribute hierarchies? They are structures that are used only when you are querying your Tabular model using MDX; Excel PivotTables, for example, generate MDX queries when they are connected to Analysis Services Tabular whereas Power BI always generates DAX queries. An attribute hierarchy allows a column on a table to be used on the rows or columns axis of an MDX query, and in Excel that means you will be able to drag that field onto the rows or columns area of a PivotTable. Attribute hierarchies are used by some DAX functionality too – for example the TreatAs() function (at least for now) needs them to be present to work. Frustratingly, the DAX functionality that does need attribute hierarchies is not documented.
To give you an example, consider a Tabular model that contains a table with three columns, Product, Customer and Sales, and a measure that sums up the values in the Sales column.
I can query this Tabular model in Power BI, for example by creating a Matrix visualisation:
I can also get the same values out using an Excel PivotTable:
Now the reason I can create this PivotTable is that Analysis Services Tabular has created attribute hierarchies on the Customer and Product columns. However, the important thing to understand is that Analysis Services Tabular creates attribute hierarchies on every column on every table by default, including the Sales column. This allows me to create a PivotTable like this, with the distinct values from Sales on the rows of the PivotTable:
You’re probably thinking, why would I ever want to use Sales – a measure column – like this? And the answer is you probably wouldn’t, even though Tabular allows this by default. What’s more, building the attribute hierarchy for Sales makes processing slower and the resulting hierarchy uses memory, so all this comes as a cost. The IsAvailableInMDX property is therefore very useful because it allows you to stop attribute hierarchies from being built on columns like Sales where they serve no real purpose.
Unfortunately at the time of writing SSDT doesn’t allow you to set the IsAvailableInMDX property but the good news is that the latest versions of Tabular Editor do:
Setting IsAvailableInMDX to false for the Sales field has no impact at all in Power BI, so long as you are not using functionality like TreatAs() that needs it. In Excel, it just means that it is no longer possible to drag Sales onto rows or columns in a PivotTable – the Sales Amount measure still works:
As a result, there are two recommendations that can be made:
- If you are not using any client tools that generate MDX queries (such as Excel) or you want to prevent your users from using them, and you can be sure that it does not affect any of your existing Power BI reports or DAX calculations, you can set IsAvailableInMDX to false on every column of every table
- If you are using client tools that generate MDX you can still probably set IsAvailableInMDX to false on every measure column and not lose any important functionality
How much of an impact will doing this have on processing times and memory usage? It depends, but it could be a lot. The anecdotal evidence on Twitter is promising:
I did my own (not particularly scientific) test using a table with five million rows and ten columns, each of which contained the integers between one and five million. Here’s the M query to generate such a table without the need for an external data source:
[sourcecode language='text' padlinenumbers='true']
let
Source =
Table.FromColumns(
List.Repeat(
{{1..5000000}},
10
)
),
#"Changed Type" =
Table.TransformColumnTypes(
Source,
List.Transform(
Table.ColumnNames(Source),
each {_, Int64.Type}
)
)
in
#"Changed Type"
[/sourcecode]
On my laptop, with IsAvailableInMDX set to true for all ten columns, a full process on this table took around 105 seconds and the table size reported by Vertipaq Analyzer was 381MB. After changing IsAvailableInMDX to false for all ten columns, the time for a full process went down to around 81 seconds and the table size was down to 191MB.
In summary, this is one of those seemingly obscure technical changes that turns out to be way more useful than you might think. If you test out setting IsAvailableInMDX on your Tabular model, please leave a comment letting me know what kind of impact it had!
[Thanks to Daniel Otykier for providing a lot of information for this post]
UPDATE 2022: the issue with TREATAS was fixed a long time ago, so you should be able to use all DAX functionality when IsAvailableInMDX is turned off.
Hi Chris, does this also apply to datasets in the Power BI service – when using Analyse in Excel? I wonder if you hide a column if that switches off this as well (as the column no longer appears in Excel).
It’s only available in Analysis Services for now, but I guess when Premium is exposed via XMLA as an Analysis Services model you’ll be able to set this property on datasets stored in Premium.
I do not see this property for my column. Any clue why it is not seen?
Great post! Thank you!
I think this feature was supposed to be in SSAS 2016! (column usage property DAXusage)
we’ve got the CU8 and did change isAvailableInMdx to false on 60 mln rows table. It did work and now it is no longer possible to drag numeric fields onto rows, but size of the 60 mln column has not changed. Why is it so?
It’s not the size of the column you should be looking at, but the size of the attribute hierarchy built on it. This is visible as a separate metric in Vertipaq Analyzer.
Thank you
I’m looking at [Columns Total Size], maybe the size have been changed without processing, just after setting the property to false?
You need CU8 and not CU7
https://support.microsoft.com/en-us/help/4295097/cant-change-isavailableinmdx-column-property-to-false-ssas-tabular-mod
I understand that I should set this on measures columns.
Should I set it on hidden columns too ?
Should I set it on primary key columns too ?
Should I set it on foreign key columns too ?
You should set it on any column that you don’t need to drag and drop into the rows or columns area of a PivotTable. So definitely yes, set it on hidden columns; the others – well it depends.
A bit of input on trying out IsAvailableInMDX in real life :-)…..I tried turning it off (false) for an entire model. Found out that columns that are sorted or used as sort by should not be changed, same goes for columns used in hierarchies. Might be there’s another option than leaving IsAvailableInMDX to true, but haven’t had time to check.
Then updated a model and tested, size was about 10% less overall which was good, processing speed didn’t change, but we do a Full process every hour, so didn’t expect much.
After deploying we discovered that all existing reports in Power BI based on the model stopped working. Got an error that each Individual table was missing a reference of sorts (dont have the error message, forgot to screenshot it). But all new content/visuals created in Power BI worked fine, it only related to the ‘old’ content in Power BI generated before we changed the model.
We then switched all columns back to true and everything worked again after a Process Default.
So be cautions before trying this out on production 🙂
Hi Chris
Thank you for an excellent Blog.
I have now tested this feature and was very hopeful, unfortunately it did not turn out as I hoped. I am running SSAS Tabular with CU10 and did not see any reduction on memory usage on the Column Hierarchy Size in Vertipaq Analyzer. The table and column size actually increased a little, maybe caused by different compression options etc. between the runs. The only thing I did gain was faster processing time due to that it did not build the Attribute Hierarchies on the measures anymore (examined the execution log), which indicate that the used memory should be less…
All my measure columns is hidden and used in calculated measures like
Market Value := CALCULATE (
SUM (Balance[MarketValue]);
LASTNONBLANK (
Date[Date];
CALCULATE(
DISTINCTCOUNT(Balance[DateKey])
))
and other parallel period measures(YTD,QTD,MTD,PY). My cube is partitioned by year and contains 200M rows, I did a Full Process on all partitions.
Is there some other way to see the gain, other than Vertipaq?
Best regards Michael
Vertipaq Analyzer is definitely the right way to measure this. Can you maybe try a Process Clear before doing your Process Full?
Hi Chris!
Do you know when/if the IsAvailableInMDX property will be available to set in SSDT?
No, sorry.
Hi again Chris!
I’m experiencing some issues in Power BI when I’ve set IsAvailableInMDX=False on a column called “Receipt No” in my FactSales table. I can no longer slice sales by Receipt No, but I thought that the property would only effect the functionality in MDX-applications like Excel?
I can still drag out “Receipt No” and SalesAmount into the same table/matrix/graph etc so that works, but the slicing don’t work anymore. The Power BI error is “Cannot query internal supporting structures for column Receipt No” Please refresh or recalculate the table FactSales.
You wrote in this article “you can be sure that it does not affect any of your existing Power BI reports or DAX calculations, you can set IsAvailableInMDX to false on every column of every table”
Is this how it’s supposed to be, or am I missing something?
If I have a typical star schema, do I need an attribute hierarchy to exist on fact table columns that are keys with relationships to dim tables? In other words, if I set isAvailableInMdx to false for key columns in a fact table, will this break the functionality of relationships?
@Daniel Torres I had this same exact question, setting this property on dim columns in the fact tables. I tried it out and it definitely won’t break (we have these hidden anyway). I just don’t know if there is any query performance hit.
Hi Chris, can we do it in Power BI Desktop anyway?
I’ve used this technique to reduce our refresh time from 4.5 hours to 75 minutes! However since making this change to a pbix file is not supported by Microsoft I would prefer to do it in a post-deployment TMSL script.
I’m really struggling to find the syntax for that. It seems that you have to use an “alter” command and that you have to supply the full table definition (which is a pain because I only want to alter one property of one column). However when I try to modify that property I get this error message:
The JSON DDL request failed with the following error: Cannot de-serialize Table. The JSON input is not properly formed. Check path ‘alter.table.columns[0].isAvailableInMDX’, line 16, position 10.;After parsing a value an unexpected character was encountered: “. Path ‘alter.table.columns[0].isAvailableInMDX’, line 16, position 10..
Any thoughts. Is this even possible or do we have to take our chances with unsupported changes to the pbix?
PS I have tried multiple different capitalisations of “isAvailableInMDX”