Embed multiple Power BI tiles into your Web

EDIT 2016-07-12: Microsoft has changed their API and policies and functionality, so this article 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/

Code re-usability is at the core of programming best practices and Business Intelligence is no exception.

None wants to code multiple times just to refactor thousands of lines of similar code whenever there’s a change in business rules.

In my previous post Use a Power BI Tile in your web page we saw a technique that allows us to use a Power BI tile into a custom web page. More often than not, our HTML documents are composed by more than one grid or chart, so we need a flexible way to be able to insert multiple tiles on the same page without rewriting the same JavaScript procedures over and over.

I reworked the previous example to make it easier and created this simple function:

[code language=”javascript”]
<script type="text/javascript">
function EmbedTile(sTileUrl, sFrameName, iWidth, iHeight) {
var iframe = document.getElementById(sFrameName);
iframe.src = sTileUrl + "&width=" + iWidth + "&height=" + iHeight;
iframe.onload = function () {
var m = { action: "loadTile", accessToken: accessToken, height: iHeight, width: iWidth };
message = JSON.stringify(m);
iframe.contentWindow.postMessage(message, "*");
}
}
window.onload = function(){
EmbedTile(
‘https://app.powerbi.com/embed?dashboardId=9153d5…..365207&tileId=2aef8……913edc524353’,
‘iFrameEmbedTile1’,
550,
350);
EmbedTile(
‘https://app.powerbi.com/embed?dashboardId=9153d5……365207&tileId=c69f153c…….9336bb’,
‘iFrameEmbedTile2’,
550,
350);
};
</script>

[/code]

Feel free to reuse it or see it in action here: Two tiles in the same page

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.

Microsoft Azure SQL Data Warehouse. How much does it cost?

As many of you know, Microsoft has open to public preview its latest BI technology: Azure SQL Data Warehouse.
On the documentation page is defined as: “an enterprise-class distributed database capable of processing petabyte volumes of relational and non-relational data.”
How much does it cost, is it worth it, and how does it differ from previous SQL offering on Azure?
The main difference -in my opinion- is the billing of compute power and storage:
in Azure SQL DW you decide how fast your query run and how big your data is. This generates a charge for DWU (Data Warehouse Unit), a unit of measure defined as the level of performance of your data warehouse; and a charge for RA-GRS Page Blobs (Read-Access Geographically Redundant Storage).
I suppose this will cause more than a headache in financial departments and SQL administrators, but it is indeed a good thing.

The main point I see here is that -wisely used- the DWU pricing can help tune the database responsiveness and number-crunching speed according to the time of the day (or day of week). I can set for example 100DWU on low traffic hours or weekends, and scale up to 2000DWU Monday morning or during the reporting season when everybody want to access data. I can even choose to pause the database -for example during holidays- and incur in no compute  charge at all. It would be extremely useful to have PowerShell scripts to switch from a DWU level to another and schedule them so that the database can auto-adjusts the compute power needed.

Doing a very simple math: assuming a 30 days month, at 100DWU we will receive a $504 (USD) bill for computing + $62.5 (USD) for 1TB of data, a total $566.5* for a fully functioning SQL Data Warehouse. On the other end of the spectrum, 2000DWU for 1 month would be $10,080(USD)*.

Additionally to the Grow, shrink, or pause capabilities, Azure SQL Data Warehouse features an MPP architecture, column-store indexes, and the ability to query relational and non relational (read Hadoop) with Polybase, more on this here.

So my .02: let’s give it a try…

*During the preview period the prices are discounted, I do not expect them to vary that much anyway, but certainly at the moment it is not clear how much the discount is and how much will the price be after the preview, see here for more details: https://azure.microsoft.com/en-us/pricing/details/sql-data-warehouse/

There OAuth to be a better way (Power BI)

Edit: The base URL has changed from https://api.powerbi.com/beta/myorg to https://api.powerbi.com/v1.0/myorg

OAuth is clearly becoming the de-facto standard for authenticating API calls around the web. In the business intelligence arena, while we can discuss its pros and cons, we undoubtedly must get acquainted with it because sooner or later we’ll be tasked with importing data from one of the many “OAuth protected” web services.

