Monday, January 19, 2015

Deploying PowerPivot and PowerView for SharePoint 2013 in Single- and Multi-Server farms

Hi! I recently had to deploy PowerPivot and Power View on our SharePoint and found that although single server deployments are well documented, multi-server deployments are not. So I put together a how-to with multi-server (dual-server, in fact) install instructions to PowerPivot and Power View. Hope it helps!

GB 
_________________

Deploying PowerPivot and PowerView for SharePoint 2013 in Single- and Multi-Server farms

Introduction

How to get SharePoint 2013 to show that workbook full of beautiful Power View reports on the browser, with all the nice interactive features?

For that, you need to install and configure Power Pivot (that opens reports in Data Model based workbooks through Excel Services and Analysis Services on SharePoint) and Power View (who displays interactive Power View reports on the browser, also using Excel Services).

The goal of this document is to clarify the "where's" and "why's" to components installation on a dual server farm (1 Web server + App server and 1 DB server), which I suppose it's the most common initial production farm deployment around.

Single-Server Deployment

For single-server deployments there's a MSDN post that covers it completely; it was the base source to my dual server installation:
http://blogs.msdn.com/b/querysimon/archive/2012/11/26/installing-the-bi-features-of-sharepoint-2013.aspx

Multi-Server Deployment

Example Topologies

From [1]:

  1. Single-server: for development and testing.
  2. Separate SQL Server: the most common “adoption topology”.
  3. Shared SQL Server: several small farms can share the same SQL Server/Analysis Services
  4. Large Farm: a large farm.
You can see a natural evolution from 1 to 4 as your farm load increases. The install we will be performing is the Separate SQL Server. The other multi-server topologies are extensions to this one.

What to Install, and Where

We start from an already installed dual server SharePoint farm: the "SharePoint (SP) Server" which acts as Web server and also as an Application server, and the "SQL Server (DB) server", in my case a SQL Server 2012 SP1 cluster.
In The DB Server
Besides SQL Server DB Engine, we will need Analysis Services in SharePoint, or PowerPivot, mode. 
Before the step-by-step, some useful things you may need to know:
  1. To install Analysis Services in SharePoint Mode you need to run SQL Server setup with SP1 or higher slipstreamed. SQL Server 2012 installation media without slipstreamed SP1 supports SharePoint 2010, not SharePoint 2013.
  2. If you, like me, do not have a slipstreamed installation media at hand, you can “slipstream on the fly”: download SP1 (or higher) and follow instructions in [5]. Short version is:
    - Download SP1
    - Run *SQL Server* (not SP1!) setup.exe like this:
    Setup.exe /Action=Install /UpdateEnabled=TRUE /UpdateSource="C:\SQLSP1"
    where “C:\SQLSP1” is the folder where you saved SP1.
  3. If you have a clustered SQL Server instance: SQL Server 2012 cluster does *not* supports PowerPivot for SharePoint ([4]: “SSAS Cluster Design Considerations | SSAS Server Modes”). How do you achieve high availability? SSAS for SharePoint must be installed on several machines (eg: the cluster nodes), as a standalone instance, and your Excel Services, which uses SSAS in SharePoint mode to process Data Models in PowerPivot, must be configured to use those instances, thus balancing the load.
The step-by-step: install Analysis Services in SharePoint mode according to [2], observing the following:
  • In “Step1: Install PowerPivot for SharePoint”, item #14: the account used as Analysis Services service account must have read-only access to the Power View reports or Excel Services will throw a “we cannot locate a server to load the workbook data model” exception when reports are loaded.
  • In “Step 2: Configure Basic Analysis Services SharePoint Integration | Grant Excel Services Server Administration Rights on Analysis Services”, item #3: the login must be added to the Administrators group.
  • In “Step 2: Configure Basic Analysis Services SharePoint Integration | Configure Excel Services for Analysis Services integration”, item #7: the messages will be logged on ULS log only after you try to load a Data Model-based report.
In The SharePoint Server

In our dual-server topology, the SharePoint Server will host the SharePoint Web server component and the Reporting Services Application server as well. Reporting Service is needed as Power View is an add-in to it.
 
Pre-requisites: you need Excel Services up and running on your farm.

The setup on the SharePoint Server is:
  1. Install SQL Server Reporting Services in SharePoint Integrated Mode. Although my farm was not a single server farm, I followed the instrunctions on [6].
  2. Download and install the PowerPivot for SharePoint add-in as in [3].

Conclusion

And that’s it. You can now upload a workbook containing some Power View reports and use them through the browser on your SharePoint site. It’s fairly common the error “"This workbook is larger than the maximum workbook size allowed to be opened in the browser”, because PowerPivot workbooks store a snapshot of the data when reports are first loaded; is this is the case, you can increase the maximum workbook size following [7].

References

[2]   “Install Analysis Services Server in SharePoint Mode for SharePoint 2013 (SQL Server 2012 SP1)”
http://msdn.microsoft.com/en-us/library/jj219067(v=sql.110).aspx
[3]   “Install or Uninstall the PowerPivot for SharePoint Add-in”
http://msdn.microsoft.com/en-us/library/jj218792(v=sql.110).aspx
[5]   “SQL Server 2012 Slipstream Updates Has Changed”
http://cecildt.blogspot.com.br/2012/08/sql-server-2012-slipstream-updates-has.html
[6]   “Install Reporting Services SharePoint Mode as a Single Server Farm”
http://msdn.microsoft.com/en-us/library/47efa72e-1735-4387-8485-f8994fb08c8c%28v=sql.110%29
[7]   “Configure Maximum File Upload Size (PowerPivot for SharePoint)”
http://msdn.microsoft.com/en-us/library/ff487972%28v=sql.110%29.aspx
 


1 comment:

  1. Congratulations, your blog is appealing and informative. Going through your Information, I found quite a few new ideas to implement.

    ReplyDelete