Integrating Microsoft Flow with Power BI for Real Time Reporting

In addition to all of the obvious business benefits that Microsoft flow brings to the table, one of the things that initially struck me about it was how useful it would be for data acquisition purposes. The first thing that I did with it was to create a flow that queries weather stations from Weather Underground, and stores the data in SQL Azure, and uses Power BI to analyze the weather patterns.

I may blog about that solution in the future, but with the Future of SharePoint event rapidly coming up, my BI Focal fellow collaborator, Jason Himmelstein convinced me that there was something more interesting that we could do with this. How about near real time monitoring of Twitter conversations for the event? All of the pieces were in place.

We rolled up our sleeves, and in relatively short order, had a solution. Jason has written about the experience on his SharePoint Longhorn blog, and he has included the videos that we put together, so I can be a little less detailed in this post.

There are currently essentially three different technologies necessary to make this happen.

  1. Microsoft Flow
  2. SQL Azure
  3. Power BI

Let’s go through each one.

SQL Azure

We could store our tweets in a variety of locations (CSV, SharePoint, Excel), and there are already a number of examples out there that demonstrate how to do this. The reason that we want to use a SQL Azure database is twofold. Firstly, Flow has actions for connecting and inserting data into it. That takes care of our storage requirement. The second, and most important part is that SQL Azure databases support DirectQuery in Power BI.

With DirectQuery, Power BI does not cache the data – every interaction results in a query back to the source, in our case the SQL Azure database. This has the effect of making the data available for reporting as soon as it has been delivered by Flow. That’s the theory at least. In reality, Power BI caches certain elements temporarily (dashboard tiles for example), but this is as close to real time as you can get in Power BI without writing data directly to t in the API. Reports are for the most part up to the minute.

You need an Azure subscription to create a database, and the process for creating it is documented in the following video.

We will be using the Twitter trigger with Microsoft flow, and it has several output variables. We want our table to be able to store the values of those variables in a table, so we use the following script to create that table.

CREATE TABLE Twitter
(
 id int IDENTITY(1,1),
RetweetCount int,
TweetText NVARCHAR(250),
TweetedBy NVARCHAR(100),
CreatedAt NVARCHAR(100),
TweetID NVARCHAR(50),
SearchTerm NVARCHAR(50)
);
Go

alter table Twitter add primary key (ID)

Once created, we are ready to fill it with tweets.

Microsoft Flow

The recently announced Microsoft Flow is a tool that allows users to automate and integrate processes from different data sources in the cloud. It is based on Azure Logic Apps, and is currently in preview, but already supports a wide variety of actions and triggers. You can sign up for, or access your flows at http://flow.microsoft.com.

Flows consist of two primary objects, triggers and actions. Most triggers, and at the moment all actions, are tied to a data connection. You can register your connections as you go, but you can also view and register them en-masse by selecting your person icon and selecting “My connections”.

image

Once registered, you can use “Browse” to start from a template, or you can go to “My flows” to start from scratch. That’s what we’ll do. To start, click on “Create new flow”, and you will be presented with the trigger selector.

image

Most of the available triggers are events, and the first 4 are special cases. The recurrence trigger allows you to schedule your flow. This is what I use for my weather gatherer – it just calls a web page every 5 minutes and passes the result into the next action. The external content source actions are in alphabetical order, so we just scroll down to the Twitter action and select it.

image

If you have already registered a Twitter account, it will be used by default. If you want to change it, or add a new one, just click on “Change connection”. It’s a good idea to use multiple Twitter accounts if you’re doing multiple queries to avoid running afoul of Twitter’s rate limiting. Finally, just enter the search term in the Query Text box. Any new post of that term on Twitter will launch the flow.

Next, we need to add the “SQL Azure – Insert Row” action. To do so, click on the “+” symbol,  click add an action, then click “Load more” at the bottom. Scroll down and select the action.

Again, if you have a database registered, it will be selected by default. If you have multiple databases registered, or want to add more, click on “Change Connection”. Once you have the correct connection selected, you can click on the dropdown and select the correct table (the one created above”). Once selected, the fields will load in to the action.

