Setting Up an Administrative Server for BI in SharePoint 2013

  • Article

SharePoint has practically always relied on a Central Administration Web site to provide a centralized administrative user interface for configuring and controlling servers and services in a farm. PowerPivot for SharePoint adds several components to this site to cover common PowerPivot administration needs, such as the PowerPivot Management Dashboard as well as application pages to manage PowerPivot service applications. However, with the new BI light-up story for SharePoint 2013, not every farm offering core BI functionality has the PowerPivot add-in for SharePoint installed. Without the add-in, there are no PowerPivot application pages or management dashboards, and even with the add-in the server running SQL Server 2012 SP1 CTP3 Analysis Services (SSAS) is no longer a part of the farm and therefore not managed through Central Administration. So, how do you manage a SharePoint 2013 BI environment efficiently?

Let’s take a look at my SharePoint 2013 Preview test environment, depicted in the following screenshot. It consists of just three different servers. The SharePoint server SP2013-SRV hosts the Central Administration site, the SQL server SPDBMS runs the SQL Server 2012 RTM database engine and management tools, and the SSAS server AS2012SP1 runs a pure 2012 SP1 CTP3 Analysis Services installation without database engine or management tools. As a consequence, I’m jumping around like a squirrel between my servers, even struggling with interoperability issues because the SQL Server 2012 RTM tools can’t always connect to my 2012 SP1 CTP3 SSAS server. For example, SQL Profiler returns an error that a class is not registered so I can’t trace the communication between SharePoint shared services and the SSAS server. A dedicated administrative server hosting Central Administration, SQL Server Management Studio, and SQL Profiler would help to make life easier.

Of course, an easy way to address the issues is to install SQL Server 2012 SP1 CTP3 management tools on SP2013-SRV, but I don’t want to use this server for administrative purposes because this server accepts connections from any user. It’s conceivably more secure to host the management tools on a separate server that isn’t so publicly accessible. Accordingly, I decided to add a second SharePoint server SP2013-CA to the farm, move the Central Administration site to it, and install the SQL Server 2012 SP1 CTP3 management tools, as in the following figure. Now life is easy.

Here are the steps to set up the administrative server:

  1. Run the SharePoint 2013 Products Configuration Wizard on SP2013-SRV , accept the default option “Do not disconnect from this server farm”, and then on the next wizard page called Modify SharePoint Central Administration Web Application Settings select “Yes, I want to remove the web site from this machine” to remove the Central Administration site.
  2. Verify that the Central Administration site was removed from SP2013-SRV, and then install SharePoint 2013 on SP2013-CA. When SharePoint Setup completes, choose to run the SharePoint 2013 Products Configuration Wizard, connect to the existing farm, and verify that the wizard configures the new server to host the Central Administration site.
  3. Download and install the Analysis Services data providers (MSOLAP, ADOMD.NET, and AMO) through spPowerPivot.msi from the SQL Server 2012 SP1 CTP3 Feature Pack at https://www.microsoft.com/en-us/download/details.aspx?id=30375. As a general rule, you should perform this step on all SharePoint servers in a BI-enabled farm, and this administrative server is no exception. Note that it isn’t necessary to install or run the PowerPivot Configuration Tool for SharePoint 2013, which is also included spPowerPivot.msi, but you need the data providers.
  4. Run SQL Server 2012 SP1 CTP3 Setup, choose the option to install a new standalone server, select SQL Server Feature Installation (in other words, don’t select the option SQL Server PowerPivot for SharePoint because you are not installing Analysis Services on the administrative server), and then on the Feature Selection page, choose Management Tools – Complete, and finish Setup. It is not necessary to select any other components.
  5. Lock down the administrative server by configuring the local Windows firewall to accept connections only from a known set of IP addresses. The servers inside the farm should be able to communicate with each other but client computers of the general user community should not be allowed to connect to the administrative server.

And that’s it. By using SSMS on the administrative server, it is now uncomplicated to control the Analysis Services configuration. Just connect remotely to the SSAS server and you can use the graphical user interface to register server administrators, configure server properties, and enumerate loaded workbook data models. Of course, you can also use SSMS to configure the SQL Server database engine. The administrative server also helps to streamline troubleshooting. You can run SQL Server Profiler to connect to the SSAS server in order to examine the communication between SharePoint shared services and Analysis Services in great detail. The following screenshot shows me interacting with a PowerPivot workbook in the browser while at the same time tracing the Excel Services/Analysis Services communication in SQL Profiler, and checking in SSMS that the workbook’s data model is loaded on the SSAS server.

As a side note, in case you are curious, the database name SharePoint20Images_0a816ebc5aab45ecba8c349efccc8e7c_5cc70d4c3f26427d81fe77c5ff8045f0_SSPM clearly identifies the database as a data model loaded through Excel Services in SharePoint 2013. The first part refers to the workbook name, possibly truncated if the total database name would exceed 100 characters, followed by a GUID that identifies the Excel Services instance that loaded the model, and then followed by a randomly generated GUID that uniquely identifies the data model on the SSAS server. The last tag, SSPM, stands for Streaming Server Pool Module, which is the component inside the Excel Calculation Services process that maintains the SSAS server pool and loads the data models. Subsequent blog posts will cover in more detail how SSPM interacts with Analysis Services. Stay tuned!