Forum Discussion

Datatouille's avatar
Datatouille
Solution Sage
8 years ago
Solved

Understanding the Vertipaq Engine

Hello   I am doing a deep dive into the Vertipaq engine and I have a few questions regarding a concrete use case.   I have a .pbix file with two Fact Tables: - Raw (300k rows and 17 columns) wit...
Need Help
  • marcorusso's avatar
    marcorusso
    8 years ago
    1. Columns Hierarchies are internal structures storing a sorted textual version of the content of each column. It is required for MDX, and it is now used also internally for optimizing certain queries (in other words - we cannot get rid of it, even for columns we never use un MDX)
    2. See above - even numbers are converted into strings
    3. I don't understand the question - but VertiPaq Analyzer reports the memory consumption of the data model, whereas the size of the files could be different (serialization and additional structures).

     

    I hope it helps!

     

    Marco Russo - SQLBI

marcorusso's avatar
marcorusso
Most Valuable Professional
8 years ago
  1. Columns Hierarchies are internal structures storing a sorted textual version of the content of each column. It is required for MDX, and it is now used also internally for optimizing certain queries (in other words - we cannot get rid of it, even for columns we never use un MDX)
  2. See above - even numbers are converted into strings
  3. I don't understand the question - but VertiPaq Analyzer reports the memory consumption of the data model, whereas the size of the files could be different (serialization and additional structures).

 

I hope it helps!

 

Marco Russo - SQLBI

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Dear Marco,

     

    I hear you in this video sayng that is possible to disable Hier Size in SSAS, when not used, of course.

    And now I can also see the green tickbox just on top, which makes me think that is really possible.

    Could you be so kind to address me to some specific article where I can see how to do that?

    Also let me thank you for all the availability, support and videos.

    You are making a great job among PBI and Dax world! A Big thank you.

    • marcorusso's avatar
      marcorusso
      Most Valuable Professional
      5 years ago

      You should set the "Available In MDX" property to False. The internal property name is "isAvailableInMdx" and it is described here: Chris Webb's BI Blog: How The New IsAvailableInMDX Property For Analysis Services Tabular Can Reduce Memory Usage And Speed Up Processing Chris Webb's BI Blog (crossjoin.co.uk)

      This property is available only in Tabular Editor and Visual Studio, you cannot modify it from Power BI Desktop.

      Disabling it in Power BI Desktop is not supported - it could work, I never tried it. It is supported for Analysis Services (Azure and SSAS). It should work on Power BI Service as well through XMLA endpoint.

      Don't disable this feature on columns that could be used as filter or exposed as visible columns to Excel. Disable only when a column is only used in an aggregation like SUM (...) and it is never visible to user.

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        I will definitely follow your recommendations. Thanks a lot for replying quickly.

        Hopefully I will get the best from VertyPaq analyzer and improve performance of the Tabular Model which we have.

        Thanks again.