Working with Cubes and Measures
[!NOTA]
Questa caratteristica verrà rimossa a partire dalla prossima versione di Microsoft SQL Server. Non utilizzare questa caratteristica in un nuovo progetto di sviluppo e modificare non appena possibile le applicazioni in cui è attualmente implementata.
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:
Add the cube to the MDStores collection of the database using the AddNew method.
Add a data source to the new cube.
Set the SourceTable property of the cube.
Set the EstimatedRows property to the approximate number of rows in the table.
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.
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.
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