In Part One of this Two Part post, we discussed the transformation of BsC’s XML format into English using an XML transformation I created. This provided two demonstrations – we parsed the entire company file of a BsC example, and we also made it possible to have an English version of the original Spanish XML schema.
Today we’ll build on our expertise with XML to integrate a BsC project into Google Docs so that we can create some interesting financial data.
One of the really cool features of Google Docs spreadsheets is it’s ability to read XML data over HTTP. As you may recall from the Part One exercise, we hosted a BsC strategic plan document on DropBox, and we can repurpose it now to access it and extract specific data values right from inside a Google Docs spreadsheet.
Creating an XML Spreadsheet
For this example, I created a blank Google Docs spreadsheet and formatted columns to accommodate month, target value, real value, and delta. The objective is to get the target and real values for twelve months and compare their differences. This is a simple example, but it demonstrates two basic integration essentials – (i) using BsC XML documents and (ii) parsing them to retrieve the desired information.
Google Docs provides a special function for retrieving data from XML documents. The format of the function is:
By programming a specific cell with this function and including the URL to the BsC document and the XPath to the information we desire, we can specify specific data elements or ranges of elements. XPath, the standard for Parsing XML files, is necessary because the BsC document contains many data elements, but for this example we only want the twelve months of target and real values associated with idIndicator #23.
To limit the scope of the data we harvest into the spreadsheet, the XPath will look like this:
The URL we will use is the same one we created in the Part One article that transformed the original BsC Spanish version to an English version, which I can understand a little better.
The complete function that we will use to retrieve the target values for the desired data looks like this:
By placing this function in the spreadsheet on the first cell under Target Value, the Google Docs scripting engine will retrieve the first XPath matching target value and place it in the same cell. The XMLImport() function is also smart enough to retrieve all XPath matches for idIndicator #23 and drop the values in the subsequent 11 additional rows of the same column. With one function call we have acquired a full year’s data for this target value.
Since we need the Real Value for each of the twelve months for this indicator, we must repeat the process in the adjacent cell of the first row. The function to retrieve the real values for all twelve months is almost identical, except that the XPath must now parse for “realValue” instead of “targetValue”.
Our spreadsheet is complete – we are able to harvest the two columns with our two XMLImport() functions and compare target values with real values.
As the BsC data is updated in the XML company file, as long as it is available at the same URL, the spreadsheet will always show this report using the latest data.
The XMLImport() function can be used to extract BsC data for reporting and many other spreadsheet uses. It can also be integrated into many other applications; this example demonstrates just one possible use case for BsC XML documents. But this also demonstrates how BsC data can be liberated from the confines of an iPad app and its proprietary data format. Once the information is freed, we can extend this example by using it in a dynamic BI dashboard.
I like Geckoboard (a lot). My affinity is the ease with which you can sculpt XML on the fly to create dynamic dashboards. I perfected a scripting model that also allows me to generate Geckoboard XML components from within Google Docs, so this is a great opportunity to extendthe BsC data out to a totally different BI dashboard.
By taking a selection of the data from the dynamically updated spreadsheet, I can transform it into a dashboard widget. In this example, I’ll use the January data to create a widget that shows the target value, the real value, and the delta.
As you can see – the freedom to repurpose BsC data is there – you just need to liberate the information using XML. While there are many aspects of XML integration and always-available web services that need to be considered including security, the tools and services are relatively straightforward. If you have questions about these exaples or ways you can leverage BsC data, don’t hesitate to contact me.