MDX performance on calculated measure

Hi,

i have two problems with the following mdx statement:

CALCULATE;

create member [Logistik_TakteReklaRedressen].[MEASURES].[delivery quantity] as null;

scope ([MEASURES].[delivery quantity]);

this = Sum([EVT Date].[HierWeek].currentMember.Children, [MEASURES].[delivery quantity]);

/*Non_Empty_Behavior(This) = {[MEASURES].[lfmenge1],[MEASURES].[lfmenge]};*/

end scope;

scope ([MEASURES].[delivery quantity], [EVT Date].[Week].[Week].Members);

this = Iif( Not (IsEmpty([MEASURES].[lfmenge1]) AND IsEmpty([MEASURES].[lfmenge])),

Aggregate(PeriodsToDate([EVT Date].[Week].[(All)], [EVT Date].[Week].currentMember), [MEASURES].[lfmenge1]) + [MEASURES].[lfmenge],

null);

/*Non_Empty_Behavior(This) = {[MEASURES].[lfmenge1]};*/

end scope;

My primary objective is to improve performance for any query with measure delivery quantity. Can the second scope statement with iif could better coded?

With the storage of weekly delta for one part of my data (lfmenge1), i can save about 90% of data sets in the fact table. Would it be better to store any data set for performance standpoints?

The second problem is that, when I have query with a where statement on time dimension and the time dimension is not on the axis. The first scope statement work not correctly.

Sorry for my broken english.

Regards Alexander

Hi Alexander,

Could you explain the logic for this calculated measure, with examples of data - that would help in making suggestions. Also, what are the attributes and structure of the [EVT Date] dimension - and is [Week] the key attribute or lowest level?

|||

Hi,

i work for a magazine publisher. We have weekly shipping from magazines for our subscribers. The delivery take place with post or mostly with our own delivery system.
The geographic structure on it's lowest level has a dimensionality of 40000. Every week we have an avervage supply quantity about 4 million.

I want to refresh the cube every week.

I separate the data in two fact tables. In the first table are the facts from the magazines with non weekly frequency (lfmenge). In this table I store every delivery (accumulated in the lowest level over all dimensions).
In the second table I store only changes and stock at beginning from the magazines with weekly frequency (lfmenge1). So I can save 90% of storage amount.

I have a hierarchy with Year -> Week. The time dimension is build from server time with key date to the fact tables.

Weekly Magazin Non Weekly Magazin

A

B

C

D

Week 52 27.12.2006 100 200 Week 52 28.12.2006 1000 2000 Week 01 02.01.2007 Week 01 03.01.2007 -10 Week 01 04.01.2007 20 Week 02 10.01.2007 5 210 Week 02 11.01.2007 1 1900 MDX sumPeriodsToDate Cum. Week 52 100 1000

+

200 2000

=

3300 Week 01 90 1020

+

=

1110 Week 02 95 1021

+

210 1900

=

3226 2006 100 1000

+

200 2000

=

3300 2007 185 2041

+

210 1900

=

4336 All 285 3041

+

410 3900

=

7636

Regards Alexander|||

Maybe a different approach could work, where [MEASURES].[lfmenge1] is assigned cumulative values at the week level, so (assuming it is a "sum" measure) these values get rolled up to the year and "All" levels. Then [MEASURES].[delivery quantity] could simply be a sum of the 2 cube measures:

CALCULATE;

scope ([MEASURES].[lfmenge1], [EVT Date].[Week].[Week].Members);

this = Aggregate(PeriodsToDate([EVT Date].[Week].[(All)], [EVT Date].[Week].currentMember));

end scope;

create member [Logistik_TakteReklaRedressen].[MEASURES].[delivery quantity] as

[MEASURES].[lfmenge1] + [MEASURES].[lfmenge];