Q: How can I see Analysis Services database structure that is stored within PowerPivot ?

User Rating: 4 / 5

Star ActiveStar ActiveStar ActiveStar ActiveStar Inactive
 

Q: How can I see Analysis Services database structure that is stored within PowerPivot?

A: PowerPivot stores data in the Analysis Services database structure. That database is stored inside xlsx file. Here are steps how you can extract that database:

  • Copy .xlsx file that contains PowerPivot database to temporary location
  • Change extension of that file from .xlsx to .zip
  • Unzip file into new location
  • In the unzipped folder go to sub-folder xl\customData. There you will find file item1.data.
  • Rename file "item1.data" to "item1.abf"

Rename xlsx file to zip

  • Copy file item1.abf to location where Analysis Services will be able to access for restore operation, for example: c:\SSASBackup\item1.abf
  • Start SQL Server Management Studio, connect to Analysis Services instance that is configured to work in SharePoint integration mode and restore database from file item1.abf. Note: you will not be able to restore this file to Analysis Services instance that runs in regular MOLAP/ROLAP/HOLAP storage mode.
  • Now you can explore new restored database. In the screenshot below I restored database with the name "VidasTest1":

SSAS Gemini Database restored

 

  • Did you notice that each PowerPivot table is saved as a Measure Group and also as a Dimension?

You can also access database when PowerPivot for SharePoint publishes it for querying. To do so:

  • Create PowerPivot workbook and publish it to the SharePoint Excel Services.
  • Access that workbook from SharePoint Excel Services and perform some interaction with it - for example select filter or click on the slicers. This will trigger SharePoint to deploy Analysis Services database to configured Analysis Services instance.
  • You can start SQL Server Management Studio and connect to that Analysis Services instance and explore published database:

SSAS DB published by PowerPivot for SharePoint

  • Done.

Tags: sharepoint, internals, faq, excel

 

2007-2015 VidasSoft Systems Inc.