• Article

Working with Cubes and Measures

[!REMARQUE]

  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é.

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).

The previous examples created a new database, added a data source, and added shared dimensions and levels. The following three examples demonstrate how to list, add, and remove a cube.

List Cubes

Because each MDStore database object contains a collection of MDStore cube objects, it is easy to list the cubes and their properties for each database.

The following code example prints a list of cubes for each database on a given server to the Immediate window. The SubClassType and SourceTable properties for each cube are also printed.

Private Sub ListCubes()
    Dim dsoServer As New DSO.Server
    Dim dsoDB As DSO.MDStore
    Dim dsoCube As DSO.MDStore

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

    ' Step through the databases in the server object.
    For Each dsoDB In dsoServer.MDStores
        ' Print the name and description of the database
        Debug.Print "DATABASE: " & dsoDB.Name & " - " & _
        dsoDB.Description

        ' Step through the cubes in the database object.
        If dsoDB.MDStores.Count = 0 Then
            Debug.Print "  Cube: None"
        Else
            For Each dsoCube In dsoDB.MDStores
                ' Print the name of the cube.
                Debug.Print "  Cube: " & dsoCube.Name

                ' Check to see whether the cube is regular or virtual.
                If dsoCube.SubClassType = sbclsRegular Then
                   Debug.Print "       SubClassType: Regular"
                   Debug.Print "        SourceTable: " & _
                     dsoCube.SourceTable
                Else
                    Debug.Print "       SubClassType: Virtual"
                End If
             Next
        End If
    Next

End Sub

Add a Cube

The following example illustrates how to add a cube, named TestCube, to the MDStores collection of the database:

  1. Add the cube to the MDStores collection of the database using the AddNew method.

  2. Add a data source to the new cube.

  3. Set the SourceTable property of the cube.

  4. Set the EstimatedRows property to the approximate number of rows in the table.

  5. Add the shared dimensions that you created in the Working With Dimensions and Levels topic to the cube's Dimensions collection with the AddNew method.

  6. Create an SQL INNER JOIN clause for connecting the dimension table to the source table, and then assign it to the cube's JoinClause property.

  7. Make the changes to this cube permanent by calling the cube's Update method.

The following code example adds a new cube, TestCube, to the TestDB database:

Private Sub AddCube()
    Dim dsoServer As New DSO.Server
    Dim dsoDB As DSO.MDStore
    Dim dsoCube As DSO.MDStore

    Dim strDBName As String
    Dim strCubeName As String
    Dim strJoin As String

    ' Initialize variables for the database and
    ' cube names.
    strDBName = "TestDB"
    strCubeName = "TestCube"

    ' Define joins between the fact table and the dimension tables
    ' to be used later in the subroutine.

    ' Join the fact table to the Product table.
    ' sales_fact_1998.product_id = product.product_id
    strJoin = "(""sales_fact_1997"".""product_id""=""product"".""product_id"")"
    strJoin = strJoin & " AND "

    ' Join the fact table to the Store table.
    ' sales_fact_1998.store_id = store.store_id
    strJoin = strJoin & "(""sales_fact_1997"".""store_id""=""store"".""store_id"")"

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

    ' Ensure the database exists first.
    If dsoServer.MDStores.Find(strDBName) = False Then
        MsgBox "Database " & strDBName & " is not found."
    Else
        Set dsoDB = dsoServer.MDStores(strDBName)

        ' Check for existing data sources, dimensions, and
        ' cubes.
        If dsoDB.DataSources.Count = 0 Then
            ' No data source
            MsgBox "Database " & strDBName & " has no data sources."
        ElseIf dsoDB.Dimensions.Count = 0 Then
            ' No dimensions
            MsgBox "Database " & strDBName & " has no dimensions."
        ElseIf dsoDB.MDStores.Find(strCubeName) Then
            ' Cube already exists
            MsgBox "Cube " & strCubeName & " already exists " & _
                "in database" & strDBName
        Else
            ' Add the cube to the database.
            Set dsoCube = dsoDB.MDStores.AddNew(strCubeName)

            ' Further define the cube.
            With dsoCube
                ' Provide the data source for the cube.
                .DataSources.AddNew dsoDB.DataSources(1).Name

                ' Provide the fact table for the cube.
                .SourceTable = """sales_fact_1997"""

                ' Provide an estimated number of rows.
                .EstimatedRows = 100000

                ' Add the Products and Stores shared dimensions.
                .Dimensions.AddNew "Products"
                .Dimensions.AddNew "Stores"

                ' Join the fact table with the dimension tables.
                .JoinClause = strJoin

                ' Update the database.
                .Update
            End With

            ' Inform the user.
            MsgBox "Cube " & strCubeName & _
                " created and dimensions added"

        End If
    End If

