Saturday, May 21, 2011

You can create new SSRS reports, Excel reports, or PPS Dashboard reports based on PowerPivot workbooks that are published in a SharePoint PowerPivot Gallery. PowerPivot data access on a server is supported through SharePoint 2010 with SQL Server 2008 R2.

SSRS Reports

You can use either Report Designer in BIDS or Report Builder to access the PowerPivot data. The Reporting server could be in native mode or in SharePoint integrated mode. Not like the Excel reports which include embedded data, SSRS reports always access PowerPivot data as an external data source.

In order to support PowerPivot connections from Report Designer, you must have the SQL Server 2008 R2 OLE DB Provider for Analysis Services installed. Windows authentication is also required to integrate between the SSRS server and the PowerPivot server. More information could be found at http://msdn.microsoft.com/en-us/library/ee210631.aspx.

Steps for SSRS Reports:

Create a new Report Server Project in Visual Studio 2008 or BIDS

Add new data source in Share Data Source

Using the Microsoft SQL Server Analysis Services Type with Connection String pointed to the SharePoint location for the PowerPivot spreadsheet, for example: Data Source="http://SharepointServerName/PowerPivot Gallery/PowerPivotDemo.xlsx"

Click on the Credentials tab to set the Authentication to be Windows and click on OK button.

Add a new report and select the Share data source just created, then click on Next.

Click on the Query Builder button

The Query Designer will be opened with a Sandbox cube

You now should be able to work on your MDX query to get your result as needed.

Excel Reports

You could also create new Excel reports to access the PowerPivot workbooks that you have permissions with.

Steps for Excel Reports:

Open Excel, go to Data tab and select From Data Connection Wizard on Other Sources menu

On the Data Connection Wizard window, select the Other/Advanced option and click on Next button

Select the Microsoft OLE DB provider for Analysis Services 10.0 and click on Next

The Select Database and Table window will then be opened with Sandbox Cube been selected to be used. Click on the Next to save the data connection file and finish the wizard. Here’s what the connection string may look like:

Since Excel Pivot Tables that go against PowerPivot data do not support the write back, offline cubes, and drill through, there are some known issues when using the PowerPivot data source in PPS reports such as:

Show Details error

Error running data source query. DrillThrough is not supported because the server is running in VertiPaq mode.

Monday, May 9, 2011

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)

About Me

I am a Business Intelligence IT professional and currently work for Duke Energy. I enjoy working in BI. This blog was created as a way for me to share some of the challenges and ideas that I have experienced.