none
Which is better as a Reporting Layer(PowerPivot in XL 2010 or Pivot in XL2010) for SQL2008R2 AnalysisServer Cube?

    Question

  • Team,

    I have a very basic Question. I have a Cube which is developed in SQL 2008 R2. I need to design multiple charts and reports (in the same sheet) by using SQL2008 R2Cube and it should be deployed in SharePoint 2010.

    Can any one suggest me why should I choose XL 2010 Power Pivot Why not do the same in XL 2010 Pivot?

    Here my source is 2008R2 Cube and I know it can be connected from XL 2010 Pivot and I can do the multiple charts and reports in the same sheet.

    So please clarify that choosing reporting layer as a XL 2010 Power Pivot is preferred for Relational database or OLAP database too?

    Thanks


    Liyasker Samraj K
    Wednesday, December 29, 2010 11:57 AM

Answers

  •  

    Hello Liyasker,

     

    That depends a lot in what you'll be doing. Nevertheless, I'd suggest you to use PowerPivot for Excel 2010 to build your model using your SQL2008 R2 Cube as its datasource. Once you've imported the data, you can work on your own to create your analysis and reports. Once you have it done, you may upload to a SharePoint 2010 site with PowerPivot for SharePoint. You can also take advantage of the automated data refreshes by using PowerPivot for SharePoint's data refresh feature to schedule periodic refreshes to your workbook so it can be kept up to date for its users.

    There are a number of things the can favor the use of PowerPivot instead of using pure Excel or any other tool to create your analysis and reports. With PowerPivot you are working with the data in your machine, and not in a server, after you import it. You can work with a variaty of different data sources and bring them together (bring data from SQL Server relational database, SQL Server Analysis Services cubes, Oracle, Teradata, DB2, etc, not to mention you can import data from Reporting Services reports (as data feeds), or any data feed using the Atom (odata) protocol), and even copy and paste). Also, the PowerPivot for Excel, uses a new technology to work with data in-memory that is very, very fast and it compacts the data very efficiently. So working with millions of rows in memory is very fast and uses as little memory as it is possible.

    All in all, there are many reasons why to choose to work with PowerPivot and very little (if any) to not use it. :)

    I hope that helps,

    Mariano


    Mariano Teixeira Neto Analysis Services SQL Server BI Microsoft Corp.
    Wednesday, December 29, 2010 11:12 PM
  •  

    Hi Liyasker,

     

    I'm not so sure of what it is when you say Pivot 2007 or Pivot 2010. I presume it is the regular pivot table from Excel, correct? If so, PowerPivot adds a whole new world to the regular Excel feature when it comes to handling and analysing large amounts of data.

    Regarding you other question, the way you get data from a AS cube is the way you described. It will open a MDX Query import wizard so you can write your own query, but you always can click on Design (on that very window) to open a graphic interface to import data where the cube's attributes are exposed. Once you have imported the data from the AS cube to your workbook, the data can be exposed in the PowerPivot Field List while in the Excel window.

     

    There are lot's of blogs on how to make the best use of PowerPivot. I'd suggest you to take a look at those:

    www.powerpivotpro.com

    www.powerpivotblog.nl

    www.powerpivot-info.com

     

    And to better addess questions regarding PowerPivot for Excel, I'd suggest you to post questions in this forum:

    http://social.msdn.microsoft.com/Forums/en/sqlkjpowerpivotforexcel/threads

     

    Hope that helps.

    Mariano


    Mariano Teixeira Neto Analysis Services SQL Server BI Microsoft Corp.
    Friday, December 31, 2010 8:42 PM

