This is section XIV of the MBP SharePoint 2013 BI Farm Setup Guide.
This section describes how to configure Business Intelligence, including detailed steps to configure most of the key BI features you will likely need in a SharePoint farm. Specifically:
This guide does not include configuration of PerformancePoint Services.
This section describes how to configure Business Intelligence, including detailed steps to configure most of the key BI features you will likely need in a SharePoint farm. Specifically:
- Secure Store Service
- The Business Intelligence Center (BIC) Site Collection
- Reporting Services (AKA SQL Server Report Server in SharePoint Mode and Reporting Services Add-In)
- Excel Services
- PowerPivot for SharePoint
- PowerView
This guide does not include configuration of PerformancePoint Services.
Below are links to all the sections of this Guide.
|
XV. Turn Windows Firewalls Back On for All Servers
|
As mentioned in Section I, MBP stands for "Martin's Best Practices." I use MBP as the acronym for the enterprise, the domain and also the name of the farm. MBP does not correspond to any actual company or client. You may use this guide as a template by globally replacing "MBP" with the acronym or name of your company or client.
A. Create the Secure Store Service
Create the Secure Store Service by running the CreateSecureStoreService.ps1 script on any server, in this case the Central Admin server, MBP-CA.1. Connect to the SharePoint Server that has the PowerShell scripts (MBP-CA, in this case) and login as the Setup User Account (sp_admin)
2. Right-click SharePoint 2013 Management Shell and select Run as Administrator
3. At the UAC prompt, click Yes
4. In the SharePoint 2013 Management Shell, navigate to the folder (F:\Scripts) where the scripts reside, then enter the following command:
.\CreateSecureStoreService.ps1
B. Set Up the New Secure Store Service
Before we can use the new Secure Store Service, we must generate a new Key and add the setup account as one of the administrators.1. Navigate to Central Administration --> Manage service applications
2. Select the Secure Store Service then click the Manage button on the ribbon
SharePoint displays “Before creating a new Secure Store Target Application, you must first generate a new key…”
3. Click the Generate New Key button on the ribbon
4. From the Generate New Key dialog box, enter a Pass Phrase (different than the Farm Security Pass Phrase)
Secure Store Service Database Pass Phrase: Secure Store Service Rocks
5. Click OK
SharePoint displays “this shouldn’t take long” followed by a message that there are no Secure Store Target applications in the SSS.
Now add the Setup User Account (e.g. mbp\sp_admin) as one of the Administrators of the Secure Store Service.
6. Navigate back to the SharePoint Central Administration Manage Service Applications page.
7. Click to the right of the Secure Store Service link to select the Secure Store Service but not open the “Manage SSS Application” page. With the Secure Store Service row selected, click the Administrators button from the ribbon
8. From the Administrators for Secure Store Service dialog, Add the Setup User Account
9. Grant the Setup account Full Control and click OK .
C. Create the Business Intelligence Center Web Application
The Logical Architecture we planned for the MBP domain back in section II – C calls for a web application (bi.mbp.com) dedicated to the Business Intelligence Center site collection. In this section we create that Web Application and configure it into the MBP farm.These steps assume you previously configured the host (A) record in DNS for bi.mbp.com [Section III – E].
1. Connect to any SharePoint Server where the PowerShell scripts reside (MBP-CA, in this case) and login using the Setup User Account (sp_admin).
2. Right-mouse click on SharePoint 2013 Management Shell and select Run as Administrator. At the UAC prompt, click Yes.
3. In the SharePoint 2013 Management Shell window, navigate to the folder that contains the PowerShell scripts (F:\Scripts in this case).
4. Run the following PowerShell script to create the BI web application:
.\CreateBIWebApp.ps1
CreateBIWebApp.ps1 uses the MBP_BI_URL and other environment variables specified earlier to create a new Web Application and host header. This script takes several minutes to complete.
D. Create the BI Center Site Collection
Create the Business Intelligence Center site collection by running the CreateBISiteCollection.ps1 PowerShell script. This creates a site collection using the BICenterSite#0 template.1. Connect to any SharePoint server (MBP-CA in this case) and login using the Setup user account (sp_admin)
2. Right-mouse click on SharePoint 2013 Management Shell and select Run as Administrator. At the UAC prompt, click Yes.
3. In the SharePoint 2013 Management Shell window, navigate to the folder that contains the PowerShell scripts (F:\Scripts in this case).
4. Run the following PowerShell script to create the BI web application:
.\CreateBISiteCollection.ps1
Note that SharePoint automatically adds the URL for the BI Center to the “Local SharePoint Sites” Search Content Source so that crawl will include content from this BI web application in your search results.
Now that your farm has a BI Center site, you normally want to share it with users by adding them the “Visitors” or “Members” groups.
1. Open your BI site (http://bi.mbp.com in this case).
2. Click the “Share” icon in the upper right corner (near “Follow”)
3. From the Share ‘BI Center’ dialog, in the Invite people to ‘Contribute’ field, enter Everyone
4. Click Share
E. Install Reporting Services Report Server in SharePoint Mode
This section specifies how to install SQL Server Report Server in SharePoint mode on the SharePoint BI services server you previously added to the farm (MBP-BI in this case).Recall from section II – D that our architecture and plan for the MBP farm calls for the SQL Server Reporting Services Service to run only on the BI Services server (MBP-BI) and not on any of the other servers. After installing Reporting Services – SharePoint, the SQL Server Reporting Services Service will be running on MBP-BI.
This section requires that a copy of the SQL Server 2012 with SP1 install media (ISO file) be available on the server that will run BI services. If you have not previously copied this ISO to the BI server, do so now.
1. Connect to the SharePoint server that will run BI Services (MBP-BI, in this case) and log in using the Setup User account.
2. Mount the SQL Server 2012 SP1 Installation media (ISO).
3. Run Setup.exe (the SQL Server Installation Wizard).
4. In the SQL Server Installation Center, on the left side of the wizard, Click Installation.
5. Click New SQL Server stand-alone installation or add features to an existing installation.
SQL Server 2012 Setup displays the Setup Support Rules page.
6. Assuming all rules passed, Click OK
SQL Server 2012 Setup displays the Product Key page.
7. Enter your product key (if necessary) and click Next >
8. On the License Terms page, accept the licenses terms and click Next>
9. On the Product Updates page click Next>
SQL Server 2012 Setup displays the Install Setup Files page.
a. Depending on what is already installed on your computer, you might see the following message:
“One or more affected files have operations pending. You must restart your computer after the setup process is completed.”
b. Click Ok.
c. SQL Server 2012 Setup may display the Setup Support Rules page again.
d. After all the support files have completed installing and the Setup Support Rules pages shows "Operation completed," click Next.
10. If you are building a one-server farm and/or previously installed SQL Server on this system, the next screen may be the Installation Type screen, asking whether you want to “Perform a new installation of SQL Server 2012” or “Add features to an existing instance of SQL Server 2012” (e.g. you may already have an instance named “SPDB” on this system). In this case accept the default “Perform a new installation of SQL Server 2012”, click Next and after 2 more screens you will get the Setup Role screen of the next step.
11. On the Setup Role page, accept the default selection SQL Server Feature Installation and Click Next
12. When Setup displays the Feature Selection page, only select Reporting Services – SharePoint because, for the MBP farm, we are only installing services on the SharePoint BI services server (MBP-BI). If your server is also a Web Front End, or if your farm is a single-server farm, also select Reporting Services Add-in for SharePoint Products. For the MBP farm, the next section of this guide has steps to run SQL Server Feature Installation again and install Reporting Services Add-in for SharePoint Products on the WFE server (and on the CA server).
13. Click Next.
Setup displays the Installation Rules page.
14. When the Installation Rules page displays “Operation completed,” click Next.
15. Setup displays the Disk Space Requirements page. Click Next.
16. When Setup displays the Reporting Services Configuration page, accept the default selection “Reporting Services SharePoint Integrated Mode (Install only)” and click Next.
17. When Setup displays the Error Reporting page, click Next.
18. When Setup displays Installation Configuration Rules page with status “Operation completed,” click Next.
19. When Setup displays the Ready to Install screen, click Install
Setup will display the “Installation Progress:” page:
20. When Setup displays “Complete” click Close.
21. Restart the server if prompts during setup indicated a restart is required.
F. Install Reporting Services add-in for SharePoint Products
This section specifies how to install SQL Server Reporting Services add-in for SharePoint Products. You must install this add-in on the SharePoint Web Front-End server, the SharePoint App (Central Admin) server, and any other SharePoint server that may render reports or Reporting Services management pages. (ref: http://msdn.microsoft.com/en-us/library/ms157231.aspx -- The Reporting Services add-in must be installed on each web server from which you want to utilize the web application features such as viewing reports or Reporting Services management pages for tasks such as managing data sources or subscriptions. )1. Connect to the SharePoint Web Front-End server (MBP-WFEI, in this case) and log in using the Setup User account.
2. Mount the SQL Server 2012 Business Intelligence with SP1 Installation media (ISO).
3. Run Setup.exe (the SQL Server Installation Wizard).
4. From the SQL Server Installation Center, in the left side of the wizard, click Installation
5. Select New SQL Server stand-alone installation or add features to an existing installation
6. When the SQL Server 2012 Setup displays the Setup Support Rules page with “Operation completed”, click OK
7. On the Product Key page, enter the product key if necessary and click Next
8. On the License Terms page, select I accept the license terms and click Next
9. On the Product Updates page click Next
SQL Server 2012 Setup displays the Install Setup Files page and installs setup files.
If you see the message, “One or more affected files have operations pending. You must restart your computer after the setup process is completed”, click OK.
10. When the SQL Server 2012 Setup displays the Setup Support Rules page with “Operation completed”, click Next >
11. When SQL Server 2012 Setup displays the Setup Role page, accept the default selection “SQL Server Feature Installation” and click Next >
12. On the Feature Selection page, make the following selections:
a. Reporting Services Add-in for SharePoint Products and
b. (optional) Management Tools – Basic
13. Accept the other defaults and click Next >
14. When the SQL Server 2012 Setup displays the Installation Rules page with “Operation completed”, click Next >
15. When SQL Server 2012 Setup displays the Disk Space Requirements page, click Next >
16. When SQL Server 2012 Setup displays the Error Reporting page, click Next >
17. When SQL Server 2012 Setup displays the Installation Configuration Rules page, click Next >
18. When SQL Server 2012 Setup displays the Ready to Install page, click Install
SQL Server 2012 Setup displays Installation Progress for several minutes.
If Install displays a message about Computer restart required, click OK.
If install ends with one or more failures, they can often be resolved by shutting down and restarting the server, then re-starting the installation of just the component(s) that failed to install.
19. When SQL Server 2012 Setup displays the Complete window, click Close.
20. Restart the server if Install displayed a message about Computer restart required.
21. Repeat steps F 1 – F 20 on the Central Admin server MBP-CA
Install Analysis Services Server in SharePoint Mode for SharePoint 2013 (SQL Server 2012 SP1)
G. Install an Analysis Services Server in SharePoint Mode
In this section we install a SQL Server Analysis Services (SSAS) Server in SharePoint mode on the SSAS server (NPB-SSAS). This install requires the SQL Server 2012 (Business Intelligence or Enterprise Edition) with SP1 x64 DVD, running the SQL Server Setup and selecting SQL Server PowerPivot for SharePoint.Note the “PowerPivot for SharePoint” terminology is confusing. This step installs an instance of SQL Server Analysis Services Server. Despite the term “SQL Server PowerPivot for SharePoint” that we select in the Setup Role page (step 12), this installs an instance of SSAS service that is not to be confused with Power Pivot for SharePoint which is a SharePoint add-in and is discussed in section XI - J. The instance of SSAS service is also sometimes referred to as data model server because you will specify this instance of SSAS in the Excel Services Data Model Settings when you configure Excel Services.
For the MBP farm, we install SSAS now because Excel Services requires an instance of SQL Server Analysis Services (SSAS). Excel Services uses SSAS to perform advanced data analysis calculations. You may be able to skip this section if you already have an instance of SSAS in SharePoint mode running somewhere in your network. Later you will register one or more instances of SQL Server 2012 Analysis Services with Excel Services so Excel Services can perform advanced calculations.
1. Setup the SQL Server PowerPivot for SharePoint Role
This section closely follows Jan Gremmen’s blog post of September 2013 (http://jantjesworld.blogspot.com.br/2013/09/install-analysis-services-server-in.html).When you created the SSAS server (MBP-SSAS) you obtained the ISO file for SQL Server 2012 Business intelligence with Service Pack 1 (x64) - DVD (English), and copied it to the installs folder on the F: (data) drive. Note that Enterprise Edition with SP1 will work fine and includes all the required components.
1. Remote Desktop to MBP-SQL and login as the SQL Administrator: MBP\sql_admin.
2. Mount the ISO file for SQL Server 2012 Business intelligence with Service Pack 1 (x64) - DVD (English)
3. Launch setup.exe (the SQL Server Installation Wizard for SQL Server 2012 + SP1). At the UAC prompt, click . At the UAC prompt, click Yes.
4. From the SQL Server Installation Center, click Installation on the navigation pane to the left.
5. Click New SQL Server stand-alone installation or add features to an existing installation.
6. On the Setup Support Rules page, review any warnings and click OK.
7. If you see the Product Key page, enter your product key for the appropriate edition and click Next.
8. Accept the Microsoft Software License Terms of agreement, and then click Next.
9. On the Product Updates page, accept the defaults and click Next.
10. The Install Setup Files page runs for several minutes.
11. If you see another Setup Support Rules page, review any warnings and click Next.
12. On the Setup Role page, select SQL Server PowerPivot for SharePoint. This option installs Analysis Services in SharePoint mode.
13. Accept the selected option to add an instance of the Database Engine to your installation. The Database Engine this provisions will be installed as a PowerPivot named instance, meaning the instance name will be “PowerPivot”. Later, when you specify a connection to this instance in the Excel Services configuration, you must enter the database name in this format: [servername]\PowerPivot.
14. Click Next.
15. On the Feature Selection page, the feature options are displayed in read-only mode for informational purposes. You cannot add or remove items the preselected items for this role. Click Next.
16. On the Installation Rules page, review any warnings and click Next.
17. On the Instance Configuration page, a read-only instance name of 'POWERPIVOT' is displayed for informational purposes. This instance name is required and it cannot be modified. However, you can enter a unique Instance ID to specify a descriptive directory name and registry keys. Click Next.
18. On the Disk Space Requirements page, verify that you have sufficient disk capacity to install the feature and then click Next.
19. On the Server Configuration page, configure the Service Account Names and Passwords to the accounts we planned for in section II – B and configured in AD in Section III - C.
a. SQL Server Agent – accept the default Account Name and Startup Type
b. SQL Server Database Engine – set the Account Name to the SQL Server Service account e.g. MBP\sql_service. Keep Startup Type: Automatic.
c. SQL Server Analysis Services – set the Account Name to the SQL Server Analysis Services (PowerPivot) service account e.g. MBP\ssas_pvtsvc. Keep Startup Type: Automatic.
d. SQL Server Browser – accept the default Account Name and Startup Type
Jan Gremmen’s blog warns: Never provision service accounts with your own domain user account. Doing so grants the server the same permissions that you have to the resources in your network. If a malicious user compromises the server, that user is logged in under your domain credentials. The user has the permissions to download or use the same data and applications that you do.
20. Click Next
21. On the Database Engine Configuration page, on the Server Configuration tab, under Specify SQL Server administrators, click Add Current User to grant your user account (MBP\sql_admin) administrator permissions on the Database Engine instance.
22. Select the Data Directories tab
23. On the Data Directories tab, change the Data root directory to point to the F: (data) drive. In the Browse for Folder dialog, create a new folder named Microsoft SQL Server on the F: drive and click OK to select this folder.
This automatically changes all the database directories to folders on the F: (data) drive.
24. Click Next.
25. On the Analysis Services Configuration page, add the accounts that will run any service applications that require access to the Analysis Services server instance: all these accounts will need to have Analysis Services Administrative permissions.
a. Click Add Current User to grant your user account (MBP\sql_admin) administrative permissions. You will need administrative permission to configure the server after Setup is finished.
b. Click Add… and add the SharePoint setup user (sp_admin) and any other user who may require administrative permissions to, for example, connect to the Analysis Services service instance in SQL Server Management Studio to troubleshoot database connection problems.
c. Click Add… and add the SharePoint Service Application app pool Account (MBP\sp_svcapp) which is the account that will run the Excel Services.
d. Click Add… and add the Unattended service account (MBP\sp_unattended) which is the account used by Excel and Visio services for data refresh.
e. Click Add… and add the SharePoint farm account (MBP\sp_farm), which is used as the identity of the web application that hosts Central Administration.
26. Click Next.
27. On the Error Reporting page, click Next.
28. On the Installation Configuration Rules page, review any warnings and click Next.
29. On the Ready to Install page, click Install.
30. If you see the dialog Computer Restart Required, click OK.
31. When the installation is complete, click Close.
32. Restart the computer.
2. Install Cumulative Update Package 10 for SQL Server 2012 SP1
Now that you have SSAS installed, go to support.microsoft.com and get the latest Cumulative Update (CU) for the version of SQL Server you installed. We found Cumulative Update 10 for SQL Server 2012 SP1 was required to resolve a SQL Server Management Studio problem (“Cannot show requested dialog”) that occurs when running SQL Server 2012 SP1 on Windows Server 2012 R2.
1. Connect to the SSAS server (MBP-SSAS) and login as the SQL Administrator account (MBP\sql_admin).
2. Open IE and locate Cumulative Update 10 for SQL Server 2012 SP1
3. Click Hotfix Download Available
4. On the Hotfix Request page, select SQLServer2012_SP1_CU10_2954099_11_0_3431_x64
5. Enter E-mail to obtain the hotfix by email.
6. Click Request Hotfix
7. Via your email, download the hotfix, extract and install it on the SSAS server (MBP-SSAS).
8. SQL Server 2012 update runs. Click through the SQL Server 2012 update wizard, accepting the defaults.
9. When the update displays “Complete”, click Close then test that you can launch SQL Server Management Studio and connect to the POWERPIVOT instance.
H. Start Excel Calculation Service and Create Excel Service App
1. Verify App Pool Account has access to Content DBs
The Excel Services service application will run as the MBP SP Service Application app pool account, MBP\sp_svcapp. This account must have access to the content database for the Home Web Application as well as the content database for the BI Center Web Application.1. Connect to the SharePoint Server that has the PowerShell scripts (MBP-CA, in this case) and login as the Setup User Account (sp_admin)
2. Right-click SharePoint 2013 Management Shell and select Run as Administrator
3. At the UAC prompt, click Yes
4. In the SharePoint Management Shell, enter the following PowerShell commands to grant sp_svcapp access to the content database for the Home web app:
$w = Get-SPWebApplication -identity http://home.mbp.com
$w.GrantAccessToProcessIdentity("MBP\sp_svcapp")
5. Repeat the above PowerShell commands to grant sp_svcapp access to the content database for the BI web app.
$w = Get-SPWebApplication -identity http://bi.mbp.com
$w.GrantAccessToProcessIdentity("MBP\sp_svcapp")
To check this access, follow these steps:
1. Open the SQL Server where the SharePoint databases reside (NPB-SQL) and login as the SQL Administrator (sql_admin).
2. Launch SQL Management Studio and Databases sp_content_home. Expand Security Users and select the SP Service Application app pool Account (MBP\sp_svcapp
3. In the Database User dialog, select Membership and you should see sp_svcapp is a member of role SPDataAccess
4. Repeat this for sp_content_bi.
2. Create the Excel Services Service Application
In this section we use the Central Administration UI to create the Excel Services application. (We experienced bugs using our CreateExcelServicesSvcApp.ps1 script and decided it wasn’t worth fixing because the manual steps using the Central Administration UI are just as simple, so that’s what we’ll document in this guide.)a. Start the Excel Calculation Service on the BI server
1. Central Administration Manage services on server2. In the upper right of the Services on Server screen, pull down the Server: drop-down, click Change Server and select the BI server (MBP-BI in this case)
3. Locate Excel Calculation Services and click Start
b. Create the Excel Services Service Application
1. Central Administration Manage service applications
2. On the Manage service applications page, from the ribbon, click New and then Excel Services Application
3. In the Create New Excel Services Application, enter the following values:
Name = Excel Service
Application Pool = Create new application pool
Application pool name = Excel Service App Pool
Select a security account = Configurable
Select MBP\sp_svcapp
4. Click OK.
3. Register the Analysis Services Server (SSAS) with Excel Services
Now that we have installed Analysis Services (SSAS) in SharePoint mode and we also have the Excel Services service application, we can register the instance of SSAS we configured for use by Excel Services. This section roughly follows http://technet.microsoft.com/en-us/library/jj219780(v=office.15).aspx1. Using the SharePoint Setup account (MBP\sp_admin), open Central Administration and navigate to Manage service applications
2. On the Manage Service Applications page, click the Excel Services service application you created with the script
3. From the Excel Services page, click Data Model Settings
4. From the Data Model Settings page, click Add Server
5. From the Add Server page, in the Server Name field, enter the name of the instance of SQL Server 2012 SP1 Analysis Services (SSAS) that you configured in the previous section, in the format <server name>\<instance name>. In the case of the MBP farm, this must be MBP-SSAS\POWERPIVOT.
6. Click OK
I. Suppress OWA Settings for Excel Files
In this section we suppress the setting that causes SharePoint Office Web Apps (OWA) to handle requests to view Excel (xlsx) files. This setting must be suppressed in order for Excel Services to handle Excel workbook interaction, including key Business Intelligence functions.When OWA Server view mode is used to view workbooks, the following BI features will not be available.
• Excel Web Access Web Part
• Refresh OData connections
• View and interact with Power View reports
• View and interact with PowerPivot data models
• Refresh PowerPivot data models
• Refresh data by using the Excel Services unattended service account
• Refresh data by using Effective User Name connections
• Kerberos delegation
We can use filters and slicers for PowerPivot workbooks if we suppress OWA from handling the .xlsx file type and force SharePoint to use SharePoint view mode This section follows technet article http://blogs.technet.com/b/excel_services__powerpivot_for_sharepoint_support_blog/archive/2013/01/31/powerpivot-for-sharepoint-browser-refresh-fails-data-refresh-not-supported-in-office-web-apps.aspx
1. Open a Remote Desktop connection to the Central Administration server (MBP-CA). Login as the SharePoint Setup Account: sp_admin.
2. From the Windows Start page, open the SharePoint 2013 Management Shell as Administrator
3. At the UAC prompt, click Yes.
4. From the SharePoint 2013 Management Shell, run the following PowerShell command
New-SPWOPISuppressionSetting –extension xlsx –action view
J. Install PowerPivot for SharePoint
This section (Install PowerPivot for SP) and the next (Configure PowerPivot for SP) follow the Technet article Configure PowerPivot for SharePoint 2013 (http://technet.microsoft.com/en-us/library/dn456880(v=office.15).aspx).Note that for the MBP farm, we planned to use PowerPivot App pool account, MBP\sp_ppvtsvcapp, as the managed account that will run the application pool for the PowerPivot for SharePoint Service Application.
1. Create the PowerPivotUnattendedAccount Secure Store Target Application
To create a target application named “PowerPivotUnattendedAccount”:1. In Central Administration, under Application Management, click Manage service applications.
2. Click the Secure Store Service application.
If this is your first time creating a Target Application in Secure Store Service, it displays the message, “There are no Secure Target Applications…”
3. In the ribbon, click New.
4. In the Target Application ID box, type PowerPivotUnattendedAccount.
5. You may enter the same PowerPivotUnattendedAccount for the Display Name.
6. Enter the e-mail for a contact for the PowerPivot SSAS application into the Contact E-mail field.
7. In the Target Application Type field, make sure Individual is selected
8. Click Next.
9. In the second page of the wizard, accept the default values (Windows User Name and Windows Password), and click Next
10. In the third page of the Create New Secure Store Target Application wizard, enter the names of users or the name of a group who will have Administrative access to this Secure Store Target Application. For the MBP farm, we’ll just add the setup account mbp\sp_setup.
11. Click OK
12. SharePoint displays the Secure Store Service application page, this time with your new PowerPivotUnattendedAccount listed as a Target Application. Select your new PowerPivotUnattendedAccount target application by clicking the check box to the left, and then in the Credentials section of the ribbon, click Set.
13. In the Set Credentials for Secure Store Target Application dialog, enter the PowerPivot App pool account, MBP\sp_ppvtsvcapp, into the Credential Owner field, because this is the managed account we created for the Power Pivot application pool.
14. In the Windows User Name and Windows Password fields, enter the credentials for MBP\sp_unattended, the Unattended service account for Excel and Visio services, AKA the Unattended data refresh account. This is the account that must have access to the data sources.
15. Click OK.
2. Grant the Service Application Account Read Access to the Admin content DB
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.In the MBP farm, the SP Service Application Pool account, MBP\sp_svcapp, runs the Excel Services application pool (section II – D). The content database for Central Admin is named sp_content_ca (section II – F). In a more default SharePoint farm, the content DB for Central Admin may be named SharePoint_AdminContent_<GUID>.
1. Open SQL Server Management Studio and connect to the SPDB instance of the SQL Server
2. In the Object Explorer, expand the MBP-SQL\SPDB instance and open the Databases folder.
3. Expand the sp_content_ca database, expand the Security folder, and expand the Users folder.
4. If MBP\sp_svcapp is already in the Users folder, double-click it to open Database User dialog.
5. If MBP\sp_svcapp is not yet in the Users folder, right-mouse-click on the Users folder and add a new User of type Windows user. Add MBP\spsvcapp.
6. In the Database User dialog, select the Membership page.
7. Select the SPDataAccess Role
8. Click OK.
9. Exit SQL Server Management Studio.
3. Install PowerPivot for SharePoint
Install PowerPivot for SharePoint (spPowerPivot.msi) on all SharePoint servers in the farm.1. Using the Setup User account, log in to one of the servers that has a copy of the SQL Server 2012 with SP1 setup files. In the MBP farm, this file is on server mbp-sql and mbp-ssas.
2. Mount the SQL Server 2012 with SP1 Installation media (ISO file).
3. Search the mounted SQL Server install disk for the file SpPowerPivot.msi
4. Copy the SpPowerPivot.msi file to the installs folder on every SharePoint server. In the MBP domain, we have 4:
a. MBP-CA
b. MBP-WFE1
c. MBP-SEARCH
d. MBP-BI
5. Connect to the first SharePoint server and login with the SharePoint setup account (sp_admin). We are only installing SpPowerPivot at this time, not configuring, so the order in which you install on the servers is not important.
6. Run SpPowerPivot.msi
7. At Welcome, click Next.
8. Accept the license agreement and click Next.
9. At the Feature Selection page, leave all the items selected (the default) and then click Next
10. At the Ready to Install page click Install to complete the wizard.
11. When Setup has completed, click Finish.
12. Repeat the install of PowerPivot for SharePoint on all remaining servers in the farm.
K. Configure PowerPivot for SharePoint
This section has the steps to configure PowerPivot and create the PowerPivot service application.1. Run the PowerPivot for SharePoint 2013 Configuration tool
Once you have installed sppowerpivot.msi on each server in the farm, the next step is to run the PowerPivot for SharePoint 2013 Configuration tool on just the Central Admin server (at first) but prevent the tool from creating a service application for Power Pivot.1. Open a Remote Desktop connection to the Central Administration server (MBP-CA). Login as the SharePoint Setup Account: sp_admin.
2. Run the Power Pivot for SharePoint configuration tool. It is a Windows application located on the Apps page (Windows Start, then Apps. Or use Windows Search and search for PowerPivot.)
3. From the PowerPivot Configuration choices page, choose the Configure or Repair Power Pivot for SharePoint option, and then click OK.
4. PowerPivot Configuration Tool displays the Parameters page. The left pane displays a long tree-structured list of tasks that the configuration tool will perform. About two thirds of the way down, locate and select the Create PowerPivot Service Application task. You must un-check the “Include this action in the task list” checkbox on the right. This excludes the selected task and all subsequent tasks from the list. We need the tool to perform the first tasks in this list but we must prevent the tool from creating the PowerPivot Service Application. Later, we will create the PowerPivot Service Application manually, then re-run this tool later to complete the remaining tasks.
5. If the tool displays any warning icons, click the Validate button. When there are no warnings and the Run button is enabled, click Run.
6. Oddly, a Warning dialog displays to tell you all the settings are valid. Click Yes.
7. The Configuration Tool adds solutions, deploys solutions, installs features, and registers the PowerPivot System Service.
8. When the Configuration Tool completes, click OK,
9. Then click Exit.
In subsequent steps we will run the Power Pivot for SharePoint 2013 Configuration tool on the other SharePoint servers in the farm. First, however, we must manually create the Power Pivot service application.
2. Create the PowerPivot Service Application
We prevented the Power Pivot configuration tool from creating a Power Pivot service application, so that we could create one manually and use the PowerPivot App pool Account that we planned for. For the MBP farm, the PowerPivot App pool account is MBP\sp_ppvtsvcapp.1. Central Administration Manage service applications
2. From Ribbon, New SQL Server Power Pivot Service Application
a. Name = Power Pivot Service Application
b. Application Pool = Create new application pool --- name = Power Pivot Service Application
c. Security account = Configurable – MBP\sp_ppvtsvcapp
d. PowerPivot Service Application Database Server = sql_server_alias
e. Database Name = sp_powerpivot_service
f. Database authentication = Windows authentication
g. OK
h. OK
3. Refresh the Service Applications page to see the Power Pivot Service Application displayed.
3. Configure the Power Pivot unattended data refresh account
In this section we configure the Data Refresh configuration parameter of the Power Pivot Service Application by specifying the Power Pivot unattended data refresh account that we created earlier in the Secure Store.1. Central Administration Manage service applications
2. Click the Power Pivot Service Application
a. SharePoint should display the PowerPivot Management Dashboard. If you see “Sorry something went wrong”, reboot the Central Administration server.
3. From the PowerPivot Mangement Dashboard, on the right, Under Actions, click Configure service application settings.
4. SharePoint displays the PowerPivot Settings page. Scroll about a third of way down. Under Data Refresh, locate the PowerPivot Unattended Data Refresh Account field. In this PowerPivot Unattended Data Refresh Account box, type the target application ID of the Secure Store Target application that you created: PowerPivotUnattendedAccount.
5. Leave all other fields default.
6. Scroll to the bottom of the PowerPivot Settings page and click OK.
If the Workbook Activity section is prompting you to install Microsoft Silverlight, you may want to do that now.
4. Rerun the Power Pivot 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 on the Central Admin server.1. Open a Remote Desktop connection to the Central Administration server (MBP-CA) if necessary. Login as the SharePoint Setup Account: sp_admin.
2. Run the Power Pivot for SharePoint configuration tool. (Recall it is a Windows application located on the Apps page).
3. On the options page, choose Configure or Repair Power Pivot for SharePoint, and then click OK.
4. If the tool displays a warning on the first task in the list of tasks in the left pane (Configure or Repair PowerPivot for SharePoint) you may have to provide a parameter in the right side. I had to enter the Farm Security Passphrase, which we planned and documented in section VII – B. Click Validate to ensure the tool can perform the tasks.
5. On the page that lists the tasks in the left pane, select the task named, “Activate Power Pivot Feature in a Site Collection.”
6. On the left side, on the Parameters tab, in the Site URL field, make sure the URL of the BI Center site collection is selected. For the MBP farm, this is http://bi.mbp.com.
7. Click the Validate button to ensure the tool can perform all the tasks.
8. Click the Run button.
9. At the warning (confirmation) click Yes
10. When it says, “…completed successfully” click OK. Then Exit the PowerPivot Configuration Tool.
5. Run the PowerPivot for SharePoint Configuration tool on the other servers
It is recommended to install PowerPivot on all the SharePoint servers in your farm. You may then stop the service on servers such as the Search server that we dedicated to another purpose. Use the following procedure on each server in your farm.1. Open a Remote Desktop connection to the server and login as the SharePoint Setup Account: sp_admin.
2. Run the Power Pivot for SharePoint configuration tool (located on the Apps page).
3. On the options page, choose Configure or Repair Power Pivot for SharePoint, and then click OK.
4. On the page that lists the tasks in the left pane, look for “Activate PowerPivot Feature in a Site Collection”. Select this task, and then in the right hand pane in the Parameters tab, select the BI site collection where we want to activate Power Pivot or uncheck the “Include this action in the task list” check box to skip activating the feature in that site collection.
5. If you make any changes, click Validate
6. Click Run.
7. When it says, “…completed successfully” click OK. Then Exit the PowerPivot Configuration Tool.
8. Repeat steps 1 – 7 on each of the remaining SharePoint servers in the farm.
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. For the MBP domain, we don’t want this service running on every server. It is OK to install PowerPivot on all SharePoint servers in the farm, then stop it on certain servers. In the MBP farm we stop the service on the MBP-SEARCH server.
1. Central Administration Manage services on server
2. In the upper right of the Services on Server screen, pull down the Server: drop-down, click Change Server and select the Search server (MBP-SEARCH)
3. Locate the PowerPivot Service in the list and click Stop.
Next Steps
In this section we configured most of the key BI features you will likely need in a SharePoint farm, including SQL Server Report Server in SharePoint Mode and Reporting Services Add-In, Excel Services, PowerPivot for SharePoint and PowerView.
In the next section, Section XV: Turn Windows Firewalls back on, we turn on firewalls that we turned off while configuring the SharePoint farm and configure the ports that need to be open for SharePoint to work in the network.
In the next section, Section XV: Turn Windows Firewalls back on, we turn on firewalls that we turned off while configuring the SharePoint farm and configure the ports that need to be open for SharePoint to work in the network.
