Filter a Power BI tile on 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/

======================================================================================================================

I received a lot of feedback regarding my two recent posts: http://moraschi.com/2015/12/08/embed-multiple-power-bi-tiles-into-your-web/, and http://moraschi.com/2015/11/30/use-a-power-bi-tile-in-your-web-page/

So I decided to continue in my exploration of the Power BI API. The next natural step in creating customized dashboards is the ability to dynamically filter the tiles based on user interaction. As of today, this is something you cannot achieve in https://app.powerbi.com (you can only filter using selectors in Reports).
There is a post here, with very scarce information about a parameter of the embed API, namely $filter. We can use this parameter much like its oData cousin, with the same syntax.

&$filter={tablename/fieldname}+eq+'{literal constant}’

I tested it for string parameters and it works. Did not have such luck with dates. Everything will eventually come into place, as this API is still under development as I understand.

I created a sample web page trying to keep it very simple, to demonstrate how this works. You can find it here and you are welcome to help yourself reusing my code and adapting it to your needs. In the example you will see a list box, clicking on it will trigger a refresh of the tile with the appropriate results.

Happy coding!

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.

Some like it OAth (BigQuery)

Second issue of the series about OAuth (you can see the previous here). I’ll try to explain how to use the Google OAuth 2.0 mechanism to authorize a server side scripts without the need of the user’s credentials, in order to automate the INSERT or SELECT (or WHATEVER) processes.

I am testing this with BigQuery service, so in another post I’ll be able to get data from the sample datasets provided by Google.

Prerequisites:

First we need to enable BigQuery for our project (go create one if you don’t have one):

  1. enable google api
  2.  bigquery api

When we click on the Enabled APIs tab, we should see it listed:

bigquery api enabled

Go to APIs & auth / Credentials section on the left and Create new Client ID with the Service account option:

service account

Your browser will download a json file, that you should copy it in a safe place, but we’re not going to use it.

email address

note down the Email address that was generated for your Client ID.

You’ll need to generate also a P12 file, by clicking on the Generate new P12 key button, and download it:

p12 key

notice the pass-phrase for the P12 file (by default is notasecret).

Suppose the downloaded P12 file is named tutorial.p12. We will convert this P12 file to a PEM by issuing this command in a terminal console:

[code language=”bash” gutter=”true” light=”false”]
openssl pkcs12 -passin pass:notasecret -in tutorial.p12 -nocerts -nodes -out tutorial.pem
[/code]

this will remove the pass-phrase from the P12 file and generate a tutorial.pem that we can use with openssl.

Next step is to get an Access Token from Google with a JSON Web Token (JWT): we create the JWT with the Email address above plus some boilerplate constants. I’m not going into details on how the JWT is generated, but it is essentially a base64 encoded string composed by a header, a claim set and a signature; the signature is a little more tricky as it is calculated using SHA-256 hashing algorithm and has the characters forward slash [/] and underscore [_] substituted respectively with plus [+] and minus [-] signs. See documentation here. The JWT is finally sent via a HTTP POST call to https://www.googleapis.com/oauth2/v3/token.

here is the complete bash script, please change the EMAIL_ADDRESS variable with the appropriate value:

[code language=”bash” gutter=”true” light=”false”]

#!/bin/bash

JWT_HEADER="$(echo -n ‘{"alg":"RS256","typ":"JWT"}’ | /usr/bin/openssl base64 -A -e)"
#echo JWT_HEADER=$JWT_HEADER

EMAIL_ADDRESS="11327003054-us……………..g@developer.gserviceaccount.com"

JWT_CLAIM_SET="$(echo -n "{"iss":"$EMAIL_ADDRESS","scope":"https://www.googleapis.com/auth/bigquery.readonly","aud":"https://www.googleapis.com/oauth2/v3/token","exp":"$(($(date +%s)+3600))","iat":"$(date +%s)"}" | /usr/bin/openssl base64 -A -e  | /bin/sed ‘s/=//g’)"
#echo JWT_CLAIM_SET=$JWT_CLAIM_SET

JWT_SIGNATURE_INPUT=$JWT_HEADER.$JWT_CLAIM_SET
#echo JWT_SIGNATURE_INPUT=$JWT_SIGNATURE_INPUT

