Category Archives: Business Intelligence

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…

Completing the Microsoft Reporting Roadmap

In the recent announcement outlining the SharePoint integration strategy on the SQL Server Reporting Services Team’s blog, there was a statement that was almost hidden that I think deserves more attention. The statement was:

“….in time, we aim to support web-based viewing of Excel workbooks in Native mode…”

This may not sound like a big deal – after all, we’ve been able to serve up Excel workbooks in a browser since Excel Services was initially introduced with SharePoint 2007. However, as per Microsoft’s Reporting Roadmap from October 2015, Reporting Services is their on-premises solution for BI report delivery. If an Excel workbook is to be considered a report, the SSRS absolutely should be able to do it. The roadmap defined four types of reports:

  • Paginated
  • Interactive
  • Analytical
  • Mobile

I tend to see there being two types of reports, Structured and Analytical. In the list above, Structured corresponds with Paginated, and the other 3 types are different subtypes of Analytical. They four categories do, however line up well with the different reporting technologies available.

Report Type File Type
Paginated RDL (Classic SSRS)
Interactive PBIX (Power BI)
Analytical XLSX (Excel)
Mobile RSMOBILE (SSRS Mobile aka Datazen)

The roadmap was primarily concerned with the future of SSRS, but SSRS is Microsoft’s stated report delivery platform for on-premises reporting. The platform for cloud reporting is of course Power BI. There is a third platform for the delivery of “Analytical”, or Excel based reports, and that’s Excel Online. On premises, it’s called Office Online Server, but it is the same technology. The three platforms and their capabilities are shown below.

SSRS

Excel Online/OOS

Power BI

Paginated

Yes

N/A

No

Interactive

Preview

N/A

Yes

Analytical

Announced

Yes

Yes

Mobile

yes

N/A

Yes

The technical preview of Power BI reports in Reporting Services is available for testing, which covers Interactive reports in SSRS, and the above statement indicates that there is a solution to support Analytic reports in SSRS as well. The Power BI platform does this already, and it is done by leveraging the capabilities of Excel Online. Given the fact that Office Online server provides the same capabilities on premises, it makes sense that it would be used by SSRS when the Excel workbook support is added.

It should also be noted that the above comparison shows Mobile reports being supported by Power BI. To be clear, Power BI does not support RSMOBILE files, but regular Power BI reports are inherently mobile and available through the Power BI mobile client. which is also how RSMOBILE reports are delivered to end users.

The Reporting Services team is clearly very close to completing the vision laid out over a year ago, in the Reporting Roadmap for on-premises users. If the goal is to have parity between on-premises and cloud platforms, the only thing remaining (apart from possible support of the RSMOBILE format) is support for Paginated reports in Power BI. There have been no statements made regarding this capability, but its absence is certainly notable.

The Future of Report Integration with SharePoint

Yesterday, Microsoft made official what many, including myself had been suspecting ever since the release of SQL Server 2016 – that SQL Server Reporting Services Integrated mode would not exist in the future. With the announcement, we now know the timeline of when that will happen. SSRS Integrated Mode will not be included with the next version of SQL Server. Instead, SSRS Native mode will be more tightly integrated with SharePoint for those organizations that use both products. As someone that has always approached Business Intelligence from the SharePoint point of view, I see this is a good thing.

This change is another step in the process of de-cluttering and uncomplicating SharePoint. This process started arguably with the move away from fully trusted code running on SharePoint, to the newer app, add-in and now SPFx development models that run with SharePoint. When Excel Services was removed from SharePoint in SharePoint 2016, with its capabilities moved to Office Online Server this process became obvious. A decreased dependency on SharePoint allows for simpler, more streamlined architectures, better options for upgrade management, and better, more targeted performance management.

SSRS SharePoint Integrated mode has been with us in various forms since it was first introduced in SQL Server 2005 SP2. The original goal was to simplify the integration of the two products, and to take advantage of SharePoint’s storage and authorization capabilities. The integration has always worked well, but the very fact that these two products were delivered by two different product teams on different media, often on different release schedules has typically led to a great deal of confusion. The SharePoint prerequisite for Integrated Mode leads to far too many SQL servers having SharePoint installed on them.

Managing SSRS in SharePoint Integrated mode requires a combined skill set to some degree as well, with knowledge of both SSRS and SharePoint. SharePoint administrators tend to be intimidated by SSRS, and SharePoint simply mystifies SQL DBAs.

