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

[1]  “Introducing the BI Light-Up Story for SharePoint 2013”
http://blogs.msdn.com/b/analysisservices/archive/2012/07/27/introducing-the-bi-light-up-story-for-sharepoint-2013.aspx

[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

[4]   “How to Cluster SQL Server Analysis Services”
http://download.microsoft.com/download/D/2/0/D20E1C5F-72EA-4505-9F26-FEF9550EFD44/SSAS_Failover_Cluster_V4.docx

[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

 

SSRS cmdlets not available: The term 'Install-SPRSService' is not recognized as the name of a cmdlet...

If you get "Install-SPRSService : The term 'Install-SPRSService' is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the name, or if a path was included, verify that the path is correct and try again." trying to enable Reporting Services on SharePoint 2013, the following are possible causes:

1. You are on a "regular" Windows PowerShell window instead of a SharePoint Management Shell one, or Reporting Services in SharePoint integrated mode is not installed ("Install Reporting Services SharePoint Mode for SharePoint 2013").
2. Service Pack 1 for SQL Server 2012 is not installed on Reporting Services. According to "Software requirements for business intelligence in SharePoint Server 2013" SP1 is needed for Reporting Services in SharePoint integrated mode on SharePoint 2013.Only after I installed SP1 on SSRS the SSRS cmdlets became available.

My SharePoint PowerShell Scripts

1. Get the template name and ID for a web site:
   (Get-SPWeb http://website) | select WebTemplate, WebTemplateID
   A list of templates names and ID's can be found here: http://technet.microsoft.com/en-us/library/ff607979(v=office.14).aspx.

How to Install SharePoint 2013 Project Templates on Visual Studio 2012

1. Download and install Web Platform Installer: http://www.microsoft.com/web/downloads/platform.aspx.
2. Close all VS instances or the setup will fail.
3. On the Web PI select "Microsoft Office Developer Tools for Visual Studio 2012".

After that open VS and the SharePoint 2013 templates should be on the New Project dialog, under Installed | Templates | <language> | Office/SharePoint | SharePoint Solutions.

SharePoint 2013: Configure user profile synchronization service

On the default SharePoint 2013 configuration, the User Profile Synchronization Service (UPSS) is not enabled, so alerts are created but don't work because users don't have a valid e-mail address. Supposing user e-mails are somehow already loaded on AD, here what's you gonna do to configure user profile synchronization:

1. Follow the article "Synchronize user and group profiles in SharePoint Server 2013" (http://technet.microsoft.com/en-us/library/ee721049(v=office.15).aspx). Notice that the synchronization account must be Farm Admin, and local admin on the box running the UPSS instance.
2. Use ADSI Edit on the DC to give "Replicate Directory Changes" permission to the synchronization account on the container where your users are.
3. My test domain had no Exchange or other similar mail server program, so the AD attribute "proxyAddress" was blank. By default SharePoint loads an user e-mail address from this attribute, so I had to remap SharePoint "Work E-Mail" property to the "mail" attribute on AD (which I already had filled). Then run a full synchronization (an incremental one did not load the values).

Cheers,

GB

Saving InfoPath form back to subfolder in SharePoint library

Recently I needed to save an InfoPath 2007 ( :-P ) form back to its original location. The original location was on a subfolder inside a SharePoint form library, and the subfolder was created dynamically during form creation time, named based on some data that was not available on the form itself. So there was no way (at least I didn’t see one) I could create a form submit connection that enabled me to save the form back to where it was.

I’ve Googled for a solution and the only one that looked ok was to include the form URL as a field on the form itself, so I could retrieve it later when saving the form back to the SharePoint library. But since the form is running inside SharePoint, and it is an item in a list, I thought I might give it a try to the SharePoint Server API inside the form code behind, and it worked! I used SPWeb.GetListItem() to retrieve the item in SharePoint corresponding to the form, and then used the item’s properties to retrieve its location:

     public void FormEvents_Submit(object sender, SubmitEventArgs e)
     {
         // Retrieves reference to the form in the SharePoint library based on its URL
         SPListItem item = SPContext.Current.Web.GetListItem(this.Uri);
         // Saves the form. There must be a connection on the form named “SaveFile”, pointing to the
         // SharePoint library. In the following code the connection generates the file name, but if the file
         // name must be generated at run-time, it can be set through FileSubmitConnection.FileName
         FileSubmitConnection spConnection = (FileSubmitConnection)this.DataConnections["SaveFile"];
         spConnection.FolderUrl = item.Web.Url + item.File.ParentFolder.ServerRelativeUrl;
         spConnection.Execute();
         e.CancelableArgs.Cancel = false; // Tells InfoPath that form submit was ok
     }

Queue names restrictions on Azure Storage

We got the exception “One of the request inputs is out of range” using a queue to link web and worker roles on Azure. Of course my little prof-of-concept test with code copy-and-pasted from the Azure Tranning Kit ran ok, but the (almost) same code on my app did not. After some time I noticed that the exception was generated when I used upper-case letters on the queue name. Well, the rules for naming queues on Azure are here, but basically they are:

1. Names can be made of letters, digits and the dash char (-).
2. First and last chars must be letters ou digits. Consecutive dashes are not allowed.
3. All letters must be lower case. (Come on, man. You have this kind of restriction and it’s not documented on the API help? Put it in the CloudQueueClient constructor, or in CloudQueueClient.GetQueueReference(), which receives the name of the queue, or at least in CloudQueue.CreateIfNotExists(), since this method creates the queue… :-P)
4. The queue name length must be between 3 and 63 chars.