JWT_SIGNATURE="$(echo -n $JWT_SIGNATURE_INPUT | /usr/bin/openssl sha -sha256 -sign tutorial.pem | /usr/bin/openssl base64 -A -e | /bin/sed ‘s/=//g’ | /usr/bin/tr ‘/+’ ‘_-‘)"
#echo JWT_SIGNATURE=$JWT_SIGNATURE

JWT=$JWT_HEADER.$JWT_CLAIM_SET.$JWT_SIGNATURE
#echo JWT=$JWT

/usr/bin/curl -s -d "grant_type=urn:ietf:params:oauth:grant-type:jwt-bearer&assertion="$JWT -X POST "https://www.googleapis.com/oauth2/v3/token" | /usr/bin/jq -r .access_token

[/code]

this script will output the Access Token retrieved from Google. The Access token is valid for 1 hour, you can store it in a text file and reuse for your API calls until it expires, then redo from start.

# standing on the shoulders of giants: thanks to http://superuser.com/questions/606953/bash-oauth-2-0-jwt-script-for-server-to-google-server-applications

No One Here Gets OAuth Alive (flickr)

I love web services, I love API, I can’t say the same for OAuth!

It’s frustrating at times, when you’re not using any of the existing libraries to get the entire flow running.

If you have a headless server, with a terminal console and limited programming languages at hand, one of the best options you have is cURL.

There’s a lot of documentation out there about this tool, and the official manual is more than enough to start with.

I’ll try to guide you through the Kafkaesque OAuth process using a web service API that still uses the 1.0 revision of OAuth: flickr

To follow the steps all you need is cURL, openssl and sed. I am using Amazon Linux on EC2. I assume you already have a flickr account.

1. Get an API Key

api_key

2. Get a Request Token:

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

timestamp="$(/bin/date +%s)"
nonce="$(/bin/date +%s%T%N | /usr/bin/openssl base64 | /bin/sed -e s’/[+=/]//g’)"

request_url="GET&https%3A%2F%2Fwww.flickr.com%2Fservices%2Foauth%2Frequest_token"

consumerkey=b284fdada……f2fbbe1145
consumersecret=17a……..c36

basestring="$(echo ${request_url}’&oauth_callback%3Doob%26oauth_consumer_key%3D’${consumerkey}’%26oauth_nonce%3D’${nonce}’%26oauth_signature_method%3DHMAC-SHA1%26oauth_timestamp%3D’${timestamp}’%26oauth_version%3D1.0′)"
echo ‘basestring=’${basestring}

signature="$(echo -n ${basestring} | openssl dgst -sha1 -hmac ${consumersecret}’&’ -binary | /usr/bin/openssl base64 | /bin/sed -e s’/+/%2B/’ -e s’///%2F/’ -e s’/=/%3D/’)"
echo ‘signature=’${signature}

request_output="$(/usr/bin/curl –compressed -s -H "Accept-Encoding: gzip,deflate" "https://www.flickr.com/services/oauth/request_token?oauth_callback=oob&oauth_consumer_key="${consumerkey}"&oauth_nonce="${nonce}"&oauth_signature="${signature}"&oauth_signature_method=HMAC-SHA1&oauth_timestamp="${timestamp}"&oauth_version=1.0")"
# echo ‘request_output = ‘"${request_output}"
echo ${request_output} | awk -F'[;&]’ ‘{print $1}’
oauth_token="$(echo ${request_output} | awk -F'[;&]’ ‘{print $2}’)"
oauth_token_secret="$(echo ${request_output} | awk -F'[;&]’ ‘{print $3}’)"

echo ${oauth_token}
echo ${oauth_token_secret}
echo ”
echo "Please ask your user to open this url: ‘https://www.flickr.com/services/oauth/authorize?"${oauth_token}"’"

[/code]

3. Get User Authorization:

From step 2 you need to note down the oauth_token and oauth_token_secret, you’ll need them later. Open in a browser window the url resulting from the step 2. Once you authorize it, you’ll get a code, this is the oauth_verifier:

verifier

4. Get an Access Token:

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

timestamp="$(/bin/date +%s)"
nonce="$(/bin/date +%s%T%N | /usr/bin/openssl base64 | /bin/sed -e s’/[+=/]//g’)"