The fact that the two different modes did not always maintain feature parity is another problem. PowerView and several other features are only available through SharePoint integrated mode. This results in entire SharePoint farms being created for the sole purpose of providing reporting features. Since those performing these installations are typically not familiar with SharePoint best practices, these farms tend to be unreliable. The latest release of SSRS 2016 contains a massive number of new features, but many of them in Native mode only, leaving the SharePoint integrated folks with a decision whether to favour features or integration.

A strategy that reduces the dependency of one platform on the other is therefore to everyone’s advantage.

The two operating modes also represent two different code streams for Microsoft to maintain. Given the finite set of resources that is any development team, resources must be spent on maintaining both of these streams that could otherwise be applied to features. A single code stream is simply more efficient.

Preventing the wholesale move to Native mode are several SharePoint integration features that have been employed over the years that are only available in SharePoint Integrated mode.

There has been a SharePoint Report viewer web part for SSRS Native mode since SharePoint 2003. The trouble is that while it does work, it is deprecated, and hasn’t been updated since SQL Server 2008 R2. It also doesn’t allow for parameter binding, or interface control. For all intents and purposes, it is an iFrame embed of a report. The web part that is available through integrated mode allows for parameter interactivity, and significant control of the look and feel. It has been widely deployed. Integrated mode also allows for the reporting of SharePoint list data, and the ability to publish reports to a SharePoint library on a schedule. These features are well utilized in the market today.

Power View reports (RDLX) built on top of SSAS tabular models, or Excel Power Pivot models also require Integrated mode. Compounding this is the fact that Power View requires Silverlight, which does not work in either the Chrome nor the Microsoft Edge browsers.

These integration features will need to be added to Native mode before it will be possible to fully abandon Integrated mode. The good news is that the announcement commits to doing just that. Report embed, Report viewer web part, and SharePoint library destination capabilities will all be added. For Power View reports, a conversion tool will be provided to convert from RDLX into Power BI Desktop (PBIX). A technical preview is already availably that demonstrated PBIX rendering in SSRS.

This announcement signals the end of SSRS SharePoint Integrated mode, but it does not spell the end of SSRS SharePoint integration. The single mode architecture should be more approachable, simpler, and more efficient. It’s a win all the way around.

SharePoint and Power BI – Better Together


Ever since 2007, SharePoint has included Business Intelligence amongst its core workloads. There have been a variety of approaches to the workload over the years, but today those core workloads include Excel Services/Excel Online, PerformancePoint, SQL Server Reporting Services Integrated Mode, and Power Pivot for SharePoint.

Power Pivot for SharePoint and Excel Services go hand in hand and can really be considered as one of the main pillars, leaving us with three. If we quickly examine these three pillars in SharePoint 2016, it’s pretty easy to spot an emerging trend. Excel Services is gone from SharePoint 2016, its capabilities being added to Excel Online. Excel Online connects to, but does not run on SharePoint. PerformancePoint still exists in 2016, but it has received precisely 0 new features – it is identical to the version in SharePoint 2013, and remains a part of product for legacy reasons. For all intents and purposes, I consider PerformancePoint to be deprecated. SSRS Integrated mode has been greatly improved in 2016, but contains nowhere near the improvements that the Native Mode version of SSRS has in 2016.

At the same time, the past year has witnessed the spectacular rise of Power BI. Power BI is clearly the focus area for Business Intelligence within Microsoft for cloud based BI delivery. Last fall the SQL team announced that on-premises customers were not being ignored, and that SSRS was the platform for on premises BI delivery They also sketched out a roadmap that showed both platforms being able to deliver the same type of reports. In June 2016, the team delivered on a portion of this vision with SQL Server 2016 Reporting Service.

So where does this leave SharePoint in the Business Intelligence ecosystem?

In my opinion, it leaves it right where it should be – as an integrating platform, and NOT as a runtime platform as it has been in the past. SharePoint provides in context BI by connecting content to reports, and providing dashboards connected to multiple sources. In 2016, SharePoint connects to Excel Online to deliver Analytical reports. Excel runs with SharePoint now, not on it. SSRS Integrated mode still runs on SharePoint, but the investments in Native mode are a clear indication to me that this will be the direction here as well. Unfortunately, Sharepoint has been lacking tight integration with Power BI.

The recent Ignite 2016 conference was the first public appearance of the Power BI web part.

Figure 1: Insert web part dialog with Power BI web part

The Power BI web part works with Modern Sharepoint pages and is based on the new SharePoint Framework (SPFx), which means that it is completely client-side. Why does this matter to us? The fact that it is completely client side means that it will work both in SharePoint Online and on premises. Initially, it will only work with SharePoint Online, but that is because the SharePoint Framework is currently unavailable on premises.