End Sub

Remove a Cube

The process of removing a cube from a database is performed by the Remove method of the database object's MDStores collection. The following example code illustrates this by removing the TestCube cube created in the previous code example.

The following code example removes the TestCube cube from the TestDB database:

Private Sub RemoveCube()
    Dim dsoServer As New DSO.Server
    Dim dsoDB As DSO.MDStore

    Dim strDBName As String
    Dim strCubeName As String

    ' Initialize variables for the database and
    ' cube names.
    strDBName = "TestDB"
    strCubeName = "TestCube"

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

    ' Ensure the database exists on the server.
    If dsoServer.MDStores.Find(strDBName) = False Then
        MsgBox "Database " & strDBName & _
            " is not found on this server."
    Else
        Set dsoDB = dsoServer.MDStores(strDBName)

        ' Ensure the cube exists in the database.
        If dsoDB.MDStores.Find(strCubeName) = False Then
            MsgBox "Cube " & strCubeName & " is not found" & _
                " in database " & strDBName & "."
        Else
            ' Remove the cube from the database.
            dsoDB.MDStores.Remove strCubeName

            ' Inform the user.
            MsgBox "Cube " & strCubeName & " removed" & _
                " from database " & strDBName
        End If
    End If

End Sub

List Measures

Collections of measures are contained within objects of ClassType clsCube, clsPartition, and clsAggregation. The measure objects contained within each of these collections are ClassTypes clsCubeMeasure, clsPartitonMeasure, and clsAggregationMeasure.

The following code example uses the Measures collection of an MDStore cube object to list the measures associated with the cubes of each database on a given Analysis server.

The following code example lists each measure of each cube in each database on the local Analysis server, printing basic properties to the Immediate window:

Private Sub ListMeasures()
    Dim dsoServer As New DSO.Server
    Dim dsoDB As DSO.MDStore
    Dim dsoCube As DSO.MDStore
    Dim dsoMea As DSO.Measure

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

    ' Step through the databases in the MDStores collection
    ' of the server object.
    For Each dsoDB In dsoServer.MDStores
        Debug.Print "DATABASE: " & dsoDB.Name & " - " & _
        dsoDB.Description

        'Step through the cubes in the database collection.
        For Each dsoCube In dsoDB.MDStores
            Debug.Print "    Cube: " & dsoCube.Name

            'Step through measures for the cube.
            For Each dsoMea In dsoCube.Measures
                Debug.Print "        Measure: " & dsoMea.Name
            Next
        Next
    Next

End Sub

Add Measures

The task of adding measures to an MDStore cube object is performed by the AddNew method of the Measures collection, as illustrated by the following code example.

The following code example adds four measures, Product ID, Store Sales, Store Cost, and Unit Sales, to the TestCube cube created by previous code examples:

