What’s New in SQL Server 2012 SP1 Installation for Business Intelligence
- PowerPivot for SharePoint: Analysis Services provides a backend service for Excel Services to load, query, and refresh PowerPivot data models so that users can interact with Excel workbooks that contain data models in the browser. Analysis Services in SharePoint mode is fully independent of and external to SharePoint, although the server running Analysis Services must be on the same network and Active Directory Domains Services (AD DS) forest as the SharePoint farm.You install and manage Analysis Services using SQL Server installation media and tools. Choose the setup option PowerPivot for SharePoint. After you install Analysis Services in SharePoint mode, the only additional configuration tasks are to grant the SharePoint services accounts server administrator permissions in Analysis Services and to configure Excel Services to point to the Analysis Services instance.For detailed steps on installing a single server, see. Install Analysis Services Server in SharePoint Mode for SharePoint 2013 (SQL Server 2012 SP1).
- spPowerpivot.msi: A Windows Installer package available with the SQL Server 2012 SP1 Feature Pack. The installer enhances the PowerPivot for SharePoint experience with more features such as PowerPivot Gallery and Schedule Data Refresh. The .msi deploys Analysis Services client libraries, the PowerPivot for SharePoint 2013 Configuration tool, and copies PowerPivot for SharePoint 2013 installation files to SharePoint servers.After you run the .msi, you need to run PowerPivot for SharePoint 2013 Configuration to deploy the solutions and configure the farm. For more information see:
- Reporting Services: The overall Reporting Services installation remains the same with SQL Server 2012 SP1 as it was with SQL Server 2012. There is an updated Reporting Services for SharePoint add-in that supports SharePoint 2013. For more information, see Supported Combinations of SharePoint and Reporting Services Components.
Overview of Installation with SharePoint 2013
The SQL Server setup option PowerPivot for SharePoint has no dependencies on SharePoint. It does not use the SharePoint object model or interfaces to support integration. Therefore, Analysis Services can be installed on any computer running Windows Server 2008 R2 or later version. It can be but does not have to be an application server in a SharePoint farm. One of the configuration steps is to point Excel Services to the server running Analysis Services. For load balancing and fault tolerance, it is recommended to install and register multiple Analysis Services servers running in SharePoint mode.
PowerPivot for SharePoint 2013 and Reporting Services in SharePoint mode Single Server Deployment
A single server deployment is useful for testing purposes but it is not recommended for production deployments.
||Excel Service Application. The service application is created as part of the SharePoint installation.
||PowerPivot Service Application. Default name is Default PowerPivot Service Application.
||Reporting Services service application.
||Install the reporting services add-in for SharePoint from either the SQL Server 2012 SP1 installation media or the SQL Server 2012 SP1 feature pack.
||The SharePoint content, configuration, and service application databases.
||Analysis Services Server in SharePoint Mode. Configure the Excel Services Application Data Model Settings to use this server.
Follow the remaining prompts to complete the installation. After the installation completed, resist the urge to run either of the PowerPivot configuration tools. These are used if we actually ARE using full PowerPivot (see below), and that will require a SharePoint server. If you run it on a non SP farm machine, things can get very messed up.
Once that is set up, you need to tell Excel services to use it. Navigate to your Excel Services service application in Central Administration, and select “Data Model Settings”.
Now that the engine is baked into Excel, is there any need for PowerPivot any longer? As we’ve seen, for simple analysis, no. However, if we want to do anything advanced, like filtering import data, modifying the model, creating complex relationships, or using DAX (the tabular answer to MDX), the answer is emphatically yes.
On the client side, PowerPivot is actually included with Excel – no download required anymore. It is disabled by default, and is enabled through the Excel Com Add in interface. You can view this as your individual or team model designer. With it you can create complex models and then share them out via SharePoint. Optionally these very same models can be imported into Analysis Services projects if/when they become mission critical, or too large. Another nice thing is that models created with PowerPivot can be shared on a SharePoint farm that is not using the full PowerPivot for SharePoint (but does need “Analysis Services in SharePoint mode – confused yet?).
On the Server side, we can install PowerPivot for SharePoint, just as we did with SharePoint 2010. The reason that we would want to do this is to gain access to PP for SP features like the Pivot Gallery, but primarily to access refresh capabilities. In the scenarios that I’ve described above, using Excel Services, the data that we’re using is relatively static. Data is imported when creating the model, and is used for analysis, but there is no mechanism to refresh the data in the model in the way that Analysis Services can. PowerPivot for SharePoint offers this capability in SharePoint 2010, and this carries forward with SharePoint 2013.
Another thing that you should note is that the order of operations is important here. If you install Reporting Services – SharePoint prior to installing SharePoint on the farm, the option to create a Reporting Services application will not appear. That’s because it won’t be registered with the farm as a service application. If this happens, you can run the following PowerShell to register the Service Application
The reason that I have installed both the 2008 R2 versions and the 2012 versions is that some components in SharePoint 2013 seems to need the 2008 R2 versions while others use the 2012 versions.
The reason for this is that I now want to run a installation of SQL a second time this time I choose to add features to a existing instance
This time I add Reporting Services in SharePoint integrated mode and the Reporting Services Add-in for SharePoint Products. The reason that I am doing this in a separate installation is that I had some issues with SSRS not showing up in Central Admin when I added this in the first installation. This could probably have been solved in some other way but I wanted to ensure that the install went through without any glitches.
After the installation has finished it is time to install PowerPivot for SharePoint. So I start the installation of SQL Server 2012 SP 1 Preview a third time. This time I choose to add a new instance
After the installation has finished it is time to run the configuration for PowerPivot for SharePoint. You can see that there are two different configuration tools one for SharePoint 2010 and one for SharePoint 2013. Since I am using 2013 I start that tool.
Now it’s time to install SQL Server 2012. Note that Service Pack 1 for SQL Server 2012 is a requirement for PowerPivot for SharePoint. If you do not install it you will get an error on the “SharePoint installation requirement for PowerPivot for SharePoint” rule when you try to install PowerPivot for SharePoint.
My previous blog post, “Verifying the Excel Services Configuration for PowerPivot in SharePoint 2013,” discussed steps to verify that Excel Services can load workbook data models on an out-of-farm PowerPivot server running SQL Server 2012 SP1 CTP3 Analysis Services (SSAS) in SharePoint deployment mode so that users can enjoy full workbook interactivity in the browser. But data exploration in the browser is only the first step. The next logical step is to consume the data model as a data source in PerformancePoint Services, Power View, or in a custom solution running inside the farm. Let’s take a look at an architecture diagram to understand how this scenario works in SharePoint 2013.
And there you have it, SPClient is missing! Apparently, I forgot to install the data providers via spPowerPivot.msi on this computer. I installed Reporting Services using SQL Setup, but in SQL Server 2012 SP1 CTP3, the Reporting Services options do not install the SPClient assembly. This is fixed in later builds. For CTP3, downloading and installing spPowerPivot.msi on the SharePoint server solves this problem. Note that you don’t need to run the PowerPivot Configuration Tool. Simply install spPowerPivot.msi to fully deploy the data providers included in the installer package, as illustrated in the following screenshot. Rerunning the PowerShell script now shows that the SPClient assembly is present. Of course, you can also check Programs and Features in Control Panel to see if the spPowerPivot.msi package is installed.