PowerBI: Integration with Azure, Googles and other Web Services using JavaScripts

P1

I’m my opinion, there’s no doubt Tableau is still the best and well ahead of everyone when it comes to visualization in terms of looks, functionality and ease. But that’s true only for visualisation, on the client side and as we know, there’s a lot more to BI than this.

For me, a critical component is integration, the ability to integrate disparate data points and consume data anywhere, in any shape, form or size and it is here where PowerBI is in a league of it’s own. Ignoring the things still in the pipelines (and there’s a fair bit still under constructions according to the docos), there’s already a host of cool offerings presently available.

As a developer, there are many things to like but my favorite for now has to be something that looks deceptively simple-JavaScript (and CSS/HTML) integration. I say simple simply because there’s no manuals to read (I still can’t find the supporting docos for it) and nothing to install or configure. And never mind the Models, Views and Controllers, you won’t have a need for any of that (though an understanding will certainly help if you want to do the cool stuff). Just open up a tile and start coding as you would with any web page (client side-on the dashboard). Simple yes but by virtue of doing this, it has allowed you to integrate and consume a whole host of APIs and other data streaming and analytics services that for now, would not otherwise be possible or as direct. This includes things like Azure Functions, REST APIs and other services like Googles Analytics for real time analytics- your BI dashboard has effectively become a web page and you now have BI visualization side by side with stunning web based (JavaScript) visualization. For all the benefit like speed and ease that comes with BI, there are still many things it can’t and will never be able to perform which only JavaScript programming can. Through integration, PowerBI has effectively allowed you to have both on the one platform/interface without the associated overheads.

Contrast this with the alternative approach to Javascript integration as taken by Tableau;-useful as it is, it’s not the same thing, you still need a separate web server, even for the simplest of things. PowerBI also offer something similar in the form of ‘Embed’ and ‘Publish to Web’. The approaches here is, create your chart/viz in your BI tools then embed these into your  website by placing a small system generated piece of code into your pages. You can see a live example here-I simply created the charts in PowerBI and ‘embedded’ these into my website/blog. Here, I’m tracking visitors to my blog via Google Analytics. The thing to note is the data is not live and needs to be refreshed by PowerBI.

But to assess how useful something like this really is, you really need to test it. Given integration wasn’t exactly well documented or just between different systems but also platforms, such as PowerBI to Googles, CDNs etc , I have to admit I had reservations at the outset. But as it turned out, with a little tweaking and applying the same principals you would for general web development, it seems there’s not much that it can’t do that you can with web development. I only thing I didn’t like about it is that it is only available on PowerBI Server and not desktop and the surface area for the embedded code was way too small.

What follows is a brief summary of what was tested and the issues you’re likely to face as well as the way to handle them. It is not intended as step by step manual but it is assumed you already have working knowledge of these technologies if you want to follow along. To get started, simply add a tile of the web content type to your dashboard.

PowerBI  Web Content

Once you do that, a blade opens up. Simply paste your code into the box for that and hit refresh. That’s it.

Power BI Web Content

Third Party iFrames and PlugIns

If you’ve ever own a blog or website, you’ll know how pervasive these things are and why not. For a tiny price, it allows anyone with next to zero skills in web development to add some really stunning, sometimes mind blowing features to their blogs/websites by simply adding a line or two of codes, such as this below.

<script src=”//www.powr.io/powr.js?external-type=html”></script>
<div class=”powr-microblog” id=”42d4f7ad_1492065619″></div>

In my test, I tried a few of my favourites providers like POWr, no issues here. Even for professional developers, it can save you a lot of time.

Power BI Integration

 

JavaScripts, Third Party Providers and CDNs

As with everything, you can create your own charts using JavaScript from scratch or use ones created by a third party providers, often for a price in a commercial environment. I won’t go through all the test, but for my favourites like Googles, Highcharts and even D3, there weren’t any issues. The only requirement is external sources like CDNs have to be secured (HTTPS). When it’s not possible, simply embed it with the rest of the code.

