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:


I received a lot of feedback regarding my two recent posts:, and

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 (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!

A map, a map! My kingdom for a Choropleth map! (Power BI)

One of the most fascinating aspect of Business Intelligence is the power to picture data in an easy to understand way. I personally like maps a lot, not the google or bing type with lots of circular points which give very low added value to the information, but the Choropleth maps. Those shaped areas with shaded colors intended to convey an idea at the first sight.

Choropleth maps are mostly used with regional data but they can picture any kind of information on any kind of shape, the idea behind is very simple: you have a SVG image (a vectorial one), with one id for each closed area. Remember those days when we were kids and used the coloring book to paint on pre-designed images?

That’s it. Then you load your data and assign to each id a numeric value to get a colored area with a increasing intensity of color.

This is exactly what the Synoptic panel does.

It’s a custom visual that you can use with Power BI, more on this here. You can download the current version from gitHub, and import it into your Power BI report (yes, it’s free).

The Synoptic panel itself doesn’t paint the picture, you need to have a coded SVG to use with it. And creating a coded SVG is not difficult, you may use any SVG editor, or just go to which is an extremely useful web build by the same people who did the custom visual.

I must thank @DanielePerilli for his very good job and his continuous support. He was very responsive and helped me through building my first map.

spanish elections 2011

So in about an hour and a half here we have two maps with polls data from the Spanish elections in 2011. I downloaded the CSV from and used the Synoptic panel to create two maps where I filtered respectively the votes obtained by PP and PSOE.

No rocket science, anybody can do that. Just a little caveat, it’s not very intuitive: if you want to have different shades of color, you should drag a field to the “Saturation Values” box in the visualization settings. And if you want to display names on the map, you need to enable “Category Labels” in the format tab of visualization settings. That took me more than 5 minutes to figure out.

Anyway, go download it, create your own map or use those available in the gallery and happy coloring!

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:

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(){


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

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:

The Lahman's Baseball Database – real life sample data

Mr Lahman is (by his own words) a Watchdog reporter & data journalist, and an author.
But the collective wisdom of the Business Intelligence world reminds him as the creator of a wonderful public dataset: the Baseball Database.

The Lahman database is extremely useful and I am grateful to his author for several reasons:

  1. It is not a bike/sport/equipment shop sales database, that almost all examples in the BI world use (my book is not an exception…)
  2. It is real life data with an interesting subject, and frequently updated.
  3. And it has errors (very few), those little nice imperfections that come very handy when you are testing/training/developing a test case or a proof of concept.
I don’t remember who told that PowerPoint is always right and nothing can go wrong with a slide presentation -as long as the projector is working and the usb drive is plugged in- but he surely would agree that it’s no fun to demo with a database that is perfectly crafted to return exact results.
I never found in my life a “squeaky clean” production db. As a general rule there is always a primary key missing, an unenforceable foreign key, or missing records, outdated documentation, you name it…that’s why I like the Lahman db, because I can experiment -under a controlled environment- how the software behave in situations that are beyond the standard Show and Tell “look-ma-this-works” case scenarios.
It is available for free and you can download it in several formats from here:
I converted it to Oracle and SQL Server, and you can download those version too from here:
In Oracle, simply run IMPDP with the SCHEMAS=LAHMAN option, the password for the LAHMAN schema is (guess) LAHMAN.
In SQL Server copy the MDF into the DATA folder and attach it to an existing SQL Engine instance.
Hope this helps, I will often use this data to showcase MicroStrategy and other products in my next posts.

See-through MicroStrategy (playing with transparencies)

One thing that I like about MicroStrategy is the ability to create Web documents. Nowadays self-service discovery technologies (being based on Flash or HTML5) tend to constrain a little the “creative” side of the data analysis: trading off the ease of use for a quite limited set of tools to picture your data. Boundaries are build and it is difficult to bend the rules.

Web documents do not have such restrictions and -in fact- are almost limitless in possibilities. Think about the HTML container that allows us to insert custom HTML/JavaScript, or the image container.

I believe that Business Intelligence is about information as much as about design, and -surely enough- a nice looking dashboard is always preferable to a boring dull one.

A fundamental aspect of modern day web pages is the CSS Opacity / Transparency, so why not using it in a dashboard to spicy it up? The components and widget that come with MicroStrategy allow a very limited use of “No Fill” in the background: you can create a transparent Chart or Panel, but not a transparent rectangle. So I simply created a series of white PNGs with different alpha values that I can insert on top of a background picture to simulate a see-through rectangle.

Then I downloaded a background picture on Flickr (careful with the CC license…) and removed the main panel of a blank dashboard.

You can see the result here, where a Web document turns to A Whiter Shade Of Pale.

now you see me now you dont

You can download the transparent pngs here.

Average of Averages… no, seriuosly.

Tom, Bill and Sandra have one euro each; they go and buy some fruits.

Tom  buys 3 apples for 1€. Bill buys 2 oranges for 1€ and Sandra buys a coconut for 1€.
Tom says “apples cost 0,33 euro cents on average”.
Bill “oranges are 0,50 euro cents on average”
Sandra “coconuts are 1 euro on average”

What is the overall average price of fruits ?

I have seen more than once calculating this as:

0.33 + 0.50 + 1 = 1.83
1.83 / 3 = 0.61

Now, you are a Business Intelligence professional: imagine your customer asking you to calculate an average, such as the price per product grouped by product type and the total overall price per product at company level.
Next he dig into his laptop and proudly shows off his “treasure” Excel sheet that does it all, and by magic copy and paste computes the AVG() of the AVG()…

Unfortunately for your client, acrobatic math is not yet an Olympic discipline.

Tom, Bill and Sandra soon realize that they bought 3 + 2 + 1 = 6 pieces of fruit and they spent 3€, so a single piece is 0,50 euro cents.

Similarly your customer (after an intense discussion) will realize that for the last 15 years he used to calculate a price per product average “from a different point of view”. You may want to ease the pain by saying that it is a quite common error and he’s in good company: see Simpson’s paradox and the Berkeley gender bias case

In MicroStrategy terms this is what is called a “Smart Metric”.

When in the Metric Editor, switch to the Subtotals / Aggregation tab, down to the left there’s a check box that you can enable to surprise your customer… cool.

There OAuth to be a better way (Power BI)

Edit: The base URL has changed from to

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.


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


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


  • 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.


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”]


POST_RESULT="$(curl -s -X POST -d "resource="$OAUTH_CLIENT_ID"&grant_type=password&username="$OAUTH_USERNAME"&password="$OAUTH_PASSWORD"&scope=openid" "" | 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


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”]