request_url="GET&https%3A%2F%2Fwww.flickr.com%2Fservices%2Foauth%2Faccess_token"

consumerkey=b284fdada……f2fbbe1145
consumersecret=17a……..c36
oauth_token=72157651980726063-9ee23f2f77cc1edb
oauth_token_secret=f448f55629f2092e
oauth_verifier=518-883-896

basestring="$(echo ${request_url}’&oauth_consumer_key%3D’${consumerkey}’%26oauth_nonce%3D’${nonce}’%26oauth_signature_method%3DHMAC-SHA1%26oauth_timestamp%3D’${timestamp}’%26oauth_token%3D’${oauth_token}’%26oauth_verifier%3D’${oauth_verifier}’%26oauth_version%3D1.0′)"
echo ‘basestring = ‘${basestring}

signature="$(echo -n ${basestring} | openssl dgst -sha1 -hmac ${consumersecret}’&’${oauth_token_secret} -binary | /usr/bin/openssl base64 | /bin/sed -e s’/+/%2B/’ -e s’///%2F/’ -e s’/=/%3D/’)"
echo ‘signature = ‘${signature}

request_output="$(/usr/bin/curl –compressed -s -H "Accept-Encoding: gzip,deflate" "https://www.flickr.com/services/oauth/access_token?oauth_consumer_key="${consumerkey}"&oauth_nonce="${nonce}"&oauth_signature="${signature}"&oauth_signature_method=HMAC-SHA1&oauth_timestamp="${timestamp}"&oauth_token="${oauth_token}"&oauth_verifier="${oauth_verifier}"&oauth_version=1.0")"
echo ‘request_output = ‘"${request_output}"

oauth_token="$(echo ${request_output} | awk -F'[;&]’ ‘{print $2}’)"
oauth_token_secret="$(echo ${request_output} | awk -F'[;&]’ ‘{print $3}’)"

echo ${oauth_token}
echo ${oauth_token_secret}

[/code]

5. Use the Access Token with API calls:

According to the OAuth 1.0 spec the Access Token should never expire. So you only need to do the steps 1-4 one time.

With every API call you should use the oauth_token you received in step 4, in this example I’m using the flickr.test.login

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

timestamp="$(/bin/date +%s)"
nonce="$(/bin/date +%s%T%N | /usr/bin/openssl base64 | /bin/sed -e s’/[+=/]//g’)"

request_url="GET&https%3A%2F%2Fwww.flickr.com%2Fservices%2Frest"

consumerkey=b284fdada……f2fbbe1145
consumersecret=17a……..c36
oauth_token=721576519…..393-c6255e48d9fed8b7
oauth_token_secret=8547b…..1524ff0a

basestring="$(echo ${request_url}’&format%3Djson%26method%3Dflickr.test.login%26nojsoncallback%3D1%26oauth_consumer_key%3D’${consumerkey}’%26oauth_nonce%3D’${nonce}’%26oauth_signature_method%3DHMAC-SHA1%26oauth_timestamp%3D’${timestamp}’%26oauth_token%3D’${oauth_token}’%26oauth_version%3D1.0′)"
echo ‘basestring = ‘${basestring}

signature="$(echo -n ${basestring} | openssl dgst -sha1 -hmac ${consumersecret}’&’${oauth_token_secret} -binary | /usr/bin/openssl base64 | /bin/sed -e s’/+/%2B/’ -e s’///%2F/’ -e s’/=/%3D/’)"
echo ‘signature = ‘${signature}

request_output="$(/usr/bin/curl –compressed -s -H "Accept-Encoding: gzip,deflate" "https://www.flickr.com/services/rest?format=json&method=flickr.test.login&nojsoncallback=1&oauth_consumer_key="${consumerkey}"&oauth_nonce="${nonce}"&oauth_signature="${signature}"&oauth_signature_method=HMAC-SHA1&oauth_timestamp="${timestamp}"&oauth_token="${oauth_token}"&oauth_version=1.0")"
echo ‘request_output = ‘
echo "${request_output}" | /usr/bin/jq .
[/code]

if the call is successful you’ll receive your user id and username

output

# standing on the shoulders of giants: thanks to https://twittercommunity.com/t/can-you-get-public-timeline-using-oauth-by-only-using-curl-and-openssl-in-unix-shell/1476/2