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!

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.

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!

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