SQL Server 2012 SP1 Analysis Server in SharePoint mode runs outside a SharePoint 2013 farm. Analysis Services server can be installed on a server that is part of the SharePoint farm but the Analysis Services is not dependent on services in the SharePoint in farm.

Example Deployment Topologies:

REF: http://technet.microsoft.com/en-us/library/jj218795(v=sql.110).aspx


PowerPivot Deployment Topologies

 

      

SQL Server Reporting Services and PowerPivot Deployment Topologies      

 Pre-requisites

To use PowerPivot for SharePoint on SharePoint 2013, it is required to install PowerPivot for SharePoint with the Slipstream version of SQL Server 2012 SP1. If you install SQL Server 2012 and then use the upgrade version of SQL Server 2012 SP1 to upgrade, the environment will not support SharePoint 2013.

B. Install SQL Server Analysis Services 2012 in SharePoint integrated mode

We're going to install PowerPivot Server (SSAS in SharePoint mode) in SQL BI Server which is outside the SharePoint Farm.

 

C. Verify that you can connect To PowerPivot instance

To make sure you can access SSAS from remote computers make sure the port is activated in firewall

http://technet.microsoft.com/en-us/library/ms174937(v=sql.110).aspx

D. Install PowerPivot for SharePoint 2013 in SharePoint Server (Application role)

Note: http://technet.microsoft.com/en-us/library/jj218792(v=sql.110).aspx

Where to install spPowerPivot.msi?

A recommended best practice is to install spPowerPivot.msi on all servers in the SharePoint farm for configuration consistency, including Application servers and webfront end servers. The installer package includes the Analysis Services data providers as well as the PowerPivot for SharePoint 2013 configuration tool. When you install spPowerPivot.msi you can customize the installation by excluding individual components.

Data providers: Several SharePoint and SQL Server technologies use the Analysis Services data providers including Excel Services, PerformancePoint Services, and Power View. Installing spPowerPivot.msi on all SharePoint servers ensures the full set of Analysis Services data providers and PowerPivot connectivity is consistently available across the farm.

You must install the Analysis Services data providers on a SharePoint 2013 server using spPowerPivot.msi. Other installer packages available in the SQL Server 2012. SP1 Feature Pack are not supported because these packages do not include the SharePoint 2013 support files that the data providers require in this environment.

Configuration Tool: The PowerPivot for SharePoint 2013 configuration tool is required on only one of the SharePoint servers. However a recommended best practice in multi-server farms is to install the configuration tool on at least two servers so you have access to the configuration tool if one of the two servers is offline.

Download the PowerPivot for SharePoint 2013 add-in (SpPowerPivot.msi) from the SQL Server 2012 SP1 Feature Pack. If you have SQL Server 2012 SP1 slipstream version, you can find it in this location.

D:PCUSOURCE1033_ENU_LPx64Setup

The PowerPivot for SharePoint 2013 needs to be installed and configured in the application server role. If you have redundant application role servers, you need to install in that server as well. You need not install or configure PowerPivot for SharePoint in web server role, however, you should customize the options to install other necessary libraries in web server roles.

http://technet.microsoft.com/en-us/library/jj218792(v=sql.110).aspx


E. Configure PowerPivot for SharePoint 2013

