Reverse Engineering SSAS Reports

MDX is not SQL. It may look like it has SELECT/FROM/WHERE clauses, but god help you if you start drawing parallels to your standard TSQL query.

This is an exercise I had to go through recently, because A) the reports in question were deployed in SSRS but used an SSAS backing, i.e. cubes, and the source queries (MDX) were not stored in source-control, and B) I don’t write MDX queries.

a basic MDX query example with labeled parts
shamelessly borrowed from an excellent article

The outline:

  1. Run Profiler or XEvents against the SSAS server
    1. set to capture “Query Begin” events only, with “Event Subtype = 0” (for MDX query)
    2. optionally, set filter on NTUserName to the dedicated SSRS account (if you have it set up that way)
  2. Run the SSRS report(s) that you want to dive into
  3. For each event in the Trace, copy-paste the MDX query to a new MDX editor window
  4. SSRS parameter substitution happens via some XML at the bottom; but in MDX, the parameters are standard @params like in T-SQL.  So we need to manually substitute our parameter values.
    1. 2 blocks of XML: the “Parameters”, and the “PropertyList” — delete the latter.
    2. In the former, text-replace & for simply & .
    3. Side-bar: You’ll notice that the MDX parameters are usually inside STRTOMEMBER() or STRTOSET(), which are built-in MDX functions that do exactly what they sound like — parse a string into a dimension’s attribute’s member or set of members.  That’s why they’ll usually have at least 3 .‘s (dots) — Dimension.Attribute.&MemberValue, for example.  I’m grossly oversimplifying that because it’s beyond the scope of this post, but read the docs if you need more gritty details.
  5. For each parameter node:
    1. Copy/cut the <Value> node content (I like to ‘cut’ because it helps me keep track of which ones I’ve done already)
    2. Find-and-Replace @ParameterName with that Value node’s content, surrounded in single-quotes
    3. Example: we have parameter ​@ReportDate (in MDX), corresponding to <Parameter><Name>ReportDate</Name> in XML, with <Value xsi:type="xsd:string">[Some Dimension].[Some Attribute].[Some sub-attribute].&[2017-05-01T00:00:00]</Value> — where that last bit is a standard SQL datetime literal.
    4. So you replace @ReportDate with '[Some Dimension].[Some Attribute].[Some sub-attribute].&[2017-05-01T00:00:00]' .
  6. Delete the XML block.
  7. Boom, now you have a valid MDX query that you can run and view results.

Why do this?  Well, it can help you learn MDX from a working example, instead of from super-basic dummy examples.  That’s not always a good learning style — you should still learn the fundamentals of MDX and why it’s so very different from SQL.  Especially if you’ll be responsible for writing and maintaining more than a few MDX queries.  But, in a pinch, if you need to start somewhere, and possibly all that the MDX / overlaying report needs is a slight tweak, this may be enough to get you going.

Lessons learned:

  • Profiler can still be a useful tool, despite some people’s attempts to kill it.
  • MDX is not SQL.  It may look like it has select, from, and where clauses, but god help you if you start drawing parallels to your standard TSQL query.
  • SSRS does parameter-passing in an odd way.
  • SSAS & MDX are fascinating and I need to learn more about them!
Canadians, eh?