Chris Webb's BI Blog

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

Joining the results of two MDX queries together

with 40 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

40 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

  20. Chris, you’re the man.

    Nick

    October 22, 2014 at 6:01 pm

  21. Hi
    I have 2 MDX queries from the same cube. Both use the same measure but with different time sets (both the time sets are same dimension but different hierarchies).

    I want to join them in the same table results, so it will present the 2 measures (cut by the different sets) and another time dimension (“Day of Week”) that also uses the same time dimension.
    The queries can be run separately as follows:
    with member [Measures].[AVG_6_WEEKS] as
    [Measures].[Number of Answered Comms] /6
    select
    nonempty([Comm Date UTC].[Day of Week].children)
    on 0,
    [Measures].[AVG_6_WEEKS]
    on 1
    from (select {LASTPERIODS( 42,[Comm Date UTC].[Year Month Day].lastsibling.lastchild.lastchild.lastchild.prevmember )}
    on 0 from comms)

    ;
    with member [Measures].[Answered Comms] as
    [Measures].[Number of Answered Comms]
    select
    nonempty([Comm Date UTC].[Day of Week].children)
    on 0,
    [Measures].[Answered Comms]
    on 1
    from (select {LASTPERIODS( 7,[Comm Date UTC].[Year Month Day].lastsibling.lastchild.lastchild.lastchild.prevmember )}
    on 0 from comms)

    Can it be done? I always get an error that I can’t use the same time hierarchies in the query…
    Any idea? Something like SQL were I can join 2 views?
    Thank you
    Yoni.

    Yoni

    November 3, 2014 at 9:26 am

    • It sounds like you need to use calculated measures something like this:

      with member [Measures].[AVG_6_WEEKS] as
      aggregate(LASTPERIODS( 42,[Comm Date UTC].[Year Month Day].lastsibling.lastchild.lastchild.lastchild.prevmember ),
      [Measures].[Number of Answered Comms])/6
      member [Measures].[Answered Comms] as
      aggregate(LASTPERIODS( 7,[Comm Date UTC].[Year Month Day].lastsibling.lastchild.lastchild.lastchild.prevmember ),
      [Measures].[Number of Answered Comms])
      select
      non empty
      [Comm Date UTC].[Day of Week].children
      on 0,
      {[Measures].[AVG_6_WEEKS]}
      on 1
      from comms

      Chris Webb

      November 4, 2014 at 12:54 am

      • Hi Chris,
        I tried that script but i got an error:
        Query (3,1) Aggregated functions cannot be used on calculated members in the measures dimension.

        the dimension: [Comm Date UTC].[Year Month Day] is a time hierarchy can it be done with a work around? maybe with another but different time dimension?

        thank you

        Yoni

        November 4, 2014 at 6:17 am

      • So the measure [Number of answered comms] is a calculated measure? Can you tell me how it is defined?

        Chris Webb

        November 4, 2014 at 10:30 pm

      • Hi Chris,
        yes, It’s a calculated measure, simple count with a filter that joins a dimension table as:
        ([Measures].[Number of Comms],[Response Code].[Response Codes].[Success Type].&[0])

        please see what I wrote to Prashanth – maybe it will help?

        Yoni

        November 5, 2014 at 8:58 am

      • If so, then try something like this:

        with member [Measures].[AVG_6_WEEKS] as
        aggregate({[Response Code].[Response Codes].[Success Type].&[0]} * LASTPERIODS( 42,[Comm Date UTC].[Year Month Day].lastsibling.lastchild.lastchild.lastchild.prevmember ),
        [Measures].[Number of Comms])/6
        member [Measures].[Answered Comms] as
        aggregate({[Response Code].[Response Codes].[Success Type].&[0]} * LASTPERIODS( 7,[Comm Date UTC].[Year Month Day].lastsibling.lastchild.lastchild.lastchild.prevmember ),
        [Measures].[Number of Comms])
        select
        non empty
        [Comm Date UTC].[Day of Week].children
        on 0,
        {[Measures].[AVG_6_WEEKS]}
        on 1
        from comms

        Chris Webb

        November 6, 2014 at 4:51 am

      • Hi Chris,
        Running this script resulted in a smeared results in each day I got the same number…
        And I tried playing with it a bit but it comes down to the same error as before:
        Aggregated functions cannot be used on calculated members in the measures dimension.

        Do you think I need a separated time dimension so I could cross join the measures and time?

        Yoni

        November 6, 2014 at 7:18 am

      • No, the way to solve this will be with calculated measures, not by changing the design of your cube (unless there’s something very wrong with the design of your cube – and it’s hard to say without seeing the cube). You just need to keep experimenting with the MDX.

        Chris Webb

        November 6, 2014 at 2:36 pm

    • 1) question U can join two mdx queris result set using linked server but I that query u have to run in relational database only

      2) same hierarchy 2 members can’t use same time but with descendants function u can do that

      But it Wil come in single column ,to attached to any grid then u will get 2 columns

      Send me ur dought

      Thanks Prashanth

      Sent with AquaMail for Android http://www.aqua-mail.com

      Prashanth Kothi

      November 4, 2014 at 2:28 am

  22. Thank you Prashanth,
    For (1) it’s not relevant in my case since i have to use olap and MDX only,
    and for (2) i’m not sure how DESCENDANTS will work in my case, can you please give me an example of the code you mean?

    Nonetheless, i do need 2 measures in one grid, the business reason is to see one measure as an average and the other as the last week members.

    thank you

    Yoni

    November 4, 2014 at 6:32 am

  23. Hello Chris
    I have a special query from customer.
    He wants in one chart bar to see the sales amount in percent for each country AND the average of sales amount for the all the country.
    The problem is that the average of sales amount is provide by the customer and not calculated. So I store this value in another fact table.

    It seams the join of several measures.
    Could you help me
    thanks

    Mourad

    November 6, 2014 at 4:48 pm

    • Example of the wanted result, where the global is the value provided by the customer :

      Global 76

      France 75
      Italia 50
      USA 100

      Mourad

      November 6, 2014 at 4:52 pm

    • Are your fact tables in two different cubes? If so, then they need to be in the same cube – once they are it will be easy to show the two values in the same query.

      Chris Webb

      November 7, 2014 at 6:33 am

      • No the fact tables are in the same cubes. I have a dimension country a dimension customer. one fact table for with relation with country and customer.
        And I have another fact table with the global value related only on the customer dimension.

        My model is a tabular model and I use MDX in SSRS.

        How would be the query look like ?
        Thanks

        Mourad

        November 7, 2014 at 8:08 am

      • The query will be straightforward – what you’ll need to do is to create a DAX measure calculation on your model that displays the country values at the country level and the average at the Global level. The series of blog posts that starts here http://javierguillen.wordpress.com/2012/05/02/scoping-at-different-granularities-in-dax-part-i/ should give you an idea of what you need to do; in general, though, I suggest you try to avoid importing values at different granularities. If you are unable to do this because you don’t have the raw data you need it usually suggests there’s something wrong with the underlying design of your data warehouse.

        Chris Webb

        November 7, 2014 at 2:33 pm


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,962 other followers

%d bloggers like this: