Dimension Interface
Note
This feature will be removed in the next version of Microsoft SQL Server. Do not use this feature in new development work, and modify applications that currently use this feature as soon as possible.
In Decision Support Objects (DSO), dimensions are represented by objects that contain collections of other objects that, in turn, represent levels in the dimensions. For example, a Time dimension often contains the levels Year, Quarter, Month, and Day. The levels of a cube are defined by columns in a dimension table that is stored in the data warehouse database. When a dimension object is processed, the Analysis server constructs the dimension and its levels and then populates them with members from the dimension table. For more information about DSO, see Introducing Decision Support Objects.
All DSO dimension objects implement the Dimension interface, and you manipulate these objects through this interface. The ClassType property of the dimension object specifies the dimension's type. The ClassType value of a database dimension is clsDatabaseDimension. The ClassType values of cube, partition, and aggregation dimensions (supported by the CubeDimension, PartitionDimension, DbDimension, and AggregationDimension objects) are clsCubeDimension, clsPartitionDimension, and clsAggregationDimension, respectively. The Dimension interface provides collections, methods, and properties to manipulate these objects. Dimensions reside in the Dimensions collection of the MDStore object that represents a database, cube, partition, or aggregation.
To use dimensions, create them in the Dimensions collection of a database object. Then assign some or all of the dimensions to a cube. The dimensions assigned to a cube automatically apply to its partitions and aggregations, and you can explicitly associate them with virtual cubes that use the cube.
This topic discusses different types, varieties, and uses of dimensions and provides information about how to work with them in DSO..
Types of DSO Dimensions
The following table describes each type of dimension and the context in which it is used.
Dimension type |
Description |
|---|---|
Database dimension |
The dimensions are contained in a database |
Cube dimension |
The dimensions are contained in a cube |
Partition dimension |
The dimensions are used in a partition |
Aggregation dimension |
The dimensions are contained in an aggregation |
Database dimensions define the structure of the dimension and the data source where the dimension tables exist.
Any of the database dimensions can be used in a cube, if a join can be made between the dimension table and the fact table of the cube. Cube dimensions have the same name as their corresponding database dimensions. When an existing database dimension is added to the Dimensions collection of a cube, DSO automatically defines and creates all of the cube level objects for the cube dimension. A cube dimension inherits its properties from the corresponding database dimension; some of those properties can be customized in the cube. For example, you can specify how a cube dimension is used in the design of aggregations by setting the AggregationUsage property of the dimension.
The dimensions of a partition relate to the dimensions of its parent cube in the same way that cube dimensions relate to database dimensions. Each partition dimension has a corresponding cube dimension and has the same number of levels as the cube dimension.
Aggregation dimensions are the dimensions used within an aggregation. They are different from their corresponding partition dimensions in that they usually have fewer levels. This is because the aggregation dimensions represent the level of granularity of the data in that aggregation.
For example, the following illustration represents two aggregations. The first summarizes sales by year for store cities and product brand. The Time aggregation dimension in this case has only one level: Year. The Store and Product dimensions have all of their respective levels. The second aggregation summarizes sales by Quarter and Region for products by category. The Time aggregation dimension has two levels: Year and Quarter. The Store and Products dimensions, on the other hand, contain only the first levels of each dimension: Region and Category.
DSO dimensions can be either shared or private. A shared dimension is one that can be used in multiple cubes, but a private dimension can only be used in a single cube. For more information about shared and private dimensions, see Shared and Private Dimensions.
Private dimensions use a special naming convention to identify the cubes to which they belong. In all other aspects, private dimensions are identical to shared dimensions. The name of a private dimension is constructed by using the cube name, followed by the caret character (^), followed by the dimension name. To create a private dimension in DSO, name the dimension according to this convention. This naming convention allows private dimensions in different cubes to have the same name, and the cube name prefix ensures uniqueness within the dimension collection of the database. For example, NorthWestSales^Stores represents a private dimension of stores created for use in the NorthWestSales cube.
You can programmatically determine whether a dimension is shared or private by reading its IsShared property. DSO determines the value of this property by examining the name of the dimension. All cubes, partitions, and aggregations that use the dimension inherit its IsShared property.
Parent-Child Dimensions
Parent-child dimensions contain self-joining hierarchies. Because the level hierarchy is variable, rather than rigidly set, parent-child dimensions are more flexible than regular dimensions. For more information about parent-child dimensions, see Parent-Child Dimensions.
In DSO, a parent-child dimension has a maximum of two levels: the (All) level, which is optional, and a second level that acts as a template for building other levels.
You can create a parent-child dimension by setting the SubClassType property to sbclsParentChild. When you define a parent-child dimension, the system uses source table data to build a dimension that has a level hierarchy of variable depth.
Virtual Dimensions
Virtual dimensions are based on the columns or member properties of existing regular dimensions, and they can provide additional information in cubes that use these regular dimensions. For more information about virtual dimensions, see Virtual Dimensions.
To build a virtual dimension using DSO, create a regular dimension and set its IsVirtual property to True. Next, set the DependsOnDimension property to refer to the underlying source dimension. Finally, create levels for the virtual dimension and configure the MemberKeyColumn property of each virtual level to point to a source level or member property in the source dimension.
Although it is possible to create a shared virtual dimension that is based on a private dimension, this virtual dimension will work only in the cube that contains the private dimension.
Note
In Microsoft® SQL Server™ 7.0 OLAP Services, virtual dimensions were limited to having an (All) level and a second level whose SubClassType was set to sbclsVirtual. The dimension itself had a SubClassType of sbclsRegular. This convention is still supported for compatibility, but the new method of setting the dimension's IsVirtual property to True should be used, because it is more flexible and efficient.
Examples
A. Adding a Parent-Child Dimension to a Database
The following example shows the addition of a parent-child dimension to a database:
Dim dsoServer As New DSO.Server
Dim dsoDB As DSO.MDStore
Dim dsoDim As DSO.Dimension
Dim dsoLevel As DSO.Level
' Connect to local Analysis server
dsoServer.Connect "LocalHost"
' Open FoodMart 2000 database
Set dsoDB = dsoServer.MDStores("FoodMart 2000")
' Add a new dimension and set the data source
Set dsoDim = dsoDB.Dimensions.AddNew("ParentChild Dimension", _
sbclsParentChild)
Set dsoDim.DataSource = dsoDB.DataSources("FoodMart")
' Add a new level to the new dimension.
Set dsoLevel = dsoDim.Levels.AddNew("Template Level", _
sbclsParentChild)
' Set the member key, parent key, and member name
' columns for the new level.
With dsoLevel
.MemberKeyColumn = """Account"".""account_id"""
.ParentKeyColumn = """Account"".""account_parent"""
.MemberNameColumn = """Account"".""account_description"""
End With
' Update the dimension.
dsoDim.Update
B. Creating a Database with Two Dimensions
This example shows how to create a database with two dimensions, DbDimA and DbDimB, and three cubes, CubeX, CubeY, and CubeZ. DbDimA is shared, but DbDimB is private to CubeZ. DbDimA can be associated with any or all of the cubes, but DbDimB can be associated only with CubeZ.
Dim dsoServer As New DSO.Server
Dim dsoDS As DSO.DataSource
Dim dsoCubeX As DSO.MDStore
Dim dsoCubeY As DSO.MDStore
Dim dsoCubeZ As DSO.MDStore
Dim dsoDbDimA As DSO.Dimension
Dim dsoDbDimB As DSO.Dimension
Dim strDSName As String, strDSConnect As String
' Set data source name and connection string
' to be used later.
strDSName = "FoodMart"
strDSConnect = "Provider=MSDASQL.1;User ID=sa;" & _
"Data Source=FoodMart;Connect Timeout=15"
' Connect to local Analysis server.
dsoServer.Connect "LocalHost"
' Create a new database.
Set dsoDB = dsoServer.MDStores.AddNew("TestDB")
' Create a new data source.
Set dsoDS = dsoDB.DataSources.AddNew(strDSName)
' Set the data source name and connection string
' properties for the data source.
With dsoDS
.Name = strDSName
.ConnectionString = strDSConnect
.Update
End With
' Create three new cubes.
Set dsoCubeX = dsoDB.MDStores.AddNew("CubeX")
Set dsoCubeY = dsoDB.MDStores.AddNew("CubeY")
Set dsoCubeZ = dsoDB.MDStores.AddNew("CubeZ")
' Create a shared dimension.
Set dsoDbDimA = dsoDB.Dimensions.AddNew("DbDimA") 'Shared
' Associate the shared dimension with CubeX and CubeY.
dsoCubeX.Dimensions.AddNew "DbDimA"
dsoCubeY.Dimensions.AddNew "DbDimA"
' Create a private dimension. The use of the
' caret (^) character separates the cube name
' and the private dimension name.
Set dsoDbDimB = dsoDB.Dimensions.AddNew("CubeZ^DbDimB")
' Associate the private dimension with CubeZ.
dsoCubeZ.Dimensions.AddNew ("CubeZ^DbDimB")