image

Populating the fields is a simple matter of selecting the appropriate output variable from the Twitter trigger. The final field, SearchTerm, is used to distinguish between different Twitter searches. Each flow only triggers on one term, but we want to set up multiple flows. We manually enter the value here (in our case “FutureOfSharePoint”). Later, that will be used as a slicer in Power BI.

Once complete, give the Flow a name, click on “Create Flow”, and then “Done”. At that point, you really are done. That’s it, that’s all there is to it. You can query SQL Azure to check for data, and you can also use the information icon to check on the status of Flow runs.

image

image

All of these steps are well documented in Jason’s video below:

Power BI

We want to surface this data with Power BI. We can do this directly from the web interface, but we have a lot more options if we design the report with Power BI Desktop.  The next step is to launch Power BI Desktop, Select “Get Data”, select “Microsoft Azure SQL Database” and press the “Connect” button. At this point, you enter in the details about the Azure SQL Server and database, and most importantly, select the DirectQuery option.

image

The import option will retrieve data from the SQL database and cache it in an embedded model within the report. Once published, the Power BI service can keep it refreshed, but no more than 8 times per day. This is contrasted with DirectQuery, where no data is persisted in the service, and every interaction results in an immediate call back to the data source. For frequent updates, this is what we need.

A word of caution here – we pay a significant penalty from a feature standpoint when using DirectQuery mode. Most of the functions in Power Query and many of the functions in DAX are unavailable to us in this mode. However, with this particular data set, these restrictions are an acceptable tradeoff for the frequent updates.

Again, Jason has done a great job explaining the steps required to build the reports and dashboards in the video below, so I am not going to repeat them here.

Once the report is published, you may want to present it to a wider audience. You can do that through dashboard sharing if your recipients have access to Power BI, or you can publish it anonymously. Given that this is Twitter data, it’s certainly public, and there is no harm in doing so.

To publish the report anonymously, simply open the report in the Power BI service, and select File – Publish to web.

image

You will then be presented with a dialog box that will give you both a link and an embed code for 3 different possible renditions of the report. Simply select the one you want to use and paste it into the ultimate destination. My report can be seen below, and I will likely update it from time to time to follow current events.

One thing to keep in mind about reports shared anonymously is that even though the report is using DirectQuery, the visuals are only updated approximately every hour. The above report will lag reality by about an hour.

You can see here the power of these tools working together. Flow is an easy to use but yet powerful integration tool. SQL Azure is a rock solid database available in the cloud to other cloud services, and Power BI allows for rapid insights to be built by Power users. No code was harmed in the building of this solution, but regardless, it’s still quite powerful.

From here, I can only see it getting better. My ask from the Flow team? A Power BI action that pumps data directly into a Power BI data model, thus eliminating the need for the Azure DB, and allowing for self updating visuals in Power BI, but that’s a topic for another day.

Power BI Analyze in Excel – The beginning of a beautiful thing?

One of the announcements made at the Microsoft Data Summit in this past March 2016 was the availability of Analyze in Excel. This feature allows an Excel workbook to connect to a data model that is stored in the Power BI service, and to use it to analyze the data contained within. With this approach Excel is not importing data, or at least it is not importing any more data than the query results. It is exactly like connecting data to SQL Server Analysis Services data sources, something that Excel users have been doing for years. Well, to be completely accurate, it’s not LIKE connecting to SSAS, it IS connecting to SSAS. The only difference is that in this case SSAS is in the cloud. This feature significantly enhances the utility of the Power BI Service, and is important for several reasons that may not be all that obvious. I’d like to walk through a few of them, but let’s start with the obvious.

Excel is a very powerful analytical tool

