SharePoint Server 2016 Business Intelligence Architecture

In previous versions of SharePoint, there was a service application called Excel Services. Excel Services allowed users to display reports in Excel as well as use PowerPivot and Power View. Office Web Apps Server also offered users the ability to view Excel documents in the browser, but was not able to display reports. Managing two tools that almost did the same things, each with their strengths and weaknesses, caused headaches for SharePoint Administrators. With SharePoint Server 2016, Microsoft decided to eliminate Excel Services from SharePoint and shift the functionality to Office Online Server.

While this will make life easier for administrators, there are some architecture changes to keep in mind when deploying SharePoint Business Intelligence Services. As we learned in Chapter 9, Office Online Server must be deployed on a server other than SharePoint; therefore, a highly available Business Intelligence setup would require two extra servers over what it did in the past. Furthermore, since Excel Services is now on the Office Online Server, we have an extra hop between the SharePoint Server and our SQL Server Analysis Services (SSAS) server. Therefore, for some scenarios, it will be mandatory to enable Kerberos Constrained Delegation (KCD) . Some scenarios such as displaying Excel workbooks connected to SSAS Data Sources can still be done by using the EffectiveUserName; however, displaying Excel workbooks that connect to Windows Authentication SQL Server Data Sources will need to have KCD configured. EffectiveUserName is a SSAS connection string property that contains the name of the user who is accessing a report or dashboard. This allows you to specify the appropriate level of data access for a given user on the OLAP cube itself, without the need to configure Kerberos delegation. Something to take into consideration is that PowerPivot works only with Windows Claims–based Web Applications, and will not work with SAML-based authentication.

It is also important to know that while SharePoint Server 2016 supports both SQL 2014 and SQL 2016 for its databases, you need to have a SQL 2016 SSAS instance to have the full functionality. Features such as Excel Services and Power Query will not work with SQL 2014. Furthermore, none of the four MinRole roles is configured to support SSRS; therefore, you must have at least one SharePoint Server with the Custom MinRole in your SharePoint 2016 farm to run SSRS.

If you wish to configure PowerPivot to use an unattended account to access certain data sources, you will also need to create a service account for this purpose. For this book we have created an account with the username s-PowerPivot that will serve as the PowerPivot Unattended Service Account.

Installing SSAS in PowerPivot Mode

Excel Online requires an Analysis Services instance deployed in PowerPivot mode in order to load and query data models. As previously stated in this chapter, this instance needs to be a SQL Server 2016 Analysis Services instance since SQL 2014 is not supported for the BI Stack. It is recommended that the SSAS instance that you deploy is on a server separate from the rest of your databases; however, for testing and development purposes with a smaller load, it can be hosted on the same SQL Server as your databases.

Before starting the SQL Server installation, you will need to enable the .NET Framework 3.5 on the Windows Server that will be hosting the SSAS instance. Once you start the SQL Server 2016 Setup Wizard and get to the Feature Selection Page, select “Analysis Services” as seen in Figure 12-1.

Figure 12-1. SSAS

On the Instance Configuration name, make sure to use a Named Instance and not the default instance. In past versions of SharePoint this instance was always named PowerPivot, but that is not mandatory anymore. In our environment, we will name it PowerPivot2016 as seen in Figure 12-2.

Figure 12-2. SQL Server Instance Configuration

On the Analysis Services Configuration screen, make sure to select the Power Pivot Mode in the Server Mode section, and add the required administrators with permissions to your SSAS instance as seen in Figure 12-3.

Figure 12-3. Analysis Services Configuration

After this step, you will need to confirm the features and finally click the Install button to proceed with the installation. With the Instance installed, we will need to open port TCP/2382 for the SQL Browser, as well as open the SSAS port on the Program in order to enable dynamic ports. TCP/2382 is used by the SQL Browser service to listen for SSAS instances.

