Use a Power BI Tile in your web page

EDIT 2016-07-12: Microsoft has changed their API and policies and functionality, so this artcile is obsolete now. I will shut down the sample site. For details, please see: https://powerbi.microsoft.com/en-us/blog/what-s-new-and-what-s-next-for-power-bi-embedded-july-2016/

 

Creating dashboards is more witchcraft than art and more art than science. Think about how many times you have banged your head to produce a useful chart according to the specs of your client. You have the numbers, you have the chart and everything in place but the customer keeps saying: “it’s just not the right shade of green…”

Many times we have to create custom web pages to accomplish what the BI solution can’t offer out of the box; and Power BI provides you just that, the ability to use a tile from a dashboard without forcing the customer to open the entire Power BI user interface. With this technique you can embed just a piece of a bigger BI report into an HTML page. It’s not very straightforward (at least for me it wasn’t), and I must say that the documentation often makes it worse (there’s an errata in the GET REST Uri of the Step 3 – Get user’s tile information).

So you want to embed a Power BI Tile into a web page: to quickly see how it works go to this example page. If you want more details, just read on.
I assume you already have a Power BI dashboard and you know how to get an access token. This is probably the most difficult part and I covered that in another article.

I am using cURL from a headless EC2 server just for sake of simplicity, you can do this with your favorite programming language.

First step is to get the ID of your dashboard:

[code]

curl -k -X GET "https://api.powerbi.com/beta/myorg/dashboards" -H "$AUTH_HEADER_LOCAL"

[/code]

where $AUTH_HEADER_LOCAL is the “Authorization: Bearer eyJ0eXAiOiJKV1QiLCJhbGciOiJSUzI1NiI……..” you created when asking for an access token.

The server will respond something like this:
recorte

From here you need to extract the id element which represents your dashboard.

Second step is to get the tile ID:

[code]

curl -k -X GET https://api.powerbi.com/beta/myorg/dashboards/${DASHBOARD_ID}/tiles -H "$AUTH_HEADER_LOCAL"

[/code]

where $AUTH_HEADER_LOCAL is the same as above and ${DASHBOARD_ID} is the value you got in step 1.

recorte
the answer will include an element named “embedUrl” which is the link to your tile.

Now that you have the embedUrl and the access token, you can build a simple HTML page:

  • Create an <iframe> element that will host your tile
  • Have your access token ready into a variable, I personally have it in an external .js file that I load as a <script> inside the page
  • Add a <script> element to activate the <iframe> with the access token and the dimensions of the tile

[code language=”javascript”]
<script type="text/javascript">
var width = 800;
var height = 600;
var embedTileUrl = "https://app.powerbi.com/embed?dashboardId=1d555f40-…..2618e&tileId=8cd8bc5….7624a";

window.onload = function () {
updateEmbedTile();
};

function updateEmbedTile() {
if ("" === embedTileUrl)
return;
iframe = document.getElementById(‘iFrameEmbedTile’);
iframe.src = embedTileUrl + "&width=" + width + "&height=" + height;
iframe.onload = postActionLoadTile;
}

function postActionLoadTile() {
if ("" === accessToken)
return;
var h = height;
var w = width;
var m = { action: "loadTile", accessToken: accessToken, height: h, width: w };
message = JSON.stringify(m);
iframe = document.getElementById(‘iFrameEmbedTile’);
iframe.contentWindow.postMessage(message, "*");
}

</script>
[/code]

Note that the access token will expire after 1 hour, so you need to have a way to refresh it on the server side.

Feel free to use http://powerbitile.azurewebsites.net/ as a reference.

Pin an Excel chart/pivot table to a Power BI dashboard

According to this post on the official Power BI blog it is now possible to bring your Excel file into a Power BI dataset as a whole (not only importing data); and use portions of it as tiles on your dashboard.

Wow: you can (re)use your carefully crafted charts and amazingly engineered pivot tables in a web-shareable software as a service, not bad.

This is a very interesting feature and adds a range of new possibilities for this recent Business Intelligence Saas.

Now, few considerations.

  • To accomplish this you need to link an Excel file from OneDrive and the pin-to-the-dashboard option is only available to OneDrive for Business subscribers. So no free beers, sorry.
  • To pin a chart you actually need to select a range of cells where the chart resides and it will be pinned as an image, much like a print screen, so do not dream of interaction with the rest of the selectors/filters on the dashboard. See what I mean in this picture:
    recorte
  • Your chart/table/range will appear in the dashboard as a <div> with a base-64 encoded data:image/png background:
    recorte

I did not try to embed this tile in a web page, but it could be the next logical step to do.

So, overall, a very desirable addition. Prices for OneDrive for Business start at 5USD per user/month, and if you are a no-profit/government/university you can have it even chaper.

 

Twitter Trending Topics by Location, Date (PowerBi WordCloud)

Twitter Trending Topics by Date, Location

One of the things I like the most about Power BI is the ability to use custom visuals. I did not try building one myself and honestly I think it’s beyond my skills, yet there is a gallery with a growing number of pre-build viz that you can download and use.

Go to https://app.powerbi.com/visuals?WT.mc_id=Blog_Visuals and take a look.

I downloaded the WordCloud plus the Chiclet Slicer to create a report showing the trending topics in Twitter sliced by date and location.

Only few locations are available from Twitter API, here is a sample of spanish cities I am monitoring.

Happy tweets!