Contents

Setting Up Your Demo Infrastructure

How to Install and Configure PowerPivot for SharePoint 2016

How to Install and Configure Advanced PowerPivot Features

How to Install and Configure SSRS in SharePoint-Integrated Mode

How to Install and Configure PerformancePoint Services

 

The SQL Server 2016 BI stack for SharePoint 2016 adds many important enhancements.

With the performance optimizations in SQL Server Analysis Services (SSAS), PowerPivot data models now load faster and client queries now return quicker, resulting in an overall more responsive BI experience.

Additionally, enhancements to SharePoint-integrated Reporting Services (SSRS) include improvements with SQL Server 2016, such as new chart types, HTML5 rendering of reports, cross-browser printing, export to Microsoft PowerPoint, and an updated version of Report Builder.

Deploying the new BI features to SharePoint 2016 requires careful preparation, as many considerations have changed, due to the new architecture. Excel Services no longer ships with SharePoint 2016, making it a key requirement to deploy an Office Online Server environment along with the SharePoint 2016 farm.

This blog will provide you with detailed step-by-step instructions on how to deploy and configure a small BI demo environment—which you can use to get familiar with the new architecture and its key components as quickly as possible. To do this, we’ll show you how to deploy PowerPivot for SharePoint 2016, SSRS in SharePoint-Integrated Mode, and PerformancePoint Services.

 

Setting Up Your Demo Infrastructure

In order to try out the new BI features, you’ll need at least these two servers at a minimum.

Setting up SharePoint Servers

Server 1

Your first server will host SharePoint and SQL.

This blog assumes you have already installed SharePoint Server 2016. If you have not yet installed SharePoint Server 2016, please refer to this blog.by clicking here.

In SharePoint, you will need to configure App Management Service, Secure Store Service, User Profile Service Application, Claims to Windows Token Service, Distributed Cache, and Microsoft SharePoint Foundation Workflow Timer Service.

Server 2

Your second server will host Office Online Server.

Office Online Server can’t be installed on a SharePoint server, Active Directory domain controller, SQL server, or any other computer with existing applications. You will also not be able to install any other applications on the computer running Office Online Server. Make sure it is a dedicated deployment.

Software Requirements

In our lab, we build our demo environments on Windows Server 2012 R2 Standard Edition and have installed SharePoint Server 2016 Enterprise Edition and SQL Server 2016 Enterprise Edition.

Back to Top

How to Install and Configure PowerPivot for SharePoint 2016

1. Install Analysis Services in PowerPivot Mode
  1. Log on to SPDEV-2016 as SQLAdmin and start Setup.exe from the SQL Server 2016 installation media. Click Yes if a User Account Control window is displayed.
  2. In the SQL Server Installation Center, click Installation in the left-hand navigation area, and then click New SQL Server stand-alone installation or add features to an existing installation.
  3. On the Product Key page, select an option to indicate whether you are installing a free edition of SQL Server, or a production version of the product that has a PID key. For a demo lab, accept the default, Specify a free edition and select Evaluation.
  4. On the License Terms page, review the license agreement and, if you agree, select the I accept the license terms check box, then click Next.
  5. On the Product Updates page, accept the default settings and click Next.
  6. On the Install Rule page, verify that the rules are passed. Be sure to notice any warnings, such as one for the Windows Firewall Click Next to continue.
  7. On the Setup Role page, select SQL Server Feature Installation, and then click Next.
  8. On the Feature Selection page (pictured below), select Database Engine Services, Analysis Services, and Management Tools – Complete. Take note of the directory paths, and optionally change these paths if necessary, then click Next.

    SQL Server 2016 Setup
  9. The Feature Rules window will automatically advance if all of your rules pass.
  10. On the Instance Configuration page, make sure you select Named Instance. You can specify any valid instance name you’d like, but in this blog we’ll use SHAREPOINT as the instance name and ID. Click Next to continue.
    SQL Server 2016 Setup Instance Configuration
  11. On the Server Configuration page, accept the default accounts for SQL Server services, then click Next.
  12. On the Database Engine Configuration page, accept the default settings, then click on Add Current User to add SQLAdmin to the list of SQL Server administrators. Repeat to add the SPAdmin account as well. Click Next to continue.
  13. On the Analysis Services Configuration page, under Server Mode, make sure you select PowerPivot Mode. Click on Add Current User to add SQLAdmin to the list of Analysis Services Server administrators, and then click Next to continue. The SPAdmin account does not require admin rights in Analysis Services.
  14. On the Ready to Install page, click Install.
  15. The Installation Progress page provides status updates so that you can monitor your installation progress as Setup continues. If a Computer restart required dialog box is displayed, click OK.
  16. When you see the Complete page, click Close and restart the server to complete the SQL Server installation.
