Archive for September, 2011

Venn diagrams provide an elegant and powerful visual display of information. In essence, a Venn diagram compares the size of two or more groups while simultaneously presenting the level of overlap that exists among the groups. Just for the sake of review, let’s look at a simple business example. The chart below compares the number of customers who purchased Product A (orange) against the number of customers who purchased Product B (green). The overlapping section in the middle represents the number of customers who purchased both Product A and Product B.

It’s hard to imagine producing a better display of that information using any other charting method. In other words, if you’re creating a visualization to compare overlapping groups, the Venn diagram option is probably at the top of your list. What makes a Venn diagram so powerful is its simplicity. It’s very easy for both technical and non-technical groups to view the chart and interpret the information. Yet, in spite of all this, Venn diagrams are almost always absent in reporting tools. Certainly this is true for the three major Business Objects reporting tools – Crystal Reports, Web Intelligence, and Xcelsius – which have never offered an out-of-the-box Venn diagram charting feature.

I recently became interested in building Venn diagrams using Xcelsius. I knew there would be significant value in providing this type of visualization to report users, especially executive level users. I also felt it would create a new analytical perspective since Venn diagrams show the type of metrics that are not often presented well in business reports. I thought there had to be some trick or workaround I could use to get the job done, so I began tinkering.

The first thing I learned along the way is that Venn diagrams are not as easy to build as you might think. They look simple on the surface, but there’s actually some fairly complex geometry going on behind the scenes. (Do a Google search for “circle-circle intersections” to see what I’m talking about.)

After some testing, I developed an initial solution using the Bubble Chart component in Xcelsius 2008. The Bubble Chart is identical to the XY Chart, except there’s a third variable that defines the size of each point. Look again at my simple example above. You’ll see that the chart basically did two things. First, it sized the circles to reflect the proportional difference between the two groups and then it moved the circles together to reflect the overlap. Well, the Bubble Chart already does that first part by itself. So my solution used the existing Bubble Chart functionality to automatically determine the correct dimensions for each circle. Then, I set up Excel formulas to dynamically calculate the X value (horizontal position) for each circle so that the correct amount of overlap was displayed.

Overall, I was happy with this solution, but there were some drawbacks.   It was a bit clunky due to the math required to control the movement of the  Bubble Chart and to calculate the overlap. Also, as you can see from the  screenshot above, Xcelsius has a limit on how big it allows the individual “bubbles” to grow in a Bubble Chart. So the end result is a very small SWF file that you would need to import into another document (e.g. another Xcelsius dashboard or a PowerPoint presentation) and then re-size from there.

Shortly after that initial experiment, I set about improving the process.  That’s when I stumbled across the Google Chart API. The Google Chart API is a simple, easy-to-use web-based chart creation tool provided free by Google. The API allows you to pass a few parameters via an HTTP request which prompts Google to return a PNG image containing your requested chart. Several chart types are supported, including Venn diagrams.   Check out the Google Chart Wizard for more information on the supported charts and formatting options.

Remember the simple Venn diagram example I showed at the beginning? That was actually created using the Google Chart API! The URL string below shows the HTTP request that was used to produce the chart. To see how it works, copy and paste the URL into your browser. You should get the same chart that was shown above. Just for practice, try to adjust the parameters defined in the URL string, refresh the page, and watch how the chart changes.

• chs=600×400: Dimensions of the PNG image file.

 

• cht=v: This tells the Google Chart API that you want to create a Venn diagram. Other chart types include Line Charts (cht = lc), Pie Charts (cht = p), and so on.

• chds=0,5000: he minimum and maximum values allowable for charting. Keep the minimum at zero. Depending on your data, you may need to increase the Maximum value.

• chd=t:66,100,-1,33,-1,-1,-1: These numbers specify the size and overlaps for all groups. The ordering is as follows: Group 1 size, Group 2 size, Group 3 size, Group 1 & 2 overlap amount, Group 1 & 3 overlap amount, Group 2 & 3 overlap amount, Group 1 & 2 & 3 overlap amount. In this case, we only had two groups to display, so all of the parameter slots reserved for the third group were set to “-1”. If you have a third group, then those “-1” values would be replaced with actual amounts. The Google Chart API provides for a maximum of 3 groups in Venn diagrams.

• chdl=Customers+who+purchased+Product+A|Customers+who+purchased+Product+B: This defines the group labels that will be displayed in the legend. If you have a third group, you’ll add another pipe delimeter at the end, followed by the desired group label.

• chdlp=b: This tells the Google Chart API to place the legend at the bottom of the chart. “b” is the code for “bottom”. The other codes are “l” for left and “t” for top. If you exclude this parameter from the URL string, the legend will be displayed on the right by default.

In addition to those parameters, there are even more you can use to further customize the look and feel of your Venn diagram. You can adjust the margins, change the colors, add a title, and so on. If you’re interested, the Google Chart API links above describe all the options.

So how can you display Venn diagrams in Xcelsius using the Google Chart API? Actually, it’s very simple. You’ll need your Excel data model populated with numbers showing the size and overlap for all groups you wish to chart.  Then create string concatenation formulas in Excel to build the URL string piece by piece. Finally, add a Slide Show object to the canvas and bind it to the cell containing your URL string. And that’s it. You should now have a fully functional, dynamic Venn diagram in Xcelsius.

The dashboard below is a sample I created to demonstrate this functionality. It allows you to adjust the group sizes, overlaps, and labels.  As you adjust the values, notice that the chart changes dynamically just as you would expect from any other Xcelsius component. Also, the dashboard displays two groups by default; however, you can click the checkbox on the left side to incorporate a third group. Also, keep in mind you cannot use this approach in a completely offline format. Your dashboard user will need a connection to the internet and the ability to call the Google Chart URL.

Here is the working dashboard.  You can change the values and see the chart change.

There are certainly pros and cons to both solutions – the Google Chart API and my original Bubble Chart solution – and I hope to blog about those in the future. However, I think the Google Chart API provides a better overall solution. It’s clean and simple and it provides the functionality to incorporate a third group.

Finally, I encourage you to experiment with Venn diagrams in your own Xcelsius dashboards. Play around with the options to see what works best for you. Feel free to share your experiences and feedback in the comments.