Microsoft Power BI to be generally available on July, 24 2015

Few days ago microsoft announced that the preview phase of Power BI is now over and the product will be launched later this month.

The Power BI that was available in SharePoint and Office 365 will be superseded. This means that Power View (the Silverlight powered dashboard) will cease to exist.

<my personal note>Given that Microsoft recently aquired DATAZEN, I suppose this Power BI will also have a very short life-span.</my personal note>

It will feature a Desktop component and a cloud based hosting service. Free accounts for up to 1GB of data.

It will be able to access both on-premise and cloud data and more connectors will be added after the launch. One point worth noting is that Microsoft released the code of the visualizations on GitHub, allowing developers to add their own D3.js (or other javascript) based charts under MIT license. This means that we could write plug-ins to embed into Power BI, but I have no clear idea whether we’ll be able to embed Power BI functionalities in our own web pages/applications.

What is your overall impression with this new BI tool?

Please answer a couple of questions here: https://www.surveymonkey.com/r/W8VTBS7

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

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 http://www.microstrategy.com/us/analytics/10 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.

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

How to revoke access permission from Google account

If you granted access to some of your data in Google (analytics, calendar, contacts, etc.) to some external application (like Xobni for example); you may want -at some point- to revoke them as you no longer use that application.

To do that click on the gear icon and select “Account settings” go to your account settings once you’re logged into Google:

then click on “Authorizing application & sites” link:

 

 

 

 

 

 

In the following page click “Revoke Access” to any application you no longer use. That ensures that none will be able to see your private data with that particular application.