Tag Archives: PowerPivot

The Difference Between Reporting and Analytics is 42

In his novel “The Hitchhiker’s Guide to the Galaxy”, Douglas Adams envisioned a giant supercomputer named “Deep Thought” that was built to solve the answer to the ultimate question of life, the universe and everything. For the 5 people out there that are unfamiliar with the story, I’ll relate the important bits here. Deep Thought was commissioned by a race of pan-dimensional beings and required seven and a half million years to complete its calculations. When it was finally complete, Deep Thought informed the ancestors of the original creators that the answer was 42. The receivers were understandably disappointed with this response, and when they questioned Deep Thought further, the computer postulated that perhaps the problem was that they never really knew what the question was.

Undeterred, the race then commissioned a second computer (which happened to be the Earth) that would calculate the ultimate question. After a couple of 10 million year attempts, the ultimate question was determined to be “What do you get when you multiply six by nine”. Of course, Adams never claimed that the universe made sense.

To my mind, this is an excellent demonstration of the difference between reporting and analytics. The accurate answer (report) provided a result, but not meaning. Further analytics were necessary to determine context.

Like many information technology terms (Big Data, machine learning, CRM) Business Intelligence (BI) is one of those umbrella terms that many people use regularly without fully understanding its meaning. BI is comprised of many tools that help to glean information and insights from raw data. Thus, an ETL package that moves data from one location to another is just as much a BI tool as is a fancy looking infographic. Combine this lack of clarity with the overloading of the term “reporting, and we wind up with some real confusion in this space.

Reporting is the process of using data to highlight things or trends that have already happened. This can be contrasted with monitoring, which does the same for things that are happening now, and predictive analytics, which tries to predict what will happen in the future based on the same data. The difference between reporting and monitoring is only one of data latency, and as such, monitoring is often referred to as real time reporting, which further muddies the water. However, for the purposes of this article, I want to focus on historical reporting.

Reports are typically one of two types, either operational or analytical. Tools that are good at producing one type are typically not so good at producing the other. What’s the difference? Operational reports are designed to provide information that we know we need, and analytical reports are designed to help us discover things that we didn’t know, or to help answer unanticipated questions. Operational reports are typically designed to be printed. They are typically well paginated, pixel perfect, and provide a single view of the data within any given report. Analytical reports are just the opposite. They are designed with visuals as a starting point, but allow for the ability to pivot on or drill down into the data as appropriate to answer ad-hoc questions. Printing is typically a weakness for analytical reports, whereas drilldown is a weakness for operational reports.

Both report types have their place but they both have very different design point. The data that backs an operational report should ideally be relatively flat, as that best reflects the report layout and helps with performance. Conversely, cubes and data models exist simply because a flat data structure does not adequately support analytical reporting. With analytical reporting, a user may at any point decide to view quantitative data (a measure) through the lens of a different facet (dimension). This difference is so great, that we need a different type of engine to support it. OLAP cubes and tabular models are both examples of this.

Another difference is the data that is necessary to support both report types. Operational reports tend to concern themselves with various levels of subtotals per the predefined facets. In a case like that, the data mart that backs the report only needs to store those subtotals. The granularity, or resolution of the data stored in the data mart does not need to exceed that of report that references it. Analytical reporting is different. Since users will be expected to drill down on data, from on dimension to another, or to filter the data according to increasingly granular facets, it is critical to store all of the data in the data mart backing the data model. We don’t know the level of resolution the analyst will need; therefore, all detail is required.

As a simple example of this, consider the case where we want to analyze some server log data over a period of time. We can pre-aggregate the data in the data model such that it stores the total of the log entries of various entries on a daily basis. There would need to be a total based on each dimension, but the overall data storage would be less than for the raw data. Such data would allow an analyst to spot trends over several days, but the decrease in resolution means that it will be impossible to spot any usage trends within a given day. If daily trends will never be necessary, then this doesn’t matter, but the nature of analytical reports means that the designer can never be sure.

The more that the source data for the report is pre-aggregated, the less that report becomes analytical in nature, and the more it approaches operational. This is regardless of the tool used; you can build either report type with any tool, it’s just that it may not be optimal.

The issue here is one of semantics. Semantics however are important in knowing what you are getting if reports are being provided to you. Calling something “Analytics” does not make it so. If you spin up a content pack in Power BI, and find that the underlying data model provides just enough dimensions and measure to construct the provided report, and that you can’t deconstruct the data in any meaningful way, what you have is a report, not analytics, no matter what the platform. As with anything, there is a trade-off between complexity and power. Given the nuances of this topic, it’s important to look under the hood to know what you are getting.