As nice, and as attractive as Power BI visuals are, Excel still rules the roost when it comes to doing advanced analytics. Excel has been doing this for years and is very mature. It supports features such as pivot tables, pivot charts, and drill through to data, where Power BI reports still do not. The lack of these features can be a blocker for Power BI on its own, but if the data models in Power BI can be analyzed with Excel, suddenly a move to Power BI is not an either/or decision – you can have it both ways. You can deploy models and reports into Power BI and take advantage of all the goodness there, but you can also connect with Excel when the deep analysis is needed. With Analyze in Excel, you can have it both ways.

A wider audience for your data models

Very often, the person that builds the data model is the same person that does the analysis. This is the nature of self-service analytics. In the past when the only multidimensional analysis tools were OLAP cubes and connected Excel workbooks, cube design was a specialized skill. The cubes were published and users would use them as is. The advent of the data model (Power Pivot) and self service analytics lowered the skills bar so that analysts could acquire data, model it and analyze it, making the entire process much quicker and responsive. However, this still puts the model out of reach for those with no data modeling skills or interest.

Analyze in Excel provides the best of both worlds. Analysts can build models and reports in Power BI, and users that need more in depth analysis can connect to them with Excel without having to reinvent the wheel. This in effect provides the same capability that PowerPivot for SharePoint does on premises. One model can now reach a much wider audience of users. This has many of the benefits of an on-premises SSAS deployments without all of the organizational overhead of getting them up and running.

Uses the Analysis Services OLEDB Driver

The connection that is made from Excel to the Power BI services uses the latest version of the classic Analysis Services OLEDB driver. This is the driver that has always been used to allow Excel (and other tools) to communicate with Analysis Services, and this new version has been updated in order to work with the cloud based SSAS service. In fact, in order to use the feature, you must first download and install the updated driver. Therefore, in theory, any tool that uses this driver should be able to communicate with Power BI models as if Power BI was one great big SQL Server Analysis Services server (because it is).

It really is Analysis Services in the cloud

The Power BI service itself is backed by tabular mode SSAS. Until now, it was necessary to go through the service to access it. Analyze in Excel is the first instance that I know of that a client application communicating directly with that SSAS instance. While this connection is really using the Power BI API, it does beg the question – can a fully Platform as a Service version of Analysis Services be very far away?

Claims based authentication and Power BI API

None of the products in the SQL Server suite currently supports claims authentication. This is true even for the yet unreleased SQL Server 2016. Even SQL Azure, a cloud based version of SQL Server, requires SQL authentication only (although Azure Active Directory authenticated databases are currently in preview). However, looking at the connection string contained in the ODC file used by the Analyze in Excel feature reveals some interesting things. Here’s one connection string:

<odc:ConnectionString>Provider=MSOLAP.7;Integrated Security=ClaimsToken;Identity Provider=AAD;Data Source=https://analysis.windows.net/powerbi/api;;Initial Catalog=xxxxxxx; ……..

The value MSOLAP.7 for the provider indicates that this is the next version of the SSAS OLEDB Driver. No surprises there, but this does hint at future compatibility (see SharePoint below). The value for Integrated Security, and Identity Provider (ClaimsToken and AAD) indicate that it is leveraging Azure Active Directory Claims authentication. We therefore have a version of SSAS that can use Claims based authentication. This isn’t available to on-premises installations, but given that the capability has been built, I imagine that it is not all that far away.

Finally, the Data source indicates that the Power BI API is being used to marshal all communication with the back end API service. I think that it is reasonably to conclude that any API for a PaaS based version of SSAS would be based on, or strongly resemble the Power BI API. They may even be one and the same.

Excel Online in SharePoint

As anyone that has set up PowerPivot for SharePoint can tell you, SharePoint supports the configuration of new OLEDB drivers. This support carries forward into the Office Online Server in the world of SharePoint 2016. Given that both SharePoint and OOS utilize claims based authentication, it should theoretically possible to create a workbook that uses the Analyze in Excel feature, store it in SharePoint, and have it work for multiple users from within a browser. I imagine that more plumbing is needed at this point, but it would be an interesting way of integrating Power BI in the cloud with SharePoint both on premises and Online.

Reusing the Excel Files, and Limitations

