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

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"

• 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":

• 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:

• Done.