The answer “42” is perfectly acceptable if you already knew that the question was “what is 6×9?”. But if you want to know why, that takes a little more digging. You’d also know that there might be a data problem…

SQL Server 2016–Which Edition Do You Need for Business Intelligence?

For the past several releases, SQL Server has come in 6 possible editions. Developer, Express, Web, Standard, Business Intelligence, and Enterprise. Developer, Express and Web are for specific workloads, which leaves Standard, BI, and Enterprise. The choice of which edition to use would seem to be obvious – the one named Business Intelligence. However, Enterprise contained all of the features that the BI edition did, and in many cases, wound up being a better choice from a licensing perspective. Standard mode also provided many BI capabilities, but not all.

The biggest difference (but not the only one) from a BI standpoint between Standard, and either BI or Enterprise edition was the support of the Tabular Mode in SQL Server Analysis Services. For those unaware, Tabular Mode is the engine behind PowerPivot, and increasingly importantly, Power BI. From a price standpoint the difference between Standard and either BI or Enterprise is quite significant. This has put the Tabular model out of reach for some small and medium sized businesses which is unfortunate, given that tabular is at the center of Microsoft’s future BI efforts.

SQL Server 2016 removes the BI Edition as an option, leaving us with a choice between only Standard and Enterprise. The biggest news in my opinion from a licensing perspective with 2016 is that Tabular Mode will now be supported in Standard Edition. This puts the tabular model within the reach of all organizations, and closes the licensing gap in the BI stack. This is fantastic news.

There are of course limitations with Standard mode. Tabular in Standard Mode is restricted to 16 GB of RAM, which may seem like a lot, but keep in mind that tabular is an in-memory technology. It’s possible to bump into this limit fairly quickly, but it’s a limit that serves the small/medium business space rather well.

PowerPivot for SharePoint also remains an Enterprise only feature. However, given the capabilities available in Power BI, and the upcoming rendering capabilities of SSRS, this may be less important than it previously was.

Given that it’s relatively simple to move from Standard to Enterprise (from a technology perspective), this approach allows organizations to get up and running, and then scale up if necessary. It removes that up front Enterprise cost barrier. It’s much easier to get budget for and Enterprise license when its value has already been proven.

Another difference between Standard and Enterprise in SSAS is that Standard mode does not support partitioning, perspectives or DirectQuery. DirectQuery allows for real-time analytical reports, which removes the cached data storage from the picture. All queries go directly back to the source. An explanation of partitions and perspectives is beyond the scope of this post, but if you don’t know what they are, the chances are that you don’t need them.

From an SSRS standpoint, the traditional differences between Standard and Enterprise are still in place. These include data alerting, data driven subscriptions, PowerView support  and scale out capability. All of the new features of SSRS 2016 are available in both Standard and Enterprise modes with one very notable exception. The new Mobile Reports are only available with Enterprise.

Mobile reports are the result of last year’s acquisition of Datazen, which has been fully integrated into SSRS. It allows on-premises SSRS servers to provide rich mobile reports on a variety of platforms. If your organization is using Power BI already, then you likely have a mobile solution, but if not, Mobile reports may fill that gap.

A complete summary of the differences between all of the different SQL Server editions can be found here. A quick PDF chart of what’s new in SQL Server can be found here.

In summary, both Standard and Enterprise editions of SQL Server 2016 are now suitable for use in business Intelligence solutions. The decision to move to Enterprise can now be based on scale and enterprise requirements, not on basic functionality. This, in my opinion, is all to the good. 

What you need for Business Intelligence in SharePoint 2016

Over the past few weeks, I’ve put together a number of posts that outline the intricacies of setting up SharePoint 2016 with its BI workloads, in particular Excel, PowerPivot, and SQL Server Reporting Services. With the full release today of SharePoint 2016, I wanted to summarize these posts, and to provide some context.

The major change to the BI world is of course the fact that Excel Services is no longer included, its capabilities having been replaced by Office Online Server (OOS). The posts below discuss the implications of this change, as well as how to configure all of the BI features in the new platform.

Article Description
Rethinking Business Intelligence in SharePoint and SQL Server 2016 My take on the changes to on-premises BI in the Microsoft world, and what the implications are for the present and future
Adding Excel Services Capabilities to a SharePoint 2016 Farm How to Set up Office Online Server to support the services previously available in Excel Services
Enable PowerPivot Support in Office Online Server 2016 and Sharepoint 2016 How to set up SharePoint 2016 and Office Online Server to support Excel workbooks with embedded PowerPivot data models
Using PowerPivot for SharePoint with SharePoint 2016 How to configure the PowerPivot for SharePoint 2016 service application
Configuring SSRS 2016 Integrated Mode with SharePoint 2016 How to configure SQL Server Reporting Services 2016 Integrated mode in SharePoint 2016
Integrating SharePoint 2016 with SSRS Native Mode How to configure SQL Server Reporting Services 2016 Native mode and integrate it with SharePoint 2016

