Extended Data Access

When MicroStrategy first announced their extended data access (XDA) support many moons ago, the concept didn’t really resonate with me.  Most of the use cases for XDA were one-off hacks that really didn’t have a place in the traditional data warehouse based-environments.  In recent years, however, the number of open access APIs has exploded, and combined with multi-source capabilities, dashboards, Visual Insight, and especially Mobile, the utility inherent to being able to gather a data set from a REST, SOAP, or WebDAV means that the data warehouse isn’t as critical as it used to be.

One of my favorite sites to peruse is data.gov — when the site was first created much of the data was a disparate set of Excel dumps, XML fragments, CSV, text, etc..  Not only does is make it harder to integrate these formats, but it requires someone to update the extracts.  Since I have an interest in environmental and regulatory matters, I wanted to integrate business intelligence with some EPA data.  I have also been doing some research to find ways to avoid writing ETL jobs whenever possible, and having the option to use a RESTful interface within MicroStrategy means that I can integrate data without having to write a set of data integration packages.

The high-level flow of tasks needed to create a report based on a data API is as follows:

  1. create a database instance for Xquery
  2. gather the proper Xquery syntax using the Xquery Editor and Generator
  3. create a new freeform SQL report
  4. drop the xquery definition into the freeform SQL editor
  5. create a visual insight document on top of the new report

So, creating a new xquery database instance is pretty straightforward.  The only trick needed is to reference the xquery data base type:

The real effort comes when getting the proper xquery syntax together.  When I first started researching how to create a report, the documentation wasn’t very clear on what the syntax needed to look like, so I was worried that it would entail a lot of hand coding, which leads to mistakes, which leads to me getting frustrated and then giving up.  But as I started to play with the editor, I realized that it was quite flexible, and could pretty quickly spin out the syntax I needed.

So, long story short, here’s what the query looks like in a free form SQL report:

None of this needs to be typed.  The Xquery Generator and Editor handles it all.  In this example I am using the following URL to request data from the EPA (New Hampshire):


The full query:

declare copy-namespaces no-preserve, no-inherit;
declare namespace mstr-rest = 'http://www.microstrategy.com/xquery/rest-functions';
declare function mstr-rest:post($uri,$payload,$http-headers) external;

let $uri := ('http://iaspub.epa.gov/enviro/efservice/tri_facility/state_abbr/NJ')
let $http-headers := ( )
let $payload := ( )
let $result := mstr-rest:post($uri,$payload,$http-headers)

<ColumnHeader  name='FACILITY_NAME' type='xs:string' />
<ColumnHeader  name='PREF_LATITUDE' type='xs:string' />
<ColumnHeader  name='PREF_LONGITUDE' type='xs:string' />
<ColumnHeader  name='FAC_CLOSED_IND' type='xs:string' />
<Data>{for $ancestor0 in $result/tri_facilityList/tri_facility
order by $ancestor0/FACILITY_NAME
return <Row>

This entire string was created by the editor (found in the MicroStrategy –> Desktop folder).  The only trick that I had to figure out was that the fields in the tree view response section have to be dragged and dropped onto the Main Grid View section.  When the desired fields are in place, the code can be generated and loaded.  The fully query is available in the Xquery generation window and can be copied as-is and dropped into the freeform SQL.

Once the query is in the report editor, the number of XML ColumnHeaders has to correlate to the number of attributes / metrics defined in the report.  In my example there are four column headers, thus four attributes in my report.

Because the data that is returned by the url is XML, MicroStrategy knows how to crosstab and render the results.  I had to make one little adjustment to the data and that involved putting a dash before the PREF_LONGITUDE field, otherwise the data will map to Russia…which isn’t very useful.

The resulting data is the lat / long coordinates of EPA toxic release sites. The high level data doesn’t indicate exactly what has happened at the site, but presumably additional extended queries to the other TRI tables would yield more information. The lat and long is easily consumed by the Visual Insight report, giving us this final output for Pennsylvania (my link gives New Hampshire data which is smaller and comes back faster):

One note: I haven’t been able to tell whether MicroStrategy will be able to consume JSON as a data format any time soon.  Doing so would presumable expand the number of native data sets that MicroStrategy could report off of.

MicroStrategy has several features that they just seem to throw out there without much fanfare or for that matter documentation.  Having SOAP and REST support is important and opens up the platform to doing pretty interesting (and real-time) things — especially on Mobile devices.  Here’s just one idea: combine NOAA and Twitter data to localize weather information and sentiment.  Is it really cold outside?  If so, just how cold?  500 tweets in your zip code in the last hour cold???