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
MDX query based on a subquery
nattylife
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 1from
[Adventure Works]where
NonEmpty([Customer].[Customer Geography].[Full Name]* {[Date].[Calendar].[Date].&[915]},
{([Product].[Product Categories].[Category].&[3],
[Measures].[Internet Sales Amount])})
>>
SGriffiths
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-
KristenB
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 COLUMNSFROM
(
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