Working with Dimensions and Levels

List Dimensions

The Dimensions collection of the DSO.Server object contains all shared dimensions on an Analysis server, as illustrated by the following code example.

The following code example illustrates the hierarchical nature of dimensions and levels by listing the levels in order of precedence for every dimension contained in every database on a given Analysis server, printing basic properties of each dimension and level in the Immediate window:

Private Sub ListDimensions()
    Dim dsoServer As New DSO.Server
    Dim dsoDB As DSO.MDStore
    Dim dsoDim As DSO.Dimension
    Dim dsoLev As DSO.Level

    ' Create a connection to the Analysis server.
    dsoServer.Connect "LocalHost"

    ' Enumerate databases on a server.
    For Each dsoDB In dsoServer.MDStores
        Debug.Print "DATABASE: " & dsoDB.Name & " - " & _
            dsoDB.Description

        ' Enumerate dimensions in a database.
        For Each dsoDim In dsoDB.Dimensions
            Debug.Print "    Dimension: " & dsoDim.Name

            ' Enumerate levels in a dimension.
            For Each dsoLev In dsoDim.Levels
                Debug.Print "        Level: " & dsoLev.Name
            Next
        Next
    Next

End Sub

Add Dimensions and Levels

To add a dimension, the AddNew method of the Dimensions collection for an MDStore database object is used. After a new dimension has been created in this way, levels are added using the AddNew method of the Levels collection for the new Dimension object.

The following code example adds the Products dimension with two levels, Brand Name and Product Name, and the Stores dimension with four levels, Store Country, Store State, Store City and Store ID, to the TestDB database. In addition, the Store ID level has a member property named Store SQFT associated with it. The following diagram graphically displays the relationships.

For more information about member properties, see clsMemberProperty.

The following code example adds two new dimensions to the TestDB database:

Private Sub AddDimensions()
    Dim dsoServer As New DSO.Server
    Dim dsoDB As DSO.MDStore
    Dim dsoDS As DSO.DataSource
    Dim dsoDim As DSO.Dimension
    Dim dsoLev As DSO.Level
    Dim dsoMember As DSO.MemberProperty

    Dim strDBName As String

    ' Constants used for ColumnType property
    ' of the DSO.Level object.
    ' Note that these constants are identical to
    ' those used in ADO in the DataTypeEnum enumeration.
    Const adWChar = 130
    Const adInteger = 3
    Const adDouble = 5

    ' Initialize variables for the database name.
    strDBName = "TestDB"

    ' Create a connection to the Analysis server.
    dsoServer.Connect "LocalHost"

    ' Set the database object.
    Set dsoDB = dsoServer.MDStores(strDBName)

    ' Set the data source for the database object.
    ' A data source is required to run this example.
    If dsoDB.DataSources.Count = 0 Then
        MsgBox "Database " & dsoDB.Name & _
            " has no data sources."
    Else
        Set dsoDS = dsoDB.DataSources(1)
    End If

    ' Create Products dimension and levels.
    Set dsoDim = dsoDB.Dimensions.AddNew("Products")
    Set dsoDim.DataSource = dsoDS   ' Dimension data source
    dsoDim.FromClause = "product"   ' Related table
    dsoDim.JoinClause = ""          ' Used in snowflake schema

    ' Add Brand Name level.
    Set dsoLev = dsoDim.Levels.AddNew("Brand Name")
    dsoLev.MemberKeyColumn = """product"".""brand_name"""
    dsoLev.ColumnSize = 255         ' Column data size in bytes
    dsoLev.ColumnType = adWChar     ' Column data type
    dsoLev.EstimatedSize = 1        ' Distinct members in column

    ' Add 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 Products dimension.
    dsoDim.Update

    ' Inform the user.
    MsgBox "Dimension " & dsoDim.Name & " added to " & _
        dsoDim.DataSource.Name & " data source."

    ' Create Stores dimension and levels.
    Set dsoDim = dsoDB.Dimensions.AddNew("Stores")
    Set dsoDim.DataSource = dsoDS   ' Dimension data source
    dsoDim.FromClause = "store"     ' Related table
    dsoDim.JoinClause = ""          ' Used in snowflake schema

    ' Add Store Country level.
    Set dsoLev = dsoDim.Levels.AddNew("Store Country")
    dsoLev.MemberKeyColumn = """store"".""store_country"""
    dsoLev.ColumnSize = 50          ' Column data size in bytes
    dsoLev.ColumnType = adWChar     ' Column data type
    dsoLev.EstimatedSize = 1        ' Distinct members in column

    ' Add Store State level.
    Set dsoLev = dsoDim.Levels.AddNew("Store State")
    dsoLev.MemberKeyColumn = """store"".""store_state"""
    dsoLev.ColumnSize = 50
    dsoLev.ColumnType = adWChar
    dsoLev.EstimatedSize = 1

    ' Add Store City level.
    Set dsoLev = dsoDim.Levels.AddNew("Store City")
    dsoLev.MemberKeyColumn = """store"".""store_city"""
    dsoLev.ColumnSize = 50
    dsoLev.ColumnType = adWChar
    dsoLev.EstimatedSize = 1

    ' Add Store ID level.
    Set dsoLev = dsoDim.Levels.AddNew("Store ID")
    dsoLev.MemberKeyColumn = """store"".""store_ID"""
    dsoLev.ColumnSize = 4
    dsoLev.ColumnType = adInteger
    dsoLev.EstimatedSize = 1

    ' Add a member property to the Store ID level.
    Set dsoMember = dsoLev.MemberProperties.AddNew("Store SQFT", _
        sbclsRegular)
    dsoMember.Description = "Store size in square feet"
    dsoMember.SourceColumn = """store"".""store_sqft"""
    dsoMember.ColumnSize = 4
    dsoMember.ColumnType = adDouble

    ' Update the Stores dimension.
    dsoDim.Update

    ' Inform the user.
    MsgBox "Dimension " & dsoDim.Name & " added to " & _
        dsoDim.DataSource.Name & " data source."

End Sub

See Also

Reference

Concepts