New-NetFirewallRule -DisplayName “Allow Inbound SSAS” -Direction Inbound -Program "C:Program FilesMicrosoft SQL ServerMSAS13.POWERPIVOT2016OLAPinmsmdsrv.exe"  -Action Allow
New-NetFirewallRule -DisplayName "SQL Browser Inbound" -Direction Inbound –Protocol TCP –LocalPort 2382 -Action allow

Do not forget to change the –Program parameter to match the path to the instance name you have created. With the SSAS Instance created, it is time to configure the Office Online Server for Business Intelligence.

Configuring Office Online Server for BI

Office Online Server is an important part of configuring Business Intelligence functionality in SharePoint Server 2016, and without a configured Office Online Server farm you cannot go forward. If you did not install and configure Office Online Server yet, follow Chapter 9 to get it configured. The first thing we have to do on Office Online Server is to make sure that it can reach our SSAS instance. Use the following PowerShell script and change the Connection String with your instance name to validate that your Office Online Server can query the SSAS Instance. You need to be logged in as a user that has access to the SSAS Instance in order for the script to work.

$ssasConnection = New-Object System.Data.OleDb.OleDbConnection
$ssasConnection.ConnectionString = "Provider=MSOLAP;Data Source=LSSASS01PowerPivot2016"
$ssasConnection.Open()

$guid = New-Object System.Guid "3444B255-171E-4cb9-AD98-19E57888A75F"
$restrictionList = @($null, $null, $null, $null, $null, $null, $null, "Administrators")
$schemaTable = $ssasConnection.GetOleDbSchemaTable($guid, $restrictionList);
[xml]$admins = $schemaTable.METADATA

$ssasConnection.Close()

$admins.Role.Members.Member

If the Office Online Server can access the SSAS Instance, the PowerShell script will display all the administrators of the instance as seen in Figure 12-4.

Figure 12-4. Validating Office Online Server access to SSAS

Next, we need to give access to the Office Online Server machines to the SSAS Instance. Since the Office Online Server service runs under the Network Service account, the computer account needs to be an administrator of the SSAS Instance. From the SSAS Instance Properties in SQL Server Management Studios, go the Security and add a new Administrator. Make sure to select Computer Objects and the Entire Directory as location. Enter the name of all your servers from your Office Online Server farm as seen in Figure 12-5, and click OK.

Figure 12-5. Granting access to Office Online Server machines

The next step is to configure this Office Online Server to use the SSAS Instance. This is done by PowerShell on any Office Online Server in the farm. Run the following cmdlet and change the ServerID with your server and instance name.

New-OfficeWebAppsExcelBIServer –ServerId LSSASS01PowerPivot2016                                    

Since this is a farm-level change, it needs to be done only on one server in the Office Online Server Farm. Next we need to enable the Claims to Windows Token Service on the Office Online Server. By using notepad as an administrator, open the c2wtshost.exe.config file located at C:Program FilesWindows Identity Foundationv3.5c2wtshost.exe.config and remove the comment tags around <add value="NT AUTHORITYNetwork Service" /> as seen in Figure 12-6.

Figure 12-6. Claims to Windows Token Service Configuration

Afterward, you will need to set the Claims to Windows Token Service to start up automatically with Windows, and start it right away. This is done with the following PowerShell cmdlets:

Set-Service -Name C2WTS -startuptype "automatic"
Start-Service -Name C2WTS

With Excel services enabled in Office Online Server, we now need to configure PowerPivot.

Configuring PowerPivot Services

The next step to configure the BI stack on SharePoint Server 2016 is to configure PowerPivot. The first step will be to install the PowerPivot Add-in on all the SharePoint Servers in the farm. This will allow any server to display PowerPivot reports when requested from a user. Download the PowerPivot Add-in from the following link:

