Monday, March 24, 2008

Motion Chart provided by Google--an outstanding BI tool for free

Google just announced a gadget gallery containing one gem for the business intelligence consultants. It's a motion chart, that is, an XY chart with a time dimension (feel free to play with it).



Explanation: The XY chart above displays ten countries according to how much they collect taxes and amount of government spending (bubble size = population, bubble color = unemployment percentage). When you play years between 1970 and 1999, you see countries changing locations. By analyzing the motion it's clear that since 1970 big European countries (UK, Germany, France, Italy, Spain) have adopted policies that make them more alike (bubbles moving near each other from starting positions) while Sweden all the time stands out with high taxes and government spending. Japan seems to be the other extreme with low taxes and government spending. Between 1993 and 1997 Spain turns red with a high unemployment rate (over 20%).

I'm really astonished for several reasons:

  • Motion chart as such is a very useful concept for tracking changes in time (it makes you understand the data, not just display it).
  • I was able to build the chart above and publish it here in two hours, that is, very quickly as a first-time-user (+one hour more for searching useful data).
  • Google motion chart works really well (try changing the chart above by using pop-up menus provided--it's a dynamic chart!).
  • No else BI provider offers this kind of visualization tool.
  • And it's available for free!
Of course, the motion chart concept is someting we have been waiting for ever since Google bought GapMinder. Now Google gives it for use for everybody. For example, you can load the motion chart with the products of your company and see their profiles changing over time.

Here are the steps (I made) to publish the motion chart...

1. I searched data from web with keywords "time series data". The macroeconomic data I found here. I decided to limit my test in ten countries. I collected the text data into spreadsheet. One hour.

2. The data was not in the right format for the motion chart (see example here). I used several methods to clear and pivot the data (not only in spreadsheet) until I was ready. One hour.

3. I copy-pasted the data into a Google spreadsheet document (yes, you must start using Google Docs to create and publish your motion chart). I inserted the motion chart gadget and it worked immediately. I still changed the format several times, because I needed (wanted) to learn how motion chart uses spreadsheet data. One hour.

NOTE: If you want to use quarters, months, or weeks in the time dimension, convert them into dates--such as 2008-Q3 saved as "7/1/2008" or 3rd week of 2008 saved as "1/17/2008" (Thursday, the middle day of week). Dates must be in the US format (MM/DD/YYYY) and decimal numbers with a dot as the decimal separator.

4. From the top-right gadget menu, I chose Publish Gadget, and it gave me html to put into a web page. One minute.

Final thoughts... I'm not completely satisfied with the fact that in order to use the motion chart Google will host my (perhaps confidential) data. Nevertheless I'm going to explore the motion chart concept with my customers in the future, I'm sure.

12 comments:

BCC said...

Thanks for the example. I'm pretty excited about Google's visualization API (though I worry it may starve some open source visualization toolkits).

Johan Myrberger said...

Hi, I am playing around with the Motion Graph option, and it doesn't seem to work in IE6 when embedded outside of the spreadsheet. Have you seen a similiar issue? (Ie the grah in this post does not work in IE6, but in Firefox)

Janne Pyykkö said...

Hi Johan. With my limited experience (I've not yet deployed motion chart with my customers): IE7 and Firefox are ok with the graph in this post. There's also another way to publish motion chart, that is, by publishing the sheet and chart together, but that knocks out IE7 (Firefox still works).

Beta Data Blogger said...

I just did this with ETF data (return, volatility, and correlation). Pretty cool, just need to figure out how to post the gadget.

JC

http://mktbetadata.blogspot.com/

Janne Pyykkö said...

Beta data blogger: First click the gadget so that the gray title bar appears, then choose Publish Gadget from the gadget menu at the right end of the gadget title bar. The rest is obvious...

TrendCompass said...
This comment has been removed by a blog administrator.
DX said...

Any idea if you can take the motion chart offline and use it in presentations (e.g. powerpoint?)

Janne Pyykkö said...

DX, I don't know.

Sandy said...

I tried to publish the chart onto a my web page by pasting the code in an html file but when I preview the html file in the browser I get the following error" access denied"
Could you help me fix this?

gorengpisang said...

I also got the same error as Sandy. Able to help?

Nashville TGA Planning Council said...

How did you get your motion chart to display in blogger? Mine just loads forever but never appears....

Steph Ladbrooke said...

Really useful set of tips - thanks!