To use the new web part, first create or edit a Modern SharePoint page. The Modern pages support the new Modern web parts. Click on a “+” icon to open the insert part control (Figure 1). Once inserted, add the report URL, and the page. The report page should immediately render within the context of the SharePoint page.

Figure 2: Power BI Report page rendered within a SharePoint page

Since the web part is rendering client side, the consuming user obviously needs to have access to the report. This means that the source report must have been shared with them through Power BI dashboard sharing, or the report is in a group within which the consuming user is a member. This latter case makes the most sense given that all Office 365 Groups will have a corresponding Modern Team site. Embedding the report within group pages should “just work”.

The devil is of course in the details, and all of these details are not yet available, but Given the number of questions that I have received over the past year about Sharepoint/Power BI integration, I expect that its existence will come as welcome news. Over time I would expect to see it picking up support for parameters and the ability to work with individual report items (this is speculation, but it makes sense). It’s also not much of a stretch to see how SSRS could make available a Modern web part that worked in the same fashion with on premises SSRSs. That web part could conceivably work both on premises an Online, bringing SSRS to SharePoint Online for the first time.

SharePoint is still very much a platform for integration and for Business Intelligence content delivery. SSRS and Power BI will be the de facto reporting engines for on-premises and the cloud respectively, and Sharepoint will be the dashboarding/integrating platform for both environments.

A Simplified Method of Working with SharePoint Data in Power BI

Although I typically advise against it, there are valid reasons to report on SharePoint list data directly. Power BI Desktop makes this data quite easy to access – you can use the built in connectors for SharePoint or SharePoint Online, or, due to the fact that any SharePoint list is available via OData, you can also use the OData data connector. Microsoft has recently made improvements to both methods, but the SharePoint connectors bring some significant usability advantages. One of these advantages is what I’m calling “summary columns”.

The Problem

Consider the following SharePoint list with different field types:

Connecting to this list with Power BI desktop and editing the query returns all of the list fields regardless of their visibility in the user interface. Assuming that we want to work with the above field values for analysis purposes, we can discard the fields that we don’t need, and reorder the remaining, leaving us with only these fields.

However, you can immediately see that we’ll need to do some work in order to get our data in a usable format.

The title field is simple enough – it’s value is immediately available, no issues there, and no changes are necessary. From the Name field, several columns are returned. Two are the ID of the name in the site collection’s user list. It would be possible to connect to the root of the site collection, retrieve the user list, and establish a relationship between the tables, but clicking the expand icon will allow Power Query to do a lookup for each ID and return the desired attribute, in this case, Name.

The same is true for the lookup field type – the column can be expanded in order to include any attribute of the source item. The field using managed metadata works in a similar fashion; it can be expanded in order to retrieve the text value of the managed metadata item. Link fields work the same way – the can be split into two columns, the link itself and the description. However, the field containing multiple values is a little different. The great news here is that it’s possible – previous versions of Power Query couldn’t work with multi value fields, but now the SharePoint data source supports it.

Multiple value field values are returned as a list. With list items, the expand icon will duplicate the entire record for each value on the list. This may or may not be the desired behaviour, but remember, this is Power BI. Everything can be aggregated.

Before After

The conclusion to be drawn here is that in order to represent SharePoint list data that is using any sort of control more complex than text or number, we need to do some work. However, the good news is that someone (I’m not sure if it’s the Office Team of the Power BI team) has added a feature that makes this whole process much simpler.

The Solution

After connecting to your SharePoint list, edit the query. Instead of diving in and performing all of these manual transforms, select your multi value column(s) if you have any (this will make more sense momentarily). Select any rich text columns as well, and then scroll right to find a column named “FieldValuesAsText”. Select this column, then right click on it and select “Remove Other Columns”.

The FieldValuesAsText column is our magic bullet. It automatically converts most (rich text fields being the exception) of the more complex SharePoint data types to simple text that work well within Power BI. Simple click on its expand button, select the columns that you want to include in your analysis. I find it useful to deselect “Use original column name as prefix” as well. We are left with textual representations of our field data.

You will notice that the multiple value fields here have their values separated by commas. For multiple values, I tend to prefer the “raw” approach, which is why we retained the multiple value column above. We can still expand it and create a separate line for each value, and remove the column created by “FieldValuesAsText”.

Finally, you may have noted that the Rich Text field isn’t automatically converted. In order to extract useful text from it, we still need to use Power Queries transformation functions such as Replace Value, Trim, and Clean.

In a nutshell, if you’ve been frustrated by formatting or data type limitations when using SharePoint data in Power BI, have another look, and check out the FieldValuesAsText column. It will make everything a lot simpler.