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:
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:
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:
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
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.
It\’s situations like this that the flexibility of Excel\’s CUBEMEMBER() and CUBEVALUE() formulas come in handy 🙂
cool — very nice stuff, Chris!!!
I didnt\’ get the DMX part, but the MDX was greatSo simple, so great
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.
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 – 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]
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?
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!
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])
Hi Valmik, I\’m not sure exactly what you want to do here… can you be more specific?
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).
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.
Never mind Chris. I got it to work. I figured out I had to use nested crossjoins. Thanks again.
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
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
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.
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
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
Can you post your query please?
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
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
Can you post the query that isn’t working?
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
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, you’re the man.
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.
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
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
So the measure [Number of answered comms] is a calculated measure? Can you tell me how it is defined?
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?
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
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?
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.
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
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
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
Example of the wanted result, where the global is the value provided by the customer :
Global 76
France 75
Italia 50
USA 100
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.
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
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.
Hi Chris,
I have a clarification, in my report, I am using three members as row level and another a related member in col level and related measure values shown in respective cell.
My requirement is for the first three member I need to group based on the second member, even if the first and the third member values are different. We need to take the lowest of seq#(Member 1) and member3 accordingly and show it in the report.
Measure values should be merged to a single line based on the Member 2 grouping.
=============================
Seq # Res Code Test Desc 185553 185822 187131 195135 195235 196080
480 RRV2FNL VIS AT 4.8% 51.92 1.81
590 RRV2FNL VISCOSITY AT 1.92 2.12 1.81 2.12
=============================
My requirement is to show this in a single row
=================================
Seq # Res Code Test Desc 185553 185822 187131 195135 195235 196080
480 RRV2FNL VIS AT 4.8% 51.92 1.92 2.12 1.81 1.81 2.12
==================================
Member Group based on Res Code and take the lowest seq# and respective Test Desc.
Here is my MDX query
SELECT NON EMPTY { [FACT_BLEND_TEST_RESULTS].[REPORT_RESULT_SORT].[REPORT_RESULT_SORT].ALLMEMBERS
* [DIM_RESULT].[RESULT_CODE].[RESULT_CODE].ALLMEMBERS
* [FACT_BLEND_TEST_RESULTS].[REPORT_RESULT_DESC].[REPORT_RESULT_DESC].ALLMEMBERS
* [DIM_RESULT].[RESULT_CATEGORY].[RESULT_CATEGORY].ALLMEMBERS } ON ROWS ,
NON EMPTY { [DIM_TESTKEY].[PERFORMANCE_TESTKEY].[PERFORMANCE_TESTKEY].ALLMEMBERS
* [FACT_BLEND_TEST_RESULTS].[INSPECTION].[INSPECTION].ALLMEMBERS } ON COLUMNS
FROM ( SELECT ( { [DIM_TESTKEY].[PERFORMANCE_TESTKEY].&[0000 185553], [DIM_TESTKEY].[PERFORMANCE_TESTKEY].&[0000 185822], [DIM_TESTKEY].[PERFORMANCE_TESTKEY].&[0000 187131], [DIM_TESTKEY].[PERFORMANCE_TESTKEY].&[0000 195135], [DIM_TESTKEY].[PERFORMANCE_TESTKEY].&[0000 195235], [DIM_TESTKEY].[PERFORMANCE_TESTKEY].&[0000 196080] } ) ON COLUMNS
FROM ( SELECT ( {[FACT_BLEND_TEST_RESULTS].[MAX_HOURS].[MAX_HOURS].&[1] } ) ON COLUMNS
FROM ( SELECT ( {[DIM_REPORT].[REPORT_CODE].[REPORT_CODE].&[TK]} ) ON COLUMNS
FROM [PTREPORTS_Tabular_Cube])))
where {[Measures].[Sum of TEST_RESULT_VALUE]}
Can you tell me from this MDX qry, how to modify to reach the expected result. I am a beginner in MDX, please help me to write this qry.
Thanks, please let me know if you need any further information.
Regards,
Joby
Hmm, it’s hard to say what you need to do here – I don’t quite understand your requirements.
SEQ# Res Code Res Desc 185553 185553 185553 185553
480 RRV2FNL Viscosity 3.4 2
590 RRV2FNL VISCOSITY AT 2.2 3.6
Expected Result
SEQ# Res Code Res Desc 185553 185553 185553 185553
480 RRV2FNL Viscosity 3.4 2.2 3.6 2
First Three Columns are Member columns, I wanted to group based on 2nd column and while grouping I need to user the lowest seq# and corresponding Description(Third column), Hope you understand my question.
It sounds like you need to remodel your dimension rather than try to solve the problem in MDX.
I am not able to put the Excel tables here,o I am not sure if you understand my query.
ok, Thanks for your replay Chris. Is there any work around for this issue. Project is already in production and we can’t think of re-remodel the DIM’s.
We have 3 cubes (SSAS Tabular) that have data/metrics for Accounts within our organization. The 3 cubes are Financials, Sales, Order2Cash. Each of them have several metrics/measures. I’m looking to show all these metrics side by side for each Account in table in a SSRS report. Is the best way to do that a LOOKUP in SSRS or can it be done at MDX level?
Really, what you should be doing is creating one SSAS Tabular model with all the data in. If you don’t, you will end up trying to hack data together for a long time to come! You can’t really merge the data in MDX (well, you can, but all the ways to do it will create other problems) and using a lookup in SSRS is also a bit manual for my liking, but probably the best solution if you can’t build a single model.