Dynamic aggregation is the process by which metric results at the base level returned by the database are further aggregated in the Analytical Engine in OLAP Services reports, as shown below:
Metrics are normally created with the dynamic aggregation function set to 'Default,' meaning that the dynamic aggregation function will be chosen based on the metric's formula. In most cases, this is ok, but sometimes the function should be set explicitly. For more information about the scenarios in which it should be set directly, refer to the following Strategy Knowledge Base technical note:
KB15843 (KB5200-75x-2330): What is the function of the ‘Total subtotal function’ and ‘Dynamic aggregation function’ settings in Strategy SQL Generation Engine 8.x?
It is possible to set the dynamic aggregation function of a metric using the Strategy Intelligence Server SDK 9x. The following code demonstrates how to accomplish this:
Private Sub cmdGetMetrics_Click()
Set oObjSource = MySession.Component(DssRoleObjectSource)
'The metric we want to modify
Dim strM1 As String
strM1 = "B2A422B8485C2EC21F8ECFB7CFB96D0D"
Set oMetric1 = oObjSource.FindObject(strM1, DssTypeMetric)
'Get the subtotal to use for the aggregation function:
Dim strSub1 As String
strSub1 = "8D678D5B11D3E4981000E787EC6DE8A4" 'the Total function
Dim CorrectSubtotal As IDSSMetric
Set CorrectSubtotal = oObjSource.FindObject(strSub1, DssTypeMetric)
'Get the subtotals collection for our metric
Set oSubtotals = oMetric1.Subtotals
'Iterate through the collection of subtotals until the one named 'Aggregation' is found
Dim SubtotalIndex As Integer
For SubtotalIndex = 1 To oSubtotals.Count
Set oSubtotal = oSubtotals.Item(SubtotalIndex)
If oSubtotal.Name = "Aggregation" Then
'Change the Aggregation function to the correct Subtotal function
Set oSubtotal.Implementation = CorrectSubtotal
End If
Next
'Save the metric back to the MD
oMetric1.Info.Save
End Sub