• Article

Working with Dimensions and Levels

Notes

  Cette fonctionnalité sera supprimée dans la prochaine version de Microsoft SQL Server. Évitez d'utiliser cette fonctionnalité dans de nouveaux travaux de développement, et modifiez dès que possible les applications qui utilisent actuellement cette fonctionnalité.

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 should be distinct categories that you want to add to cubes in your database (such as Time, Customer Education, and Customer Age). 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 Dimensions example lists existing dimensions and their related levels. The Add Dimensions example creates new 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.

Notes

  The TestDB database is created using some of the prior examples in this topic. For more information, see Working with Databases.

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