Forum Discussion

Koushikrish's avatar
Koushikrish
Helper I
4 years ago
Solved

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 capa...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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 - YouTube

  • marcorusso's avatar
    marcorusso
    4 years ago

    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.

marcorusso's avatar
marcorusso
Most Valuable Professional
4 years ago

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.

  • Koushikrish's avatar
    Koushikrish
    Helper I
    4 years ago

    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.