curl -k -s "" -H "$AUTH_HEADER" | /usr/local/bin/jq -r .


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”]


POST_RESULT="$(curl -k -s -X POST -d "resource="$OAUTH_CLIENT_ID"&grant_type=refresh_token&username="$OAUTH_USERNAME"&password="$OAUTH_PASSWORD"&scope=openid&refresh_token=${REFRESH_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


So, no browser, no GUI, no problem!

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.


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

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

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

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


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


JWT_CLAIM_SET="$(echo -n "{"iss":"$EMAIL_ADDRESS","scope":"","aud":"","exp":"$(($(date +%s)+3600))","iat":"$(date +%s)"}" | /usr/bin/openssl base64 -A -e  | /bin/sed ‘s/=//g’)"


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=$JWT

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


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

Reminder: MicroStrategy Express is Retiring

MicroStrategy Express will be retired at the end of June 2015. We hope you have found it a valuable tool in exploring and visualizing your data.

Now join us in welcoming the era of MicroStrategy Desktop – the latest in data discovery and visualization for business users, available to install on Mac and PC.

We are pleased to announce that as an Express user, you qualify to receive complimentary access to the new MicroStrategy Desktop 10. Valued at $600, this will be available to MicroStrategy Express users for free. In order to get access to the new MicroStrategy Desktop, please fill out this request form.

We are committed to helping you make the transition from Express as smooth as possible. In order to preserve any MicroStrategy Express dashboards or information, we encourage you to back up files to a local machine by June 30th.

For guidance on how to back up your MicroStrategy Express dashboards, please refer to the FAQ below.

MicroStrategy Express is Retiring: What You Need to Know

When will MicroStrategy Express service be retired?

End of June 2015, at which time all accounts will be terminated and you will no longer have access to your teams and dashboards on that account. All information on the MicroStrategy Express service, including user accounts, dashboards, and data will be deleted.

Can I back up all my dashboards on my MicroStrategy Express account?

Yes. Over the next few weeks while the accounts are still active, you may back up your dashboards to avoid losing them. However, if you don’t back them up, they will be deleted when Express is retired.

How do I back up dashboards within my Express teams?

By clicking on the “Share” menu option for each dashboard, you can select the “Export to MicroStrategy File” option to download your dashboard with data and save the file (.mstr) on your local computer.

Is there another option to back up my dashboards if I don’t do it myself?

Unfortunately there isn’t. Account holders will have to back up their own dashboards as no one else will have access to these accounts.

How can I continue to use my dashboards and create new dashboards with MicroStrategy after June 2015?

As an Express user, MicroStrategy will be giving you complimentary access to the new MicroStrategy Desktop client. This is valued at $600 for each Desktop user, but will be available to MicroStrategy Express users for free. The MicroStrategy Desktop client is the crown jewel in the new MicroStrategy 10 release, and is available as a client install for both Mac OS and MS Windows. All dashboards created using templates in Express can be used with the new MicroStrategy Desktop. Advanced Template dashboards may only be imported using MicroStrategy Web and will need the Server license.

What new capabilities exist in MicroStrategy 10 that were not available in MicroStrategy Express?

MicroStrategy 10 offers a completely new analytics experience, with a revamped HTML5 interface, native connectivity to more data sources, data wrangling, new types of visualizations, and faster performance. Visit for more information about all the exciting new features.

Can I back up dashboards built with Out-of-the-box Templates (Visual Insight)?

Yes. Dashboards built with existing templates can be backed up while the account is still active over the next few weeks. Dashboards built with live connections to databases (Express Delivery dashboards) may not be backed up.

Can I back up dashboards built with Advanced Templates?

Yes. Dashboards built with Advanced Templates can be backed up. In order to use them, you will need MicroStrategy Server and Web license as Desktop does not support advanced templates.

Can I back up my Mobile and Team Launchpads?

No. The Team Launchpad only applies to MicroStrategy Express, and you cannot back it up.

Can I back up the list of user accounts and profiles within my team?

Yes. On the administrator tab for team owners, you may click on the “Export Users” option to retrieve the list of users and profiles in Excel format.

If you have other questions about transitioning off of MicroStrategy Express, please don’t hesitate to contact us.