Dimensions define the categories you use to organize multidimensional data. For example, typical dimensions in a sales cube might be Time, Store, Product, and Promotion. These dimensions enable you to analyze the sales of products by store and promotion over time. Dimensions organize the quantitative data provided by measures to answer questions about how much, what, where, when, and how, such as, “How many computers were sold in the NorthWest region in 1998 under the Student Discount promotion?”
A dimension provides a hierarchical organization to its members. For example, a typical Time dimension organizes time by years, quarters, months, and perhaps days. A Store dimension might organize stores geographically into countries, regions, states or provinces, cities, and individual stores. Product dimensions often organize products into categories, subcategories, brands, and product names. A Promotion dimension could describe special discounts that were applied to sale transactions.
Dimension hierarchies organize information into levels of granularity of data. For example, the Time dimension organizes its members from the least granular (all time in the database) through increasingly granular levels (years, quarters, and months) to the most granular level (days). Other dimensions organize different types of data, such as products, in similar levels of granularity. Dimension hierarchies provide the mechanism to summarize quantitative data (measures) into meaningful information.
In Decision Support Objects (DSO), dimensions are represented by objects that contain collections of other objects to represent levels within the dimensions. Typical levels for a time dimension are All, Year, Quarter, Month, and Day. The levels are defined by columns in the dimension table in the data warehouse database. When a dimension object is processed, the OLAP server constructs the dimension and its levels and populates them with members from the dimension table. For more information about DSO, see About Decision Support Objects.
DSO has several types of dimensions and contexts in which they are used: database dimensions, cube dimensions, partition dimensions, and aggregation dimensions. All dimensions are created in the Dimensions collection of a DSO database object. 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 provided that a join can be made between the dimension table and the cube’s fact table. Cube dimensions have the same name as the corresponding database dimension. When a dimension is added to a cube’s Dimensions collection, DSO automatically creates all of the levels in the cube dimension. A cube dimension inherits its properties from the corresponding database dimension, with some of the properties available for customization in the cube. For example, you can specify how a cube dimension is used in the design of aggregations by setting the dimension’s AggregationUsage property.
A partition’s dimensions relate to its parent cube’s dimensions in the same manner 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 different. An aggregation dimension specifies the granularity of a partition dimension in an aggregation (a table that contains precalculated summary data). Aggregation dimensions can have fewer number of levels than the corresponding partition dimension. For example, if a partition contains an aggregation that summarizes sales by year for stores and product brands, the Time aggregation dimension will have only two levels: All and Year. The Store and Product dimensions in this aggregation will have all of their respective levels.
DSO dimensions can be either shared or private. A shared dimension can be used in multiple cubes, but a private dimension can be used in only one cube. Unlike shared dimensions, private dimensions use a special naming convention to identify the cubes to which they belong. The name of a private dimension is constructed by using the cube name followed by the caret character (^) followed by the dimension name. For example, “NorthWestSales^Stores” would represent a private dimension of stores created for use in the NorthWestSales cube. This naming convention allows private dimensions in different cubes to have the same name - the cube name prefix ensures uniqueness within the database’s dimension collection. The qualifying cube name prefix of a private dimension name is not exposed in the user interface by the OLAP Manager. Instead, it uses the prefix to determine a dimension’s location in the tree view. To create a private dimension in DSO, name the dimension according to this convention.
You can determine whether a dimension is shared or private by testing its IsShared property to see whether it is True or False. This property is determined by DSO by examining the name of the dimension. The value of a dimension’s IsShared property is inherited by the cubes, their partitions, and their aggregations that use the dimension.
Except for the naming convention and usage restrictions, private dimensions are identical to shared dimensions.
All DSO dimension objects implement the Dimension interface, and you manipulate the dimension objects through the interface. The type of dimension is specified by the ClassType property of the dimension object. The ClassType of a database dimension is clsDatabaseDimension. The ClassType of cube, partition, and aggregation dimensions are clsCubeDimension, clsPartitionDimension, and clsAggregationDimension, respectively.
Dimensions reside in the Dimensions collection of the MDStore object that represents a database, cube, partition, or aggregation.
For more information about the Dimension interface collections, methods, and properties and applicability to the associated objects, see Collections, Methods, and Properties.
A virtual dimension is a special type of dimension. A virtual dimension can have only two levels, the default “All” level, and a second, virtual level that is made up of members of a different (regular) dimension’s member property. A virtual dimension can be used in a cube just like a regular dimension, except that the regular dimension whose member property is used in the virtual dimension must also be present in the same cube. Virtual dimensions enable you to create cubes that can analyze data in a variety of ways from a single dimension table. For example, you can create a virtual dimension that uses a member property containing product size and another that uses product color to analyze sales by size versus color.
A virtual dimension must have only two levels, the default “All” level, and a second level whose SubClassType property value is sbclsVirtual. In contrast, the SubClassType property for levels in a regular dimension is sbclsRegular. However, the dimension’s SubClassType value is sbclsRegular for both virtual and regular dimensions. To determine whether a dimension is a virtual dimension, check to see whether it contains a level whose SubClassType is sbclsVirtual.
Virtual dimensions can only be shared dimensions, not private. Regular dimensions that contain member properties used by virtual dimensions must also be shared dimensions.
The virtual level in a virtual dimension is constructed dynamically instead of being precalculated and stored in a cube. This saves storage space, but queries that use virtual dimensions are slower than ones that use only regular dimensions.
For more information about levels and virtual levels, see Level Interface. For more information about the SubClassTypes enumeration, see SubClassTypes in Enumerations.
| clsAggregationDimension | clsDatabaseDimension |
| clsCubeDimension | clsPartitionDimension |
The basic model for using dimensions is to create them as shared dimensions in a database object’s dimensions collection. 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 can be explicitly associated with virtual cubes that use the cube.
The following example illustrates creating a database with two dimensions, DbDimA and DbDimB, and three cubes, CubeX, CubeY, and CubeZ. DbDimA is to be shared, but DbDimB is to be private to the cube named CubeZ. DbDimA can be associated with any or all of the cubes, but DbDimB can only be associated with CubeZ.
'Create and connect to a server
Dim dsoServer As DSO.Server
Set dsoServer = New DSO.Server
'MyServer is the name of the Windows NT Server
'where the OLAP server is installed and running
Set dsoServer.Name = ("MyServer")
dsoServer.Connect
'Create database object
Dim dsoDB As DSO.MDStore
Set dsoDB = dsoServer.MDStores.AddNew("MyDB")
'Create cubes
Dim CubeX As MDStore
Dim CubeY As MDStore
Dim CubeZ As MDStore
Dim dsoDS As DSO.Datasource
Set dsoDS = dsoDB.Datasources(1)
Set CubeX = dsoDB.MDStores.AddNew("CubeX")
Set CubeY = dsoDB.MDStores.AddNew("CubeY")
Set CubeZ = dsoDB.MDStores.AddNew("CubeZ")
'Create shared dimension
Dim DbDimA As DSO.Dimension
Set DbDimA = dsoDB.Dimensions.AddNew("DimA") 'Shared
'Associate shared dimension with CubeX and CubeY
CubeX.Dimensions.AddNew("DimA")
CubeY.Dimensions.AddNew("DimA")
'Create private dimension
Dim DbDimB As DSO.Dimension
Set DbDimB = dsoDB.Dimensions.AddNew("CubeZ^DimB")
'Associate private dimension with CubeZ
CubeZ.Dimensions.AddNew("CubeZ^DimB")