2. Install Office Online Server Prerequisites
  1. Log on to your SharePoint Server as SPAdmin.
  2. In Server Manager, on the Welcome to Server Manager page, click on Add roles and features.
  3. When you see the Before you begin page, click Next.
  4. Next, you’ll see the Select Installation type Accept the default selection for a Role-based or feature-based installation and click Next.
  5. On the Select destination server page, make sure the local computer is selected, and then click Next.
  6. On the Select server roles page, select Application Server and Web Server (IIS).
  7. An Add Roles and Features Wizard dialog box will appear, make sure that Include management tools (if applicable) is selected, click Add Features, and then Next.
  8. On the Select features page, in addition to the default selections, select Ink and Handwriting Services and Windows Identity Foundation 3.5, and then click Next.
  9. For the Application Server page, take note that the installation of Web Server (IIS) Support is recommended, and then click Next.
  10. On the Select role services page, select Web Server (IIS) Support.
  11. The Add Roles and Features Wizard dialog box will appear again, make sure that you check the Include management tools (if applicable) box, click Add Features, and then Next.
  12. No need to do anything on the Web Server Role (IIS) page, just click Next.
  13. You’ll then be brought back to the Select role services Click Next to move on to the Confirm installation selections page and click Install.
  14. Following the feature installation, click Close. You’ll need to restart your computer to complete any pending update operations.
  15. Log on to SPWAC-2016 as SPAdmin. When you see the Server Manager, click on Add roles and features
  16. Click Next three times to reach the Select server roles
  17. Expand Web Server (IIS), expand Web Server, and finally expand Application Development. You’ll then need to select the Server Side Includes check box and click Next.
  18. Nothing needs to be done on the Select features page, just go ahead and click Next.
  19. On the Confirm installation selections page, click Install.
  20. Following the feature installation, click Close.
  21. Download and then run the installation package for Microsoft Identity Extensions which can be found here.
  22. You will then be prompted to read the Microsoft Identity Extensions License Terms page, review the license agreement and, if you agree, select the I accept the terms of the License Agreement check box, and click Install.
  23. If a User Account Control window is displayed, click Yes.
  24. When you see the Completed the Microsoft Identity Extensions Setup Wizard page, click Finish.
  25. Download the Update for Universal C Runtime in Windows that is appropriate for your operating system, such as All supported x64-based versions of Windows Server 2012 R2, and then run the installer package.
  26. The Windows Update Standalone Installer dialog box will ask you if you want to install the updates, click Yes.
  27. In the Installation complete dialog box, click Close.
3. Install Office Online Server
  1. Log on to SPWAC-2016 as SPAdmin and run com from the Office Online Server installation media.
  2. If a User Account Control window is displayed, click Yes.
  3. On the Read the Microsoft Software License Terms page, review the license agreement and, if you agree, select the I accept the terms of this agreement check box, then click Continue.
  4. On the Choose a file location page, accept the default paths or specify alternative file locations, then click Install Now.Microsoft Office Online Server File Location
  5. On the final Office Online Server page, if all went well, you’ll be informed that the installation was successful. All that’s left to do is to click Close.
4. Connect Office Online Server Farm
  1. Log on to SPWAC-2016 as SPAdmin.
  2. Right-click on the Windows PowerShell icon in the taskbar, and click on Run as Administrator.
  3. If a User Account Control window is displayed, click Yes.
  4. Once you’re in Windows PowerShell, run the following commands. Make sure if you use different computer names to replace http://WAC2016 accordingly:
    Connect Office Online Server Farm 2
  5. A message will appear to inform you that you should only set EditingEnabled to TRUE if licenses permit online editing. Acknowledge this message by pressing Y for Yes.
  6. It is recommended that you copy & paste the resulting output into a document to keep a reference of the Office Online Server configuration.
  7. Also note that the New-OfficeWebAppsFarm cmdlet creates a new firewall rule called Microsoft Office Web Apps to open TCP ports 80, 443, and 809 for inbound traffic.
