Hi,

In a cube, we have certain custom aggregation functions (which are
written by using Script Command in Calculations) and some calculated
measures too.
The custom aggregation function is like: (i.e. basically to override
the aggregation type from sum to something which comes from the
property)

CASE
WHEN[DimConfig].[ConfigHierarchy].currentmember.level.name = "Metric"
THEN
CASE
WHEN
[DimConfig].[ConfigHierarchy].currentmember.properties("RollUpAggregationType")
= "SUM"
THEN
Sum([DimConfig].[ConfigHierarchy].currentmember.children,[Measures].currentmember)

WHEN
[DimConfig].[ConfigHierarchy].currentmember.properties("RollUpAggregationType")
= "AVERAGE"
THEN
Avg([DimConfig].[ConfigHierarchy].currentmember.children,[Measures].currentmember)
WHEN
[DimConfig].[ConfigHierarchy].currentmember.properties("RollUpAggregationType")
= "MIN"
THEN
Min([DimConfig].[ConfigHierarchy].currentmember.children,[Measures].currentmember)
WHEN
[DimConfig].[ConfigHierarchy].currentmember.properties("RollUpAggregationType")
= "MAX"
THEN
Max([DimConfig].[ConfigHierarchy].currentmember.children,[Measures].currentmember)
ELSE
Max([DimConfig].[ConfigHierarchy].currentmember.children,[Measures].currentmember)
END
WHEN ....
THEN ....

WHEN ....
THEN ....


WHEN ....
THEN ....

END

Now,
After processing the cube, if we fire the query similar to the one
specified above, it takes some 27 seconds.

If we remove these custom roll up formulas, then again fire the same
query then it takes 10 seconds.

And hence its calculating these custom roll ups at run time.

So the question is, how to carry out these custom aggregations at
processing time instead of run time?
Is there any other way to specify these MDX queries than script command
which would be executed at processing time?

I have also tried the option of Custom Roll up columns specified in the
SSAS for an attribute, but doesn't seem to be helpful.

Regards,
Gayatri

Hi Gayatri,

There are possible performance improvements to the MDX script, based on
suggestions 1, 6 and 7 in this paper:

http://www.sqlserveranalysisservices...Performance%20
Hintsv1.htm
...
Don’t Filter on Member Property Values

In SQL Server Analysis Services 2000, the filter function was a common
means of selecting members from a set whose member property satisfied
some condition; for example male customers could be expressed as :

Filter(Customer.name.Name.members,
Customer.name.currentmember.properties(“Gender”) = “Male”)

Don’t do this in SQL Server Analysis Services 2005. Instead, create an
attribute hierarchy Customers.Gender.Male
...
Use Scoped Assignments instead of Nested IIFs

Expressions can depend on their position in a hierarchy. For example, if
an expression relied on the value of a member property defined on a
members of a level, the expression is only good if the current member
from that hierarchy is from that level.

In SQL Server Analysis Services 2005, create multiple scoped assignments
instead of creating a single calculation with multiple IIFs with
different calculations depending on level ordinal.
...
Conditions in Assignments

If you can avoid it, don’t use conditions in assignments. Instead, scope
only on the space where the calculation should be applied. (The scope
statement is new in SQL Server Analysis Services 2005).
...
So something like this might improve performance:


Scope([DimConfig].[ConfigHierarchy].[RollUpAggregationType].[SUM]);
this = Sum([DimConfig].[ConfigHierarchy].Children);
End Scope;

Scope([DimConfig].[ConfigHierarchy].[RollUpAggregationType].[AVERAGE]);
this = Avg([DimConfig].[ConfigHierarchy].Children);
End Scope;

Scope([DimConfig].[ConfigHierarchy].[RollUpAggregationType].[MIN]);
this = Min([DimConfig].[ConfigHierarchy].Children);
End Scope;

Scope([DimConfig].[ConfigHierarchy].[RollUpAggregationType].[MAX]);
this = Max([DimConfig].[ConfigHierarchy].Children);
End Scope;

End Scope;

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

Hi,

I am newbie to SSAS 2005, i am trying to perform custom rollup in a date dimension so that for a quarter on rolling up from weeks it should show me the data for the last week in the quarter.

Is [RollUpAggregationType] a property or a name chosen for a member for example sake. Please calrify whether i can use the previously mentioned methods to achieve the custom rollup i desire ?

Regards,
Shilpi

If you're using SSAS Enteprise Edition, maybe the LastChild or
LastNonEmpty semi-additive aggregations will meet your needs?

http://msdn2.microsoft.com/en-us/library/ms175356.aspx

Defining Semiadditive Behavior
...
LastChild
The member value is evaluated as the value of its last child along the
time dimension.

LastNonEmpty
The member value is evaluated as the value of its last child along the
time dimension that contains data.
...

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

Thanks Deepak,

But it worked for me using tail(nonempty...) for a scope above it.

Regards,
Shilpi.