Working with Dimensions and Levels
.gif "ms135454.note(zh-tw,SQL.90).gif") 附註： |
|---|
| 下一版的 Microsoft SQL Server 將不再提供此功能。請勿在新的開發工作中使用此功能，並且儘速修改使用此功能的應用程式。 |
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.
| 附註： |
|---|
| 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