5. Configure Office Online Server in Analysis Services
  1. Log on to SPDEV-2016 as SQLAdmin and start SQL Server Management Studio.
  2. Connect to the SPDEV-2016\SHAREPOINT instance of Analysis Services.
  3. Right-click the instance name in Object Explorer and click Properties.
  4. Click Security in the Select a Page pane, then click Add.
  5. In the Select Users or Groups dialog, click
  6. Select Entire directory and click OK.
  7. Click Object Types, select the Computers check box, and click OK.
  8. In the Enter the object names to select textbox, type the name of the computer, such as SPWAC-2016, and then click Check Names to verify the computer account is found.
  9. Click OK twice to apply the changes and close the Analysis Server Properties dialog box.
    SQL Server Management Studio Analysis Server Properties
6. Configure Excel Online BI Server
  1. If you are still logged on to SPWAC-2016 as SQLAdmin, log off, and then log on as SPAdmin.
  2. Right-click on the Windows PowerShell icon in the taskbar, and then click on Run as Administrator.
  3. If a User Account Control window is displayed, click Yes.
  4. From the Windows PowerShell command prompt, run the following command:
    Configure Excel Online BI Server
    Administrator Windows Power Shell Configure BI server
7. Configure Claims to Windows Token Service for Office Online Server
  1. From the Windows PowerShell command prompt, run the following command to open the configuration file of the Claims to Windows Token Service (C2WTS) in Notepad.exe:Configure Excel Online BI Server - Notepad Code
  2. Remove the comment tags (<!– and –>) from the NT AUTHORITY\Network Service line and save the changes.c2wtshost notepad
  3. From Computer Management, set Startup type to Automatic and start Claims to Windows Token Service:Computer Management
8. Register Office Online Server in the SharePoint 2016 Farm
  1. Log on to SPDEV-2016 as SPAdmin, and then on the Desktop, right-click on the SharePoint 2016 Management Shell, and then click Run as administrator.
  2. If a User Account Control window is displayed, click Yes.
  3. From the SharePoint 2016 Management Shell, run the following commands to update the SharePoint farm properties for Excel Services SOAP API support. This will provide the basis for Analysis Services client libraries to communicate with Excel Online:
    Register Office Online Server in SharePoint Farm 1
  1. From the SharePoint 2016 Management Shell, run the following commands to register a new WOPI Binding for the Office Online Server and allow authentication over HTTP connections:
    Register Office Online Server in SharePoint Farm 2
  2. Close the SharePoint 2016 Management Shell.
9. Verify basic Excel Online Configuration for BI
  1. Visit the BI Root Site at http://SP2016FE as a SP User.
  2. Open the Documents library and then click on Upload. You’ll need to Upload an Excel workbook with a PowerPivot data model. Sample PowerPivot workbooks can be downloaded from the Microsoft Download Center.
  3. Following the upload, click on the workbook to open it in the browser. Note that the workbook renders in Excel Online.
  4. Interact with the data by clicking on a slicer. This will ensure Excel Online loads the data model on the Analysis Services instance.
  5. Log on to SPDEV-2016 as SQLAdmin and start Microsoft SQL Server Management Studio.
  6. In the Connect to Server dialog box, under Server type, select Analysis Server. Under Server name, type SPDEV-2016\SHAREPOINT. Click Connect.
  7. Expand the Databases node and verify that the workbooks data model is listed.
    Microsoft SQL Server Management Studio

Back to Top

 

Install and Configure Advanced PowerPivot Features

1. Install the PowerPivot Add-In Files on the SharePoint Server
  1. Log on to SPDEV-2016 Server as SPAdmin and download the PowerPivot for SharePoint 2016 add-in installation package (spPowerPivot16.msi) from the SQL Server 2016 Feature Pack.
  2. Run msi and then click Next on the Welcome page.
  3. You’ll then see the License Agreement page, review the license agreement and, if you agree, select check the I accept the terms in the license agreement check box, and click Next.
  4. On the Feature Selection page, make sure all of the features are selected, and click Next.
  5. When you see the Ready to Install the Program page, click Install.
  6. If a User Account Control window is displayed, click Yes.
  7. On the Completing the SQL Server 2016 PowerPivot for SharePoint installation page, click Finish.
  8. If a notification is displayed informing you that you must restart the computer, click Yes.
