Each database contains an MDStores collection of cubes, that is, objects of ClassType clsCube. A cube is the central object in a multidimensional database. A cube contains dimensions and their levels, measures, data sources, roles, and commands. Each cube also contains an MDStores collection of partitions, that is, objects of ClassType clsPartition.
In the previous examples you created a new database and added a datasource along with shared dimensions and levels. The following three examples in this topic demonstrate the steps required to list, add, and remove a cube.
Add a command button to the form named cmdListCubes. Add the following code to the cmdListCubes_Click() event:
Private Sub cmdListCubes_Click()
Dim dsoTempDB As DSO.MDStore
Dim dsoTempCube As DSO.MDStore
Dim DBCounter As Integer
Dim CubeCounter 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's MDStores collection
If dsoTempDB.MDStores.Count = 0 Then
Debug.Print " Cube: None"
Else
For CubeCounter = 1 To dsoTempDB.MDStores.Count
Set dsoTempCube = dsoTempDB.MDStores(CubeCounter)
Debug.Print " Cube: " & dsoTempCube.Name
'Inform the user whether this cube is regular
'Or virtual.
If dsoTempCube.SubClassType = sbclsRegular Then
Debug.Print " SubClassType: Regular"
Debug.Print " SourceTable: " & _
dsoTempCube.SourceTable
Else
Debug.Print " SubClassType: Virtual"
End If
Next CubeCounter
End If
Next DBCounter
End Sub
Save your project and run the application. Try listing the cubes.
In the following example, we will add a cube to the MDStores collection of the database using these steps:
Add a command button to the form named cmdAddCube. Add the following code to the cmdAddCube_Click() event:
Private Sub cmdAddCube_Click()
On Error GoTo AddCube_Err
Dim dsoDS As DSO.DataSource
Dim strDBName As String
Dim strCubeName As String
'Do we have a database?
If dsoDB Is Nothing Then
strDBName = InputBox("Database to add new Cube to.", _
"Add Cube")
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
'Does database have an attached datasource?
If dsoDB.DataSources.Count = 0 Then
MsgBox ("Please attach Datasource to Database")
Exit Sub
End If
'Does database have Dimensions and Levels?
If dsoDB.Dimensions.Count = 0 Then
MsgBox ("Please add Dimensions to Database")
Exit Sub
End If
'Enter Cube Name and check for duplicate
strCubeName = InputBox("Enter the new cube name.", _
"Adding New Cube")
If strCubeName <> "" Then
If dsoDB.MDStores.Find(strCubeName) Then
MsgBox (strCubeName & " already exists")
Exit Sub
End If
End If
'Create new Cube
Set dsoCube = dsoDB.MDStores.AddNew(strCubeName)
'Set the cube's DataSource
Set dsoDS = dsoDB.DataSources(1)
dsoCube.DataSources.AddNew (dsoDS.Name)
'Set source Fact Table and Estimated Rows in Fact Table
dsoCube.SourceTable = """sales_fact_1998"""
dsoCube.EstimatedRows = 100000
'Add shared dimensions that were created under
'cmdAddDimensions_Click method
dsoCube.Dimensions.AddNew ("Products")
dsoCube.Dimensions.AddNew ("Store")
dsoCube.Dimensions.AddNew ("Time")
'Create joins between Fact Table and Dimension tables
'Set strJoin = “sales_fact_1998.product_id = product.product_id AND
' sales_fact_1998.store_id = store.store_id AND
' sales_fact_1998.time_id = time_by_day.time_id”
Dim strJoin As String
'Fact Table to Product Table join
'sales_fact_1998.product_id = product.product_id
strJoin = "(""sales_fact_1998"".""product_id""=""product"".""product_id"")"
strJoin = strJoin & " AND "
'Fact Table to Store Table join
'sales_fact_1998.store_id = store.store_id
strJoin = strJoin & "(""sales_fact_1998"".""store_id""=""store"".""store_id"")"
strJoin = strJoin & " AND "
'Fact Table to Time Table join
'sales_fact_1998.time_id = time_by_day.time_id
strJoin = strJoin & "(""sales_fact_1998"".""time_id""=""time_by_day"".""time_id"")"
dsoCube.JoinClause = strJoin
'Update the cube
dsoCube.Update
MsgBox (strCubeName & " and Dimensions added")
Exit Sub
AddCube_Err:
Debug.Print "Error adding new cube"
Debug.Print Err.Number, Err.Description, Err.Source
Err.Clear
End Sub
Save your project and run the application. Click Add Cube, and then enter a unique name for the new cube. Verify that the cube was added by listing cubes.
Add a command button to the form named cmdRemoveCube. Add the following code to the cmdRemoveCube_Click() event:
Private Sub cmdRemoveCube_Click()
On Error GoTo RemoveCube_Err
Dim strDBName As String
Dim strCubeName As String
'Do we have a database?
If dsoDB Is Nothing Then
strDBName = InputBox("Database to remove cube from.", _
"Remove Cube")
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
' Do we have a cube?
If dsoCube Is Nothing Then
strCubeName = InputBox("Enter the name of the cube to be removed.", _
"Removing Cube")
If strCubeName = "" Then
MsgBox ("You must enter the name of a cube to remove")
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
dsoDB.MDStores.Remove strCubeName
MsgBox (strCubeName & " removed")
End If
Exit Sub
RemoveCube_Err:
Debug.Print "Error removing cube"
Debug.Print Err.Number, Err.Description, Err.Source
Err.Clear
End Sub
Save your project and run the application. Try removing the cube. Verify that the cube has been removed by listing cubes.