locked
PowerPivot versus Excel with SSAS Cube RRS feed

  • Question

  •  

    I would like to have a list of tips that help folks decide when its best to use Excel with SSAS Cubes versus PowerPivot.  I'll start and am happy to edit mistakes.  So far I have not found too many reasons to use PowerPivot over SSAS cubes but I know those reasons exist.  So:

     

    You might want to consider SSAS cubes and Excel when:

    1. You need to have incremental builds.  If your data is very large, you may not be able to refresh it fast enough.  In this case you will want to use SSAS and only add "new" data to the cube.  PowerPivot does not support incremental builds (AFAIK).
    2. If you data is large and wont fit into the memory you have on your server.   PowerPivot is an in memory solution and if you dont have enough, I suspect performance will start to plummet.
    3. SSAS allows you to build hierarchies at design time so end users dont have to do it for themselves.
    4. SSAS has a nice interface for building calculated measures
    5. SSAS cubes are an open data source that many BI other reporting tools can read including SSRS, Business Objects....

    Consider using PowerPivot when:

    1. You want slicers.  Slicers dont exist in Excel against SSAS cubes for whatever reason.  Slicers only exist when using Excel against a PowerPivot data source.   Slicers are great.  You can specify exactly what reporting object (pivot tables, pivotcharts) are "filtered" by slicers.  I love this.
    2. You want an "easier" way to integrate other data on the fly.  This is great for power users but also could be a liability if people do not know what they are doing.
    Just a start folks and I realize it might already be inaccurate but lets work this list!

     

     

     


    Tim Webber
    • Edited by TimWebber Wednesday, February 9, 2011 8:33 PM
    Tuesday, February 8, 2011 5:19 PM

Answers

  • In that case I think your current OLAP based SSAS solution works and there doesnt seem to be a need to change or migrate to something else.  I suggest you read this great blog post by the Analysis Service team. On it, T.K. Anand explains that traditional SSAS development versus the upcoming BISM model (which is essence the technical foundation for PowerPivot as well) follows a similar relationship of that between C++ and C#.   When C# was invented C++ already existed but it was sometimes too complex for rapid business implementations. So now a lot of those are written in C#.  But that by no means implies that C++ is dead.  On the contrary, many powerful applications are still written on it; and for applications that were successfully written in C++ and are fully functional, there is no need to port them into C#, just as for functional UDM/OLAP SSAS implementations there will not always be a need or reason to port into the new BISM SSAS model.

     




    Javier Guillen
    http://www.msbicentral.com/Blogs/JavierGuillen.aspx
    Thursday, February 10, 2011 9:12 PM
    Answerer
  • Take a look at Chris Webb�??s blog on this http://cwebbbi.wordpress.com/2010/11/19/powerpivot-vs-ssas-quiz/
     
    Bob

    "TimWebber" wrote in message news:388d247d-c9cb-4549-ba34-cb0c1e0c728e@communitybridge.codeplex.com...

     

    I would like to have a list of tips that help folks decide when its best to use Excel with SSAS Cubes versus PowerPivot.  I'll start and am happy to edit mistakes.  So far I have not found too many reasons to use PowerPivot over SSAS cubes but I know those reasons exist.  So:

     

    You might want to consider SSAS cubes and Excel when:

    1. You need to have incremental builds.  If your data is very large, you may not be able to refresh it fast enough.  In this case you will want to use SSAS and only add "new" data to the cube.  PowerPivot does not support incremental builds (AFAIK).
    2. If you data is large and wont fit into the memory you have on your server.   PowerPivot is an in memory solution and if you dont have enough, I suspect performance will start to plummet.
    3. SSAS allows you to build hierarchies at design time so end users dont have to do it for themselves.
    4. SSAS has a nice interface for building calculated measures
    5. SSAS cubes are an open data source that many BI other reporting tools can read including SSRS, Business Objects....

    Consider using PowerPivot when:

    1. You want slicers.  Slicers dont exist in Excel against SSAS cubes for whatever reason.  Slicers only exist when using Excel against a PowerPivot data source.   Slicers are great.  You can specify exactly what reporting object (pivot tables, pivotcharts) are "filtered" by slicers.  I love this.
    2. You want an "easier" way to integrate other data on the fly.  This is great for power users but also could be a liability if people do not know what they are doing.
    Just a start folks and I realize it might already be inaccurate but lets work this list!

     

     

     


    Tim Webber
    Saturday, February 12, 2011 12:45 PM