2. Deploy the PowerPivot Add-In in the SharePoint 2016 Farm
  1. Log on to SPDEV-2016 as SPAdmin, run the PowerPivot for SharePoint 2016 Configuration tool. If a User Account Control window is displayed, click Yes.
  2. In the PowerPivot Configuration Tool dialog box, select Configure or Repair PowerPivot for SharePoint, and click OK.
  3. On the Parameters tab, assuming you followed the instructions in this blog, provide the following information:
    Parameter Recommendation
  4. Click Validate, and then when you see the Test Validation dialog box informing you that the validation succeeded for all actions, click OK.
  5. Click run to start the PowerPivot deployment and farm configuration steps, and confirm the actions by clicking Yes in the Warning dialog box.
  6. In the Task Configuration dialog box, informing you that the selected tasks completed successfully, click OK.
  7. In the PowerPivot Configuration Tool, click Exit.
    PowerPivot Configuration Tool
3. Configure SharePoint Services access to Analysis Services
  1. Log on to SPDEV-2016 as SQLAdmin and start SQL Server Management Studio.
  2. Connect to the SPDEV-2016\SHAREPOINT instance of Analysis Services, right-click the instance name in Object Explorer, and then click Properties.
  3. Click Security in the Select a Page pane, and then click Add.
  4. In the Select Users or Groups dialog, click Locations, and select Entire directory, then click OK.
  5. In the Enter the object names to select textbox, type the name of the SharePoint farm/services account, such as fmtus\SPFarm, and then click Check Names to verify the account is found.
  6. Click OK twice to apply the changes and close the Analysis Server Properties dialog box.
4. Verify PowerPivot Features in a SharePoint site
  1. Visit the BI Root Site at http://SP2016FE.
  2. Click on the cogwheel at the top right corner of your site’s home page, then click on Site settings.
  3. Under Site Collection Administration, click on Site collection features.
  4. On the Site Settings – Site Collection Features page, make sure that the PowerPivot Feature Integration for Site Collections is Active.
    SharePoint Site Collection Features
  5. Click on Site Contents and then add an app.
  6. On the Site Contents – Your Apps page, click on PowerPivot Gallery.
  7. In the Adding PowerPivot Gallery dialog box, type a name for the new repository, such as PowerPivot Gallery, and then click Create.
  8. Click on the new link to the PowerPivot Gallery library that appears in the navigation Quick Launch pane for the current site and upload a sample workbook by clicking on Files and then Upload Document.
    Note 1
  9. Verify that the PowerPivot Gallery displays the uploaded document.
  10. After a short period of time, refresh the gallery view and verify that the PowerPivot Gallery displays the individual worksheets as thumbnails.
  11. Click on the Refresh Schedule button, which looks like a calendar, at the top right corner of your PowerPivot Gallery page.
  12. On the Manage Data Refresh page, under Data Refresh, select the Enable check box.
  13. Specify the desired Schedule Details and then select the Also refresh as soon as possible check box.
  14. Under Credentials, select Connect using the following Windows user credentials, and then type in the credentials required to access the data source, such as the SPUser account, and then click OK.

Back to Top

Install and Configure SSRS in SharePoint-Integrated Mode

While the deployment steps for installing the SSRS binaries and configuring a service application are very similar to the steps in previous SQL Server releases, it is important to keep in mind that only SQL Server 2016 Reporting Services is supported in SharePoint 2016.

Be sure to remember to assign the Custom server role to the SharePoint server where you plan to run the Reporting Services service application. The deployment will succeed even if you skip this step, but during the next SharePoint maintenance window, MinRole will stop the SSRS service. Without assigning a custom server role, MinRole will detect that SSRS in SharePoint-integrated mode does not indicate support for any of the other SharePoint server roles as the SSRS service application only supports the Custom role.

