This section describes using Power View within SharePoint. It can be used with the enterprise editions of SharePoint 2010 or SharePoint 2013.
Although Power View is very easy to use, setting up the required infrastructure when using it with SharePoint can be a bit tricky. Power View is automatically installed when you run the SQL Server 2012 setup and choose Reporting Services Add-in for SharePoint Products on the Feature Selection page. This is a newer version of the add-in, which includes Power View, compared to the version that is installed with SharePoint 2010 that does not include Power View. The following are the software requirements for Power View:
- SQL Server 2012 database engine (Business Intelligence Edition or Enterprise Edition).
- SQL Server 2012 Reporting Services (in SharePoint Integrated mode).
- SQL Server 2012 PowerPivot for SharePoint.
- SQL Server 2012 Analysis Services (SSAS) in tabular mode if using a tabular model connection.
- SQL Server 2012 Analysis Services in multidimensional mode if using a multidimensional model connection. It requires that SQL Server 2012 Service Pack 1 Cumulative Update 4 (CU4) be applied.
- SQL Server 2008/2012 Analysis Services in PowerPivot for SharePoint mode if using a PowerPivot for SharePoint workbook.
- SharePoint Server 2010/2013 Enterprise Edition Reporting Services Add-in for SharePoint Products installed from SQL Server 2012 onto the SharePoint server on which you want to have Power View.
- Microsoft Silverlight 5.
Note
Providing detailed instructions for installing all the software to support Power View is outside the scope of this book. For help with this, see the Microsoft white paper titled Power View Infrastructure Configuration and Installation: Step-by-Step and Scripts, which walks you through installing and testing Power View and its infrastructure using multiple scenarios: http://bit.ly/1bqaSZT.
The data sources that Power View in SharePoint can use are a tabular model connection or a multidimensional model connection (support for multidimensional models was added after SQL Server 2012 was released via SP1 CU4).
The tabular model connection can be implemented in a number of ways:
- It can be a PowerPivot workbook (
.xlsx) that is published to a SharePoint site that has PowerPivot enabled (called PowerPivot for SharePoint). Once published, the data is actually saved to a SharePoint PowerPivot flavor of the tabular engine. You can then use a special-purpose document library called PowerPivot Gallery to preview, share, and access published workbooks. - It can be a BISM report server data source (
.rsds) type that is published in a SharePoint document library in which it connects to a database running on a SQL Server 2012 Analysis Services tabular mode server (which can use either Windows authentication or stored credentials as Windows credentials). - It can be a BISM connection file (
.bism) that is published in a SharePoint library (which has the BISM connection file content type) in which the connection is pointing to either one of the following:- A database running on a SQL Server 2012 Analysis Services tabular mode server (which can use only Windows authentication and not stored credentials).
- A PowerPivot for SharePoint workbook. Note that an embedded PowerPivot database inside an Excel workbook is the equivalent of a tabular model database that is run on a standalone Analysis Services tabular mode server. You can use workbooks that are created using either the SQL Server 2008 R2 or the Microsoft SQL Server 2012 versions of PowerPivot for Excel.
You can also open the previously mentioned BISM connection file in Excel as an ODBC file. If you do this, Excel will open a workbook that contains a PivotTable field list that is populated with fields from the underlying data source.
PowerPivot for SharePoint requires Excel Services and also requires that you install SQL Server PowerPivot for SharePoint (which installs the "PowerPivot for SharePoint" server mode in SSAS). A benefit when using the tabular model is that a PowerPivot for SharePoint model workbook is able to use many different data sources, such as Microsoft Access, SQL Server 2008, SQL Azure, multidimensional databases, Excel files, text files, and so on. When using one of these sources, a SSAS tabular cube is created behind the scenes. So, the tabular model acts as a bridge, or a semantic layer, between the complexities of the data sources at the backend and your perspective of the data.
Be aware that when the tabular model connection uses a PowerPivot for SharePoint workbook, you are using the saved data in that workbook and are not connected to the source of that data. For example, if you are using a Microsoft Access database as a source for PowerPivot, when you save the PowerPivot workbook to SharePoint, you in fact are using a static copy of the data from the Microsoft Access database that was imported into PowerPivot. Then, when you are using Power View connected to this PowerPivot for SharePoint workbook, you are using that static data and are not connecting to the Microsoft Access database until you refresh the PowerPivot model either manually or using a scheduled refresh.
If you would like to use the first tabular model connection discussed earlier to start Power View, you would first use PowerPivot in Excel to pull in the data and then save the PowerPivot workbook to the PowerPivot Gallery in SharePoint. Then, you would go to the PowerPivot Gallery in SharePoint. Just to the right of the name of the PowerPivot workbook will be a Create Power View Report control. Click on that, and you will be able to create a Power View report from the PowerPivot model in the workbook. See the following screenshot on how to start Power View in PowerPivot Gallery:
Starting Power View in PowerPivot Gallery
To start Power View using the other tabular model connections discussed earlier, just click on the connection in the library or open the connections context menu and you will see the option Create Power View report.
The multidimensional model connection is made by creating a BISM report server data source (.rsds) type that is published in a SharePoint document library. This data source connects to a database running on a SQL Server 2012 Analysis Services multidimensional mode server (which can use either Windows authentication or stored credentials as Windows credentials). See the following screenshot to see what your BISM report server data source connection should look like:
BISM report server data source connection
To start Power View with a multidimensional model connection, in the SharePoint document library, either click on the connection to the multidimensional model or open the multidimensional models context menu and you will see the option Create Power View report.
It is important to understand that when you create a Power View report using the multidimensional model, you are working with a tabular model type representation of a multidimensional model. Some objects and behaviors may appear different from the traditional tabular models. For more information on the differences, see http://bit.ly/1djQlt4.