In the same vein as discussed with SharePoint, Power BI itself allows Excel files to be interacted with in the service in exactly the same manner that Excel Online does. Theoretically, one should be able to use Analyze in Excel to build a workbook, then connect it to Power BI and have it work for interaction. While it is possible to connect it, all interactions fail at the moment. It appears that the Power BI service (or the backing Office Online service) does not yet support the new OLEDB driver.

Another current limitation of this feature is that data sources using Direct Query (this includes SSAS sources) or sources created by the Power BI API cannot be used with Analyze in Excel. At least not yet.

Analyze in Excel is another useful tool in the Power BI arsenal, but as outlined above, I think that it’s a harbinger of even greater things to come.

Analyze in Excel – Setup and initial issues

Analyze in Excel is an extremely important new Power BI feature for reasons that I’ll outline in more depth in an upcoming post. If you want to try this feature for yourself, there’s a good possibility that you’ll hit one of the errors that I ran into. This post will hopefully help other intrepid pioneers past the hurdles and get working with this fantastic new feature.

Initialization of the Data Source failed

In order to enable the Analyze in Excel feature, Excel must use the Microsoft AS OLE DB Provider from SQL Server 2016. This driver supports claims based authentication for SSAS, which is what Power BI uses. Observant readers will notice that I just used “claims based authentication” and “SSAS” in the same sentence, but I digress. What’s that you say? SQL Server 2016 has not yet reached RTM? That’s OK because you can use the driver from the Release Candidates, which can be obtained from the SQL Server 2016 Feature Pack directly. More commonly though, you’ll get it from Power BI user interface. There it can be acquired in one of two ways.

You can proactively download the updates from the Power BI “Download Center”, which is the little down arrow in the upper right of the toolbar:

Clicking the “Analyze in Excel updates” button will download the installer for the driver, which you can run right away.

Running the file takes you through the installation of the driver, which is simple and wizard driven.

Once installed, you may then navigate to a data source and launch “Analyze in Excel”.

At this point you will be prompted for two things. Firstly, you will be prompted to download and launch an ODC. This is the connection file that will be used to connect your Excel client to the SSAS service that your data source is housed in. If you’ve already installed the new driver, you can just go ahead and run it, and Excel will launch. If you haven’t you can take advantage of the second (simultaneous) prompt, which is to download the driver. This prompt will appear whether or not you have already installed it.

This approach is a bit different. This dialog has a big yellow box that just begs to be clicked, and it will install the 32-bit version of the driver. You can also take the high road and install the 64-bit version which of course you’ll need if your Excel is 64 bit. After all, anybody serious about doing data in Excel is using 64 bit Excel, right? In any event that’s the difference between the second approach and the first. The first approach does not give the option, it just goes ahead and uses the 32-bit version.

I of course originally opted for the first option, and whenever the ODC launched, and Excel opened, I received the error “Initialization of the Data Source failed”, which is hardly intuitive.

The issue of course is a mismatch between the bit level of the driver, and the bit level of Excel. The solution to this problem is quite simple. The 32-bit version needs to be removed, and the 64-bit version installed. You’ll find the offending package in listed in Programs and Features as “Microsoft AS OLE DB Provider for SQL Server 2016”, but it makes no mention of the bit level.

If you happen to have both installed (you can) the only way to tell the difference between the two is that the 64-bit version is that the 64-bit version is about twice as large. This distinction also holds for the installer files – they are named the same but the 32-bit version is approximately 29 MB, and the 64-bit version weighs in around 62 MB.

Excel cannot find OLAP cube Model

About a week ago I completed a small IoT project that takes data from several weather stations and pumps it into a number of sources, one of them being Power BI. Writing data directly to a Power BI data model through the API (which is what Azure Streaming Analytics does) introduces a number of idiosyncrasies into the mix, one of them being that the data can only be updated from the API. Apparently another is that you can’t use the Analyze in Excel feature with it. The error that you get when you try to do so is:

You also cannot use Analyze in Excel with any Direct Query data sources or on-premises SSAS through a gateway. The solution to this is apparently patience – it’s currently not supported. I can only presume that it is coming soon, but for now, you can stop beating your head against the wall, it won’t work.

