MDX Union? Use Subreports...

SQL Server

Another interesting question came up in a recent SQL Server Business Intelligence class: "Can I perform a UNION in MDX similar to SQL?". There is a UNION operator but it's not quite the same, so we had to think outside of the box for this one...

The question came from a student who was trying to switch a complex report from running against the relational Data Warehouse to produce the same results against the multi-dimensional cube.

MDX is very different from SQL in that it is designed to retrieve "cells" of aggregation using measures, dimensions, tuples and sets. However, some constructs appear similar like SELECT, WHERE and UNION so it is tempting to try to recode SQL in MDX. Unfortunately, it is not as easy as that.

It turns out that the MDX UNION operator only operates on sets, and unlike SQL cannot be used to concatenate multiple MDX statements that produce a similar result set.

One solution we came up with was to use SQL Server Reporting Services. Each report allows the generation of an MDX statement in the Report Dataset using a drag-and-drop Query Designer similar to the Cube Browser. This was introduced in 2005 with great relief since we had to code MDX from scratch with the 2000 version.

You can even include parameters using the Sub-Cube area for filtering although the parameter check box is hidden away on the right. You can then switch from Design mode to MDX mode to finish the statement if you want to use some more complex operators. Once you are in MDX mode you cannot go back to Design mode but the editor warns you of this.

If you can design your report in a modular fashion you can then use the SubReport construct in a parent report. Each report runs individually with the results displayed on the parent. You can pass parameters from the parent to the subreports for dynamic queries. If you hide the headers in the subreports you can effectively concatenate result sets from multiple reports. And therefore, multiple MDX queries.

Clever, eh?

cheers
Brian

• Microsoft
• analysis services
• mdx
• reporting services
• SQL Server
• SQL Server 2008
• ssas
• ssrs