Hi All

The below query hangs, however if I remove the ([YearMonthDay].[Range])
in the where clause it atleast executes but doesn't hang.

Hangs
______

'WITH Member [ReferenceClassA].[tot] AS ''SUM(Filter({ Descendants
([ReferenceClassA].[All ReferenceClassA], [ReferenceClassA].[All
ReferenceClassA].Level, after)},
([ReferenceClassA].CurrentMember.Properties("Desc") <>
"Ignored"))))''
SET [Day Range] AS ''{[YearMonthDay].[Actual Date].[01/01/2004]
:[YearMonthDay].[Actual Date].[12/31/2004]}''
MEMBER [YearMonthDay].[Range] AS ''AGGREGATE([Day Range])''
SELECT {[MONTH].[Month Short Name].MEMBERS} ON ROWS,
{[ReferenceClassA].[tot]} ON COLUMNS
FROM [ACube] WHERE ([YearMonthDay].[Range])'


Doesn't hang and returns results
_________________________________

'WITH Member [ReferenceClassA].[tot] AS ''SUM(Filter({ Descendants
([ReferenceClassA].[All ReferenceClassA], [ReferenceClassA].[All
ReferenceClassA].Level, after)},
([ReferenceClassA].CurrentMember.Properties("Desc") <>
"Ignored"))))''
SELECT {[MONTH].[Month Short Name].MEMBERS} ON ROWS,
{[ReferenceClassA].[tot]} ON COLUMNS
FROM [ACube]'


Can anybody please help me out in figuring out why the date range
slicer hangs.

thanks in advance

Milind

I don't think the query is hanging, I think it's just taking a very long time
to complete! Summing up all those days in your date range is going to take a
long time, plus I'll bet that your second query (because it doesn't mention
the YearMonthDay dimension at all) probably hits aggregations whereas your
first query doesn't.

Since this is a fairly common problem I've just made it the subject of the
first entry of my new blog, which you can read here:
http://spaces.msn.com/members/cwebbbi/Blog/cns!1pi7ETChsJ1un_2s41jm9Iyg!107.entry

In your case, I think the approach of replacing the day members in your day
range set with common ancestors could solve the problem.

HTH,

Chris

"Milind" wrote:

Hi Chris,

Here's an alternative formulation that doesn't refer to the individual
time dimension levels explicitly, so it should work with various
hierachies. An assumption here is that 'MYRANGE' is at the leaf level of
the time hierarchy:

SET MYRANGE AS '{[Time].[1997].[Q1].[3]
:[Time].[1998].[Q3].[7]}'
/*Test whether Time member is a subset of range */
Member [Measures].[InRange] as
'Except(Descendants([Time].CurrentMember,, LEAVES),
MYRANGE).Count = 0'
/* Include all members in range whose parents are not in range */
SET MYNEWRANGE AS
'Filter([Time].Members, [Measures].[InRange]
AND Not ( [Measures].[InRange], [Time].Parent))'
/*Helper calculated members to display the steps*/
MEMBER MEASURES.STEP1 AS 'SETTOSTR(MYRANGE)'
MEMBER MEASURES.STEP5 AS 'SETTOSTR(MYNEWRANGE)'
/*Show working*/
SELECT {MEASURES.STEP1,
MEASURES.STEP5} ON 0
FROM SALES

Is there a way that this (common) pattern could be recognized/optimized
by the MDX query engine?


- Deepak

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

Hi Deepak,

Nice thinking! Definitely a lot easier to read than my MDX.

I agree, it would be good if the MDX engine could recognise these types of
situations, but I'm sure that things like calculated cells and custom rollups
probably make it a little risky - you have to be sure that the sum of your
set of months returns the same thing as the sum of a set of years, quarters
and months.

Regards,

Chris

"Deepak Puri" wrote: