Chris Webb's BI Blog

Analysis Services, MDX, PowerPivot, DAX and anything BI-related

Joining the results of two MDX queries together

with 24 comments

One question I get asked occasionally is whether it’s possible to join the results of two MDX queries together. Although I seem to remember this kind of functionality is mentioned in the OLEDB for OLAP spec it certainly isn’t supported in Analysis Services MDX and I don’t expect it ever will be; therefore, as all good consultants know, when you’re faced with a request for functionality that doesn’t exist what you have to do is look closely at the requirement to see if there’s a different way of solving the problem to get the result the customer wants…

What people usually want to do when they think about joining MDX queries is this: they want to create a query that shows members from two different hierarchies side-by-side on the same axis. For example, in Adventure Works you might want to see a query with Calendar Years on Rows and Countries followed by Product Categories on Columns, something like this:
           

  Australia Canada Bikes Clothing
CY 2002 $2,154,284.88 $621,602.38 $6,530,343.53 (null)
CY 2003   $3,033,784.21 $535,784.46 $9,359,102.62 $138,247.97

It’s clear we can get the results we need by running two different queries, as follows:

SELECT {[Customer].[Country].&[Australia],[Customer].[Country].&[Canada]}  ON 0,
{[Date].[Calendar Year].&[2002],[Date].[Calendar Year].&[2003]} ON 1
FROM [Adventure Works]
WHERE([Measures].[Internet Sales Amount])

SELECT {[Product].[Category].&[1],[Product].[Category].&[3] }  ON 0,
{[Date].[Calendar Year].&[2002],[Date].[Calendar Year].&[2003]} ON 1
FROM [Adventure Works]
WHERE([Measures].[Internet Sales Amount])

Depending on the tool we’re using, we could try to put the results next to each other to make them more easily comparable. What we can’t of course do is something like the following query:

SELECT {[Customer].[Country].&[Australia],[Customer].[Country].&[Canada],[Product].[Category].&[1],[Product].[Category].&[3]}  ON 0,
{[Date].[Calendar Year].&[2002],[Date].[Calendar Year].&[2003]} ON 1
FROM [Adventure Works]
WHERE([Measures].[Internet Sales Amount])

This will result in the following error message:
Members belong to different hierarchies in the  function.
…for the very good reason that we have violated one of the fundamental rules of MDX – a set has to contain members of the same dimensionality, and here we have a set containing Countries and Product Categories.

What can we do to make the query work? Well, there is a simple MDX solution: create a set of tuples containing Countries and Product Categories:

SELECT
{
CROSSJOIN({[Customer].[Country].&[Australia],[Customer].[Country].&[Canada]},{[Product].[Category].[All Products]}),
CROSSJOIN({[Customer].[Country].[All Customers]},{[Product].[Category].&[1],[Product].[Category].&[3]}) 
}
ON 0,
{[Date].[Calendar Year].&[2002],[Date].[Calendar Year].&[2003]} ON 1
FROM [Adventure Works]
WHERE([Measures].[Internet Sales Amount])

What I’ve done here is created a set using two Crossjoins. The first returns a set containing the Countries we want crossjoined with the All Member from Product Categories; the second returns a set containing the All Member from Countries crossjoined with the Product Categories we’re interested in; we can then union them together and use them on the same axis because the tuples in the set have the same dimensionality, ie (Country, Product Category). Here’s what you get back:

image

It’s not quite what we wanted, but it’s all the data we need in a single query and we can probably get the user to ignore the All Members, or possibly hide them in the client tool somehow. The only problem with this approach is that it becomes unwieldy the greater the number of different hierarchies we want to display on columns.

If we’re using SSRS 2008 to display the results of our query, there’s another possible approach: we can use the new Tablix control to create the style of layout we’re after instead quite easily. You need to start by using the query designer and paste in a version of the query above with Years, Countries and Product Categories on Rows and Internet Sales Amount on columns:

SELECT
[Measures].[Internet Sales Amount] ON 0,
{
CROSSJOIN({[Customer].[Country].&[Australia],[Customer].[Country].&[Canada]},{[Product].[Category].[All Products]}),
CROSSJOIN({[Customer].[Country].[All Customers]},{[Product].[Category].&[1],[Product].[Category].&[3]}) 
}
*
{[Date].[Calendar Year].&[2002],[Date].[Calendar Year].&[2003]} ON 1
FROM [Adventure Works]

