Hi,

I was wondering if anyone had an explination of why this query runs in
under 30sec:

SELECT
{
[VARIABLES].[CURRENT BACK ORDER UNIT],
[VARIABLES].[CURRENT BACK ORDER COST],
[VARIABLES].[CURRENT BACK ORDER RETAIL]
}
ON COLUMNS,
NON EMPTY{[PRODUCT].[FOUR DIGIT].Members} ON ROWS
FROM
[ORVIS MDS]
WHERE
(
[TIME].[WE 2004-04-02],
[CHANNEL].[ALL CHANNELS],
[DEMAND SOURCE].[ALL DEMAND SOURCES],
[VERSION].[TY ACTUAL],
[MEASURES].[VALUE]
)


While this one takes 3+ minutes

SELECT
{
[VARIABLES].[CURRENT BACK ORDER UNIT],
[VARIABLES].[CURRENT BACK ORDER COST],
[VARIABLES].[CURRENT BACK ORDER RETAIL]
}
ON COLUMNS,
{FILTER([PRODUCT].[FOUR DIGIT].Members,[VARIABLES].[CURRENT BACK ORDER
UNIT] >0)} ON ROWS
FROM
[ORVIS MDS]
WHERE
(
[TIME].[WE 2004-04-02],
[CHANNEL].[ALL CHANNELS],
[DEMAND SOURCE].[ALL DEMAND SOURCES],
[VERSION].[TY ACTUAL],
[MEASURES].[VALUE]
)

The [VARIABLES] members are all pre-aggregated, no calculated measures
used.

Thanks.

Looks like there may be a lot of empty [PRODUCT] members in the "Where"
slice. The OLAP server keeps track of pre-aggregated non-empty members,
so the NON EMPTY clause (in this case) is presumably optimized. No
iteration over all
[PRODUCT].[FOUR DIGIT] members may be occurring.

However, the Filter() will require iteration over all
[PRODUCT].[FOUR DIGIT] members, to test for > 0 (which
can't be assumed to be equivalent to NON EMPTY). With a large member
set, this can consume more memory and time.
So, you boost performance by leveraging set sparsity.


- Deepak

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