All replies

  • Hi Tim

    You definitely can use slicers in Excel against SSAS cubes.  It is just not part of the Field List as SSAS uses a pivot table field list that was created before PowerPivot.  But you can add a slicer to a SSAS-based pivot table by going to the "Pivot Table Tools" tab and then click on  the "Insert Slicer" button on the ribbon.

    Regarding when to use SSAS vs PowerPivot, I think there are at least two possible ways of looking at it -

    1) SSAS is just another source to PowerPivot, just as SQL Server and Oracle are as well.  In this case, you are just adding data to the PowerPivot Vertipaq engine from SSAS just as you would from any other data source.

    2) Replacing a corporate BI implementation that is currently in SSAS with PowerPivot (or deciding to use PowerPivot for corporate BI as opposed to SSAS).  In this case, it is important to keep in mind that even though PowerPivot is currently a user oriented product, in contains the building blocks of the next generation SSAS engine (namely, Vertipaq and DAX).  In the upcoming SSAS 2011, an Excel PowerPivot data model is in itself a Business Intelligence Semantic Model (BISM) that BI professionals will be able to leverage in order to go from Personal BI to Corporate BI.  This continuum of PowerPivot on the Excel client, to BISM on the server is what is being put forth by the newest Microsoft BI technology.  There will still be the option to develop UDM/OLAP SSAS cubes, though.  But from the perspective of Sql Server 2011, there would be no reason to think in terms of PowerPivot OR SSAS, but instead think of PowerPivot AND SSAS.

    Hope that helps

     




    Javier Guillen
    http://www.msbicentral.com/Blogs/JavierGuillen.aspx
    Wednesday, February 9, 2011 12:49 AM
    Answerer
  • I'd like to know more about SSAS 2011.  I'll look it up.  Here's what is formulating in my mind (but i am just beginning with powerpivot...so...formulating is the key.

     

    First, I want slicer functionality "in" Excel pivot tables hooked directly to SSAS cubes!  Not through PowerPivot.  I dont see "why" slicers should be only in the domain of PowerPivot "pivot" tables.  All pivot tables should be able to do the same thing!!!  All the functionality in EXcel should be the same regardless of whether you are using Powerpivot or SSAS cubes as a data source.

    PowerPivot to me seems somewhat redundant.  I like the idea that certain power users can leverage easier ways to integrate data on the fly but...I would like to define my model with SSAS.  Its a robust tool.  I appreciate that PowerPivot is a different data source than an SSAS cube.  I wish they were the same (ie that MS decided to load a cube into memory instead) but alas they are not.  So, I would be happy if my SSAS would simply "generate" a PowerPivot data source.  Why should I have to write MDX?  Why not just let me select the dimensions and measures I want and let SSAS do the rest including building the powerpivot calculated measures, relationships and all the rest of the good stuff i so carefully designed in SSAS?

    In the end PowePivot (to me) could be just MS' in memory olap data source while SSAS cubes are the OLAP database (again nice if they were the same).   Pivot table functionality should be through excel and not dependent on the data source.  

    Maybe that is where SSAS 2011 is going?


    Tim Webber
    Wednesday, February 9, 2011 8:18 PM
  • The slicer functionality does not depend on powerpivot.  You can use it in regular pivot tables connected directly to SSAS (no powerpivot involved). Just go to the pivot table tools tab and select 'insert slicer'.

    What Sql Server 2011 is introducing is a technology called BISM (Business Intelligence Semantic Model).  It is a new SSAS storage mode that will use Vertipaq and DAX (just as PowerPivot).  When you define a model in the PowerPivot Excel client, it is a BISM data model.  This model will have the capability to be ported to the server version of PowerPivot, which is SSAS (currently, PowerPivot for Excel IS a version of SSAS.  So much so, that Excel thinks it is SSAS, by using the same MSOLAP provider, displaying a 'Running OLAP query' mesasge when fetching data from the in-memory engine and executing DAX, or giving the capability to run Excel cube functions over the PowerPivot data).   So the breakdown between PowerPivot on one side versus SSAS on the other side doesn't really exist.  PowerPivot is the extension of SSAS into the world of personal BI.

    You will still have the traditional, time proven UDM/OLAP mode in SSAS 2011 though.  Its only that you will only be able to run one mode on a specific instance (not both at the same time). However you will be able to have one server with two instances of SSAS, one running in BISM mode and the other one in UDM/OLAP mode.  For BISM instances, in-memory relationships and DAX calculations will be available to OLAP-based clients running MDX (in other words, MDX will be able to query DAX measures). 

     




    Javier Guillen
    http://www.msbicentral.com/Blogs/JavierGuillen.aspx
    Thursday, February 10, 2011 1:28 AM
    Answerer
  • Thanks Javier. We are developing a product using the MS suite of tools (including BI) and I am trying to determine if there is any reason for us to use PowerPivot.   So far, I cant see any good reason for us to use PowerPivot yet.   It sounds like Excel against an SSAS cube has all the reporting functionality and I assume that with Excel Services, i can expose dashboard developed in Excel in much the same way as PowerPivots can be served up on Sharepoint?


    Tim Webber
    Thursday, February 10, 2011 5:55 PM
  • Hi Tim

    If you have a developed and functional SSAS cube, I'd say yes there is probably not a lot of reasons to migrate the whole thing (or even part of it) to PowerPivot for Excel/SharePoint or BISM.

    I guess some basic questions to guide the decision could be:

    are your users satisfied with the cube data? do they ever have to combine data outside of the cube in order to come up with the reports they need? Can they come up with the calculations they need by themselves or do they need help from the technology team? In other words, can the technology team offload some of the calculation development work to the subject matter experts? (PowerPivot for Excel)

    If the users are utilizing a lot of spreadsheet based mini-databases (spreadmarts), would your technology team like to have visibility across the enterprise in order to have an understanding of what those spreadsheets are and what the most popular ones are? Would the technology team benefit from this insight in order to build those validated calculations and external datasources into the data warehouse? (PowerPivot for Sharepoint)

    I hope that helps  




    Javier Guillen
    http://www.msbicentral.com/Blogs/JavierGuillen.aspx
    Thursday, February 10, 2011 7:55 PM
    Answerer
  • Good guidelines.  For most of our audience they would like the flexibility to slice and dice data and create new reports but very rarely would they create a new metric or try and link new data into their analysis.   In fact, "measures/metrics" are ususally carefully developed with end user consultation and a key outcome is to have single version of the truth so only fly creation of measures occurs rarely.
    Tim Webber
    Thursday, February 10, 2011 8:22 PM
  • In that case I think your current OLAP based SSAS solution works and there doesnt seem to be a need to change or migrate to something else.  I suggest you read this great blog post by the Analysis Service team. On it, T.K. Anand explains that traditional SSAS development versus the upcoming BISM model (which is essence the technical foundation for PowerPivot as well) follows a similar relationship of that between C++ and C#.   When C# was invented C++ already existed but it was sometimes too complex for rapid business implementations. So now a lot of those are written in C#.  But that by no means implies that C++ is dead.  On the contrary, many powerful applications are still written on it; and for applications that were successfully written in C++ and are fully functional, there is no need to port them into C#, just as for functional UDM/OLAP SSAS implementations there will not always be a need or reason to port into the new BISM SSAS model.

     




    Javier Guillen
    http://www.msbicentral.com/Blogs/JavierGuillen.aspx
    Thursday, February 10, 2011 9:12 PM
    Answerer
  • Take a look at Chris Webb�??s blog on this http://cwebbbi.wordpress.com/2010/11/19/powerpivot-vs-ssas-quiz/
     
    Bob

    "TimWebber" wrote in message news:388d247d-c9cb-4549-ba34-cb0c1e0c728e@communitybridge.codeplex.com...

     

    I would like to have a list of tips that help folks decide when its best to use Excel with SSAS Cubes versus PowerPivot.  I'll start and am happy to edit mistakes.  So far I have not found too many reasons to use PowerPivot over SSAS cubes but I know those reasons exist.  So:

     

    You might want to consider SSAS cubes and Excel when:

    1. You need to have incremental builds.  If your data is very large, you may not be able to refresh it fast enough.  In this case you will want to use SSAS and only add "new" data to the cube.  PowerPivot does not support incremental builds (AFAIK).
    2. If you data is large and wont fit into the memory you have on your server.   PowerPivot is an in memory solution and if you dont have enough, I suspect performance will start to plummet.
    3. SSAS allows you to build hierarchies at design time so end users dont have to do it for themselves.
    4. SSAS has a nice interface for building calculated measures
    5. SSAS cubes are an open data source that many BI other reporting tools can read including SSRS, Business Objects....

    Consider using PowerPivot when:

    1. You want slicers.  Slicers dont exist in Excel against SSAS cubes for whatever reason.  Slicers only exist when using Excel against a PowerPivot data source.   Slicers are great.  You can specify exactly what reporting object (pivot tables, pivotcharts) are "filtered" by slicers.  I love this.
    2. You want an "easier" way to integrate other data on the fly.  This is great for power users but also could be a liability if people do not know what they are doing.
    Just a start folks and I realize it might already be inaccurate but lets work this list!

     

     

     


    Tim Webber
    Saturday, February 12, 2011 12:45 PM
  • Thanks Javier. We are developing a product using the MS suite of tools (including BI) and I am trying to determine if there is any reason for us to use PowerPivot.   So far, I cant see any good reason for us to use PowerPivot yet.   It sounds like Excel against an SSAS cube has all the reporting functionality and I assume that with Excel Services, i can expose dashboard developed in Excel in much the same way as PowerPivots can be served up on Sharepoint?


    Tim Webber


    I have come to this conclusion for pretty much the same reasons.  We already have a functioning cube and are looking for a better way to accomplish user defined reports than Report Builder.  As this discussion is several months old, does anyone have new information that might alter this conclusion.

    Wednesday, August 31, 2011 6:33 PM