clsPartitionAnalyzer

clsPartitionAnalyzer

In multidimensional database technology, you must balance precalculated aggregation storage requirements against online query process performance. A high percentage of aggregations increases query speed but requires more storage space.

The number of aggregations that must be precalculated and stored increases proportionally to the level of query performance.

A Decision Support Objects (DSO) object of ClassType clsPartitionAnalyzer encapsulates an algorithm that automatically designs a set of aggregations in a partition. It analyzes the schema of a partition and generates a collection of aggregations that improves query performance. You can run the analysis without constraints, or you can constrain the analysis in either of the following ways:

• Specify one or more goal queries that you want to optimize.
  
  
• Include existing aggregations or aggregations that should be preserved before the analysis is run.

To analyze a partition using DSO, follow these steps:

1. Initialize the analysis session using the InitializeDesign method.
   
   
2. Add one or more goal queries using the AddGoalQuery and PrepareGoalQueries methods. The resulting members of the DesignedAggregations collection will be optimized for this set of goal queries. If no goal queries are specified, the analysis will yield a generalized optimization.
   
   
3. Add one or more existing aggregations using the AddExistingAggregation method.
   
   
4. Perform an initial analysis using the NextAnalysisStep method.

   The analysis generates new aggregations that are added to the DesignedAggregations collection. It also returns the calculated percentage performance gain, aggregation storage requirements, and total number of aggregations created.

5. Review the results of the analysis step and determine whether you want to perform another analysis iteration. Running subsequent analysis steps adds new aggregations to the DesignedAggregations collection and recalculates the percentage performance gain, aggregation storage requirements, and total number of aggregations created.
   
   
6. Manually or programmatically determine the point at which you want to conclude the analysis.
   
   
7. Optionally, when the partition analyzer is finished running, replace the aggregations of the partition with the members of the DesignedAggregations collection.
   
   
8. Close the analysis with the CloseAggregationsAnalysis method.

An object of ClassType clsPartitionAnalyzer provides collections, methods, and properties through its own internal interface.

Example

This example analyzes the default partition of a cube and designs aggregations that can fulfill 20% of all possible queries without having to access the fact table:

'   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 set the storage mode of the partition.
    ' This example sets 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
    
    ' Design aggregations for 20% of queries.
    ' NextAnalysisStep incrementally builds the
    ' optimal set of aggregations.
    ' Tell the partition analyzer to stop designing 
    ' aggregations 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 meta data repository.
    On Error GoTo Err_Update
    dsoPartition.Update

Exit Sub

Err_Update:
    ' Failed to persist the cube definition in the meta data repository
    ' Possible reasons:
    '   - the meta data repository is unreachable
    '       you can see where the meta data 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 applications to persist the 
    '       same object at the same time.
    '       It is not possible to persist a DSO object because another DSO
    '       application has explicitly locked it.

    MsgBox "Aggregation design for partition failed" & _
    vbCrLf & Err.Description
End Sub