Configure a Gemini Shared Service Application
Important: Click PowerPivot for SharePoint or Configure PowerPivot for SharePoint to view the latest PowerPivot product documentation on the Microsoft web site.
Microsoft SQL Server PowerPivot for SharePoint is a collection of server components, dashboards, web parts, and library templates for sharing PowerPivot data in Excel 2010 workbooks in a SharePoint farm. It is required infrastructure for sharing PowerPivot data in Excel workbooks in SharePoint.
To use PowerPivot for SharePoint, you must create and configure a PowerPivot service application. If you installed PowerPivot for SharePoint on a new SharePoint server that was configured by SQL Server Setup, the service application might be created and configured already. Otherwise, you must follow the steps in this article to create an application for the PowerPivot service you installed.
What do you want to do?
Learn more about PowerPivot for SharePoint
PowerPivot for SharePoint is Analysis Services integration with SharePoint 2010, introduced in SQL Server 2008 R2 as part of a broad business intelligence strategy that enables self-service data analytics on the workstation and on a SharePoint farm. You install it by running the SQL Server Setup program on a SharePoint server. After it is installed, you can configure and manage it as as a shared service in Central Administration, similar to how you might manage Excel Services or Access Services.
PowerPivot for SharePoint works with Excel Services, intercepting requests for PowerPivot data inside the Excel 2010 (.xlsx) workbook and forwarding those requests to a PowerPivot service in the farm.
PowerPivot also works with the Analysis Services in-memory data storage and processing engine that supports large scale data in a PowerPivot workbook. The Analysis Services engine service and PowerPivot service are installed together and work as a single unit. The PowerPivot service provides integration with SharePoint, while the Analysis Services engine service responds to requests for loading and unloading PowerPivot data in memory on the same application server.
Create a PowerPivot Service Application
A PowerPivot service application is a specific configuration of a PowerPivot service. Although you need only one PowerPivot service application, each SharePoint web application can have its own PowerPivot service application if you want to vary settings or isolate application configuration data. A separate service application database will be generated for each service application you create, isolating PowerPivot service application and configuration data from other Web applications that also use the same physical server instance.
-
Start Central Administration.
-
In Application Management, click Manage Service Applications.
-
Click Service Applications near the top of the page.
-
On the New button, click the down arrow.
-
Select SQL Server PowerPivot Service. If it does not appear in the list, PowerPivot for SharePoint was not installed correctly or the feature is not enabled for the farm.
-
In the Create New PowerPivot Service Application page, enter a name for the application. The default is PowerPivotServiceApplication<number>. If you are creating multiple PowerPivot service applications, a descriptive name will help other administrators understand how it should be used.
-
In Application Pool, create a new application pool and a security identity for the application if you want to run it under a different account (recommended). Be sure to specify a domain user account if plan to scale out the deployment to include additional service instances that will run under the same identity.
-
Enter information used to create a database that stores application data for this service application. For database server, the default value is the SQL Server Database Engine instance that hosts the farm configuration databases.
-
In Database Name, the default value is PowerPivotServiceApplication1_<guid>. You must create a unique database for each PowerPivot service application. The default database name corresponds to the default name of the service application. If you entered a unique service application name, follow a similar naming convention for your database name so that you can manage them together.
-
In Database Authentication, the default is Windows Authentication. The default value always works if the PowerPivot service application is running on the same computer as the database or on a computer that is accessed over a trusted connection. It might not work if the service and database are on different computers or domains. In this case, use SQL Authentication. Before you do, verify the server supports mixed authentication and that encrypted connections are enabled. For more information, see Encrypting Connections to SQL Server in SQL Server Books Online.
-
Select the checkbox for Add service application proxy to the default server's group.This adds the service application connection to the default service connection group. If this is the only PowerPivot service application in the group, the application will be immediately available to new and existing SharePoint Web applications that use the default service connection group. If it is an additional entry in a connection group that already has a Gemini service application, it will not be used unless you select it in a custom list.
-
Click Save.
Configure a PowerPivot Service Application
A PowerPivot service application is created using a default configuration. The default settings are recommended for most scenarios. Change them only if you encounter slow response time or dropped connections, or if you are varying PowerPivot service configuration for specific SharePoint Web applications.
-
Start Central Administration.
-
In Application Management, click Manage Service Applications. In the list of service applications, you should see the service application you just created and named. The default is PowerPivotServiceApplication1, but yours will be different if you created a second service application.
-
Double-click the PowerPivot service application from the list. The PowerPivot Settings page appears.
-
In Database Load Timeout, increase or decrease the value to change how long the PowerPivot service waits for a response from the Analysis Services engine service instance to which it forwarded a load data request. Because very large datasets take time to move over the wire, you must allow sufficient time for the PowerPivot service instance to retrieve the Excel workbook and move the PowerPivot data to the Analysis Services instance for query processing. Because PowerPivot data can be unusually large, the default value is 30 minutes.
-
In Connection Pool Timeout, increase or decrease the value to change how many minutes PowerPivot service will keep an idle data connection open. The default value is 5 minutes. During this period, the PowerPivot service will reuse an idle data connection for read-only requests that come from the same SharePoint user for the same PowerPivot data source. If no further requests are received for that data during the period specified, the connection is removed from the pool. Valid values are 0 or 1 to maximum integer.
-
In Maximum Data Connection Pool Size, increase or decrease the value to change the maximum number of idle connections the PowerPivot service will create individual connection pools for each SharePoint user, PowerPivot data source, and PowerPivot service instance combination. The default value is 100 idle connections. Valid values are -1 (unlimited), 0 (disables user connection pooling), or 1 to maximum integer. These connection pools enable the service to more efficiently support ongoing connections to the same read-only data by the same user. However, you should always limit the number of open connections to ensure that they do not accumulate over time and consume server memory unnecessarily. Note that changing the limit on connection pool size will not result in dropped connections. The PowerPivot service will never deny a connection based on connection pool settings.
-
In Maximum Server Connection Pool Size, increase or decrease the value to change the number of open connections in a connection pool created for a PowerPivot service connection to the Analysis Services engine service. Each PowerPivot service instance opens a separate administrative connection that has Analysis Services Sysadmin permission on the engine instance. PowerPivot service creates a separate pool to reuse administrative connections for the purpose of checking for idle connections and monitoring server health. The default value is 5 connections. Valid values are -1 (unlimited), 0 (disables admin connection pooling), or 1 to maximum integer.
-
In Allocation Method, you can specify the load balancing schema that the PowerPivot middle tier uses to select a specific PowerPivot service instance for handling a client request. The default is Round Robin, which allocates requests to servers in the same repeating order, regardless of whether a service is busy or idle. You can choose Health Based to allocate requests based on server state, as measured by available memory and processor utilization.
-
In Data Refresh, in Business Hours, you can specify a range of hours that determines a business day. Data refresh schedules can run after the close of a business day to pick up transactional data that was generated during normal business hours.
-
In Data Refresh History, you can specify how long to retain a historical record of data refresh processing. This information appears in data refresh history pages that are kept for each workbook that uses data refresh.
-
In Usage Data Collection, in Query Reporting Interval, specify an interval of time for reporting query statistics. Query statistics are reported as a single event to minimize server-to-server communication.
-
In Usage Data History, specify how long to keep a historical record of usage data.
-
In Usage Data Collection, in each query response threshold, specify an upper limit that determines where one category stops and another begins. These categories establish a baseline against which query behavior is measured. You can use these categories to monitor trends in query response times for your system.
-
Click OK to save your changes. Changes to the load timeout or allocation method are only applied to new incoming requests. Requests that are already in progress are subject to the values that were in effect when the request was received.
Assign a PowerPivot Service Application to a Web Application
After you configure a PowerPivot service application, you can assign it to a Web application by adding it to the service application connection list for that Web application. There are two ways to do this:
-
Add it to the Default connection group. The default connection group is a collection of service application connections that are available to any Web application that references it.
-
Create a Custom connection list for a specific Web application. If you created multiple PowerPivot service applications, you can choose which one to use by selecting it in a custom list.
The default connection group will accept more than one service application of the same type. Be aware, however, that if you add additional PowerPivot service applications, they will be ignored. Only the first instance in the list will be used.
-
In Central Administration, in Application Management, click Manage web applications.
-
Click Web Applications at the top of the page.
-
Select the application for which you want to assign a connection (for example, SharePoint -80).
-
Click Service Connections.
-
In Edit the following group of associations, select default or [custom].
-
For [custom], select the checkbox next to each service application connection you want to use. If you have multiple PowerPivot service applications (indicated by Type set to Microsoft.AnalysisServices.SharePoint.Integration.PowerPivotServiceApplicationProxy), be sure to choose just one.
-
Click OK.
No comments:
Post a Comment