(http://technet.microsoft.com/en-us/library/dn456880(v=office.15).aspx)

Prepare accounts and permissions

  • Register a managed account to run the PowerPivot application pool, if not registered already.
  • Grant the managed account access to the content database. Run the following script for each web application associated with PowerPivot

$w = Get-SPWebApplication -identity http://
$w.GrantAccessToProcessIdentity(" ")

 

If in the future you add more content databases, you'll need to rerun these cmdlets to ensure that Power Pivot has access to the new databases.

  • ​Create a target application store in Secure Store 

The next step is to create a target application in Secure Store for the Power Pivot unattended data refresh account. This target application will be used to refresh the data in your Power Pivot reports.    

  • In Central Administration, under Application Management, click Manage service applications.
  • Click the Secure Store service application.
  • In the ribbon, click New.
  • In the Target Application ID box, type PowerPivotUnattendedAccount.
  • Be sure to use this value so that the Power Pivot for SharePoint 2013 Configuration tool will recognize it.
  • Type a display name and email address.
  • For Target Application Type, make sure Individual is selected, and then click Next.
  • Ensure Field Type is set to Windows User Name and Windows Password, and click Next.
  • Type a name or group for the Target Application Administrators, and then click OK.
  • Select the target application that you just created, and then in the Credentials section of the ribbon, click Set.
  • For Credential Owner, type the account that you created for the Power Pivot application pool (the one that you just configured as a managed account).
  • Type the user name and password of the credentials that have access to your data sources. I have created one unattended service account which I make sure has access to data sources, and I use it wherever needed.    

  • ​​​​​Grant access to SharePoint admin content database.

In order for the Power Pivot Management Dashboard reports to work, the account that runs the Excel Services application pool must have read access to the SharePoint Server admin content database. You configure this in SQL Server.

  • Open SQL Server Management Studio and connect to the database engine.
  • Expand Security and double-click the account that is used for the Excel Services application pool.
  • On the User Mapping page, click the SharePoint Admin content database (usually named SharePoint_AdminContent_).
  • Select the SPDataAccess database role check box.
  • Click OK    
  • ​​​​​​​​​Grant Exce​l Service Application account administrative privileges on SSAS Server SharePoint mode.  

F. Run PowerPivot for SharePoint Configuration in application server role

  • Run this program on the server that is running the SharePoint Central Administration website.

 

Select "Create PowerPivot Service Application" option in left panel and uncheck "Include this action in the task list". We'll provision the service application manually. Also, in Configure or Repair PowerPivot for SharePoint, put the name of the PowerPivot instance of SSAS Server (PowerPivot Server) that we installed initially in beginning of this post. Validate the settings and then run.

  • ​Create a Power Pivot service application

    • In Central Administration, under Application Managementclick Manage service applications.
    • In the ribbon, click New, and then click SQL Server Power Pivot Service Application.
    • Type a name for the service application in the Name text box.
    • Type a name for the new application pool in the Application pool name text box.
    • From the Configurable dropdown list, choose the account that you registered as a managed account for the Power Pivot application pool.
    • Click OK
       
  • Configure the Power Pivot unattended data refresh account

    • In Central Administration, under Application Management, click Manage service applications.
    • Click the Power Pivot service application.
    • Under Actions, click Configure service application settings.
    • Under Data Refresh (about a third of the way down), type the target application ID of the target application that you created (PowerPivotUnattendedAccount) in the PowerPivot Unattended Data Refresh Account box.
    • Click OK
  •  ​Re-run PowerPivot for SharePoint 2013 Configuration tool

    Now that the service application has been created and we've configured the unattended data refresh account, we need to run the Power Pivot for SharePoint 2013 Configuration tool once again to complete the remainder of the steps

    • Click Start > All Programs > Microsoft SQL Server 2012 > Power Pivot for SharePoint 2013 Configuration.
    • Choose the Configure or Repair Power Pivot for SharePoint option, and then click OK.
    • In the Power Pivot Configuration Tool, in the left pane, select Activate Power Pivot Feature in a Site Collection.
    • On the Parameters tab, choose the site collection where you want to active Power Pivot from the Site URL dropdown list.

If you want to activate Power Pivot in more than one site collection, choose one to start with here. It's easy to add more later

  • If you changed the default site collection setting, click Validate.
  • Click Run, and then click Yes to confirm.
  • When the action completes, click OK, and then click Exit.

Power Pivot setup is now complete and Power Pivot is ready to use.

G. Run PowerPivot for SharePoint 2013 Configuration tool on other servers in the farm

http://technet.microsoft.com/en-us/library/dn456880(v=office.15).aspx

Because we've only run the Power Pivot for SharePoint 2013 Configuration tool on one server, the SQL Server Power Pivot System Service will only be available to run on this server. It's fine to use Power Pivot this way, but if you have multiple servers in your farm, we recommend that you install the service on all of them. This will allow you to change which server you run the service on (for example, if you want to balance out the services on your farm) or to run the service on multiple servers for greater capacity.

You install the SQL Server Power Pivot System Service by running the Power Pivot for SharePoint 2013 Configuration tool on the other servers in your farm.

When you run the Power Pivot for SharePoint 2013 Configuration tool, it installs and turns on the SQL Server Power Pivot System Service on each server where you run it. You probably don't want this service running on every server, so be sure to go to Manage services on server in Central Administration and stop the service on the servers where you don't want to run it.

H. Install PowerPivot client library in SharePoint Server (web server role)


I. Verify PowerPivot for SharePoint installation by using sample PowerPivot and PowerPivot workbook and report.

To test PowerPivot, we're going to download some sample PowerPivot workbooks and publish it to SharePoint.

  • In the PowerPivot gallery, create a folder called "HelloWorldPicnicSQL2012"
  • In that folder, create another folder named "images" and upload all images downloaded above to this folder.
  • Open the "HelloWorldPicnicPowerViewRTM.xlsx" file in Excel 2013 that has PowerPivot for Excel 2013 included. Open the PowerPivot window.

 

  • Here, we are first going to update the references to the images files that have now been uploaded to SharePoint. Go to the Items worksheet and update the columns: Photo, Drawing, Category Drawing and Category Photo to link to the new image files location.
  • Upload the above updated worksheet and the report to PowerPivot gallery in folder "HelloWorldPicnicSQL2012".
  • Select the report dropdown and click "Manage Data Sources" option, then click on EntityDataSource link.

 

  • In the Data Source Link, click on custom data source and add in details for this data source as shown in figure below. 

 

If you come across the error as shown above, "We cannot locate a server to load the workbook data model", couple of things to check

  • Make sure that in excel service application SSAS server is listed
  • PowerPivot service application is checked in web application service connection.
  • Make sure that SQL Browser is running in SSAS SharePoint mode (PowerPivot Server) and port is not blocked by firewall.
  • The excel service account has administrative privileges on SSAS SharePoint mode.

     Perform an IISReset if you make this change.

Test the data source connection again. If connection was successful, clock OK and then Close.

  • Now we've uploaded PowerPivot file, PowerView report and also report to use PowerPivot file as data source, we'll now see if the workbook, report, and SSAS are all working.
  • First we'll check if we can browse the PowerPivot workbook and verify that slicers are working.

To do this, open the HelloWorldPicnicPowerViewRTM.xlsx file and filter the charts using slicer and verify that chart gets refreshed without any issue. 

 

  • Next we'll check that the PowerView report is working. To check this, open HelloWorldPicnicReport.rdlx. If everything is set up properly, including the links to the images file that we fixed earlier, then you should see report like as shown below. Work with the report to ensure that report is working fine.

 

  • Finally, we'll login to SSAS Server and see that it's being used by Excel Services to load the data models. Login to the PowerPivot instance of SSAS to see if there's been any databases provisioned by PowerPivot and PowerView report. 

            

 

Now we've confirmed that SSAS Server SharePoint Mode is being used as expected.

Leave a Reply

Your email address will not be published. Required fields are marked *