Just a quick glance at the articles above will show a deep dependency on SQL Server 2016. For example, in prior versions of SharePoint, multiple versions of SSRS were supported on SharePoint. This is no longer the case with SharePoint 2016. To be clear, I am talking about the BI components (SSRS, PowerPivot for SharePoint) and not the core database server for SharePoint. SharePoint 2016 requires SQL Server 2016 versions of both PowerPivot for SharePoint and SSRS. This means that if you’re invested in Business Intelligence in SharePoint 2013, you’re going to need to wait for SQL Server 2016 before you upgrade in a production environment.

SQL Server 2016 is currently at the Release Candidate (RC0) stage, and its release won’t be that far off. You can get started today on your test migrations, knowing that the full release will likely be available by the time your testing is complete. The articles above were all written while using the CTP 3.3 version of SQL Server 2016.

Looking through the articles you’ll find a number of configurations, and requirements that line up with specific scenarios. Below is a quick guide to outline what is required to support what feature in the SharePoint 2016 BI space.

Feature Requirements
Excel workbooks connected to SSAS Data Sources Kerberos Constrained Delegation (KCD) between OOS and SSAS data source

OR

EffectiveUserName enabled on OOS Server(s)

OOS Server account(s) added to Admin list on SSAS server(s)

Connected Excel workbooks to Windows Authenticated SQL Server Data Sources KCD between OOS and SQL Server

Claims to Windows Token Service running on OOS Server with Network Service enabled

Connected Excel workbooks using stored credentials (Excel Services Authentication Options) Secure Store Service (SSS) credential created

OOS machine account added to SSS Members list

“AllowHttpSecureStoreConnections = true” set on OOS server if HTTP is used

PowerPivot enabled Excel workbooks SSAS PowerPivot Mode server available

SSAS PP Mode server added to BI server list on OOS Server via New-OfficeWebAppsExcelBIServer cmdlet

OOS Server account added to Administrators list of SSAS PowerPivot Mode Server

Automatic Refresh of PP enabled workbooks PowerPivot for SharePoint

Silverlight (client side)

PowerPivot Gallery PowerPivot for SharePoint

Silverlight (client side)

Excel files as a data source PowerPivot for SharePoint

PP4SP must have admin access on SSAS PP mode Server

KCD between OOS and SharePoint application

Claims to Windows Token Service running on OOS Server with Network Service enabled

External ODC file support
PowerPivot Management Dashboard
S2S Trust Configured between OOS and SharePoint
Power View reports SSRS Integrated mode

Silverlight (client side)

Power View in Excel
Power View with Excel as a data source
SSRS Services account must be added to the Admin group on the BI server

Silverlight (client side)

I’ll update this post if anything significant changes between now and the release of SQL Server 2016, but this should help those interested get up to speed today on Business Intelligence in SharePoint 2016.

Using PowerPivot for SharePoint with SharePoint 2016

While the capabilities previously provided with Excel Services have been moved to Office Online Server (OOS) in the 2016 version of SharePoint, PowerPivot for SharePoint (PP4SP) has not. PP4SP remains a SharePoint service application in the 2016 edition of the product. This service application is responsible for providing the automatic data refresh capability for PowerPivot for SharePoint enabled workbooks. As an aside, it can also refresh connected workbooks, as I discuss here. Given that the rendering engine now exists on a separate server, there are a few additional steps to perform, and this article aims to walk through them.

Basic Installation

Prior to setting up PowerPivot for SharePoint, you’ll need a SharePoint farm that has been enabled for PowerPivot workbooks, as I have previously outlined here. The Add-In is available from Microsoft here.

Installing is a simple matter of downloading the add-in and running setup. You’ll be presented with a straightforward dialog box with 4 options.

This should be installed on every SharePoint server in the farm, whether or not it will run the Service application. Technically the first option is not required for front end web servers, but it is small, and I like to keep my options open. After clicking next, the bits will be installed.

