none
Connect to Embedded PowerPivot data without SharePoint publishing RRS feed

  • Question

  • Hi All,

    I'm wondering if it's possible to connect to the offline cube stored in the XLSX file without publishing the XLSX file to SharePoint, i.e. given an oledb connection string is there a way to connect directly to the embedded cube?

    My main objective is to query the embedded data in an XLSX before loading it into Excel (sort of a pre-flight check if you will) as part of a business scenario I'm working on in a current project.

    Possible?
    Thursday, January 14, 2010 5:45 AM

Answers

  • The item1.data file does indeed contain the cube data.  However, the format for this file is different from a local cube that the OLEDB provider is capable of reading.  To work with a Power Pivot model we must load the new in memory server engine and convert OLEDB requests to a format that will work with the new in memory engine. 

    Excel 2010 recognizes when a spreadsheet contains a PowerPivot data source and will load the a local instance of the in memory engine to handle the cube in the .data file. 

    When working with SharePoint integration, the PowerPivot service for SharePoint is running the in memory engine in a multi-user mode, Excel Calculation Services sends the contents of the .data file to the server to be loaded, and all OLEDB requests for that cube are then redirected to the server.

    So no, it is not possible to connect to a PowerPivot model with just OLEDB as the OLE DB provider doesn't have the in memory engine embedded in it.

    hth,
    Wayne




    Tuesday, January 19, 2010 8:59 PM
  • No, this is not possible. You can bring up the file in Excel, but you cannot connect to it from another application (for example, as if it was a local cube, if you know SSAS features). Our collaboration capabilities are only with PowerPivot for SharePoint.

    The only 'pre-fligh check' that you can do is to examine the workbook with Excel Desktop using the PowerPivot addin.

    Beyond that, you have to put in on a SharePoint farm with PowerPivot for SharePoint installed on it -- then you can connect to the data.
     
    Dave Wickert (MSFT) blog: http://www.powerpivotgeek.com/ Enjoy!
    Friday, January 15, 2010 5:59 AM

All replies

  • We'll get someone to look at this at the earliest.

    Thanks.
    Thursday, January 14, 2010 10:13 PM
    Moderator
  • No, this is not possible. You can bring up the file in Excel, but you cannot connect to it from another application (for example, as if it was a local cube, if you know SSAS features). Our collaboration capabilities are only with PowerPivot for SharePoint.

    The only 'pre-fligh check' that you can do is to examine the workbook with Excel Desktop using the PowerPivot addin.

    Beyond that, you have to put in on a SharePoint farm with PowerPivot for SharePoint installed on it -- then you can connect to the data.
     
    Dave Wickert (MSFT) blog: http://www.powerpivotgeek.com/ Enjoy!
    Friday, January 15, 2010 5:59 AM
  • Thanks Dave,

    >>you have to put in on a SharePoint farm with PowerPivot for SharePoint installed on it -- then you can connect to the data

    Would you be able to elaborate on why that is the case? My guess is that PPforSP opens the XLSX file and extracts the cube for processing--is that correct? Here's what my investigation has yielded thus far--

    To examine the XLSX package I renamed the extension to "zip" and unzipped it. Within the unzipped package I have \xl\customData\item1.data. Within that same directory there's an XML file called "itemProps1.xml." That file contains a <datastoreItem> element with an ID attribute of "MS_27579183-554e-4159-bbed-859468fdf056." If I go up one level in the package hierarchy there's a "connections.xml" file that contains a connection string who's "Initial Catalog" property points to that same ID. What I am guessing is that the item1.data file contains the cube (it's a big binary file ~12MB).

    So my question is: using the connection string from the connections.xml file shouldn't I be able to connect to the cube with the regular old OLEDb provider? BTW, I have tried to connect to it unsuccessfully (some SQL component crashes and I get some sqldumper.exe output). I just want to know whether that should be theoretically possible.

    Thanks, and sorry for the long post :)
    Friday, January 15, 2010 9:28 PM
  • The item1.data file does indeed contain the cube data.  However, the format for this file is different from a local cube that the OLEDB provider is capable of reading.  To work with a Power Pivot model we must load the new in memory server engine and convert OLEDB requests to a format that will work with the new in memory engine. 

    Excel 2010 recognizes when a spreadsheet contains a PowerPivot data source and will load the a local instance of the in memory engine to handle the cube in the .data file. 

    When working with SharePoint integration, the PowerPivot service for SharePoint is running the in memory engine in a multi-user mode, Excel Calculation Services sends the contents of the .data file to the server to be loaded, and all OLEDB requests for that cube are then redirected to the server.

    So no, it is not possible to connect to a PowerPivot model with just OLEDB as the OLE DB provider doesn't have the in memory engine embedded in it.

    hth,
    Wayne




    Tuesday, January 19, 2010 8:59 PM