The following example demonstrates the process of analyzing a cube’s default partition and designing aggregations that will provide an optimal response time to 20% of the queries made.
' CreateAggregations - design aggregations for the cube
'
Public Sub CreateAggregations()
' aggregations are designed per partition
' get the default partition from the cube
' m_dsoCube is a publicly declared variable
' of DSO ClassType clsCube
Dim dsoPartition As DSO.MDStore
Set dsoPartition = m_dsoCube.MDStores(1)
' first we need to set the storage mode of the partition
' we will set it to MOLAP
' (facts and aggregations are loaded into
' multidimensional structures on the OLAP server)
' olapmodeMolapIndex is an enumerated constant indicating
' that the storage mode for a partition is MOLAP
dsoPartition.OlapMode = olapmodeMolapIndex
' get the partition analyzer
Dim dsoPartitionAnalyzer As DSO.PartitionAnalyzer
Set dsoPartitionAnalyzer = dsoPartition.Analyzer
' initialize the analyzer
dsoPartitionAnalyzer.InitializeDesign
' we will design aggregations for 20% of queries
' NextAnalysisStep incrementally builds the
' optimal set of aggregations
' we will stop when PercentageBenefit reaches 20
Dim PercentageBenefit As Double
Dim AccumulatedSize As Double
Dim AggregationsCount As Long
Do While dsoPartitionAnalyzer.NextAnalysisStep(PercentageBenefit, _
AccumulatedSize, _
AggregationsCount)
If PercentageBenefit > 20# Then
Exit Do
End If
Loop
' apply the designed aggregations to the partition
Dim dsoAggregation As DSO.MDStore
For Each dsoAggregation In dsoPartitionAnalyzer.DesignedAggregations
dsoPartition.MDStores.Add dsoAggregation
Next
' close the analyzer
dsoPartitionAnalyzer.CloseAggregationsAnalysis
' save the cube definition in the metadata repository
On Error GoTo Err_Update
dsoPartition.Update
Exit Sub
Err_Update:
' Failed to persist the cube definition in the metadata repository
' Possible reasons:
' - the metadata repository is unreachable
' you can see where the metadata repository resides by looking
' up the following registry entry:
' HKEY_LOCAL_MACHINE\Software\Microsoft\OLAP Server\Server
' Connection Info
' Repository Connection String
' - the DSO cube object is being locked by another DSO application
' It is not possible for two DSO apps to persist the same
' object at the same time.
' It is not possible to persist a DSO object, another DSO app
' has explicitly locked it
MsgBox "Design aggregations for Partition failed" & _
vbCrLf & Err.Description
End Sub