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])})
>>
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