Like SharePoint, once the bits are installed, they must be configured. This is done through the PowerPivot for SharePoint configuration tool, which the earlier setup installed. It should be available from the application list on the server. It works much the same as it did with earlier versions of PP4SP and SharePoint. Run it and you’ll be prompted for the installation type. Select “Configure or repair..” and click OK. Next, you’ll be presented with the configuration detail dialog. The dialog contains a number of configuration nodes, which drive a series of PowerShell scripts that are used to perform the configuration (tip – click on the Script tab to see the scripts in question). The exclamation point icon indicates that parameters need to be supplied.

The first node is mandatory – Configure or Repair.

Here, you enter the credentials of the user that can perform the configuration – I normally use the user that was used to configure SharePoint in the first place – spSetup in my demo environments. This is the only step that is critical. However, I find it to be good practice to change the name of the Service application and database.

The default values begin with “Default Power Pivot…” and the database contains a GUID as part of its name. When searching alphabetically for PowerPivot, I tend to look under P not D, so I remove the word default from both, the GUID from the database, and further change the database name to conform with naming conventions. Finally, it’s a good idea to check the Site Collection that will be activated.

The configuration tool will activate the PowerPivot solution in one site collection by default. It can be activated later in others, but it’s worth starting off on the right foot.

Click the “validate” button, and if all of the indicators are green, go ahead and complete the configuration.  Once configured, no further Central Admin work should be necessary, at least not at this point.

PowerPivot Gallery

A PowerPivot gallery is not required. All of the PowerPivot for SharePoint features can be used in a regular document library, but the gallery centralizes things and makes these features more discoverable. It should be noted that just as with SharePoint 2013, the PowerPivot gallery is a customized document library that uses Silverlight to display its contents. This dependency on Silverlight means that in order to use it, workstations must have Silverlight installed, and neither Google Chrome, nor Microsoft Edge browsers will support it.

To create a new PowerPivot Gallery, navigate to the site contents of the target SharePoint site, and select “Add an app”. Select the PowerPivot gallery and give it a name. If you don’t see PowerPivot Gallery as an option, you may need to enable the PowerPivot Feature for Site collection in the Site Collection features list. Once added, upload a PowerPivot enabled workbook. This workbook should contain a data model where the data was imported directly into PowerPivot (not via Power Query). Once uploaded, after a few moments, the thumbnails from the workbook objects should show up in the gallery. It should be possible to interact with the workbook, as PP4SP is not required for that, but the two (or 3 depending on whether or not SSRS has been installed) icons on the right of the workbook provide access to PP4SP capability.

In order to set up scheduled refresh, click on the calendar icon (The Excel icon is for using Excel as a data source – see below). This opens up the data refresh history for this workbook. To configure it, click on the “Configure Schedule link”. On the configuration screen, select the enable check box, enter the desired schedule, and the credentials needed to connect to the source data. For testing purposes it is more deterministic to explicitly enter credentials here, but refresh supports a “refresh account” (configured via the Secure Store Service), or any Secure Store Service credentials. Also, selecting “Also refresh as soon as possible” will immediately force a refresh cycle, which will begin within 5 minutes of saving, and is useful for testing.

Once complete, open the refresh history for the workbook, and you should see either a stopwatch icon, indication a refresh is in progress, a green check mark, indicating successful completion, or a red x, indicating failure.

One thing should be noted – data models created by using Power Query in Excel will always fail – this is true as of March 2016. Power Query refresh has been stated as a feature for PowerPivot for SharePoint 2016, but as of this writing, it has not yet been included.

Workbook as a Data Source – Kerberos Enablement

The URL of a workbook that contains a data model can be used in a connection string in another workbook, and PowerPivot for SharePoint can intelligently route that connection to the backing SSAS PP Mode server. To the consuming workbook, it looks just like a regular SSAS server.

In prior versions of SharePoint and PP4SP, using a workbook as a data source “just worked”, because the service and the workbook were all on the same server. With OOS, the server is on a different server. OOS needs to connect to the source workbook (the one with the data model) with the credentials of the consuming user, which means that for this to work, Kerberos Constrained Delegation (KCD) must be configured between OOS and SharePoint.

To be sure, you only need to configure KCD if you wish to use Excel files as a data source. If not, this step can be safely skipped.

You need to allow the computer account for the OOS server to delegate credentials to the account running the HTTP service for the SharePoint application that contains the workbooks to be used as data sources. In the example below, the OOS Server is NAUTILUS2016OOS, the service account is NAUTILUS\spApps, and the application is http://home.nautilus.local. This PowerShell can be run on any Domain Controller server.

$allowedPrincipals = @()
$allowedPrincipals += Get-ADComputer -Identity NAUTILUS2016OOS

# Set the delegation property on the application pool identity.
Set-ADUser spApps -PrincipalsAllowedToDelegateToAccount $allowedPrincipals

