So folks might have noticed the Feb 1st update to stream analytics. There’s a fair bit of stuff in this release.. What I am going to focus on though is how stream analytics integrates into Power BI now.
Power BI has had the addition of Stream Data sets sometime after Azure Stream Analytics (ASA) integration. Folks who have worked with it might be aware that when you hook up ASA to Power BI it just creates a dataset and doesn’t create a stream dataset. When I first did this it jumped out as something that probably needed to be consolidated… and now it has.
So what does the implementation experience look like now… Well from the stream analytics nothing much appears to have changed. I have a test query that’s not doing much it just fires through some data. I’m actually firing json in through the event hub but will focus on the ASA PowerBI bit. The query is as follows:
WITH customers AS ( SELECT [customer].customerCode, [customer].firstname, [customer].lastname, [customer].addressline1, [customer].addressline2, [customer].addressline3, [customer].postcode, [customer].town, [customer].county, [customer].country, [customer].customerEmail, [transaction].transactionCode, [transaction].status, [transaction].[transactionTime], [transaction].[transactionAmount] FROM [evehub] ) SELECT * INTO [TestReport] FROM customers
When creating the output everything seems as it was before. My assumption was that something would’ve changed here and that I would have to create my streaming dataset end point definition first in Power BI and choose my data set here. But that’s not the case…
The subtle impact here is that it’s not like an API where I can just fire json at the secure end point. I still have to authorize with an account! This means you need to have a PowerBI account for your stream analytics service which in a production environment you probably won’t want that to be a named user account. Also, if the workspace needs to be shared then it will need to be a Pro account.
So far we’ve hooked up ASA to Power BI but we haven’t done anything with Power BI. Well it turns out we don’t have to manually define the end point like we do with API integration. When data starts streaming through the data set appears automatically in our Streaming Datasets within Power BI. So when I turn on my data stream using a C# app I get the following in PowerBI.
When I click the pencil to edit the data set I can see that it’s created all the attributes, typed them and defaulted history to on.
Now it seems we have some control of how these data sets are shaped in Power BI and we can flip the history on or off. We also have an end point that we can use with an api, cURL or PowerShell…
This means we might be able to stream in data to the data set using another api source not just stream analytics. Also it might seem I can do the following:
- add an attribute that’s not in my ASA stream
- remove an attribute that is in my ASA stream
Add an Attribute
I created a streaming tile just totting up transactions realtime.
When I add a new column the streaming tile still displays without issue. However when I go to create a new report or edit the existing report I used to pin to the dashboard from my dataset I start hitting issues. To see the new column in the dataset on the report editor I have to hit refresh. After doing this my data set and report is broken.
Editing the data set again, removing the column and hitting refresh doesn’t fix the issue. It seems I have to trash and recreate the data set.
How about ASA? Well ASA also had a problem. It didn’t stop the job but failed to send the transactions meaning the seemingly unaffected tile won’t update with any new data. Note the lack of column name in both the Power BI and ASA exception.
Remove an Attribute
What happens when we remove an attribute?
Well… the same thing as we might expect from trying to add a column.
This is a good change and consolidates the features of streaming data sets into Power BI for API’s and Stream Analytics. We gain some further control over datasets but it seems ASA is very tightly bound to the data set definition and in reality we can’t do much without breaking it.
It might mean that we can stream data in from another source not just ASA since we now have an endpoint we can use… I haven’t tried this yet.
All in all I’m still left a bit wanting in the mashing features for realtime data in Power BI. I’m sure it will get there… Microsoft are doing a great job… We are a tough bunch to please these days 🙂