You then create a new matrix, drop Calendar Year onto the row group, Internet Sales Amount into the Data area, Country onto column group, then right click on the rightmost column and select Add Group->Column Group->Adjacent Right, to create a new column group, set it to group by Product Categories and again drop Internet Sales Amount into the data area:

image

Then, for each Column Group you need to make sure that you don’t see aggregated values for the All Members (which of course in SSRS are returned not with the All Member’s name, but with blank names); You do this by setting a filter on each group property, using an expression like:
=Fields!Country.Value IS Nothing
In this case [Country] is the name of the Country in the report, and if this expression returns False we have a Country name and we’re therefore not looking at the All Member.

Anyway, you then get an output like this, which is what we wanted:

image

Here’s one last impractical but fun way to solve the problem. While playing around with DMX recently it occurred to me that the SHAPE statement could also be useful in solving this problem, and a lot of help on the syntax from my friend and DMX (as well as SSIS) guru Mr Allan Mitchell, I came up with the following:

SELECT FLATTENED t.*
FROM
                [Sequence Clustering] — arbitrary just has to be a mining model
NATURAL PREDICTION JOIN
SHAPE
{
SELECT {[Customer].[Country].&[Australia],[Customer].[Country].&[Canada]}  ON 0,
{[Date].[Calendar Year].&[2002],[Date].[Calendar Year].&[2003]} ON 1
FROM [Adventure Works]
WHERE([Measures].[Internet Sales Amount])
}
APPEND
(
                {
SELECT {[Product].[Category].&[1],[Product].[Category].&[3] }  ON 0,
{[Date].[Calendar Year].&[2002],[Date].[Calendar Year].&[2003]} ON 1
FROM [Adventure Works]
WHERE([Measures].[Internet Sales Amount])
                }
RELATE [[Date]].[Calendar Year]].[Calendar Year]].[MEMBER_CAPTION]]]
TO [[Date]].[Calendar Year]].[Calendar Year]].[MEMBER_CAPTION]]]
) AS MyNestedTable as t

image

To get this to work you just need to have a mining model in your SSAS database so you can put it in the FROM clause; it doesn’t matter what it is because it’s going to be ignored. I was able to join the queries on the MEMBER_CAPTION field from [Date].[Calendar Year], which contained the names of the Years on rows, although it was a struggle to work out how and where to add all the extra opening and closing square brackets that are needed in the RELATE clause! Notice, though, that we can just paste the MDX queries we need in there – usually SHAPE is used with OPENQUERY, but of course even though this is a DMX query we’re staying within the same SSAS database to get the data so that’s not necessary. Not the most elegant solution, of course, but interesting nonetheless.

Written by Chris Webb

May 20, 2009 at 1:18 pm

Posted in MDX

24 Responses

