Hi,

I have a question regarding the way AS generates a "set". Shown below is
snippet 1, part of a query that aims to generate the ranking of reps based
on monthly sales and/or revenue.

Snippet 1.
....
SET [SetRepsWithMonthSales] AS '(FILTER([Rep].[RepName].Members, (Time.[All
Time].[2004].[January], [Measures].[Sales]) > 0))'

SET [SalesOrderedReps] AS '(ORDER([SetRepsWithMonthSales], (Time.[All
Time].[2004].[January], [SalesAmount]), BDESC))'
SET [RevenueOrderedReps] AS '(ORDER([SetRepsWithMonthSales], (Time.[All
Time].[2004].[January], [Revenue]), BDESC))'
......

My question about the statement is:

- When [SetRepsWithMonthSales] is generated (the first line), is the
resulting set "cached" (stored in the memory)? If no, does that mean that
in the statement that generates [SalesOrderedReps] and [RevenueOrderedReps]
sets still has to do the "FILTERing".

- Is there an alternative to the filter function, any statement that offers
performance boost.

FYI. Whenever I run the query, the counter AS:Agg Cache - Misses/sec just
goes high up the perf chart.

TYIA.
Wolve

If the intent of the Filter() is to select [RepName] members that have
Sales data in [2004].[January], then NonEmptyCrossJoin() should enhance
performance:

[Rep].[RepName].Members,
{Time.[All Time].[2004].[January]},
{[Measures].[Sales]}, 1)'

- Deepak

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

DP, thanks so much. I really appreciate your response and I will certainly
try it.

BTW, I forgot to mention that I am generating the result from a virtual cube

Flashback, snippet 1.
....
SET [SetRepsWithMonthSales] AS
'NonEmptyCrossJoin([Rep].[RepName].Members,{Time.[All
Time].[2004].[January]}, {[Measures].[Sales]}, 1)'

SET [SalesOrderedReps] AS '(ORDER([SetRepsWithMonthSales], (Time.[All
Time].[2004].[January], [Sales]), BDESC))'

SET [RevenueOrderedReps] AS '(ORDER([SetRepsWithMonthSales], (Time.[All
Time].[2004].[January], [Revenue]), BDESC))'
....

Since the Sales measure comes from a different source cube (Sales cube) than
the Revenue measure (Revenue cube), do you think that this could also be a
factor in the slowness of the query? Note that when [RevenueOrderedReps] is
generated, it has to "cross" to get the reps with Sales (for January) from
the Sales cube, OR does it?

Again, TYIA.
Wolve

"Deepak Puri" <deepak_puri (AT) progressive (DOT) com> wrote

To allow for a virtual cube, I explicitly specified [Measures].[Sales]
in the NonEmptyCrossJoin(). Of course, the resultant set is then also
ordered by a measure from the other cube, namely [Revenue]; and I'm not
sure whether every RepName with [Sales] data also has [Revenue] data.

I suspect that the speed of the query will depend on the performance of
each physical cube, for the respective ordered set. But it should not be
adversely affected by their combination into a virtual cube.


- Deepak

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!