Information, tips, news and announcements about SQL Server Analysis Services and PowerPivot directly from the product team.
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:
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!
Hi Kay! Great Article. I wish I had found this sooner.
Can you comment on the need for this admin server now that SP1 is RTM? Or can you point me towards another article that might talk about this?
The question of a dedicated admin box in a farm is somewhat unrelated to SP1 versus RTM, or even SP2010 versus SP2013. I just used SP2013 and SP1 here because these are our current versions. Ultimately, you are dealing with a security question. Is your environment secure enough so that you can leave the admin tools fully available at all times? If yes, you might not need a dedicated admin box. If No, such as in an Extranet configuration, it might make sense to run the farm without any WFE hosting the CA Web app. A dedicated admin machine can be placed in a secure network area that is unavailable to the client segments. It depends on the specific situation.
Hope this helps.