JSON Result From SSAS Cube

Question

I am new to working with Business Intelligence/Analysis Services and MDX queries. I am developing a web app that pulls/embeds reports from a report server (SSRS reports build upon this cube data), however the result is slow and the resulting look is lacking IMO. I am also generating chart widgets using the same method (looks even worse).

To find a new solution one task at a time, I have explored the possibility of generating the widgets (bar, pie, line chats etc...) with some really nice SVG javascript libraries. However, these libraries are wanting to be supplied data in JSON or XML (some other formats supported like CSV as well...).

I'd like to ditch my current use of the reporting widgets, and attempt to render my own charts based on this cube data. However, I am at a loss for gathering the data in the appropriate format. I'd prefer JSON to save file size, but XML will suffice. Any suggestions?

Answer 1

In fact, low level, all results that are returned from SSAS are in XMLA, which is XML. This XMl contains a lot of metadata information which you probably do not need.

To see the outcome of some MDX statements, you can run an XMLA query in Management Studio as follows: Open an XMLA window and enter your MDX enclosed in an XML Statement element like this:

<Statement>
  select [Date].[Calendar].[Calendar Year].Members
         on columns,
         [Sales Territory].[Sales Territory Country].Members
         on rows
    from [Adventure Works]
</Statement>

This will return the result as XML in so-called multidimensional format. There is also a tabular format which is more like a relational result set. You can get that by using the full syntax:

<Execute xmlns="urn:schemas-microsoft-com:xml-analysis">
  <Command>
    <Statement>
      select [Date].[Calendar].[Calendar Year].Members
             on columns,
             [Sales Territory].[Sales Territory Country].Members
             on rows
        from [Adventure Works]
    </Statement>
  </Command>
  <Properties>
    <PropertyList>
      <Catalog>Adventure Works DW 2008</Catalog>
      <Format>Tabular</Format>
    </PropertyList>
  </Properties>
</Execute>

If you change the <Format>Tabular</Format> to <Format>Multidimensional</Format>, you should get the same result as in my first code sample. In fact, Management Studio just surrounds the first code I showed above by some XML so that is appears similar to my second code example in order to save you some typing.

Some remarks:

• In your MDX code, you will have to escape some characters like & and < to make the request valid XML.
• The <Catalog>Adventure Works DW 2008</Catalog> part in the complete code is the name of the catalog, aka database, that you access.
• The documentatiion of the XMLA syntax can be found here: http://msdn.microsoft.com/en-us/library/ms186691.aspx