• Microsoft SQL Server 2016 PowerPivot for Microsoft SharePoint 2016 ( https://www.microsoft.com/en-us/download/details.aspx?id=52675 )

After the PowerPivot Add-in is installed on all the servers in the farm, on the server that you want to run PowerPivot, run the PowerPivot for SharePoint 2016 Configuration. When opening the tool you might get an error specifying that “The user is not a farm administrator” as seen in Figure 12-7. You might get this error even if this user is a farm administrator.

Figure 12-7. The user is not a farm administrator

In order to get around this error, you need to configure your current user as a Site Collection Administrator on the Central Administration as seen in Figure 12-8.

Figure 12-8. Central Administration Site Collection Administration

Reopen the PowerPivot Configuration tool and select to Configure or Repair PowerPivot for SharePoint as seen in Figure 12-9.

Figure 12-9. Configure or repair PowerPivot for SharePoint

There are some settings you need to configure in the PowerPivot Configuration tool. First, you will need to enter the “Default Account Username” and password, which is an account that needs to be a farm administrator, be able to deploy farm solutions and activate features on a Site Collection we will select later. In Figure 12-10 I have chosen to run the install with my own account, and selected the default database server.

Figure 12-10. PowerPivot Configuration Tool Default Account Username

On the next screen that accepts parameters seen in Figure 12-11, we need to enter the Service Application Name as well as the database name.

Figure 12-11. PowerPivot Configuration Tool Service Application Configuration

The PowerPivot Configuration Tool will enable the PowerPivot on one Site Collection by default and you can choose this site collection in “Activate PowerPivot feature in a Site Collection” seen in Figure 12-12. If you don’t want to activate this feature yet, simply uncheck the box “Include this action in the task list”

Figure 12-12. PowerPivot Configuration Tool Site Collection to Activate Feature

In the last configuration screen seen in Figure 12-13, we have to specify the TargetApplicationID, Friendly Name and the unattended account username and password for the Secure Store Target ID the PowerPivot Configuration Tool creates.

Figure 12-13. PowerPivot Configuration Tool Unattended Account

After clicking Validate, and then Run, the configuration tool will deploy the farm solutions and activate them if you have selected to do so. As deploying farm solutions causes an IIS Reset, make sure to do this outside business hours to avoid service disruption. Next, you will also need to give admin permissions to the SSAS instance to the account running the Service Application pool of the PowerPivot Service Application, which in our case is CORPs-services.

In order to allow the use of Workbook as a Data Source, we will need to enable KCD. The first thing we need is to configure our Web Application to use Kerberos, and we have covered this in Chapter 3. We then need to allow the computer account for the Office Online servers to delegate credentials to the account running the Web Application Pool of the SharePoint Web Application. You will need to run this PowerShell script either from a Domain Controller, or from a computer that has the Active Directory Management Tools installed. We have created a variable called $allowedPrincipals, in which we will get the computer property for all the Office Online Servers in our farm. In our case, we added the three servers and then ran the Set-ADUser cmdlet to apply the change in the Active Directory.

$allowedPrincipals = @()
$allowedPrincipals += Get-ADComputer -Identity LSOOS01
$allowedPrincipals += Get-ADComputer -Identity LSOOS02
$allowedPrincipals += Get-ADComputer -Identity LSOOS03
Set-ADUser s-web -PrincipalsAllowedToDelegateToAccount $allowedPrincipals

To verify the Kerberos configuration, run the setspn –l cmdlet, on the account running the Web Application pool, in our case corps-web.

SetSPN -l corps-web

If everything is configured correctly, you should see the Web Application where you have configured Kerberos. In our case, it’s the sharepoint.learn-sp2016.com Web Application.

HTTP/sharepoint
HTTP/sharepoint.learn-sp2016.com

We also need to make sure that we have some SPNs set on each Office Online Server. Run the Setspn –l <OOS Server Name> cmdlet for each office Online Server, and validate that you see the HOST<Server Name> and HOST<FQDN> in the list of SPNs. Those entries are highlighted in Figure 12-14.

Figure 12-14. Validating Office Online Server SPN

Finally we need to install the Analysis Services OLE DB Provider on Office Online Server. The Analysis Services OLE DB Provider can be downloaded from the SQL Server 2016 Feature Pack from the Microsoft Download Center at the following link: https://​www.​microsoft.​com/​en-us/​download/​details.​aspx?​id=​52676; the download name is <Language>X64SQL_AS_OLEDB.msi. You will need to install it on every Office Online Server in your farm. Furthermore, you need to update the registry with information about the OLE DB Provider. Save the following text in a file named MSOLAP.5.Update.reg and run it on every Office Online Server in the farm.

Windows Registry Editor Version 5.00
[HKEY_CLASSES_ROOTMSOLAP.5]
@="MSOLAP 13.0 OLE DB Provider"
[HKEY_CLASSES_ROOTMSOLAP.5CLSID]
@="{FBE7F3BD-C550-490E-B38C-A8661E420070}"

A reboot will be required after performing the installation and registry edit.

Before testing the PowerPivot functionality, make sure that the “PowerPivot Feature Integration for Site Collections” is activated as seen in Figure 12-15.

Figure 12-15. PowerPivot Feature Integration for Site Collections

Once you validate that feature is activated, add a new library of type PowerPivot Gallery as seen in Figure 12-16.

Figure 12-16. New PowerPivot Gallery

You will now be able to upload and test PowerPivot Business Intelligence reports in SharePoint. You can use the PowerPivot sample files provided in the “Verify a Power Pivot for SharePoint Installation” article on MSDN at https://​msdn.​microsoft.​com/​en-CA/​library/​hh231684.​aspx.

Configuring SSRS

The last part of our Business Intelligence configuration is to configure SSRS in SharePoint integrated mode. The SSRS instance must be installed on a SharePoint server running the Custom MinRole, since none of the other roles will allow you to run the SSRS Service.

You will first need to have the SQL installation media on your SharePoint Server running the Custom MinRole, and on the Feature Selection screen shown in Figure 12-17, choose Reporting Services – SharePoint.

Figure 12-17. Reporting Services – SharePoint Feature Selection

On the Reporting Services Configuration page, select the Install Only option, and then click Install. Once the install is done, we will need to install the Reporting Services Add-in for SharePoint Products on all the servers in the farm. This can be downloaded from the following link:

• Microsoft SQL Server 2016 Reporting Services Add-in for Microsoft SharePoint https://www.microsoft.com/en-us/download/details.aspx?id=52682

The minimum requirements are that this Add-in must be installed on all the Web Front Ends in the SharePoint farm; however, we recommend installing it on all the servers in the farm for convenience, should the server role be changed in the future. When the installation is done, from the Central Administration, navigate to the Service Applications page, and create a new Service Application of type SSRS Application. Enter the name, Application Pool as well as Database, and what Web Applications you want to deploy the SSRS functionality to. In our environment, we have configured the SSRS Service Application to use the same Service Application Pool as our other Service Applications as seen in Figure 12-18.

Figure 12-18. New SSRS Service Application

After the Service Application is created, navigate to the Services on Server page, and validate that the SSRS service is started. Make sure to select the server running the Custom role on the top right of the page. If the service is not started, start it manually.

You can then deploy a simple report using SQL Server Report Builder, and validate that everything is working. The first step is to download SQL Server Report Builder from the Microsoft Download Center.

• Microsoft SQL Server 2016 Report Builder ( https://www.microsoft.com/en-us/download/details.aspx?id=52674 )

We can then either create a new document library, or use an existing one, and add the “Report Builder Report” and “Report Data Source” Content Types to your library as seen in Figure 12-19.

Figure 12-19. SSRS Content Types

Use the New button, and select Report Builder Report as seen in Figure 12-20.

Figure 12-20. New Report Builder Report

The SQL Server Report Builder will open as seen in Figure 12-21, and you can then follow a wizard to create to connect to a database and display data from that database in a simple report.

Figure 12-21. Getting Started with SQL Server Reports Builder

In Figure 12-22, we simply created a report of the Inventory database, used in Chapter 14.

Figure 12-22. Testing SSRS

Next Steps

In this chapter, we have deployed the SharePoint 2016 BI Service Applications such as PowerPivot and SSRS, allowing our developers to publish reports on SharePoint 2016. In the next chapter, we will learn how to create Web Applications and Site Collections.