Where is the data stored that PowerPivot for Excel collects and uses?

PowerPivot for Excel 2010 internally builds a SQL Server Analysis Services (SSAS) database. The data is highly compressed and is stored within the .xlsx file itself in the SSAS database format.
OK…so where is it exactly stored then?

Create a sample PowerPivot application in Excel 2010 and save the resulting file to your local file system. Next, rename the .xlsx file extension to .zip (use command prompt in Win 7).

Now open the resulting .zip file and navigate to the xl\customData directory.

The .data file is where the PowerPivot data (the actual SSAS database) is stored.

Guess what….you can deploy that SSAS database in SSAS if you like!
More info on IMDB or VertiPaq? Click here.





Advertisements

4 thoughts on “Where is the data stored that PowerPivot for Excel collects and uses?

    • That probably is because each PowerPivot uses a different dataset, otherwise you could add more sheets and slicers. Other PowerPivots use external data like flat Excel-sheets or gathered SSRS reports.

      • ?

        The .data file is a cube. Why can’t PowerPivot (from a workbook without any data in the PowerPivot window) pull in the data from the .data file the same way SSAS can?

  1. David Hager :

    ?

    The .data file is a cube. Why can’t PowerPivot (from a workbook without any data in the PowerPivot window) pull in the data from the .data file the same way SSAS can?

    Dear David,

    I personally don’t think that the *.data file is a fully processed cube, more like a flat dataset. PowerPivot uses a In-Memory BI (IMBI) database (VertiPaq) and SSAS saves the cube on disk. Both need to process the data, before they can browse it.
    As far as I know, you’re able to rename the *.data file, deploy it to your SSAS server and browse the database structure it if you like. But I don’t think it’s possible to use some kind of ‘Import Wizard’ to load *.data or cube files.

    More info on deploying *.data files to SSAS: Click here.

    Regards.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s