Performance in Time-aware MDX functions 
Author Message
 Performance in Time-aware MDX functions

Hi,

I've discovered that i have a significant performance degradation due to
calculated members containing time-aware MDX-functions. Without these
calculated members, AS responses are instantaneous. But with these
calculated members the response time is 3 seconds. I use the following
calculated members:

1. "YTD":     sum(PeriodsToDate([Period].[?r],
[Period].CurrentMember),[Measures].[Belopp])
2. "Last year":     sum({parallelPeriod([Period].[?r], 1)},
[Version].[Version].&[Utfall])

The parent dimension for Last year is "version". "?r" is year in swedish.

The Period dimension is based on a database-table, ie it's not using the
built-in time dimension in AS. The Cube is partitioned by time. I use a
two-way box with 4 GB memory.

Do you have any idea of how to increase performance without decreasing
functionality ?

:-)



Sun, 29 Feb 2004 14:35:51 GMT
 Performance in Time-aware MDX functions

Hej Mikael,
We have encountered performance issues on this.  Although I'm not sure of
any solution that will help, you may want to try using the lag function
instead of the ParallelPeriod function (if your dimension structure permits
this).
E.g. we use lag(12) to get the same month in the previous year.  If I recall
correctly it has a slight performance improvement.
I'd be interested to hear your results.
Regards,
Andreas


Quote:
> Hi,

> I've discovered that i have a significant performance degradation due to
> calculated members containing time-aware MDX-functions. Without these
> calculated members, AS responses are instantaneous. But with these
> calculated members the response time is 3 seconds. I use the following
> calculated members:

> 1. "YTD":     sum(PeriodsToDate([Period].[?r],
> [Period].CurrentMember),[Measures].[Belopp])
> 2. "Last year":     sum({parallelPeriod([Period].[?r], 1)},
> [Version].[Version].&[Utfall])

> The parent dimension for Last year is "version". "?r" is year in swedish.

> The Period dimension is based on a database-table, ie it's not using the
> built-in time dimension in AS. The Cube is partitioned by time. I use a
> two-way box with 4 GB memory.

> Do you have any idea of how to increase performance without decreasing
> functionality ?

> :-)



Sun, 29 Feb 2004 22:40:40 GMT
 Performance in Time-aware MDX functions
Hi Andreas,

Thanks for your answer.

The use of lag(12) is not applicable to the Quartal and Year - levels. So i
tried to write some iif-clauses to determine the actual level:

iif([Period].currentmember.level.name="M?nad",
 sum({[Period].currentmember.lag(12): [Period].currentmember},
[Version].[Version].&[Utfall]),
 iif([Period].currentmember.level.name="Kvartal",
  sum({[Period].currentmember.lag(4): [Period].currentmember},
[Version].[Version].&[Utfall]),
   sum({[Period].currentmember.lag(1): [Period].currentmember},
[Version].[Version].&[Utfall])))

But this MDX seems to be as slow as the original MDX.

How did you manage the levels above month ?

:-) Michael



Quote:
> Hej Mikael,
> We have encountered performance issues on this.  Although I'm not sure of
> any solution that will help, you may want to try using the lag function
> instead of the ParallelPeriod function (if your dimension structure
permits
> this).
> E.g. we use lag(12) to get the same month in the previous year.  If I
recall
> correctly it has a slight performance improvement.
> I'd be interested to hear your results.
> Regards,
> Andreas



> > Hi,

> > I've discovered that i have a significant performance degradation due to
> > calculated members containing time-aware MDX-functions. Without these
> > calculated members, AS responses are instantaneous. But with these
> > calculated members the response time is 3 seconds. I use the following
> > calculated members:

> > 1. "YTD":     sum(PeriodsToDate([Period].[?r],
> > [Period].CurrentMember),[Measures].[Belopp])
> > 2. "Last year":     sum({parallelPeriod([Period].[?r], 1)},
> > [Version].[Version].&[Utfall])

> > The parent dimension for Last year is "version". "?r" is year in
swedish.

> > The Period dimension is based on a database-table, ie it's not using the
> > built-in time dimension in AS. The Cube is partitioned by time. I use a
> > two-way box with 4 GB memory.

> > Do you have any idea of how to increase performance without decreasing
> > functionality ?

> > :-)



Mon, 01 Mar 2004 16:26:57 GMT
 
 [ 3 post ] 

 Relevant Pages 

1. Performance of Dynamic Time-Series MDX Query

2. MDX time functions

3. Data-aware controls that be aware of nothing

4. mutibyte aware functions

5. Delphi 3 DB Aware Components Making BDE Function Calls

6. Netware-aware FoxPro functions

7. Anyone Know a Data Aware Date Time Picker

8. Interbase, performance and data-aware controls

9. Data aware time component

10. Data-aware time component?

11. Hack: Moving data-aware components at run-time to other forms

12. TIME Aware Problem


 
Powered by phpBB® Forum Software