For the Googles Charts such as this Candle Stick below, coding is fairly straight forward. You can just paste this into your Dashboard and see the results yourself. The data here is hard-coded to facilitate that, rather than what you would do in real life and have this fetched from an API. No issues here (I’m only testing the ability to access the CDNs here).

Google Charts in Power BI

On PowerBI, the code above will produce something like this below.

Google Charts in Power BI

Next up, I used my one of my favourites from Highcharts. I won’t show the complete code as there’s too much of that but it’s worth noting as it will not be immediately obvious here just by looking at it, that some of these charts are much better than anything you’ll find on either Tableau or PowerBI. I encourage you to see the live demos on their site for a feel of what I mean.

HighCharts with Power BI

D3, they’re now so popular they’re even available in the Power BI Visuals Gallery The difference is the ones from the gallery are supposed to make things more simple. I’m just not sure I agree and prefer to completely code it from scratch. Visit GitHub for more information, the one below is just a simple one I tested (you can’t see the animated one from here anyway).

D3 with PowerBI

REST APIs and Websites

The great thing about this is it works with the endpoints of any web API including both dedicated and, the Controllers of your web application, whether they’re on-prem or in the clouds. This means the data for your websites can also be brought in and reused by your Dashboards. The only requirement is CORS needs to be enabled, simply changing JSON to JSONP will not work as many have suggested. The trick here is to enable CORS and add the PowerBI domain to it within the origins of your controllers. During development, I used wildcard for greater flexibility for Visual Studio Code but in Production, you would obviously limit this to just the PowerBI domains (unless if you want people to access your data:-)).

[EnableCors(origins: “*”, headers: “*”, methods: “*”)]

Below, I’m just reusing the JSON currently consumed by one of the pages on my other website-the AJAX just calls that API and renders the result with scripts from Highcharts, all within PowerBI.

piejs2

In PowerBI, it will produce something like his below.

HighCharts with Power BI

 

Azure Functions and Micros-Services.

Azure Function , marketed as event driven serverless computing, these popular Azure Apps are  flexible, lightweight, easy to maintain and monitor and you don’t even need an IDE like Visual Studio to develop or test, it can be done completely in your browsers as seen below. What I like about these services is they are easier to maintain than the traditional ASP APIs.

fnc

For PowerBI integration, it works exactly the same as your other APIs and again enabling CORS is the key. The difference is, CORS is controlled within Azure and not in your code. It can be found under Platform Features of your functions.

corsazure

The values to add for the PowerBI domains can be seen below.

CORS in Azure Functions

One thing I experienced from this is what works on your PC and even on other Azure services may not necessarily work with Azure Functions. One of the obstacles I hit was the .P12 files for my Googles authentication did not work for Azure Function though fine on both my PC and other Azure web apps. If you stumble on this, just replace it with the .JSON alternative (they did warn us .P12 was being deprecated)

Real Time Streaming with Google Analytics

And finally, something completely truly serverless and in real time. The difference between services consumed in this manner and the other approaches earlier, including Azure Functions is you only need to tier

manage the Presentation Layer;  the Application and Data layers are handled by your service provider, in this case, Googles.

For the Azure Functions earlier, there was really only two layers (I used C# to ‘GET’ and reshape the data instead of completely using JavaScripts) as I did not have a need to persist the data from Googles back into a database, though that’s certainly was an option. As data is pulled directly from Googles, everything is in real time. The query/code to produce the graph below is available in the Googles Analytics Query Explorer, the only thing needed is your account details.

Google Analytics with PowerBI

Like all the other approaches, the only requirement to get this to work is CORS need to be enabled for the PowerBI domain. You can do this within the Google Developer Console.

Google API CORS

So there you have it, another reason why I love working on the Microsoft stack.  Stay tuned as there’s a lot more coming from the Microsoft Team.

 

 

 

 

 

 


Leave a Reply