We have two time periods, for example July 2003 to Sept 2003, and Oct
2003 to Dec 2003. We want to find the number of new customers using
certain products in the second time period. The customers must not
have used the product in the first time period. The specific products
will be distributed on the columns, and we are also interested in
having end-user defined groups of products as well.

The pseudo code for the query is as follows:

1. Determine the set of customers in the first time period.

2. Determine the set of customers in the second time period.

3. Define calculated members to be used along the axis of the report:

3.1 A user-defined group of products.

3.1 The number of customers using the current product(s) in the first
time period.

3.3 The number of customers using the current product(s) in the second
time period.

3.4 The number of new customers using the current product(s) in the
second time period.

4. The report will be distributed by products on rows, and customer
counts on columns.

Our main challenge with this query is to make sure the sets and members
are evaluated in the correct time context.

For the query to execute as quickly as possible, we want to use a
separate time slicer for each of the two customer sets. The
WHERE-clause of the entire query seems to be unsuitable place in this
case since setting the slice to fit one set effectively makes the other
set empty. Calculated members must also be evaluated in their correct
time context. Again the WHERE-clause cannot be used.

For time-slicing the first_period_customer and second_period_customer
sets we use the NonEmptyCrossjoin function with the Crossjoin_Set_Count
less than the number of input sets. The extraneous sets then become
virtual time slicer-sets. To further limit the size of the sets, we
include the union of all products, too.

For time-slicing the calculated members (first_period_count,
second_period_count, and new_customers_count), we have come up with
some solutions; each with serious shortcomings:

1. Count the output of a NonEmptyCrossjoin operation.
NonEmptyCrossjoin allows us to include a time-slice, it is very fast,
but it also removes any calculated members from the crossjoined sets.
This means that any user-defined groups of products will be ignored.

2. Count the customer set with the ExcludeEmpty flag. This will
include the user-defined groups, but does not allow us to set the
correct time context for the calculation.

3. Use Filter. Response time goes up from 2-3 secs to 1 hour. This
response time is on our cube, and not the adventure works- cube. Filter
only accepts tuples, so in order to slice with a set of months; we need
to define it as a calculated member.

4. Use regular Crossjoin. This gives us an incorrect answer since it
does not remove duplicates.

Only the filter version gives us the correct numbers, while at the same
time supporting user-defined groups of products. However, response
time is unacceptable.

Can some kind of MDX subselect or subcube be used for setting the time
contexts?

Regards,

Tommy,
Christian
-----------------------------------
Sample MDX of the NonEmptyCrossjoin-version against the Adventure Works
sample database of AS2005.The simple count() is commented out in the
calculated measures.:

with
set first_period_customers as
nonemptycrossjoin(
{[Customer].[Customer Geography].[Full Name].members}
,{[Measures].[Customer Count]}
,{[Product].[Product Model
Categories].[Category].&[3],[Product].[Product Model
Categories].[Category].&[1]}
,{[Date].[Calendar].[Month].&[2003]&[7]:
[Date].[Calendar].[Month].&[2003]&[9]}
,2)


set second_period_customers as
nonemptycrossjoin(
{[Customer].[Customer Geography].[Full Name].members}
,{[Measures].[Customer Count]}
,{[Product].[Product Model
Categories].[Category].&[3],[Product].[Product Model
Categories].[Category].&[1]}
,{[Date].[Calendar].[Month].&[2003]&[10]:
[Date].[Calendar].[Month].&[2003]&[12]}
,2)

member [Product].[Product Model Categories].[Bikes and clothing] as
[Product].[Product Model Categories].[Category].&[3]--clothing
+[Product].[Product Model Categories].[Category].&[1]--bikes

member measures.first_period_count as
--count(first_period_customers, excludeempty)
count(
nonemptycrossjoin(
first_period_customers,
{[Product].[Product Model Categories].currentmember}
,{[Date].[Calendar].[Month].&[2003]&[7]:
[Date].[Calendar].[Month].&[2003]&[9]}
,1)
,excludeempty
)
member measures.second_period_count as
--count(second_period_customers, excludeempty)
count(
nonemptycrossjoin(
second_period_customers,
{[Product].[Product Model Categories].currentmember}
,{[Date].[Calendar].[Month].&[2003]&[10]:
[Date].[Calendar].[Month].&[2003]&[12]}
,1)
,excludeempty
)

member measures.[new_customers_count] as
count(except(
nonemptycrossjoin(
first_period_customers,
{[Product].[Product Model Categories].currentmember}
,{[Date].[Calendar].[Month].&[2003]&[7]:
[Date].[Calendar].[Month].&[2003]&[9]}
,1)

,
nonemptycrossjoin(
second_period_customers,
{[Product].[Product Model Categories].currentmember}
,{[Date].[Calendar].[Month].&[2003]&[10]:
[Date].[Calendar].[Month].&[2003]&[12]}
,1)

)
,ExcludeEmpty )

select
{
measures.first_period_count
,measures.second_period_count
,measures.[new_customers_count]
}on columns,
{
[Product].[Product Model Categories].[Category].&[3]--clothing
,[Product].[Product Model Categories].[Category].&[1]--bikes
,[Product].[Product Model Categories].[Bikes and clothing]
}
on rows
from [adventure works]