Overview:  Discussion of the 3 possibilities for connecting a Power View report to a PowerPivot model (aka a “Workbook as a Data Source”).  This entry lists the differences in URL formatting, authentication types, and reusability.

Data Sources Supported by Power View

In the initial SQL Server 2012 release, a Power View data source must be “tabular.” This qualifies as either:

• A PowerPivot Workbook         <—the focus of this blog entry
• A SQL Server Analysis Services Tabular Model

Power View passes DAX (Data Analysis Expressions) to the data source, so a traditional SSAS Multidimensional (OLAP) database is not currently supported.

The following 3 methods may be used to connect Power View to a PowerPivot model:

1. Direct URL
2. BISM connection
3. RSDS connection

The PowerPivot workbook must be uploaded to SharePoint in order to create a Power View report on it. A workbook stored outside of SharePoint (known as PowerPivot for Excel rather than PowerPivot for SharePoint) cannot support other Microsoft BI tools connecting to it (although some 3rd party tools can handle this scenario).

1. Direct URL Embedded in Power View Report

Within a PowerPivot Gallery, a shared data connection is not required for Power View to connect to a PowerPivot model. This is a very common type of connection, as it’s what is created when using the “Create Power View Report” icon.

Connection string:

Data Source=”http://<SharePointSite>/<Library Name>/<PowerPivotModelName>.xlsx”;

How to create:

From within the PowerPivot Gallery, find the PowerPivot model you wish to use as a data source. Click the icon at the top right called “Create Power View Report.” This creates a blank Power View report with the data connection set to the PowerPivot model you started from:

    

When finished, save the Power View report. By default, SharePoint will save it in the same PowerPivot Gallery as the workbook data source.

Viewing the connection properties:

To see the data connection properties of your Power View report, change the view to the “All Documents” view:

    

Then, locate your Power View report & select Manage Data Sources from its drop-down menu:

    

Click EntityDataSource in the next window:

    

Notice the connection string is just a URL to the xlsx file (Excel 2010 / PowerPivot workbook).

    

Type of Connection:

Custom (non-shared). This technique creates a “Custom data source” which is a report-specific data source; this means if the name or path of the PowerPivot model changes, each individual report which references the PowerPivot model will need to be updated. This additional potential maintenance is in exchange for the ease in which a Self-Service BI user can create a new Power View report from a PowerPivot model.

Authentication Types:

You can use stored credentials or prompt for credentials, in addition to the default of Windows authentication. You’d have to go to Manage Data Sources after it’s set up in order to change it from the default.

Availability of Data Connection to Other BI Tools:

Excel can use a workbook URL to connect (Data > From Other Sources > From Analysis Services > enter the URL of the XLSX workbook as the Server Name).

PerformancePoint can use a workbook URL to connect (as an Analysis Services data source).

2. BI Semantic Model (BISM) Connection to a PowerPivot Workbook

Connection string:

http://<SharePointSite>/<Library Name>/<PowerPivotModelName>.xlsx

How to create:

Go to the library where you wish to create the connection (this might be your PowerPivot Gallery, or it might be a separate Data Connections Library). Select New Document > BI Semantic Model Connection:

    

After giving it a name, enter the URL of the PowerPivot workbook. In the example below, the workbook URL is http://intranet.contoso.com/SelfService BI/Book Sales PowerPivot Model.xlsx.

    

This URL is the same one as used in option 1 above; however, it doesn’t have the quotes around it, nor the semicolon at the end. Also, the database name is unnecessary (we’d use that if we were connecting to an SSAS Tabular model instead).

Type of Connection:

Shared. (Centralized maintenance should the original file move or be renamed.)

Authentication Types:

With a BISM connection you cannot use stored credentials or prompt for credentials. Only Windows authentication is supported with a BISM data connection.

Using the Connection:

To create a new Power View report from the BISM connection, simply locate the connection & select “Create Power View Report” from the drop-down menu:

    

Availability of Data Connection to Other BI Tools:

Excel can use a BISM data connection (Data > From Other Sources > From Analysis Services > enter the URL of the BISM data connection as the Server Name).

3. Report Data Source (RSDS) Connection to a PowerPivot Workbook

Connection string:

Data Source=”http://<SharePointSite>/<Library Name>/<PowerPivotModelName>.xlsx”;

How to create:

Go to the library where you wish to create the connection (this might be your PowerPivot Gallery, or it might be a separate Data Connections Library). Select New Document > Report Data Source:

    

After giving it a name, under Data Source Type choose “Microsoft BI Sematic Model for Power View” from the drop-down list:

    

In the connection string, use the URL. Contrary to the BISM connection, an RSDS connection is formatted with the double quotes and semicolon at the end.

    

Type of Connection:

Shared. (Centralized maintenance should the original file move or be renamed.)

Authentication Types:

With a Report Data Connection you can indeed use stored credentials or prompt for credentials, in addition to the default of Windows authentication. This gives the RSDS connection type an advantage over the BISM connection type.

Using the Connection:

To create a new Power View report from the RSDS connection, simply locate the connection & select “Create Power View Report” from the drop-down menu:

    

Availability of Data Connection to Other BI Tools:

Report Builder can use a Report Data Source (RSDS) as its data connection.

Also, as of SQL Server 2012 SP1 CU4, you can use an RSDS connection to connect Power View to an SSAS Multidimensional data source.  (Whereas a BISM data source is not currently supported for that purpose.)

Excel can use a BISM data connection (Data > From Other Sources > From Analysis Services > enter the URL of the BISM data connection as the Server Name).

A Few Thoughts About Where to Store Shared Data Sources

Self-Service BI has gotten me to rethink the way some things are done, one of which is where shared data sources should be stored. Traditionally I would have created a separate Data Connections library. However, if a Self-Service BI user is working in a PowerPivot gallery, it would be more user-friendly to have the shared data sources available right there. I’m still not sure how much I like combining reports & data sources in the same library – although it’s convenient, it can get a bit messy. Good naming conventions can certainly help manage users knowing what is what (albeit difficult to enforce in a self-service environment).  There’s also a good possibility that you’ll have multiple PowerPivot galleries, organized & secured by subject area – which means you’d have duplicate shared data connections in each PowerPivot Gallery.  Where to store your shared data sources is a decision point when you are planning out your SharePoint document libraries for reporting.

A Few Words About Content Types

Not all data connection types are set up by default when a new library is created. Chances are you’ll have to manually add the following content types:

• BI Semantic Model Connection (BISM)
• Report Data Source (RSDS)
• Data Service Document (for data feeds, if desired to house them here instead of a separate Data Feeds library)

    

Finding More Information

MSDN – Create a Shared Data Source for a Data Model (SSRS)