PowerPivot as a Multidimensional datasource for PerformancePoint Service 2010

Well, may be am I the last one to notice that, but just in case you didn’t know it either….

SQL Server 2008 R2 and 2012 PowerPivot data sources are the only other type of Multidimensional datasource that can be used in PerformancePoint Services!!

The data is of course in an Excel workbook saved in a Sharepoint 2010 library with the PowerPivot components installed.

Requirements

There are some server prerequisite requirements to enable PowerPivot data sources. If you are using PPS on the same SharePoint farm that has the PowerPivot components installed, there should be no additional requirements or software.

If you are connecting to a different SharePoint farm, and the farm that PPS is running on does not have PowerPivot installed, the SQL native client on that server needs to be upgraded to SQL Server 2008 R2.

How to ?

PowerPivot datasources are not natively supported by PPS, however because they support most of the functionality and interfaces of Analysis Services it is possible to connect to them using a Analysis Services datasource.

Just edit the connection string manually on the datasource. It should ressemble something like that :

provider=molap;datasource=http://<SharepointServer>/
<Document Library>/<PowerPivot File>.xlsx

Notice that the cube name will always be “Sandbox”

Happy PPS’ing

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: