Using Grafana to Report MicroStrategy Usage

At work, we live and breathe MicroStrategy. So when they announced that the 2019 version will start phasing out Enterprise Manager in favor of Platform Analytics for internal reporting, I was intrigued.

From what I can see, MicroStrategy is now reporting realtime usage by using a combination of Kafka and their home-brew Multiple Table Data Import cubes. This allows for realtime streaming into an in-memory data store and subsequently realtime reporting on MicroStrategy usage.

MicroStrategy reporting, even the newest version, is not optimized for realtime streaming of data on to dashboards. Sure the dossiers (MicroStrategy’s wording for the industry-accepted term of dashboard) have a feature to refresh every predetermined amount when in presentation mode, but the fact that it refreshes the whole view instead of updating individual visualizations kind of throws me off.

Enter Grafana

Grafana is definitely not as feature-rich as MicroStrategy, but it is good at one thing: time-series reporting and visualization (plus it’s free!). I wanted to see if I could use MicroStrategy’s REST API to pull data from it and use it in Grafana.

Formats and more formats

To no-one’s surprise, the format in which MicroStrategy returns data from its endpoint and the format in which Grafana expects data are not the same. The good thing is that both are documented, so converting between the two becomes a semi-trivial exercise.

Workflow

The workflow is very simple. Each time Grafana wants either a list of metrics that are available or the data itself, it needs to query our translation server. In turn, the server will query MicroStrategy, translate the format and return either the data or the list of metrics.

Setting up Grafana

To install Grafana there are several routes you can take. I chose to install it on a Raspberry pi. You can install it on any machine you want. If you do want to go the Raspberry pi route, I followed this tutorial with great ease.

Installing the JSON datasource plugin

The first step in all of this is to install the simple-json-datasource plugin from Grafana. Do so by running the following:

sudo grafana-cli plugins install grafana-simple-json-datasource
sudo service grafana-server restart

What this does is that it enables Grafana to request data via REST.

Setting up our Python Server

As mentioned before, we need a server to translate the queries from Grafana into requests to MicroStrategy and the MicroStrategy response into a format Grafana understands.

Query types

There are three main types of queries we will support: Root, Search, and Query. Root only needs to return status 200 (ok) when Grafana pings the server. Search should bring back the list of metrics, and Query should bring back the full data for the metric requested.

The Server

I decided to use flask_restful for Python. It is a library for building APIs. If you’re new to flask or flask_restful, Miguel’s blog is an excellent source of information. I leveraged MicroStrategy’s python package (mstrio-py), although I had to change a few functions since I wanted to report directly off of a dossier and it’s not abstracted on the package yet.

If you’d like to know more about how exactly I built the server, please reach out to me by putting a comment below.

Data source

As mentioned before, I am reporting on Platform Analytics, so I decided to use an already available dossier.

Back to Grafana

Now that our server is setup, we need to go into Grafana and choose MicroStrategy as a source. Click on New Query and select MicroStrategy.

At this point, Grafana will ping the server and expect a 200. Now select a metric from the dropdown. As soon as you click on the bottom arrow to expand the list of metrics, Grafana will request the list from our server.

When clicking on one of the metrics, Grafana will request the data for that metric from our server.

By querying all the metrics we want, we can build a native Grafana dashboard.

Conclusion

As you can see, MicroStrategy is very flexible in allowing other applications to query its data. The benefit of using MicroStrategy as a data source is that you inherit all the security, governance, and caching from MicroStrategy in your external applications. As proof of this, I showed you how to display 14 days worth of data using a $35 computer (Raspberry pi).

Just as I used Grafana, you can use any other tool your team/company currently uses. By using open APIs, MicroStrategy makes it very easy to integrate into your current environment.