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: