Disclaimer: Yes, I know the Gartner Magic Quadrants normally should be bought to be able to use. The information in this post however is based on the following web search: http://www.bing.com/images/search?q=gartner+magic+quadrant+bi&FORM=AWIR which happens to return the Gartner Magic Quadrants…

Recently I thought about the Gartner Magic Quadrants and how they offer a great view on the BI market and the BI vendors. However, they provide static views and it is hard to spot developments over time. With that, I thought to myself that an animated scatter plot would be just the thing to make these MQs much more interesting.

Power View (part of Power BI) provides this kind of visualization and here is what I did.

First, I followed a very advanced procedure to get coordinates for each BI vendor for each year in each MQ. This advanced procedure involved complex machine learning resizing the MQ images to 100×100 pixels and me manually writing down all the coordinates. This resulted in the following table:

Vendor Date Xcoord Ycoord Completeness Of Vision Ability To Execute
arcplan

1-1-2007

18

73

-32

-23

Panoroma Software

1-1-2007

37

74

-13

-24

Spotfire

1-1-2007

32

73

-18

-23

Applix

1-1-2007

29

68

-21

-18

Actuate

1-1-2007

46

54

-4

-4

 

The vendor is or course the name of the BI vendor in the MQ. Date is the year the MQ was released (I did not take into account months). Xcoord and Ycoord are the X and Y coordinates respectively and the next two columns (Completeness Of Vision and Ability To Execute) takes Xcoord and Ycoord and substract 50 from it so the midpoint for the scatter plot is 0.

Although by now I would have been ready to plot the data I decided to make it a bit more interesting by also including market capitalization for each vendor. In order to do this I added another table which looked like this:

Vendor Stock
arcplan PRIVATE 1
Panoroma Software PRIVATE 2
Spotfire PRIVATE 3
Applix PRIVATE 4
Actuate BIRT
QlikTech QLIK
MicroStrategy MSTR
SAS PRIVATE 5

 

Using the Stock Indicator I used Power Query to get the stock information for the “date” the MQ was published (which I fixed to 1st of January of that year). The data I got from http://www.quandl.com which provides CSV downloads per stock (for example the historical market capitalization for MSFT can be retrieved from http://www.quandl.com/api/v1/datasets/DMDRN/MSFT_MKT_CAP.csv). What I needed next was a function that would retrieve the market capitalization data for a given stock label. I create a function called MarketCap that takes a stock label as input and then reaches out to Quandl to download the CSV. Next it does some formatting and returns the data. Here is the code:

All I needed to do was get Power Query to iterate over all stock labels and call this function for every stock label. I used my Excel table shown above as source and inserted a customer column that called the MarketCap function with the stock label. Next, I expanded the returned column and removed some columns. The code is shown below:

Next, I loaded all of this into PowerPivot and created a relationship between the MQ scores table and the Vendor table.

However, I could not create a relationship between the MarketCapData and the Vendor table, since I would need to draw two relationships: one on Vendor and one on date (or actually year). However, I still wanted to get the Market Capitalization per vendor for the moment the MQ was released. Luckily, we have LOOKUPVALUE (which I discussed earlier). With LOOKUPVALUE I added a calculated column to the Scores table to retrieve the Market Capitalization:

Next I went ahead and did some cleanup (hide columns / tables).

Finally, it was time to build the visualization using Power View:

What you see here is a line graph showing the market cap data and a scatter plot with play axis on Date that shows the scores per vendor for Completeness Of Vision (horizontal axis) and Ability To Execute (vertical axis). I could have used market cap data for the size of each vendor’s indicator but that makes the chart unreadable. So now we can go back in time and see all the MQs for BI one after the other in a nice animation:

Notice how in the video I show the development of Microsoft over time. Also see that the top chart responds to the selection I made in the scatter plot.

This wraps up this post. Hope you liked this demonstration of meta-BI using Power BI!