locked
Pivot Table based on Power Pivot data model, memory issue RRS feed

  • Question

  • Hi,

    I have the following data model in Power Pivot Excel 2016 (64bits) :

    Something quite classic.

    I'm doing a Power View report on it and everything is running well, fast and flowing.

    But my customer would like the same report in a tabular format through a Pivot Table.

    Si I did.

    I insert a Pivot Table and in the Design menu, for Report Layout i choose Show in Tabular form.

    I also switch off all the Grand Totals, subtotals, blank rows etc

    Except that when I want to add my last column in the Rows area ...I'm exploding my memory and I'm getting a warning issue that a 64 bits Excel could handle my operation. But I'm already with a 64 bits Excel.

    I also have a second problem : filters are not working.

    I really don't know from where this issue is coming especially I don't think it's coming from my data model as it's running fine with PowerView.

    Any idea ?

    Thx for any help!


    Bertrandr


    • Edited by Bertrandr Friday, December 9, 2016 1:19 PM filters
    Thursday, December 8, 2016 7:57 PM

Answers

All replies

  • Hi Bertrandr,

    Based on your relationship screenshot, there are large mounts of date in Power Pivot table. "exploding my memory" means it stop work or take up a lots of memory? Please share more details for further analysis.

    Best Regards,
    Angelia

    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, December 9, 2016 9:20 AM
  • Hi Angelia,

    Well it's using about 98% of my memory.

    Making my computer not responsive.

    Then after a while i'm getting an Excel pop up window telling me it's can deal with the operation and i should switch to 64bits (but as i said i'm already on 64bits, and i have 16Gb of available memory).

    I don't have so many dates. But a lot of decimals.


    Bertrandr

    Friday, December 9, 2016 9:46 AM

  • Bertrandr

    Friday, December 9, 2016 10:27 AM
  • Unfortunately the MDX generated for pivot tables (before Excel 2016) can make it very slow (like described here: https://blog.crossjoin.co.uk/2016/07/08/excel-2016-pivottable-mdx-changes-lead-to-big-query-performance-gains/  

    So if you're after a flat table, you can try to use a Reverse Linked Table instead (no need to actually link it back, and you can use slicers as well):

    http://www.sqlbi.com/articles/linkback-tables-in-powerpivot-for-excel-2013/


    Imke Feldmann TheBIccountant.com



    Friday, December 9, 2016 6:23 PM
    Answerer
  • Had a look at your model again and wouldn't be surprised if the key to a performance gain would lie in remodelling.

    Does Value 6 kill the performance? Do your really need all the columns from that table? In Power Pivot the number of columns and their cardinality is the main factor for performance.

    And even if you need all the columns, you could improve the performance of your current report by splitting the table into 2 tables: First table with the key and the field for your report and the second table with the key and the other columns. Then the performance should be OK.

    Also have a look at the memory-analysis tool that is described here: https://www.powerpivotpro.com/2016/01/restructure-data-table-improved-compression/

    And on a last note: Your model doesn't look "quite classic" to me, if your "bridge"-table is what it says and not a actually a fact table. Normally, I'd expect the many-to-many relations to be modelled in a way that the fact-table has a many-to-one-relation with a dimension table which is then connected via a bridge-table to another dimension table. But in your model your fact-table would sit on a 1-side instead (like here for example: http://www.sqlbi.com/articles/optimize-many-to-many-calculation-in-dax-with-summarize-and-cross-table-filtering/ ).


    Imke Feldmann TheBIccountant.com


    Saturday, December 10, 2016 7:18 AM
    Answerer
  • Hi Imke,

    I forgot to mention that the model is imposed to me by my customer architects.

    I can't modify anything. It's this or ... They're going on Business Object.

    "Value 6" is not the specific one causing trouble.

    In some cases this column is working fine and I don't get any issue.

    What it's hard for me to understand is that in PowerView or PowerBI Desktop everything is running fine.

    It took me 30 seconds to build the report in PowerView and PBI as well. And the values are accurate.

    In Excel Pivot Table even the Filters are not working ?!?

    Thanks for your time and useful links !


    Bertrandr

    Saturday, December 10, 2016 7:32 PM
  • It's just that the Excel-Pivots (on the data model) until 2016 create this inefficient elements, so everything else is fine.

    If the linkback-table doesn't work for you, as you need the pivot-elements, you can try to build a pivot on the linkback-table. As this would be a "normal" pivot, you will not experience the performance-problems.


    Imke Feldmann TheBIccountant.com

    Saturday, December 10, 2016 8:28 PM
    Answerer