All replies

  • Team,

    I have a very basic Question. I have a Cube which is developed in SQL 2008 R2. I need to design multiple charts and reports (in the same sheet) by using SQL2008 R2Cube and it should be deployed in SharePoint 2010.

    Can any one suggest me why should I choose XL 2010 Power Pivot Why not do the same in XL 2010 Pivot?

    Here my source is 2008R2 Cube and I know it can be connected from XL 2010 Pivot and I can do the multiple charts and reports in the same sheet.

    So please clarify that choosing reporting layer as a XL 2010 Power Pivot is preferred for Relational database or OLAP database too?

    Thanks


    Liyasker Samraj K
    Wednesday, December 29, 2010 11:13 AM
  • Although you can connect to an SSAS source from both a standard pivot table as well as powerpivot, based on your requirements above I would use powerpivot in this case as it has bult-in functionality that allows you to automatically create multiple charts off the same in-memory tables.   Also to consider is if you will have the ability to configure your SharePoint 2010 instance to support powerpivot for Sharepoint. 

     




    Javier Guillen
    MCTS / MCITP SQL Server 2008 BI
    http://www.msbicentral.com/Blogs/JavierGuillen.aspx
    Wednesday, December 29, 2010 1:44 PM
  • Hi Javier.
     
    You must work for Microsoft <g>. When you say ... if you will have the ability to configure your SharePoint 2010 instance to support powerpivot for Sharepoint ... you are making the same assumption that MS would love us all to believe. Most of my clients do not have, and have no business case for having, SharePoint.

    --

    HTH
     
    Bob

    Although you can connect to an SSAS source from both a standard pivot table as well as powerpivot, based on your requirements above I would use powerpivot in this case as it has bult-in functionality that allows you to automatically create multiple charts off the same in-memory tables.   Also to consider is if you will have the ability to configure your SharePoint 2010 instance to support powerpivot for Sharepoint. 

     




    Javier Guillen
    MCTS / MCITP SQL Server 2008 BI
    http://www.msbicentral.com/Blogs/JavierGuillen.aspx
    Wednesday, December 29, 2010 4:13 PM
  • I would argue the opposite of Javier. Unless you have a need to pull in data from a source besides the cube, I would stick to pivotting it, it makes the whole analysis far less complicated.

    --

    HTH
     
    Bob

    Team,

    I have a very basic Question. I have a Cube which is developed in SQL 2008 R2. I need to design multiple charts and reports (in the same sheet) by using SQL2008 R2Cube and it should be deployed in SharePoint 2010.

    Can any one suggest me why should I choose XL 2010 Power Pivot Why not do the same in XL 2010 Pivot?

    Here my source is 2008R2 Cube and I know it can be connected from XL 2010 Pivot and I can do the multiple charts and reports in the same sheet.

    So please clarify that choosing reporting layer as a XL 2010 Power Pivot is preferred for Relational database or OLAP database too?

    Thanks


    Liyasker Samraj K
    Wednesday, December 29, 2010 4:15 PM
  •  

    Hello Liyasker,

     

    That depends a lot in what you'll be doing. Nevertheless, I'd suggest you to use PowerPivot for Excel 2010 to build your model using your SQL2008 R2 Cube as its datasource. Once you've imported the data, you can work on your own to create your analysis and reports. Once you have it done, you may upload to a SharePoint 2010 site with PowerPivot for SharePoint. You can also take advantage of the automated data refreshes by using PowerPivot for SharePoint's data refresh feature to schedule periodic refreshes to your workbook so it can be kept up to date for its users.

    There are a number of things the can favor the use of PowerPivot instead of using pure Excel or any other tool to create your analysis and reports. With PowerPivot you are working with the data in your machine, and not in a server, after you import it. You can work with a variaty of different data sources and bring them together (bring data from SQL Server relational database, SQL Server Analysis Services cubes, Oracle, Teradata, DB2, etc, not to mention you can import data from Reporting Services reports (as data feeds), or any data feed using the Atom (odata) protocol), and even copy and paste). Also, the PowerPivot for Excel, uses a new technology to work with data in-memory that is very, very fast and it compacts the data very efficiently. So working with millions of rows in memory is very fast and uses as little memory as it is possible.

    All in all, there are many reasons why to choose to work with PowerPivot and very little (if any) to not use it. :)

    I hope that helps,

    Mariano


    Mariano Teixeira Neto Analysis Services SQL Server BI Microsoft Corp.
    Wednesday, December 29, 2010 11:12 PM
  • Mariano,

    Thanks a lot for your resposnse.

    I understood, it has some new features than Pivot 2010.

    In the same thread i am asking the another Question.

    Is there any way to expose Analysis Service cube all the attributes(Dimension,Hierarchy and Measures) in to POWER PIVOT Filed List?

    Comparision PIVOT Vs PowerPIVOT

    PIVOT:

    When we use SSAS Cube as a Source in PIVOT 2007. After we made a connection with SSAS(Data -> From Other Sources -> From AnalysisService) PIVOT 2007 Will Expose all the Dimension,Hierarchy,Measure in PIVOT list as the same manner it displays in SSAS Browser.

    POWER PIVOT:

    But in POWER PIVOT 2010, We have to Prepare MDX Query to Import the Data (PowerPivot - >PowerPivotWindow -> FromDataBase - > From Analysis Services Server Or FileName).

    And it has some issue

    1. Calculated Column formatting Issue

    2. Hierarchy wont appear as like PIVOT 2010 and SSAS Browser

    3. POWER PIVOT FieldList shows in a different form not like SSAS Browser and PIVIT Field List.

    4. We have to build separate Model for Every relevant fact and Dimension

    Building MDX by Drag and Drop and Edit the MDX is for every Model is Difilcult..

    At least if i get an answer for this question it will help

    Is there any way to expose Analysis Service cube all the attributes(Dimension,Hierarchy and Measures) in to POWER PIVOT Filed List like SSAS Browser and PIVOT 2007 & 2010?

    Regards,

     

     

     


    Liyasker Samraj K
    Friday, December 31, 2010 11:53 AM
  •  

    Hi Liyasker,

     

    I'm not so sure of what it is when you say Pivot 2007 or Pivot 2010. I presume it is the regular pivot table from Excel, correct? If so, PowerPivot adds a whole new world to the regular Excel feature when it comes to handling and analysing large amounts of data.

    Regarding you other question, the way you get data from a AS cube is the way you described. It will open a MDX Query import wizard so you can write your own query, but you always can click on Design (on that very window) to open a graphic interface to import data where the cube's attributes are exposed. Once you have imported the data from the AS cube to your workbook, the data can be exposed in the PowerPivot Field List while in the Excel window.

     

    There are lot's of blogs on how to make the best use of PowerPivot. I'd suggest you to take a look at those:

    www.powerpivotpro.com

    www.powerpivotblog.nl

    www.powerpivot-info.com

     

    And to better addess questions regarding PowerPivot for Excel, I'd suggest you to post questions in this forum:

    http://social.msdn.microsoft.com/Forums/en/sqlkjpowerpivotforexcel/threads

     

    Hope that helps.

    Mariano


    Mariano Teixeira Neto Analysis Services SQL Server BI Microsoft Corp.
    Friday, December 31, 2010 8:42 PM