locked
PowerPivot without Excel or Sharepoint RRS feed

  • Question

  • Does anyone know of a way to make use of PowerPivot's in-memory database without using Excel or Sharepoint? The "self-serve" BI model makes absolutely no sense in our environment and we would like to avoid the overhead of Sharepoint. Panorama sent out a press release that indicates this might be possible. Obviously, this is not Microsoft's marketing plan, but it would make much more sense for ISVs that have created line-of-business applications that embed BI.
    Wednesday, November 11, 2009 2:17 PM

Answers

  • Thanks Deepak. This is what I was afraid of. Do you know if this will change in the future? The power of SQL Server to us is in the ability to build smart applications. PowerPivot seems designed to help sell Office, not help ISVs build better apps.

    In one sense you are right. PowerPivot v1 is aimed at office power users, not at ISV's. You can only run the engine inside Excel or Sharepoint and there is no support for API access.

    I do not believe that there have been any public announcements about the plans for v2 and I would not expect some for a while yet seeing that v1 is still in CTP and has not been released yet. However I believe that the product team are well aware that ISV's and BI professionals are interested in being able to use this technology within their own products/projects. A number of people were asking for this at the PASS summit last week.
    http://geekswithblogs.net/darrengosbell - please mark correct answers
    • Marked as answer by Keith Henkel Thursday, November 12, 2009 1:15 PM
    Thursday, November 12, 2009 6:16 AM

All replies

  • "Does anyone know of a way to make use of PowerPivot's in-memory database without using Excel or Sharepoint?" - for SQL Server 2008 R2, you will need (at least) Excel 2010, since the PowerPivot front-end component is implemented as an Excel add-in (requring Excel 2010). A single user of a pivot table / report can work in Excel, without deploying to SharePoint. Of course, this technology could be implemented in other areas of the BI stack in future - did you have a specific scenario in mind?
    - Deepak
    Wednesday, November 11, 2009 4:16 PM
  • I had hoped that we would be able to use the in-memory Analysis Services engine to increase performance in our existing solution. I read in a blog last year that this was essentially a new storage mode for the engine that could be queried with existing MDX. I have little interest in using Excel to design or implement new models. Excel is of no use to our users who have a web-based interface to view dashboards, scorecards, and to build reports.
    Wednesday, November 11, 2009 6:40 PM
  • ".. I read in a blog last year that this was essentially a new storage mode for the engine that could be queried with existing MDX .." - based on what I heard at last week's PASS Conference, this is basically true. But there were a couple of caveats for this version (SQL Server 2008 R2):

    - The cube model is automatically created as the analyst / power user is interacting with their data, using the add-in for Excel 2010.
    - Once the PowerPivot worksheet is deployed to a SharePoint server, MDX queries can be run against the cube running on that server.
    - But calculations are added to the PowerPivot model using the new DAX language (see link below), rather than using MDX script.

    Introduction to Data Analysis Expressions (DAX) in Gemini
    ...
    DAX is an expression language based on Excel formula syntax. Because DAX is designed to work with multiple tables of data, it includes functions that implement relational database concepts. DAX also adds new functionality that allows you to create dynamic aggregations, making DAX formulas smart about calculating values in a PivotTable.
    ...

    - Deepak
    Wednesday, November 11, 2009 7:43 PM
  • Thanks Deepak. This is what I was afraid of. Do you know if this will change in the future? The power of SQL Server to us is in the ability to build smart applications. PowerPivot seems designed to help sell Office, not help ISVs build better apps.
    Wednesday, November 11, 2009 8:10 PM
  • The increased performance in using the local storage provided by PowerPivot is primarily coming from the fact that it is local and in-memory -- but you can essentially increase the performance of your existing SSAS solution via aggregation and other design design factors as wells as looking at a good cache warming strategy.

    What are the performance issues you are facing with your current SSAS implementation that have you looking at PowerPivot's engine?  In the end, PowerPivot is built off of the Analysis Services dll, so the only difference really is the local storage model and the enhanced query expressions for the PowerPivot designer.

    BI and SQL Blog


    Please click the Mark as Answer button if a post solves your problem!
    Wednesday, November 11, 2009 9:32 PM
  • Thanks Deepak. This is what I was afraid of. Do you know if this will change in the future? The power of SQL Server to us is in the ability to build smart applications. PowerPivot seems designed to help sell Office, not help ISVs build better apps.

    In one sense you are right. PowerPivot v1 is aimed at office power users, not at ISV's. You can only run the engine inside Excel or Sharepoint and there is no support for API access.

    I do not believe that there have been any public announcements about the plans for v2 and I would not expect some for a while yet seeing that v1 is still in CTP and has not been released yet. However I believe that the product team are well aware that ISV's and BI professionals are interested in being able to use this technology within their own products/projects. A number of people were asking for this at the PASS summit last week.
    http://geekswithblogs.net/darrengosbell - please mark correct answers
    • Marked as answer by Keith Henkel Thursday, November 12, 2009 1:15 PM
    Thursday, November 12, 2009 6:16 AM