Hi Everyone, We are facing some problem in the cube particularly in INTERSECT function. Here are the details. Dimension Tables: DimTime 200601 200602 DimProduct 01 02 DimUser 101 102 103 FactTables: FactPlayer Time Product User 200601 01 101 200601 02 101 200601 01 102 Transact SQL Query: Select Count(*) from ( Select userid from FactPlayer where productId = 01 INTERSECT Select userid from FactPlayer where productId= 02 ) PlayerCount We want the same result from MDX query. Can you please guide us how to do it by using INTERSECT. Expecting your valuable reply. Regards Vijay
|
Hi Vijay,
You could solve this using the Intersect function, but you don't need to. Here's an example from Adventure Works showing all the Customers who bought products from two different subcategories (mountain bikes and caps):
select {[Measures].[Internet Sales Amount]} on 0,
nonempty(
nonempty(
[Customer].[Customer].[Customer].members,
([Measures].[Internet Sales Amount], [Product].[Subcategory].&[1])
)
, ([Measures].[Internet Sales Amount],[Product].[Subcategory].&[19])
)
on 1
from [Adventure Works]
What it's doing is using the nonempty function to return a list of Customers who bought products in subcategory 1, and then using another nonempty function to filter that list by those who bought products from subcategory 19. This, I think, will be more efficient than using the Intersect function although for the record here's the same query rewritten to use Intersect:
select {[Measures].[Internet Sales Amount]} on 0,
intersect(
nonempty(
[Customer].[Customer].[Customer].members,
([Measures].[Internet Sales Amount], [Product].[Subcategory].&[1])
)
,nonempty(
[Customer].[Customer].[Customer].members,
([Measures].[Internet Sales Amount],[Product].[Subcategory].&[19])
)
)
on 1
from [Adventure Works]
HTH,
Chris
|||
Hi Chris,
Thank you very much. It is working perfectly.
Vijay
|||
Hi Everyone,
We are facing some problem in the cube particularly in INTERSECT function.
Here are the details.
Dimension Tables:
DimTime 200601
200602
DimProduct 01
02
03
DimUser 101
102
FactTables:
FactPlayer
Time Product User
200601 01 101
200601 02 101
200601 01 102
200601 03 101
Transact SQL Query:
Select Count(*) from
(
Select user from FactPlayer where productId = 01
INTERSECT
Select user from FactPlayer where productId= 02
INTERSECT
Select user from FactPlayer where productId= 03
)
PlayerCount
RESULT: 1 (UserId: 101)
We want the same result from MDX query. Can you please guide us how to do it by using INTERSECT.
Expecting your valuable reply.
Regards
Vijay
|||
I found the solution below.
SELECT NON EMPTY{[Measures].[User ID Distinct Count]} ON COLUMNS,
INTERSECT
(
NONEMPTY
(
INTERSECT
(
NONEMPTY
(
[DIM USER].[DIM USER].CHILDREN,
([Dim Time].[TimeKey].&[200602],
[Measures].[User ID Distinct Count],
[DIM PRODUCT].[DIM PRODUCT].&[3])
),
NONEMPTY
(
[DIM USER].[DIM USER].CHILDREN,
([Dim Time].[TimeKey].&[200602],
[Measures].[User ID Distinct Count],
[DIM PRODUCT].[DIM PRODUCT].&[11])
)
)
),
NONEMPTY
(
[DIM USER].[DIM USER].CHILDREN,
([Dim Time].[TimeKey].&[200602],
[Measures].[User ID Distinct Count],
[DIM PRODUCT].[DIM PRODUCT].&[12])
)
)
ON ROWS
FROM [DSV KPI]
Please reply me if there any changes in the query.
Thank You
Vijay
