Hi,

Facing problem in custom roll up aggregations for multiple hierarchies
of time dimension:

We have a time dimension with following hierarchies:

1. MonthHierarchy
Year
Month
Day
2. WeekHierarchy
Year
Week
Day

Roll up for this aggregation changes row-wise (For some rows it is
'Average' and for others it is 'Sum'). Hence we have used
custom roll up formulae (Script Command in SSAS) at Week and Month
levels.

The default aggregation type is SUM.

So basically we have the following MDX for custom rollups depending on
the hierarchy:

If 'AggregationType' = "Sum"


Sum([DimDate].[WeekHierarchy].currentmember.children,[Measures].currentmember)

If 'AggregationType' = "Average"


Avg([DimDate].[WeekHierarchy].currentmember.children,[Measures].currentmember)

If 'AggregationType' = "Min"


Min([DimDate].[WeekHierarchy].currentmember.children,[Measures].currentmember)

If 'AggregationType' = "Max"


Max([DimDate].[WeekHierarchy].currentmember.children,[Measures].currentmember)



We have similar MDX for month hierarchy too.

Cases:

1. If we keep the custom roll up for only one hierarchy (either week or
month), it works fine for both individually.
2. If the order of MDX is, first for month and second for week, then
aggregated values for week are perfect. (Default aggregation type
specified is 'Sum' i.e. set in a cube and we want to override it
with 'Average'). Now if we have 4 weeks and 1 day extra in a month
(1 day from the week of last month) then for month, it gives the result
as 'Average(week1) + Average(week2)+ Average(week3)+ Average(week4) +
1 day', thus giving month aggregations incorrect.
3. If the order of MDX is, first for week and then for month, then
aggregated values for month are perfect. (again, Default aggregation
type specified is 'Sum' i.e. set in a cube and we want to override
it with 'Average'). Now, if a month (say. March) has 3 complete
weeks and 6 days of 4th week (and April 1st week has remaining 1 day),
then it gives the correct result for those 3 complete weeks. For 4th
week, it gives the result as, 'Average(last 6 days in March) + 1 day
in April. Here week aggregations are incorrect.
i.e. In any case, the roll up which is specified later gives the
correct results.



Any help is appreciated.


Regards,
Gayatri.