Wednesday, February 23, 2011

SSAS MDX Measures on Rows in SSRS

Sooner or later Reporting Services users who create reports against Analysis Services data will encounter one of the product limitations: you can't put measures on rows when you design your report. Or can you....??
For example, the following type of query is rejected by SSRS when designing a report:
    [Date].[Calendar Year].&[2007],
    [Date].[Calendar Year].&[2007].PrevMember
    [Measures].[Internet Sales Amount],
    [Measures].[Internet Tax Amount]
FROM [Adventure Works]
It's rejected because the measure names happen to be on ROWS rather than COLUMNS.  If you attempt to use this query, you'll receive the following error message:
Query preparation failed. The query cannot be prepared: The query must have at 
least one axis. The first axis of the query should not have multiple hierarchies, 
nor should it reference any dimension other than the Measures dimension.
Honestly, I've never really understood this limitation, since pivoting data is such a natural thing to do when you're working with Analysis Serivces.  The typical (frustrating) work around employed by those running into this problem is: use some other front-end application!!  Normally, that means Excel or PerformancePoint--both of which have no problem putting measures on rows.
But there is a way to make this work--it involves winning a data shell game with reporting services.  Specifically this technique involves querying the relational SQL Server engine for the MDX data using the OPENQUERY() command rather than querying SSAS directly.
Now, OPENQUERY() isn't available by default, and will require some system configuration by your DBA (if that's not you!) to work.  However, if it's something you can do, you'll be amazed at how elegantly it solves this problem.
Here are the steps to use OpenQuery to make the MDX above work in an SSRS report:
  1. Open up a SQL query window in Management studio, and give the SSAS server a logical name that can be used when users need to query MDX data through the SQL Engine
    EXEC sp_addlinkedserver
         @catalog='Adventure Works DW 2008R2'
  2. Make sure your report has a data source for the SQL Server instance where thesp_addlinkedserver command was run.  You'll be sending MDX to the relational database, and it will be a kind of proxy server to the SSAS engine on your behalf.
  3. Create your data set in SSRS (either using ReportBuilder or BIDS).  Select the data source for the relational engine, and then paste in your query enclosed within an OPENQUERY() function call like so:
    SELECT * FROM OpenQuery(
        MEMBER [Date].[Calendar Year].TY AS [Date].[Calendar Year].&[2007]
        MEMBER [Date].[Calendar Year].LY AS [Date].[Calendar Year].&[2007].PrevMember
          [Measures].[Internet Sales Amount], 
          [Measures].[Internet Tax Amount]
        ON ROWS,
          [Date].[Calendar Year].[TY],
          [Date].[Calendar Year].[LY],
        ON COLUMNS
        [Adventure Works]')
That's it!  Now you can continue to generate your report as if the data was just a SQL query--because that's really what it is.  The SQL Engine submits the query on the user's behalf, and then reformats it as a SQL response back to SSRS.
A couple things to note here:
  1. This configuration is elegant from the report designer's point of view, but it introduces more complexity for system administration, monitoring and performance tuning.  Don't make a habit of doing things like this--use it sparingly and only when needed.  Keep running MDX queries that SSRS will accept using native SSAS connections.
  2. Since the SQL engine will now be talking to the SSAS engine as a middle-man, there is the matter of security to think about.  If you're using Windows Integrated security, theOPENQUERY() call can pass your credential through to the SSAS instance.  Note, though, that you may need to delegate credentials using Kerberos if the relational and SSAS engines are on different servers. If you're running a distributed environment, you probably are familiar with this.  If you don't want to deal with it, you could install a minimal SQL instance on the SSAS box that just handles pass-through queries.  Or if you don't care about per-user authentication, you might look at sp_addlinkedsrvlogin to see if that's an option for you. 
  3. Note how I defined the [TY] and [LY] MEMBERS in query scope.  SSRS doesn't appreciate your column names changing after you design the report, so if you're putting dynamic content on columns as this query would do once the year is parametized, it's best to factor these things out of the query such as I did here--or alternatively by nesting OPENQUERY() inside a more complex SQL query and doing some column naming within the SQL query.  I like the WITH MEMBERmethod the best because it's more concise.
For further reference, there's an excellent KB on Microsoft's site that covers some of the details better than I have here, and I encourage you to read and bookmark it!

1 comment:

  1. Is it possible to rename the output column names within the SQL query when using this method? I would like to reference one of the columns in a case statement but I can't figure out how to do so.

    Something like [Measures].[MeasuresLevel].[MEMBER_CAPTION] AS MeasureName