The dimensions of a cube store data derived from relational database tables and contain the categorical data you want to analyze.
The dimensions you build (for example, time, customer education, and customer age) should be distinct categories you want to add to cubes in your database. A dimension can be created from a single dimension table (star schema) or from multiple dimension tables (snowflake schema). Dimensions are classified as either standard or time dimensions, depending upon the data type of the corresponding column in the dimension table.
Collections of dimensions are contained within objects of ClassType clsDatabase, clsCube, clsPartition, and clsAggregation. The dimension objects contained within each of these collections are of respective ClassTypes clsDatabaseDimension, clsCubeDimension, clsPartitionDimension, and clsAggregationDimension.
The List Dimension example (below) lists existing dimensions and their related levels. The Add Dimensions example creates new dimensions and levels.
Add a command button to the form named cmdListDimensions. Add the following code to the cmdListDimensions_Click() event:
Private Sub cmdListDimensions_Click()
Dim dsoTempDB As DSO.MDStore
Dim dsoDim As DSO.Dimension
Dim dsoLev As DSO.Level
Dim DBCounter As Integer
Dim DimCounter As Integer
Dim LevCounter 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 dimensions in the cube's Dimensions collection
For DimCounter = 1 To dsoTempDB.Dimensions.Count
Set dsoDim = dsoTempDB.Dimensions(DimCounter)
Debug.Print " Dimension: " & dsoDim.Name
'Step through dimension levels
For LevCounter = 1 To dsoDim.Levels.Count
Set dsoLev = dsoDim.Levels(LevCounter)
Debug.Print " Level: " & dsoLev.Name
Next LevCounter
Next DimCounter
Next DBCounter
End Sub
Save your project and run the application. The Immediate window will list the dimensions and levels for each database.
Add a command button to the form named cmdAddDimensions. Add the following code to the cmdAddDimensions_Click() event:
Private Sub cmdAddDimensions_Click()
On Error GoTo AddDimension_Err
Dim dsoDS As DSO.DataSource
Dim dsoDim As DSO.Dimension
Dim dsoLev As DSO.Level
Dim strDBName As String
Dim strCubeName As String
Dim strMsg As String
Dim bResponse As VbMsgBoxResult
Dim DimCounter As Integer
'Do we have a database?
If dsoDB Is Nothing Then
strDBName = InputBox("Database to add dimensions to.", _
"Add Dimensions - 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
strMsg = "Create Dimensions and Levels for database " & dsoDB.Name
bResponse = MsgBox(strMsg, vbOKCancel, "Add Dimensions and Levels")
'Add shared dimensions to Database
If bResponse = vbOK Then
'Create Product Dimension and Levels
Set dsoDim = dsoDB.Dimensions.AddNew("Products")
Set dsoDim.DataSource = dsoDS 'Dimension DataSource
dsoDim.FromClause = "product" 'Related Table
dsoDim.JoinClause = "" 'Used in snowflake schema
'Product Brand Level
Set dsoLev = dsoDim.Levels.AddNew("Brand Name")
dsoLev.MemberKeyColumn = """product"".""brand_name"""
dsoLev.ColumnSize = 255
dsoLev.ColumnType = adWChar
dsoLev.EstimatedSize = 1
'Product Name Level
Set dsoLev = dsoDim.Levels.AddNew("Product Name")
dsoLev.MemberKeyColumn = """product"".""product_name"""
dsoLev.ColumnSize = 255
dsoLev.ColumnType = adWChar
dsoLev.EstimatedSize = 1
'Update the dimension
dsoDim.Update
'Create Store Dimension and Levels
Set dsoDim = dsoDB.Dimensions.AddNew("Store")
Set dsoDim.DataSource = dsoDS 'Dimension DataSource
dsoDim.FromClause = "store" 'Related Table
dsoDim.JoinClause = ""
'Store Type Level
Set dsoLev = dsoDim.Levels.AddNew("Store Type")
dsoLev.MemberKeyColumn = """store"".""store_type"""
dsoLev.ColumnSize = 255
dsoLev.ColumnType = adWChar
dsoLev.EstimatedSize = 1
'Store Id Level
Set dsoLev = dsoDim.Levels.AddNew("Store Id")
dsoLev.MemberKeyColumn = """store"".""store_id"""
dsoLev.ColumnSize = 4
dsoLev.ColumnType = adInteger
dsoLev.EstimatedSize = 1
'Store Name Level
Set dsoLev = dsoDim.Levels.AddNew("Store Name")
dsoLev.MemberKeyColumn = """store"".""store_name"""
dsoLev.ColumnSize = 255
dsoLev.ColumnType = adWChar
dsoLev.EstimatedSize = 1
'Update the dimension
dsoDim.Update
'Create Time Dimension and Levels
Set dsoDim = dsoDB.Dimensions.AddNew("Time")
Set dsoDim.DataSource = dsoDS
dsoDim.FromClause = "time_by_day"
dsoDim.JoinClause = ""
'Year Level
Set dsoLev = dsoDim.Levels.AddNew("Year")
dsoLev.MemberKeyColumn = _
"DatePart('yyyy',""time_by_day"".""the_date"")"
dsoLev.ColumnSize = 10
dsoLev.ColumnType = 2
dsoLev.EstimatedSize = 1
'Quarter Level
Set dsoLev = dsoDim.Levels.AddNew("Quarter")
dsoLev.MemberKeyColumn = _
"DatePart('q',""time_by_day"".""the_date"")"
dsoLev.ColumnSize = 4
dsoLev.ColumnType = adSmallInt
dsoLev.EstimatedSize = 1
'Month Level
Set dsoLev = dsoDim.Levels.AddNew("Month")
dsoLev.MemberKeyColumn = _
"DatePart('m',""time_by_day"".""the_date"")"
dsoLev.ColumnSize = 4
dsoLev.ColumnType = adSmallInt
dsoLev.EstimatedSize = 1
'Week Level
Set dsoLev = dsoDim.Levels.AddNew("Week")
dsoLev.MemberKeyColumn = _
"Format(""time_by_day"".""the_date"",'medium date')"
dsoLev.ColumnSize = 20
dsoLev.ColumnType = adChar
dsoLev.EstimatedSize = 1
'Update the dimension
dsoDim.Update
'Tell the user it was successful.
MsgBox ("Dimension and Levels added")
Else
MsgBox ("Canceled - No dimensions added")
End If
Exit Sub
AddDimension_Err:
Debug.Print "Error adding new dimension"
Debug.Print Err.Number, Err.Description, Err.Source
Err.Clear
End Sub
Save your project and run the application. Try adding dimensions and levels. Verify that the dimensions and levels have been successfully added by listing dimensions and levels.
Note Since we are using the FoodMart sample database for these exercises, the information for dimension names, FromClause, and MemberKeyColumn are hard coded. To add other dimension/level data you will need to provide the proper information for specific properties.