In this third release of the OAuth series I’ll show how to get an Access Token from Microsoft Power BI. The same procedure can be used for many other Azure services (by changing the appropriate scope in the resource parameter).

Our typical scenario would be an unattended server process downloading data; I’m using a headless Linux box with cURL and jq.

You can see here and here my previous posts about how to use cURL to authenticate with flickr or BigQuery and make API calls with an Access Token.

The way Azure works is a little different. You get an Access Token valid for 1 hour and a Refresh Token. You can reuse the Access Token for as many calls as you want during the hour, and then you’ll need to ask for a new Access Token presenting the Refresh Token.

Prerequisites:

  • An Azure subscription with a real work domain (no personal account)

loginazure

  • An Azure Active Directory so you can add users to @yourdomain

azuread

  • A Power BI subscription with a user belonging to your Azure Active Directory

azure users

Once you have the requisites in place, follow this article to create an app and get a Client ID.

clientid

With the username, password and the Client ID you can use this script to get an Access Token:

[code language=”bash” gutter=”true” light=”false”]
#!/bin/bash

OAUTH_CLIENT_ID="yourClientId"
OAUTH_USERNAME="youruser@yourdomain.com"
OAUTH_PASSWORD="yourpassword"

POST_RESULT="$(curl -s -X POST -d "resource=https://analysis.windows.net/powerbi/api&client_id="$OAUTH_CLIENT_ID"&grant_type=password&username="$OAUTH_USERNAME"&password="$OAUTH_PASSWORD"&scope=openid" "https://login.windows.net/common/oauth2/token" | jq -r .)"

REFRESH_TOKEN="$(echo ${POST_RESULT} | /usr/local/bin/jq -r .refresh_token)"
ACCESS_TOKEN="$(echo ${POST_RESULT} | /usr/local/bin/jq -r .access_token)"
AUTH_HEADER="Authorization: Bearer ${ACCESS_TOKEN}"
echo "${AUTH_HEADER}"

echo "${AUTH_HEADER}" &gt; ./auth_header.txt
echo "${REFRESH_TOKEN}" &gt; ./refresh_token.txt

[/code]

This script will save two files: one is the Authorization Header and the other is the Refresh Token. You will use the Authorization Header passing it to every API call that you make (during 1 hour), for example to get a list of the available datasets in your Power BI storage use:

[code language=”bash” gutter=”true” light=”false”]
#!/bin/bash

AUTH_HEADER=$(&lt;./auth_header.txt)
curl -k -s "https://api.powerbi.com/beta/myorg/datasets" -H "$AUTH_HEADER" | /usr/local/bin/jq -r .

[/code]

After an hour or so, you will ask for a new Access Token and store the new Authorization Header (can also be crontabbed every nn minutes):

[code language=”bash” gutter=”true” light=”false”]
#!/bin/bash

REFRESH_TOKEN=$(&lt;./refresh_token.txt)
OAUTH_CLIENT_ID="yourClientId"
OAUTH_USERNAME="youruser@yourdomain.com"
OAUTH_PASSWORD="yourpassword"

POST_RESULT="$(curl -k -s -X POST -d "resource=https://analysis.windows.net/powerbi/api&client_id="$OAUTH_CLIENT_ID"&grant_type=refresh_token&username="$OAUTH_USERNAME"&password="$OAUTH_PASSWORD"&scope=openid&refresh_token=${REFRESH_TOKEN}" "https://login.windows.net/common/oauth2/token" | jq -r .)"

REFRESH_TOKEN="$(echo ${POST_RESULT} | /usr/local/bin/jq -r .refresh_token)"
ACCESS_TOKEN="$(echo ${POST_RESULT} | /usr/local/bin/jq -r .access_token)"

AUTH_HEADER="Authorization: Bearer ${ACCESS_TOKEN}"

echo "${AUTH_HEADER}"
echo "${AUTH_HEADER}" &gt; ./auth_header.txt
echo "${REFRESH_TOKEN}" &gt; ./refresh_token.txt

[/code]

So, no browser, no GUI, no problem!