# Set the Service Principal Names for the application pool identity.
SetSPN -S HTTP/home NAUTILUS\spApps
SetSPN -S HTTP/home.nautilus.local NAUTILUS\spApps

Once successfully configured, it should be possible to use Excel files that contain data models as a data source for other Excel files. To create a new one, click the Excel icon beside the data refresh history icon in the PowerPivot gallery.

Wrapping Up

One other feature requires further configuration to work, and that is the PowerPivot Administration dashboard. Security constraints now prevent the use of Central Administration as a container which means that the dashboard must be set up in a regular site collection. This  requires Server to Server (S2S) trust to be configured. Given that this is not a user facing feature, it’s out of scope for this article, but details on how to do it can be found in the Deploying SQL Server 2016 PowerPivot and Power View in SharePoint 2016 document.

Setting up PowerPivot for SharePoint will still not give you the ability to render Power View reports in a browser whether they are created standalone, or in an Excel workbook. For that, it is necessary to set up SQL Server Reporting Services (SSRS) in SharePoint mode, as Power View rendering is part of SSRS. That will be the topic of an upcoming article.

Enable PowerPivot Support in Office Online Server 2016 and Sharepoint 2016

SharePoint has supported PowerPivot enabled workbooks since they were first introduced with the initial PowerPivot for Excel Add-On. This support was initially provided via PowerPivot for SharePoint, which was comprised of two major components. The first component was a special, and mostly hidden instance of SQL Server Analysis Services (SSAS) running in what came to be known as SharePoint mode. The other component was a SharePoint shared service application that provided for the scheduled refresh of workbooks and an administrative dashboard.

Initially, these two components came as a single product, PowerPivot for SharePoint, available on the SQL Server install media. The PowerPivot for SharePoint that shipped with SQL Server 2008 R2, and with 2012 prior to SP1 would install both components on to a SharePoint server. Beginning with SharePoint 2013 and SQL Server 2012 SP1 it became possible to separate the two components, allowing for greater scalability. An entry for each SSAS server running in SharePoint mode simply needed to be added to the Excel Services service application (the “BI Servers”), and they would automatically be used for any embedded PowerPivot models.

 With the deprecation of Excel Services in SharePoint 2016, this support moves to Office Online Server 2016 (OOS), and with it, some further decoupling. It is now possible to support the data model with having first installed the PowerPivot for SharePoint service application. Technically, this was possible in SharePoint 2013, but it was far from obvious as to how to make it happen. All that is necessary for PowerPivot workbook support is an Analysis Services server running in what is now called “PowerPivot Mode”.

PowerPivot Mode is that very same specialized version of Analysis Services that exists to support data models that was previously referred to as “SharePoint Mode” in earlier versions. The option to install it is now front and center along with “Tabular Mode” and “Multidimensional and Data Mining mode”. To install a PowerPivot mode Analysis Services server, simply install Analysis Services as normal, and choose “PowerPivot Mode” when you get to the options screen.


The other important thing to note in this dialog box is the inclusion of the computer account for the OOS server(s) as (an) administrator(s). This is necessary because the OOS machines will need to create and tear down data models from Excel workbooks on demand, and the OOS process on these machines run as the Network Service account. Note that this is NOT necessary because of EffectiveUserName as with a regular Tabular Mode SSAS server. EffectiveUserName is not used in PowerPivot mode. It the server gets installed without this account added as administrator, it can be added later via SSMS 2016 in the same manner described in my earlier article regarding Tabular mode SSAS.

Once the PowerPivot mode SSAS server is up and running, the OOS server needs to be configured to use it (If you don’t yet have an operational OOS environment, please see this earlier article to get up and running). As with all other OOS configuration options, this is done through PowerShell. The command to add a PowerPivot mode server is:

New-OfficeWebAppsExcelBIServer –ServerId NAUTILUSSQLBI\SHAREPOINT

In the example above, the name of the PowerPivot mode server is NAUTILUSSQLBI and the instance name is SHAREPOINT. I have found it helpful to use named instances for PowerPivot Mode servers.

Additional servers can be added to the farm in a load balanced configuration by simply rerunning the above command with any additional server ids. This is similar to the way it was done in Excel Services, by adding them to the BI Servers list.

Once this has been put into place, it should be possible to interact with any Excel workbooks that contain a data model. All slicers and pivots should work. This will not, however allow you to schedule data refreshes, or use any other PowerPivot for SharePoint features. For that, you’ll need to add PowerPivot for SharePoint to your SharePoint farm, and that is the topic of an upcoming article.