locked
SSAS Tabular performance issue with Powerbi RRS feed

  • Question

  • Hello community,

    My company has just stopped supporting our Cube and we are moving to tabular/Powerbi. I have picked up a relatively small tabular model and i need to Port some MDX queries over to Dax in the tabular model. As you may imagine we are hitting some performance issues around our tabular/measures. unfortunately, due to tight security we are not allowed to download anything not microsoft for troubleshooting.

    The tabular model has a basic Star Schema design with a Fact table with 17 million rows. While building a couple of dax queries i have noticed that they are running pretty slowly. Basic Sums are working fine but anything with Sumx or Summerize kills the process and we get a "Allocation Failure, Memory error". These Sumx/Summerize queries are required as we need Weighted Averages on WIP which requires plenty of calculations.

    Specs:

    The SSAS server sits on a VM & is running with 8 Cores and 24GB of Ram with the default Tabular model configuration settings. I run powerbi using "Connect to Live" and most of our measures run in a matrix using a hierarchy of dimensions.  I watch the resource monitor on the VM box when measures fail due to out of memory issues and the resources are hardly being used.

    Since the resource monitor is not actually showing any resource constraints during the memory error in powerbi does this mean i have set something up incorrectly? 

    Any help would be greatly Appreciated. 

    Thanks for your time.

    Friday, January 24, 2020 4:28 AM

All replies

  • Hi BLDataCrank,

    Thanks for your question.

    >>Since the resource monitor is not actually showing any resource constraints during the memory error in >>powerbi does this mean i have set something up incorrectly? 

    Which product does the feature "resource monitor" belongs to? Based on my knowledge, resource monitor has nothing to with Tabular instance. If you need to get more resources, you need to reconfigure the server properties of SSAS instance.

    Memory properties

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, January 24, 2020 8:14 AM
  • Resource monitor Meaning the Task Manager\Open Resource monitor that you can get to when you remote into the server using Task Manager. The CPU and Memory never goes over 15% while crunching these queries. I have the default configuration set in SSAS currently, is there a setting i should change?
    Wednesday, August 19, 2020 9:01 PM
  • It's hard to tell without a deeper analysis, but all the symptoms are likely to indicate you have a measure that materializes too much data during calculation, like doing something like:

    AVERAGEX ( FactTable, [measure] )

    You don't have materialization when you iterates a large table and you only use column references, but you have the problem when you invoke a measure reference.

    You should review your DAX calculation avoiding materialization. DAX is very different from MDX, I strongly suggest to spend time on DAX before having to pay a huge technical debt for it.


    Marco Russo (Blog, Twitter, LinkedIn) - sqlbi.com: Articles, Videos, Tools, Consultancy, Training
    Format with DAX Formatter and design with DAX Patterns. Learn DAX!




    Thursday, August 20, 2020 9:01 AM