Subscribe to comments with RSS.

  1. It\’s situations like this that the flexibility of Excel\’s CUBEMEMBER() and CUBEVALUE() formulas come in handy :)

    Kory

    May 20, 2009 at 4:02 pm

  2. cool — very nice stuff, Chris!!!

    cosmini

    May 20, 2009 at 4:37 pm

  3. I didnt\’ get the DMX part, but the MDX was greatSo simple, so great

    Miky Schreiber

    May 20, 2009 at 9:08 pm

  4. Thanks for the SSRS 2008 tip! I just posted the following to the SSRS community forum and haven\’t received a reply and I just stumbled on your website. I need to create a tabular report using the SSAS 2005 cube that has multiple Dimension as Columns and Rows, but NOT Nested.i.e., Quad GenderFavorite Color MU25 MO25 FU25 FO25 M F – Red 25% 30% 3% 40% 50% 60% – White 50% 65% 77% 50% 40% 30% – Blue 25% 5% 20% 10% 10% 10%Favorite Transport – Car 90% 70% 80% 80% 90% 85%- Bike 10% 30% 20% 20% 10% 15%I idea how to have multiple dimensions in the rows? P.S., I second Kory Skistad\’s point. Why doesn\’t SSRS have CUBEMEMBER and CUBEVALUE. It is exactly what I am looking for.

    Jason

    May 26, 2009 at 6:50 pm

  5. Hi Jason – can you give me some more details of what you want to do? It sounds like it\’s pretty much the same as what I describe in the post here.

    Chris

    May 26, 2009 at 10:43 pm

  6. Chris – you can use the following query to get similar results. The drawback is that for each member on 0, a calculated member needs to be createdwithmember Australia as Aggregate([Customer].[Country].&[Australia], [Measures].[Internet Sales Amount])member Bikes asAggregate([Product].[Category].&[1], [Measures].[Internet Sales Amount])select {Australia, Bikes} on 0, {[Date].[Calendar Year].&[2002],[Date].[Calendar Year].&[2003]} ON 1FROM [Adventure Works]

    Konstantin

    July 23, 2009 at 8:30 pm

  7. This is a very interesting approach. What would your thoughts be if I was interested in querying 2 different cubes and placing the results side by side?

    Colin

    August 26, 2009 at 1:23 am

  8. I\’d say that if you need to combine data from two different cubes, you shouldn\’t have two different cubes but one big one!

    Chris

    August 26, 2009 at 9:50 pm

  9. Is this possible for two sets with similar structure and having same or different date ranges on coulmns? The date columns there are dynamic in the sets. Also, i need to pass from and to date to each of the sets. How to accomplish this?Something like SELECT {CROSSJOIN({STRTOMEMBER(2009-10-12, CONSTRAINED) : STRTOMEMBER(2009-10-12, CONSTRAINED)}),CROSSJOIN({STRTOMEMBER(2009-10-10, CONSTRAINED) : STRTOMEMBER(2009-10-10, CONSTRAINED)}) }ON 0,{[Websites].[URL].[Station].ALLMEMBERS} ON 1FROM [DataStore]WHERE ([Measures].[AverageCollection])

    Valmik

    October 12, 2009 at 12:55 pm

  10. Hi Valmik, I\’m not sure exactly what you want to do here… can you be more specific?

    Chris

    October 16, 2009 at 10:20 am

  11. Thanks Chris for the response. Here is some specification of the issue.. I have two different sets(independent MDX queries), having different values but same structure(Columns). I have to perform join of these two result sets based on one column. That means, I should get the values from the two sets horizontally if value of this column matches. And if there is no match of the value, the empty value should be matched.Two independent MDX Queries are having identical design execpt the supplied filter(parameters).

    Valmik

    October 26, 2009 at 9:00 am

  12. Chris Thanks so much. This helped me solve a real head scratcher. But I also need to get this to work for three hierarchies. How would you do this for three different hierarchies instead of 2? I\’ve been experimenting and can\’t seem to get it to work.

    Lou

    January 8, 2010 at 9:07 pm

  13. Never mind Chris. I got it to work. I figured out I had to use nested crossjoins. Thanks again.

    Lou

    January 8, 2010 at 9:50 pm

  14. I have been looking for a solution that will allow me to slice on one of the measures. So, I thought the DMX query you talked about will allow me to do just that. However, when I tried to create a dataset in SSRS, it gave me the following error. I am new to BI so I may be doing something very stupid here.The operation has failed because of an error in the COM component (Microsoft® OLE DB Provider for Data Mining Services) Syntax error at line 1, offset 20, token \’*\’—————————-Query preparation failed.This is the DMX query:select flattened t.*from [Sequence Clustering]natural prediction joinshape{SELECT NON EMPTY {[Measures].[Measure1]} ON COLUMNS,NON EMPTY { [Location].[Level 03].ALLMEMBERS } ON ROWSFROM [cube] WHERE ([Customer Types].[R], [Product].[Data])}append({SELECT NON EMPTY {[Measures].[Measure2]} ON COLUMNS, NON EMPTY {[Location].[Level 03].ALLMEMBERS} ON ROWS FROM [cube]WHERE ([Customer Types].[R])}relate [[Location]].[Level 03]].[MEMBER_CAPTION]]]to [[Location]].[Level 03]].[MEMBER_CAPTION]]]) AS MyNestedTable as t

    Dorothy

    January 19, 2010 at 3:33 pm

  15. Hi Dorothy, if you need to slice by measures in a SSRS report take a look at: http://cwebbbi.spaces.live.com/blog/cns!7B84B0F2C239489A!412.entry

    Chris

    January 19, 2010 at 3:46 pm

  16. Hi Chris,
    I have two measure groups which are related to dimensions as below

    Fact Sales Actual – Dim_Product and Dim_Date

    Fact Sales KPI – Dim_Product_Hier and Dim_Date_Hier

    Dim_Product and Dim_Date are flat dimensional tables i.e. lowest level in Product will be Item – Department – Category while Day – week – Month – Year in Dim_Date. Granularity of Fact Sales Actual is Item, Day

    Dim_Product_Hier contains the the above mentioned product hierarchy in Parent child relationship, similarly Dim_Date_Hier. Reason being users will feed target sales for any combination of product-date hierarchy.

    My question is using MDX or calculated members can I achive a result set like below

    Department, Month, Sales Actual, Sales Target

    Thanks in Advance.

    Venky

    September 11, 2012 at 11:58 am

    • Hi Venky,

      You have a number of options. My feeling is that you should not have two versions of each dimension, and you should just have one Product and one Date dimension. If you get rid of the parent/child dimensions you’d need to have a lot of fact tables at each possible granularity which would be a pain to manage and you’d also need to use some MDX to get total target sales values; you might want to keep the parent/child dimensions and get rid of the flat dimensions, although this could have performance implications. Alternatively you could keep the two sets of dimensions and either use a referenced relationship, a many-to-many relationship or the LinkMember function to map the selection on one version of the dimension to the other.

      Chris

      Chris Webb

      September 11, 2012 at 12:08 pm

  17. plleae solve this error i am stukked here

    if i run this query in ssms i got the below error

    Either the user, domain\prashanthk, does not have permission to access the referenced mining model or structure, Sequence Clustering, or the object does not exist.

    please let me know what is exact error

    prashanth

    January 24, 2013 at 11:26 am

    • Can you post your query please?

      Chris Webb

      January 24, 2013 at 11:27 am

      • if i run this query in ssms i got the below error

        Either the user, domain\prashanthk, does not have permission to access the referenced mining model or structure, Sequence Clustering, or the object does not exist.

        please let me know what is exact error

        prashanthk

        January 24, 2013 at 11:28 am

  18. Hi chris,

    We have a SSRS report with a prod, monthand country prompts from a cube. in the report we want to show a report grouped by month and a seperate tablix for prod and country.some thing like this

    period 1
    prod
    Dim_1 dim_2 Sales_1 Sales_2

    Country
    Dim_1 dim_2 Sales_1 Sales_2

    period 2
    prod
    Dim_1 dim_2 Sales_1 Sales_2

    Country
    Dim_1 dim_2 Sales_1 Sales_2

    period 3
    prod
    Dim_1 dim_2 Sales_1 Sales_2

    Country
    Dim_1 dim_2 Sales_1 Sales_2

    i tried to write a single query with an or condition for prod and country but it is throwing error. i want to write 2 seperate queries and union them but thats throwing error. any idea about how to solve this?

    much appreciated in advance

    liam

    March 1, 2013 at 4:57 am

  19. Hi Chris,

    With my my mdx query below, I get this error (Two sets specified in the function have different dimensionality).
    What do you think seems to be the problem?
    Thanks!

    WITH MEMBER [Measures].RadarX___ AS ‘[Customer - First Order - Product Category].[Product - Category].CURRENTMEMBER.CHILDREN.COUNT’
    SELECT {[Measures].RadarX___} ON 0,
    {[Customer - First Order - Product Category].[Product - Category].&[-1],
    [Customer - First Order - Product Category].[Product - Category].&[2],
    [Customer - First Order - Product Category].[Product - Category].&[15],
    [Customer - Last Order - Product Category].[Product - Category].&[-1]} ON 1
    FROM [UDM]

    JO

    jasoc

    April 15, 2013 at 9:13 am

    • Strange – the query looks ok to me. Can you try without the single quotes around the calculated member definition? That might give you a more useful message if the error is in that part of the code.

      Chris Webb

      April 15, 2013 at 9:20 am


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 3,113 other followers

%d bloggers like this: