MicroStrategy and AWS

I was surprised (but pleased) to find that MicroStrategy is now available on the Amazon Web Services Marketplace.  For the past several months I had been watching the business intelligence offerings there but was seeing mostly Jaspersoft, Birt, and a handful of smaller players that I was not familiar with.  Seeing MicroStrategy available, especially the the free reporting suite, means that kicking the tires, spinning out a new instance for a prospect demo, or simply trying out 9.3 in a temporary environment is cheap and easy to do.  I also see MicroStrategy’s embrace of AWS, along with their very early support for Redshift, as signs that MicroStrategy is committed to the cloud architecture, and not just their cloud offering.

I took the MicroStrategy Suite on AWS for a test drive through my AWS account and had it up and running within 30 minutes.  The one trick was realizing that I had to decrypt the key pair I generated so that I could log into the server from remote desktop.  Once I had that in place I had no troubles getting to the machine, activating the server, and then connecting to it from my local 9.3 instance.

Unless I win the $550M PowerBall jackpot tonight I’ll probably terminate the server before the weekend.  In most companies today, to set up a VM like this I would have had to request a ticket for a new VM, haggle with the VM admin over how much memory I was allowed (I like to have at minimum 8 GB for development, and 64 GB for production), and then have to download the MicroStrategy media, move it the machine, unpack it, install it, and then reboot.  Using AWS I didn’t have to haggle with anyone, I opted for the $0.46 / hour and 7.75 GB RAM version, and instantiated the server.  All the necessary ports were open (also a typical hassle) and I was up and running without any hiccups.

SQL Server 2008 comes pre-installed on the image, and the metadata and warehouse tables are all contained in SQL Server.  Even though this was a preconfigured instance I had to run the cubes in order to get the Visual Insight reports to work in the Tutorial.  I also tweaked the memory and report governing settings, but it’s understandable that none of these were pre-configured.

I haven’t tested the mobile piece yet, but the server was fully configured and I doubt that the setup will be different than the initial spinout.  So…again, MicroStrategy goes off and does something, neglects to tout it, and through some luck I stumble upon something they’ve done that is developer / engineer friendly.

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):

http://iaspub.epa.gov/enviro/efservice/tri_facility/state_abbr/NH

The full query:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
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)


return
<Table><ColumnHeaders>
<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' />
</ColumnHeaders>
<Data>{for $ancestor0 in $result/tri_facilityList/tri_facility
order by $ancestor0/FACILITY_NAME
return <Row>
<FACILITY_NAME>{fn:data($ancestor0/FACILITY_NAME)}</FACILITY_NAME>
<PREF_LATITUDE>{fn:data($ancestor0/PREF_LATITUDE)}</PREF_LATITUDE>
<PREF_LONGITUDE>-{fn:data($ancestor0/PREF_LONGITUDE)}</PREF_LONGITUDE>
<FAC_CLOSED_IND>{fn:data($ancestor0/FAC_CLOSED_IND)}</FAC_CLOSED_IND>
</Row>}</Data></Table>

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???

 

Hubway Data Challenge – Final

While the Hubway Data Challenge has been an opportunity to explore some of the newer capabilities in MicroStrategy 9.3, it was also an opportunity to work with a new data set and a get some practice modeling in geolocation data.  Working with other developers, and seeing some of the submissions has informed my understanding of what tools are out there and what other people are doing with commercial and open source visualization tools.  The concept of animating this data on top of a map has particular appeal is appropriate for this type of data.

This submission caught my attention, but there are a few others that have been submitted in recent days.

For me I was committed to sticking with the 10.31 deadline even though the deadline was extended.   I had no shortage of ideas, but ultimately I had to pick something to show and share.  The new network diagrams in 9.3 are clean looking and visually pleasing.  So, I picked one bike (the mayor’s) and graphed it’s usage.  I set the export option for dashboards to be .pdf (as opposed to .mht) and was able to create a portable document from the Visual Insight interface.

The .pdf file is available here.

The other thing I wanted to try with the data was something using the maps.  It took some trial and error and an angry email to Google before I realized that I didn’t in fact have to pay $10,000 for a Google maps API key.  To get the data in a format that would work for the maps, I decided to create a running sum of the trips by station over time.  There were two things I had to do within MicroStrategy to make this work:

1. Set the VLDB property to retain all of the lookup table information

2. Create a running sum metric to accumulate trip information:

Within the grid report the output had to look something like this before I could build a cube off of it:

The next step was to create a Visual Insight document using the map style.  I had tried to use the density version but my data was not disparate enough to use close up, so I used the traditional map and used bubbles to illustrate the data.  I set the geographic attribute to be the start station, and because I modeled in the lat and long as part of the attribute form (as latitude and longitude data types) Visual Insight recognized the data.  The last step was to put the start date into the page by and from there I could take advantage of the play button.

I used the Quicktime player on my laptop to record my screen and then I used iMovie to speed up the movie to condense something that took ~6 minutes in MicroStrategy to play in about 24 seconds.  This became the basis for my second submission to the Hubway Data Challenge.  I posted this on Youtube:

http://www.youtube.com/watch?v=Ymd8bAoiPtU&feature=youtu.be

My overall impression with 9.3 has been favorable so far.  The cleaned up UI is pleasing, the interface is faster, and the Visual Insight enhancements not only look good, but they work pretty well too.  Having spent some time with other developers at the hack day event I realized just how many people are using R, and with the integration of R into MicroStrategy there are certainly some possibilities to consider there.  I know from past experience that the number of freely available R packages is improving, and that the corresponding library of visualization for R is growing.  This offers many possibilities for MicroStrategy development and deployment, and certainly extends the platform in ways that I am looking forward to exploring.