Q: How can I see Analysis Services database structure that is stored within PowerPivot ?
User Rating: / 4
PoorBest 
Written by PowerPivot-Info.com   
Friday, 27 November 2009 00:08

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: faq, internals, sharepoint, excel
Comments (2)
2 Thursday, 29 July 2010 12:43
PowerPivot-Info.com
Mei - it appears to me that you are trying to restore backup on Analysis Services server that is installed as MOLAP server and not as "SharePoint integrated mode" (or "PowerPivot for SharePoint") server. You cannot do that. You are able to restore PowerPivot internal backup just to SSAS server that was installed in SharePoint integrated mode. For SSAS server DemploymentMode parameter is set in file "c:\Program Files\Microsoft SQL Server\MSAS10_50.PowerPivot\OLAP\Config\msmdsrv.ini" (default location)
1 Tuesday, 27 July 2010 17:47
Mei Lee
When I've tried to restore the abf file in SSAS, it gives an error and says: "The backup file was created on a server with DeploymentMode=0 and the current server is running DeploymentMode=1". Therefore it failed.
 

Pyramid+PowerPivot