Collections of measures are contained within objects of ClassType clsCube, clsPartition, and clsAggregation. The measure objects contained within each of these collections are of respective ClassTypes clsCubeMeasure, clsPartitonMeasure, and clsAggregationMeasure.
The following examples demonstrate how to list and add new measures to the sample cube you’ve created.
Add a command button to the form named cmdListMeasures. Add the following code to the cmdListMeasures_Click() event:
Private Sub cmdListMeasures_Click()
Dim dsoTempDB As DSO.MDStore
Dim dsoTempCube As DSO.MDStore
Dim dsoMea As DSO.Measure
Dim strDBName As String
Dim strCubeName As String
Dim DBCounter As Integer
Dim CubeCounter As Integer
Dim MeaCounter As Integer
'Step through the databases in the server's MDStores collection
For DBCounter = 1 To dsoServer.MDStores.Count
Set dsoTempDB = dsoServer.MDStores(DBCounter)
Debug.Print "DATABASE: " & dsoTempDB.Name & " / " & _
dsoTempDB.Description
'Step through the cubes in the database collection
For CubeCounter = 1 To dsoTempDB.MDStores.Count
Set dsoTempCube = dsoTempDB.MDStores(CubeCounter)
Debug.Print " Cube: " & dsoTempCube.Name
'Step thorough measures for the cube
For MeaCounter = 1 To dsoTempCube.Measures.Count
Set dsoMea = dsoTempCube.Measures(MeaCounter)
Debug.Print " Measure: " & dsoMea.Name
Next MeaCounter
Next CubeCounter
Next DBCounter
End Sub
Save your project and run the application. The Immediate window lists each cube and its related measures.
Add a command button to the form named cmdAddMeasures. Add the following code to the cmdAddMeasures_Click() event:
Private Sub cmdAddMeasures_Click()
On Error GoTo AddMeasures_Err
Dim dsoDS As DSO.DataSource
Dim dsoMea As DSO.Measure
Dim bResponse As VbMsgBoxResult
Dim strDBName As String
Dim strCubeName As String
'Do we have a database?
If dsoDB Is Nothing Then
strDBName = InputBox("Database to add dimension measures to.", _
"Add Dimension Measures - Database")
If strDBName = "" Then
MsgBox ("You must enter the name of a Database")
Exit Sub
End If
If Not dsoServer.MDStores.Find(strDBName) Then
MsgBox (strDBName & " database not found on this server")
Exit Sub
Else
Set dsoDB = dsoServer.MDStores(strDBName)
End If
End If
'We must have a datasource for the database
If dsoDB.DataSources.Count = 0 Then
MsgBox ("Please add a Datasource")
Exit Sub
Else
Set dsoDS = dsoDB.DataSources(1)
End If
'Do we have existing Database Dimensions?
If dsoDB.Dimensions.Count = 0 Then
MsgBox ("Please add dimensions to Database")
Exit Sub
End If
'Do we have a cube?
If dsoCube Is Nothing Then
strCubeName = InputBox("Enter the name of the cube in this database.", _
"Add Dimension Measures - Cube")
If strCubeName = "" Then
MsgBox ("You must enter the name of a cube.")
Exit Sub
End If
If Not dsoDB.MDStores.Find(strCubeName) Then
MsgBox (strCubeName & " cube not found on this database")
Exit Sub
Else
Set dsoCube = dsoDB.MDStores(strCubeName)
End If
End If
'Ask the user to confirm adding the measures to the cube.
bResponse = MsgBox("Add Measures for Cube Dimensions", _
vbOKCancel, "Add Cube Measures")
'Add the measures to the cube.
If bResponse = vbOK Then
Set dsoMea = dsoCube.Measures.AddNew("Product Id")
dsoMea.SourceColumn = """sales_fact_1998"".""product_id"""
dsoMea.SourceColumnType = adSmallInt
dsoMea.AggregateFunction = aggSum
Set dsoMea = dsoCube.Measures.AddNew("Store Sales")
dsoMea.SourceColumn = """sales_fact_1998"".""store_sales"""
dsoMea.SourceColumnType = adSmallInt
dsoMea.AggregateFunction = aggSum
Set dsoMea = dsoCube.Measures.AddNew("Store Cost")
dsoMea.SourceColumn = """sales_fact_1998"".""store_cost"""
dsoMea.SourceColumnType = adSmallInt
dsoMea.AggregateFunction = aggSum
Set dsoMea = dsoCube.Measures.AddNew("Unit Sales")
dsoMea.SourceColumn = """sales_fact_1998"".""unit_sales"""
dsoMea.SourceColumnType = adSmallInt
dsoMea.AggregateFunction = aggSum
dsoCube.Update
MsgBox ("Measures added")
Else
MsgBox ("Canceled - no measures added")
Exit Sub
End If
Exit Sub
AddMeasures_Err:
Debug.Print "Error adding new measures"
Debug.Print Err.Number, Err.Description, Err.Source
Err.Clear
End Sub
Save your project and run the application. Click Add Measures. Verify that the measures have been added to the cube by listing measures.