MDX query based on a subquery

Hi,

I hope that some can help me with a small problem I have got. I am new to data warehousing and MDX and am wondering if it is possible to construct a query based on a subquery (something like a T-SQL IN Clause).

Want I want to do is take all the customers who has looked at one product category (ex Dairy products) and see what else they have looked at. I guess what I want to do is create some sort of a filter to be able to only take those users from the measure and slice them by the product dimension again.

I am using ProClarity and will also need to be able to use it in there.

I am thankful for any help or any pushes in the right direction :-).

Kind Regards

Stefan Ghose

Hi Stefan,

Here are 2 sample Adventure Works queries, which identify the customers who bought Clothing category products on Jan.1,2004, and their purchases in other product categories on the same day:

>>

-- Customers who bought Clothing on Jan.1,2004:

select

Non Empty [Product].[Product Categories].[Category] on 0,

NonEmpty([Customer].[Customer Geography].[Full Name]

* {[Date].[Calendar].[Date].&[915]},

{([Product].[Product Categories].[Category].&[3],

[Measures].[Internet Sales Amount])}) on 1

from [Adventure Works]

where [Measures].[Internet Sales Amount]

-- Purchases in each Category on Jan.1,2004, by Customers who bought Clothing:

select {[Measures].[Internet Sales Amount]} on 0,

Non Empty [Product].[Product Categories].[Category] on 1

from [Adventure Works]

where NonEmpty([Customer].[Customer Geography].[Full Name]

* {[Date].[Calendar].[Date].&[915]},

{([Product].[Product Categories].[Category].&[3],

[Measures].[Internet Sales Amount])})

>>

|||

Thank you soo much for your help Deepak! This was exactly what I was looking for! Now I can use this to try to make a dynamic Measuregroup that I can use in ProClarity...

-Stefan Ghose-

|||

I have found the exact answer that I was looking for. To be able to create a query that is based on a subquery you only need to select write the subquery in the FROM clause i.e

SELECT

NONEMPTY([User Dimension].[All].CHILDREN, [Measures].[Count]) ON ROWS,

[Measures].[Count] ON COLUMNS

FROM

( SELECT

NONEMPTY ([User Dimension].[All].CHILDREN) ON COLUMNS

FROM

[Cube]

WHERE

(

[Measures].[Count],

[Slicer Dimension].[Name].&[Value],

[Date Dimension].[Year - Month].[Year].&[2006].&[11]

)

)

WHERE

(

[Date Dimension].[Year - Month].[Year].&[2006].&[11],

[User Dimension.CHILDREN

)

An example of this can be found here:

http://sqljunkies.com/WebLog/hitachiconsulting/archive/2004/09/21/4295.aspx