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.