1. Install the SSRS Add-In on the SharePoint Server
  1. Log on to SPDEV-2016 as SPAdmin, start exe from the SQL Server 2016 installation media.
  2. Click Yes if a User Account Control window is displayed.
  3. In the SQL Server Installation Center, click Installation in the left-hand navigation area, and then click New SQL Server stand-alone installation or add features to an existing installation.
  4. On the Product Key page, select the option to indicate whether you are installing a free edition of SQL Server, or a production version of the product that has a PID key. For a demo lab, accept the default, Specify a free edition, and select Evaluation.
  5. On the License Terms page, review the license agreement and, if you agree, select the I accept the license terms check box, and then click Next.
  6. On the Product Updates page, accept the default settings and click Next.
  7. On the Install Rule page, verify that the rules are passed. Take note of any warnings, such as the Windows Firewall Click Next to continue.
  8. On the Setup Role page, select SQL Server Feature Installation, and then click Next.
  9. On the Feature Selection page, select Reporting Services Add-in for SharePoint Products, and then click Next.SQL Server 2016 Setup Feature Selection
  10. On the Ready to Install page, click Install.
  11. The Installation Progress page provides status so you can monitor installation progress as the setup continues.
  12. Once the installation is done, you’ll see the Complete.
  13. Click Close, and then close SQL Server Installation Center.
2. Install SSRS in SharePoint-Integrated Mode on SharePoint Server
  1. Log on to SPDEV-2016 as SPAdmin, start exe from the SQL Server 2016 installation media.
  2. Click Yes if a User Account Control window is displayed.
  3. In the SQL Server Installation Center, click Installation in the left-hand navigation area, and then click New SQL Server stand-alone installation or add features to an existing installation.
  4. On the Product Key page, select the option to indicate whether you are installing a free edition of SQL Server, or a production version of the product that has a PID key. For a demo lab, accept the default Specify a free edition, then select Evaluation.
  5. On the License Terms page, review the license agreement and, if you agree, select the I accept the license terms check box, and then click Next.
  6. You’ll then see the Product Updates page, accept the default settings and click Next.
  7. The Install Rule page will then appear, verify that the rules are passed. Be sure to notice any warnings, such as for the Windows Firewall Click Next to continue.
  8. On the Setup Role page, select SQL Server Feature Installation, and then click Next.
  9. On the Feature Selection page, select Reporting Services – SharePoint, and then click Next.
    SQL Server 2016 Setup Feature Selection 2
  10. On the Reporting Services Configuration page, accept the default option Install only, and click Next.
  11. On the Ready to Install page, click Install.
  12. The Installation Progress page provides status updates so that you can monitor installation progress as setup continues.
  13. Once setup is complete and you see the Complete page go ahead and click Close.
  14. Close SQL Server Installation Center.
3. Configure an SSRS service application
  1. Log on to SPDEV-2016 as SPAdmin.
  2. On the Desktop, open SharePoint 2016 Central Administration.
  3. If a User Account Control window is displayed, click Yes.
  4. On the Central Administration home page, under Application Management, click on Manage Service Applications.
  5. Click New and select SQL Server Reporting Services Service Application.
  6. On the Create SQL Server Reporting Services Service Application page, under Name, type a unique name for the service application, such as Reporting Services Service Application.
  7. Under Application Pool, select Use existing application pool, and from the corresponding drop down list, select SharePoint Web Services Default.
  8. In the Database server textbox, provide the name of your database server instance, such as SPDEV-2016\SHAREPOINT.
  9. Under Web Application Association, select the check box SharePoint – 80 (http://SP2016FE/).
  10. Click OK to create the Reporting Services service application.
  11. On the Create SQL Server Reporting Services Service Application page, it will inform you that the service application has been successfully created, click OK.SQL Server Reporting Services Service Application
  12. In the left navigation pane, click on System Settings, and then click on Manage services on server.
  13. On the Services on Server page, under Server, select SPDEV-2016, and then verify that the SQL Server Reporting Services Service has s If this is not the case, start it.

Back to Top

How to Install and Configure PerformancePoint Services

  1. Log on to SPDEV-2016 as SPAdmin, on the Desktop.
  2. Open SharePoint 2016 Central Administration.
  3. On Application Management Page click on Manage service applications.
  4. On the ribbon, click on the New button to bring down list of available service applications.
  5. Select PerformancePoint Service Application.
  6. This will bring up the page shown below.
    PerformancePoint Service Application
  7. Add/edit the Name, Database Server and Application Pool After setting all values click Create.
  8. SharePoint will then create service application.
    Share Point Manage Service Applications

Back to Top

Still need help? FMT is Here for You!

If you’ve followed this guide you should have successfully deployed PowerPivot for SharePoint 2016, SSRS in SharePoint-Integrated Mode, and Performance Point Services.

However, if you still need help, or would just like to speak with our team of experts about SharePoint, we’d be happy to speak with you! Simply contact us using the form below and we’d be happy to help you in any way we can!