Private Sub AddMeasures()
    Dim dsoServer As New DSO.Server
    Dim dsoDB As DSO.MDStore
    Dim dsoCube As DSO.MDStore
    Dim dsoMea As DSO.Measure

    Dim strDBName As String
    Dim strCubeName 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 adSmallInt = 2

    ' Initialize variables for the database and
    ' cube names.
    strDBName = "TestDB"
    strCubeName = "TestCube"

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

    ' Ensure the database exists first.
    If dsoServer.MDStores.Find(strDBName) = False Then
        MsgBox "Database " & strDBName & " is not found."
    Else
        Set dsoDB = dsoServer.MDStores(strDBName)

        ' Check for existing data sources, dimensions and
        ' cubes.
        If dsoDB.DataSources.Count = 0 Then
            ' No data source
            MsgBox "Database " & strDBName & " has no data sources."
        ElseIf dsoDB.Dimensions.Count = 0 Then
            ' No dimensions
            MsgBox "Database " & strDBName & " has no dimensions."
        ElseIf dsoDB.MDStores.Find(strCubeName) = False Then
            ' Cube already exists
            MsgBox "Cube " & strCubeName & " does not exist " & _
                "in database" & strDBName
        Else
            ' Add the cube to the database.
            Set dsoCube = dsoDB.MDStores(strCubeName)

            Set dsoMea = dsoCube.Measures.AddNew("Product ID")
            dsoMea.SourceColumn = """sales_fact_1997"".""product_id"""
            dsoMea.SourceColumnType = adSmallInt  'The data type for the column
            dsoMea.AggregateFunction = aggSum     'The method for the column
            'aggSum aggregates the column by summation.

            Set dsoMea = dsoCube.Measures.AddNew("Store Sales")
            dsoMea.SourceColumn = """sales_fact_1997"".""store_sales"""
            dsoMea.SourceColumnType = adSmallInt
            dsoMea.AggregateFunction = aggSum

            Set dsoMea = dsoCube.Measures.AddNew("Store Cost")
            dsoMea.SourceColumn = """sales_fact_1997"".""store_cost"""
            dsoMea.SourceColumnType = adSmallInt
            dsoMea.AggregateFunction = aggSum

            Set dsoMea = dsoCube.Measures.AddNew("Unit Sales")
            dsoMea.SourceColumn = """sales_fact_1997"".""unit_sales"""
            dsoMea.SourceColumnType = adSmallInt
            dsoMea.AggregateFunction = aggSum

            dsoCube.Update
        End If
    End If

End Sub

Process a Cube

A database, shared dimensions and their levels, and a cube and its measures are now in place, and the cube can be processed.

To process a cube, use the Process method of the MDStore cube object as shown in the following code example.

Processing the cube can take several minutes. You can view the cube data using Analysis Manager after processing is complete.

The following code example processes the TestCube cube created in the previous code examples:

Private Sub ProcessCube()
    Dim dsoServer As New DSO.Server
    Dim dsoDB As DSO.MDStore
    Dim dsoCube As DSO.MDStore
    Dim dsoMea As DSO.Measure

    Dim strDBName As String
    Dim strCubeName As String

    ' Initialize variables for the database and
    ' cube names.
    strDBName = "TestDB"
    strCubeName = "TestCube"

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

    ' Ensure the database exists first.
    If dsoServer.MDStores.Find(strDBName) = False Then
        MsgBox "Database " & strDBName & " is not found."
    Else
        Set dsoDB = dsoServer.MDStores(strDBName)

        ' Check for existing data sources, dimensions, and
        ' cubes.
        If dsoDB.DataSources.Count = 0 Then
            ' No data source
            MsgBox "Database " & strDBName & " has no data sources."
        ElseIf dsoDB.MDStores.Find(strCubeName) = False Then
            ' Cube already exists
            MsgBox "Cube " & strCubeName & " does not exist " & _
                "in database" & strDBName
        Else
            ' Retrieve the cube from the database.
            Set dsoCube = dsoDB.MDStores(strCubeName)

            ' Ensure the cube is correctly constructed.
            If dsoCube.Dimensions.Count = 0 Then
                ' No dimensions associated with the cube
                MsgBox "Cube " & strCubeName & _
                    " has no dimensions."
            ElseIf dsoCube.Measures.Count = 0 Then
                ' No measures associated with the cube
                MsgBox "Cube " & strCubeName & _
                    " has no measures."
            Else
                ' Process the cube.
                dsoCube.Process

                ' Inform the user.
                MsgBox "Cube " & strCubeName & _
                    "has been processed."
            End If
        End If
    End If

End Sub