PolymerCubed

I've recently started using Microsoft SQL Server Analysis Services for reporting purposes at work, and while querying the cubes worked great, the visualization options were rather unsatisfactory, especially when it came to building web-based dashboards.

And while there are several vendors offering solutions in this area, I decided to give it a try myself and started creating a suite of Polymer elements.

---

Step 1 - Setting up MSSAS for HTTP access

The necessary process is well documented. Essentially you use a provided DLL to setup IIS to access your cubes, and can then send MDX queries via a simple URL.

Reference: https://msdn.microsoft.com/en-us/library/gg492140.aspx

---

Step 2 - XMLA to JSON

The language of choice for communication across several OLAP providers is XMLA. While it is entirely possible (provided you can work around CORS issues) to query cubes using XMLA directly from JavaScript, I instead implemented a small backend in PHP that would send queries and convert the response to JSON for easier processing on the frontend.
A typical XMLA request with a MDX query looks like this.

This request can be sent via CURL to the URL created in Step 1...

...and then converted to JSON, stripping away some of the SOAP tags.

---

Step 3 - Simplify the JSON

The JSON you will get is a rather ugly, deeply nested construct, so to be able to use it more easily, e.g. in dom-repeat, it's best to simplify the JSON. Ideally you would do this already in the backend but while I'm trying to find the best format for my purposes I created a scary-mdx-data helper element that takes the raw JSON and transforms it into a set of arrays for row headers, rows, col headers, cols and cells. You can have a look at the scary-mdx-data demo to get an idea of what the final data looks like.

---

Step 4 - Display the Data

For actually displaying the data I started with the scary-mdx-table element with some basic drill-down functionality. The element also fires events when elements are selected which you can use for dynamic changes in the dashboard. E.g. in the scary-mdx-table demo I update two google-chart elements depending on which cell/row is selected in the table.
You could also use the events to query for additional data and splice in the results in the rows array, to prevent loading all the data at once.

---

Going forward / Disclaimer

The elements are still very much pre-alpha at this moment and the direction I will be taking with them, and what further elements I will develop, will very much depend on the business needs I will have to solve.
For now this is mostly a proof-of-concept to see what is possible in this area outside of available products you would have to pay for.