The HTTP server returned the following error: Forbidden

This one sounds pretty ominous, doesn’t it? It also doesn’t make sense. You needed to be logged in in order to find the “Analyze in Excel” button in the first place, but this error is indicating that you don’t have access. This problem occurs when you have multiple AAD (Organizational) or Microsoft accounts, and you are maintaining a connection to one that does not have access to the data source. It’s particularly galling, and difficult to remedy if you have an AAD account and a Microsoft account that use exactly the same email address. I know, because I do. In order to fix this, you need to force the connection to logout, but the only way to do that currently is to modify the ODC file.

An ODC file is simply an XML file that can be edited with any text editor. Once open, you search for the connection string (<odc:ConnectionString> and add the desired user ID to it by adding “User ID=account” as in the example below:

Launching it after the edit will force the logout of the previous connection and you should be presented with a login screen for the correct one. Unfortunately, in the case like mine where the two accounts are named identically, this does not work. What you must do is to use a different account first. This will force the logout. Once that is done, you can add the correct account, or remove the User Id section altogether. Subsequent launched will force the login, where you can choose the correct login type, and the feature will work.

These few tips should help you get up and running with Analyze in Excel, and shortly I’ll be discussing the reasons that I think this feature is such a big deal.

SSRS 2016 – Integrated or Native Mode – Which one should you use?

The answer to the SQL Server Reporting Services Integrated vs. Native mode question used to be very simple. Once upon a time, if you had a SharePoint environment, you would want to deploy SharePoint Integrated mode, and if you didn’t, you would pick Native. Integrated mode would leverage your pre-existing security model in SharePoint, it would allow reports to look like documents in SharePoint making them more user friendly, and you would be able to use the advanced features of the SSRS web parts in SharePoint. Non-SharePoint users were able to do what they needed around security and report storage with Native mode. Everyone was happy.

SQL Server 2012 changed that a little bit. Power View reports were first introduced in SQL Server 2012 as a part of SSRS. These reports leveraged the tabular (PowerPivot) data models available in SSAS 2012 and provided some very user friendly tools for self service analytical reporting. However, one of the catches introduced was that Power View reports were only available in SharePoint Integrated mode. Suddenly, the choice of mode became feature based. This suited those with SharePoint environments just fine, but those without SharePoint would now need to stand up a SharePoint farm just to gain access to Power View. This is a daunting prospect, especially for those unfamiliar with SharePoint. This requirement, coupled with the minimal investment into new features for core SSRS in SQL Server 2012 had the effect of making the Native mode users feel abandoned. After all, we know what typically happens when Microsoft stops investing in a product. The balance was heavily tilted in the favour of Integrated mode.

The new normal

This situation remained exactly the same in SQL Server 2014, but has changed dramatically with SQL Server 2016. SSRS in SQL Server 2016 contains significant advancements, chief among them are a new HTML5 rendering engine, a new report portal, mobile reports, and (soon) Power BI Desktop rendering. This is fantastic news, but it also changes the game significantly with respect to the Integrated/Native mode decision. With SSRS 2016, most of the new investments are in Native mode only – the balance has shifted. The table below shows an (incomplete) list of new features, and their supported modes.

Feature Integrated Mode Native Mode
HTML 5 based rendering engine X X
New chart types X X
PDF based printing (no ActiveX) X X
PowerPoint rendering and export X X
New UI for Report Builder X X
Customizable parameters pane X
New web portal X
Mobile reports X
KPIs X
Pint to Power BI X
Render Power BI reports* X

* Coming soon

You can see above that the balance has shifted very heavily in favour of Native mode. The folks using Native mode are very happy about this move – they are no longer having SharePoint forced on them in order to access new features. However, now it’s the SharePoint folks turn to feel abandoned, but they really don’t need to. SSRS Integrated mode is still getting a significant enhancement in 2016, it’s just not as significant as the improvement to Native Mode. Integrated mode is also still required for rendering Power View reports. Last fall’s Reporting Roadmap reconfirmed Microsoft’s commitment to SharePoint as a platform -“We will continue to support embedding of BI content into SharePoint”. SharePoint has a bright future as an report destination. The only question is how that will be brought about.

It may well be that the features had to go into Native mode first in order to meet the shipping schedules, and that they’ll be brought along eventually. I suspect however that this is not the case. I think that this is either the last, or penultimate version of SSRS to contain Integrated mode. If the same level of embedding into SharePoint could be provided by Native mode, and the user experience improved (as it has been in the new report portal) then there is very little real need for Integrated mode at all.

Building shared service applications in SharePoint is a non-trivial task, and those resources could likely be better spent on features for SSRS. A new embedding model could support both SharePoint on-premises (as it currently does) and SharePoint Online (as it currently doesn’t). The same mechanism could be used to embed Power BI reports. We’ve already seen glimpses of this hybrid interoperability in the SSRS and Excel pin visual to Power BI capability. I suspect that over time we’ll see SSRS Native mode and its reporting portal also assume the role currently played by PerformancePoint Services as well. For all of these reasons, I think that SSRS Native mode is the only future for SSRS.

But that’s the future. What about the present?

When I first learned of these developments, I suspected that I would be recommending Native mode for anyone moving forward. However, as I discuss in an earlier article, the SSRS web parts for Native mode are deprecated, and missing key pieces of functionality, parameters being first among them. They are really little more than iframes, and they certainly can’t replace the Integrated mode web parts. If you’re going to use reporting in SharePoint in any meaningful way, or you are looking to upgrade an existing SharePoint farm with SSRS integration to 2016, you’re going to need Integrated mode. That means no mobile reports, report manager, or Power BI integration.

So why choose?

There is nothing stopping you (apart from possibly licensing) from running both modes. Using Integrated mode, you can take advantage of the new rendering engine, etc, and a separate Native mode server can be used for Report Manager, mobile reports, and Power BI integration. Over time, more reports can be brought over to Native mode and the embedding story improves. Once they are all brought over in “the future”, the Integrated mode service can be simply removed. This provides for a smooth, gradual migration. In fact, you can set up an SSRS 2016 Native mode server along side an existing SharePoint 2013 farm with SSRS 2014 or earlier Integrated mode to get started. Your SharePoint reports won’t have any of the new features, but your Native mode certainly will.

We are clearly in a transitional stage when it comes to on-premises reporting technologies from Microsoft. There are significant, bold steps forward, but there is also a legacy of technology to support. The current lineup of technologies allows for both approaches for organizations to embrace at their own pace.

Where we are with the BI Workload in SharePoint 2016 – BI Focal #4

This past week, on March 24 2016, My co-host Jason Himmelstein and I had the privilege of hosting Kay Unkroth and Gregory Appel from Microsoft on our monthly BI Focal web show. Kay and Gregory are both Senior Product Managers at Microsoft. Kay is responsible for both the PowerPivot for SharePoint and SQL Server Reporting Services service applications, and Gregory is responsible for Excel Online in Office Online Server (or as we’ve come to affectionately call it, Excel Online On-prem). We titled the show SELECT From On-Prem WHERE version=’2016′. The recent release of SharePoint 2016 RTM, and the imminent release of Office Online Server and SQL Server 2016 make this topic particularly relevant.

What transpired was a frank, hour long (almost) discussion on the current state of the On-premises BI story from Microsoft. We discussed a number of burning topics such as

  • The implications for the deprecation of Excel Services
  • The new focus on SSRS as a BI platform
  • How SharePoint fits in to the new Microsoft BI vision
  • The status of PerformancePoint and Visio Services
  • Where Power BI fits into the on-prem picture
  • The upgrade story for 2016 BI, and version dependencies

I had a great time participating in this discussion, which was jam packed with great information. The recording of it is now available from IT Unity, or below.

As always, Jason and I would like to thank IT Unity for all of the logistical information for the show, and our guests who graciously donated their time.