none
PowrPivot Performance comparison

    Question

  • Hi There,

    I try to get advice on which data source will performance better for end user using Excel PowerPivot. I have SQL server data source in dimensional model (Fact table, dimensions) , I also have traditional SSAS cube developed based on my dimensional database, I also have new SQL 2012 Tabular model developed.

    Excel PowerPivot will be used for users, obviously I can connect all three data sources through powerpivot, but which one performance better in terms of retrieve time, memory usage, filter?

    Thanks


    Don

    Wednesday, June 19, 2013 7:00 PM

Answers

All replies

  • I would use SQL Server. Probably Tabular is faster, but reading one table at a time from SQL Server you shouldn't see a big difference. Moreover, with the current connection technology (based on SSAS ADODB driver) you get column names that are very bad, whereas in SQL Server you can provide to the user nice views with good names.

    Thursday, June 20, 2013 4:42 PM
  • Marco,

    Thanks for insight. Only thing I am not sure is that.

    If we import from SQL Server, does client pc need lots of memory since whole data set is at client, does this impact performance if data is too big.

    If we import from SSAS, I am guessing maybe ssas server will take memory hit, not client pc, same with tablular model.

    Lots of our users only have 32 bit machie with 2 or 3 gb of memory.

    Any more advice?


    Don

    Friday, June 21, 2013 3:59 PM
  • whenever you Import data into PowerPivot you hit the memory of the Client - this is regardless of the source

    PowerPivot always stores the whole dataset in Memory


    - www.pmOne.com -

    Monday, June 24, 2013 1:05 PM