none
SSAS cubes + Power Pivot .... or SSAS cubes + Excel 2010 Pivot Tables

    Question

  • Hi everyone,

    Ive been reading a lot about Power Pivot.  But I still cant decide if this tool suits for my enviroment.  I hope you can help me.

    I have SSAS cubes and my user wants to have a dynamic way to add or take out columns (ad-hoc style) from a huge Excel sheet.  When I say huge we are talking about 72 columns (the source for this is a virtual cube that involves 3 other cubes).   The number of rows are a huge number also because of the granularity of the reports... (almost transactional... I know its not BI ... but don´t ask =/  )  .... we can talk about 400,000 registers per month or 10,000-15,000 per day... they want to refresh the data every 6 hours... they will use 32 bit lap tops to access either Excel Pivot Tables nor Power Pivot for excel

    My question here is... if I have already cubes in SSAS what are the advantage or disadvantage of using Power Pivot for Excel instead of the Excel Pivot Tables?  what is more suitable for this situation? 

    I found in a blog that the vertipaq is only for cubes developed in power pivot so it will not apply for my case as an advantage since I have Analysis Services Cubes.

    On one hand Excel has a 1 million limit row so my report wouldn´t be accurate since I will left out many rows, and Power Pivot has 2 GB which I dont know how many rows and colums will I have to have in order to reach this limit therefore I don´t know if thier 400,000 rows x 72 columns will be even possible to achieve when they want to see a complete month... I understand that for a day wouldnt be so much trouble (in case the 72 columns don´t afect) ...

    If I go for Power Pivot connected to SASS cubes I will have to throw a huge MDX query to bring all the 72 ... once again... I don´t know how the performance would be...

    If I leave a link to the excel file with the power pivot information loaded,  the users will be able to save this file and refresh the information whenever they want... once again... I can not think of 40 users quering MDX to bring the amount of information I explained before...  meanwhile using Excel I was thinking that I can drop the 72 columns and keep the structure but not to load the amount of information... and I wonder if the refresh in this case is needed or as soon as I open the Excel the information is refreshed...?¿

    So...what do you think, wich tool should I use, is there any point of using Power Pivot here, will I make it happen with Excel?? 

    When Im reading again the lines i wrote... I just want to quit both ideas and beg for them to develop their reports using the ERP tools ...  but as far as I know is not possible (business decisions)... does this will have a happy ending at all?

    Thank you in advanced...

    Regards,

    Adriana

    Wednesday, May 02, 2012 10:43 PM

All replies

  • Hi everyone,

    Ive been reading a lot about Power Pivot.  But I still cant decide if this tool suits for my enviroment.  I hope you can help me.

    I have SSAS cubes and my user wants to have a dynamic way to add or take out columns (ad-hoc style) from a huge Excel sheet.  When I say huge we are talking about 72 columns (the source for this is a virtual cube that involves 3 other cubes).   The number of rows are a huge number also because of the granularity of the reports... (almost transactional... I know its not BI ... but don´t ask =/  )  .... we can talk about 400,000 registers per month or 10,000-15,000 per day... they want to refresh the data every 6 hours... they will use 32 bit lap tops to access either Excel Pivot Tables nor Power Pivot for excel

    My question here is... if I have already cubes in SSAS what are the advantage or disadvantage of using Power Pivot for Excel instead of the Excel Pivot Tables?  what is more suitable for this situation?

    I found in a blog that the vertipaq is only for cubes developed in power pivot so it will not apply for my case as an advantage since I have Analysis Services Cubes.

    On one hand Excel has a 1 million limit row so my report wouldn´t be accurate since I will left out many rows, and Power Pivot has 2 GB which I dont know how many rows and colums will I have to have in order to reach this limit therefore I don´t know if thier 400,000 rows x 72 columns will be even possible to achieve when they want to see a complete month... I understand that for a day wouldnt be so much trouble (in case the 72 columns don´t afect) ...

    If I go for Power Pivot connected to SASS cubes I will have to throw a huge MDX query to bring all the 72 ... once again... I don´t know how the performance would be...

    If I leave a link to the excel file with the power pivot information loaded,  the users will be able to save this file and refresh the information whenever they want... once again... I can not think of 40 users quering MDX to bring the amount of information I explained before...  meanwhile using Excel I was thinking that I can drop the 72 columns and keep the structure but not to load the amount of information... and I wonder if the refresh in this case is needed or as soon as I open the Excel the information is refreshed...?¿

    So...what do you think, wich tool should I use, is there any point of using Power Pivot here, will I make it happen with Excel??

    When Im reading again the lines i wrote... I just want to quit both ideas and beg for them to develop their reports using the ERP tools ...  but as far as I know is not possible (business decisions)... does this will have a happy ending at all?

    Thank you in advanced...

    Regards,

    Adriana

    Wednesday, May 02, 2012 10:59 PM
  • My question here is... if I have already cubes in SSAS what are the advantage or disadvantage of using Power Pivot for Excel instead of the Excel Pivot Tables?  what is more suitable for this situation? 

    Hello Adriana,

    Both SSAS and PowerPivot are multi dimension datasources and Excel PivotTable is a tool to show up MD data.

    So the question is not PowerPivot vs Excel PivotTable; it's SSAS vs PowerPivot as datasouce; but in both cases you would use Excel PivotTable as a frontend.


    Olaf Helper
    * cogito ergo sum * errare humanum est * quote erat demonstrandum *
    Wenn ich denke, ist das ein Fehler und das beweise ich täglich
    Blog Xing

    Thursday, May 03, 2012 5:45 AM
  • Hi, Thank you for your answer.  I understand what you are saying in the end I will use Excel Pivot Tables as Front, the difference would be how to access the OLAP cube,    should I use a direct conection from Excel? or should I write an MDX query from power pivot to the OLAP cube... ?  what way would be better for the requirement of showing in a tabular form 72 columns x more than a million of records... in therms of performance?

    Regards,

    Adriana

    Thursday, May 03, 2012 3:42 PM