Hi,

There are two aggregate member:
1) MEMBER [CloseFactTime].scope AS
'Aggregate({[CloseFactTime].[2002].[Quarter
3].[July]:[CloseFactTime].[2004].[Quarter 3].[August]})'

2) MEMBER [CloseFactTime].scope AS
'Aggregate({[CloseFactTime].[2002].[Quarter
3].[July].[1]:[CloseFactTime].[2004].[Quarter 3].[August].[31]})'

The first one makes the query execution time 3 seconds, while the second one
makes the query execution time 30 seconds.
They return the same result.

When I build the MDX query string, I had to write the query like this:
[Time].[2001].[Q1].[Feb].[18] : [Time].[2001].[Q1].[Feb].[28],
[Time].[2001].[Q1].[MARCH],
[Time].[2001].[Q2] : [Time].[2001].[Q4],
[Time].[2002] : [Time].[2003],
.......
......

This will make the query longer, but I think it will be faster.

When I build the query in my program, I had to check if the day is in the
[Time] dimension. For example, if [Time].[2001].[Q1].[Feb].[18] is not in
the [Time] dimension, I had to skip a day.

This will make a lot of code to perform the check operation, and it spend
some time I think. In other dimension, it is the same case.

How can I improve the check operation?


Thanks,

the query 2 aggregate at the day level and the query 1 at the month level.
its why the second is slower.

but can you explain what you want to do?



"Charlie Tong" <wentschao (AT) msn (DOT) com> a écrit dans le message de news:
eNRnOH8jEHA.3696 (AT) TK2MSFTNGP15 (DOT) phx.gbl...

The [Time] dimension is like this:
[Time].[2004].[Q1].[August].[1],
[Time].[2004].[Q1].[August].[3],
[Time].[2004].[Q1].[August].[4],
[Time].[2004].[Q1].[August].[6]
....

That is, not all continuous days in the [Time] dimension, If i put
[Time].[2004].[Q1].[August].[5] in my MDX query, it will throw an exception.
So when I build my MDX query, I have to check if
[Time].[2004].[Q1].[August].[5] is in the dimension. If so, add it in my mdx
query. If not, omit it.

Check if something is in the dimension spends a lot of code and some time.
Is there any way to overcome it?

Thanks,


<Jéj? <willgart (AT) BBBhotmailAAA (DOT) com>> wrote

why don't you fill completly your time dimension?
does this is a problem for you?

if you can't fill the dimension...
maybe, in your code you can run a query to list the days available for the
month selected :
Time.2004.Q1.August.Members
then in your code, cycle through the result to find the day (or the day
before the selected day) and then replace the selected day by a existing day
in the dimension.

Also, maybe you can use member properties.
Add a property at the day level called "Date".
Convert the selected day of the user to a real date value
[Time].[2004].[Q1].[August].[5] become : 2004/08/05
then, in your MDX query create a formula like this:
order(filter(time.daylevel.members, time.currentmember.properties("Date") <=
<Selected user date>, time.currentmember.properties("Date") ,desc ).item(0)
(this formula is not tested !!!)
Maybe the topcount can be used instead-of the order function.

good luck !

"Charlie Tong" <wentschao (AT) msn (DOT) com> a écrit dans le message de news:
u7sgSM%23jEHA.140 (AT) TK2MSFTNGP12 (DOT) phx.gbl...