Enhance PowerPivot Cube using Visual Studio 2008 R2

PowerPivot is a self service BI tool that you may use it to prototype your work quickly. The SQL Server Analysis service that PowerPivot cube used is a VertiPaq storage mode which is different from the traditional  MOLAP, ROLAP or HOLAP cube. It is a read only temporary cube that PowerPivot Service creates and manages. You may persist or enhance it using Visual Studio 2008 (SQL Server 2008 R2). 

Here are the steps to enhance it using Visual Studio:

• Backup and Restore the PowerPivot Sandbox database with a different name on the VertiPaq Mode SSAS.
• Open the Analysis Service Database option to point to the restored database from Visual Studio to create a new project.

  

• Once the project is created, you could see all the dimensions and a cube called Sandbox in the project.

  

• The Data Sources contains a connection string that connects to the original data source used by the PowerPivot workbook.
• The Data Source Views contains all the tables from the PowerPivot workbook as Named Queries.
• Since it is a read only cube, if there is any modification or trying to process the cube, you will get the following error

• To overcome the read only issue, you need to detach and then attach it by unselecting the Read-only checkbox to change the ReadWrite mode to be ReadWrite.

 

• Now you should be able to add a new MDX or DAX measure and then deploy/process it.

• You could view your result in both the Visual Studio or SSMS.

• If you try to restore the PowerPivot Cube on a traditional SSAS Server, you will encounter the following error: Backup and restore errors: Restore failed. The backup file was created on a server with DeploymentMode=1 and the current server is running DeploymentMode=0. (Microsoft.AnalysisServices)