In April 2015, we announced the availability of Azure Stream Analytics outputting data to Power BI. This feature allowed users to perform real-time stream analytics on data in motion, and then output the results to Power BI for visualization and analysis.
Three months ago, we announced public preview of streaming datasets in Power BI. At the time, these datasets could be created using streams of data from either the Power BI REST API or PubNub. Once the data streams were created, the feature set made it easier than ever to create real-time dashboards using streaming tiles optimized for showing real-time data.
Today, I am happy to announce the culmination of both these efforts: soon, Azure Stream Analytics jobs will be able to output to Power BI streaming datasets. In turn, you will be able to create streaming tiles based on Stream Analytics output. This new functionality will enable the top feature requests that we’ve received for Stream Analytics outputs, including:
- Showing the latest value from the Stream Analytics output
- Showing the values over a set time window
- Lower latency between when the data is pushed to Power BI and when the visuals update
Excited to jump in? This feature is available for private preview today! Simply email ASAtoPBIStreaming@microsoft.com with your Azure subscription ID and the region you’d like to enable for private preview, and we’ll see to the rest.
Note: in enabling private preview, Azure Stream Analytics can no longer guarantee production SLAs on that subscription, though of course our team will still be responsive to feedback, bug reports, etc. We therefore recommend enabling private preview on subscriptions/regions which are not critical for production purposes.
The following sections will detail how to get going with Azure Stream Analytics and Power BI in the private preview. If you’re curious as to what is possible with private preview, read ahead! We will set up an output from ASA to a Power BI streaming dataset, and then visualize that data in a Power BI dashboard. Here is a preview of the final product:
Setting up your Azure Stream Analytics job
The first step is to set up an Azure Stream Analytics job which outputs to Power BI.
To get a step-by-step walkthrough of setting up an Azure Stream Analytics job complete with test data, head to the real-time fraud detection tutorial. The tutorial will walk you through setting up a pipeline where sample telephone data is piped into an Azure Event Hub and from there is sent into Azure Stream Analytics for fraud detection. Of course, feel free to skip the final “create output sink” portion of the tutorial, since we’ll be creating a new output to point to Power BI instead.
The rest of this walkthrough assumes that you’ve completed said tutorial.
Next, we will make some modifications to the Azure Stream Analytics job in preparation for outputting to Power BI. Our end goal is to count the number of occurrences of fraud over five second windows, and output that to Power BI. Thus, every five seconds, we’ll want to output the following fields to Power BI:
- windowEnd: timestamp representing the end of the current five second time window
- fraudulentCalls: the number of fraudulent calls detected over that time window
First, we’ll edit the ASA query to match that schema above. Go to your Azure Stream Analytics job, and click on the Query box:
Replace the existing query with the following. Note that you may have to edit your input name, depending on what you named your Event Hub input.
/* Our criteria for fraud:
Calls made from the same caller to two phone switches in different locations (e.g. Australia and Europe) within 5 seconds) */
SELECT System.Timestamp AS WindowEnd, COUNT(*) AS FraudulentCalls
FROM "callStream" CS1 TIMESTAMP BY CallRecTime
JOIN "callStream" CS2 TIMESTAMP BY CallRecTime
/* Where the caller is the same, as indicated by IMSI (International Mobile Subscriber Identity) */
ON CS1.CallingIMSI = CS2.CallingIMSI
/* ...and date between CS1 and CS2 is between 1 and 5 seconds */
AND DATEDIFF(ss, CS1, CS2) BETWEEN 1 AND 5
/* Where the switch location is different */
WHERE CS1.SwitchNum != CS2.SwitchNum
GROUP BY TumblingWindow(Duration(second, 1))
Note that this query is very similar to the one created in the fraud detection tutorial. It only diverges to massage the fraud data into the schema detailed above.
Now, we will create an output to send the data above to Power BI. Back in the Azure Stream Analytics Overview blade, click on the Outputs box.
From there, the follow these steps to create your Power BI output:
- In the Outputs blade, click the “Add” button.
- In Output alias, give the output a name – e.g. “output-powerbi”
- In Sink, click the dropdown and select “Power BI”
- Click the Authorize button, and sign into the account that you use with Power BI
- Once authorized, select the Power BI workspace, dataset name and table name for the output that will be created
Note: if you’re an Azure Stream Analytics-Power BI veteran, you might notice that this process is no different from before private preview. That is indeed correct. Creating an streaming Power BI output from Azure Stream Analytics requires no changes in workflow from before – we’ve handled it all underneath the covers.
The end product should look something like this:
You are now ready to start your ASA job. Go back the Overview tab of your Azure Stream Analytics, and start the job.
Setting up your Power BI dashboards
Now, we will jump to Power BI in order to set up a real-time dashboard based on the streaming dataset from Power BI. We will walk through two scenarios, both of which were highly requested feature asks with our original ASA to Power BI integration: 1) showing the latest value and 2) showing values on a line chart over a time window.
Showing latest value
Showing the latest value received from the data stream is much easier using streaming datasets.
- Go to the dashboard where you’d like to see latest value
- Go to the Add tile button in the top right
- Select Custom Streaming Data, then Next
- In the following list, select the dataset that was created from ASA, then Next
- Make sure Visualization Type is set to Card
- Under Fields, select “Add Value” and set the value to fraudulentCalls
- Click Next, then Apply
And voila! You should see a card with the latest fraudulentCalls value in your dashboard.
Showing values over a time window
Follow the steps below to show your data on a line chart over a particular time window.
- Go to the dashboard where you’d like to see latest value
- Go to the Add tile button in the top right
- Select Custom Streaming Data, then Next
- In the following list, select the dataset that was created from ASA, then Next
- Make sure Visualization Type is set to Line chart
- Under Axis, select “Add Value” and set the value to windowEnd
- Under Values, select “Add Value” and set the value to fraudulentCalls
- Under “Time window to display” select the time window you’d like to use for the line chart
- Click Next, then Apply
Your line chart should now appear in the dashboard, with the requested time window.
Creating reports using the streaming dataset
As was the case before private preview, you can still create reports using the dataset created by Azure Stream Analytics. Once those report visuals are pinned to a dashboard, they will also update in real-time. Follow the steps below to get started with creating a report against the streaming dataset.
- Go to Streaming datasets in left navigation bar
- In the streaming datasets view, click on the “Create report” button under the Actions column, pictured below
You will be directed to the report creation page, from where you can begin creating reports against the streaming dataset.
Frequently Asked Questions
When Private Preview is enabled, will existing ASA jobs switch to creating streaming datasets?
Yes. However, if you are using an existing job, you will still have to create a new Power BI output in order to see the new functionality.
I’m already outputting from ASA to Power BI – will this affect any existing functionality?
The new streaming functionality is purely additive. Private preview will not affect any existing features or functionality with respect to Azure Stream Analytics and Power BI integration.
When will this functionality be ready for GA?
We are targeting Q1 CY2017.
Will there be any differences in functionality between Private Preview and GA?
No differences are currently planned, though that is subject to change as private preview progresses.
Are the existing Power BI throughput limitations still in place?
Yes. Power BI has limits to how fast data can be sent in from Azure Stream Analytics, as documented here. In addition, streaming tiles have a stricter limit. Packets larger than 10 kb will not populate streaming tiles, though tiles pinned to dashboards from reports will still update in real-time.
Where do I send feedback on private preview?
Please e-mail ASAtoPBIStreaming@microsoft.com with your feedback!
Next steps and further reading
- To sign up for Private Preview: e-mail ASAtoPBIStreaming@microsoft.com with the Azure subscription ID and region that you’d like to enable
- Blog post: public preview of Power BI streaming feature set
- Blog post: Azure Stream analytics output to Power BI
- Azure Stream Analytics Fraud detection tutorial