INTERSECT MDX Query - Reg

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