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
sql
