Working with Virtual Dimensions

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.

The steps for creating virtual dimensions with Decision Support Objects (DSO) are similar to those used to create regular dimensions. To create a virtual dimension based on the columns of another dimension, create the dimension normally, but set the IsVirtual property to True and set the DependsOnDimension property to the name of the source dimension. Creating a virtual dimension based on the member properties of a regular dimension is more complicated. The procedure is outlined in the code sample at the end of this topic.

Differences of Virtual Dimensions

Because a virtual dimension is based on the contents of an existing dimension, many of the properties for the virtual dimension object and its level objects are read-only and do not need to be set before the dimension is processed. The remaining properties for the dimension and level objects must be set to refer to the underlying dimension and/or member properties that provide the source data for the virtual dimension.

The following table lists dimension and level properties that are read-only or ignored for virtual dimensions.

Object property	Description
Dimension.FromClause	Read-only. It is taken from the source dimension.
Dimension.IsChanging	Always TRUE for a virtual dimension created using Microsoft® SQL Server™ 2000 Analysis Services.
Dimension.JoinClause	Read-only. It is taken from the source dimension.
Dimension.StorageMode	Always storeasMOLAP for a virtual dimension.
Dimension.SourceTableFilter	Read-only. It is taken from the source dimension.
Dimension.SourceTableAlias	Read-only. It is taken from the source dimension.
Level.EstimatedSize	Not used for a level in a virtual dimension.
Level.Grouping	Always groupingNone for a level in a virtual dimension.
Level.HideMemberIf	Always hideNever for a level in a virtual dimension.

Add a Virtual Dimension

Use the following code example to create a virtual dimension. The virtual dimension, except as noted in the table, is treated as any other dimension.

The following code example creates the Store Size in SQFT virtual dimension in the TestDB database. This virtual dimension is based on a member property, Store SQFT, of the Stores source dimension:

Private Sub AddVirtualDimension()
    Dim dsoServer As New DSO.Server
    Dim dsoDB As DSO.MDStore
    Dim dsoDS As DSO.DataSource
    Dim dsoDim As DSO.Dimension
    Dim dsoLevel As DSO.Level

    Dim strDBName As String
    Dim strLQuote As String
    Dim strRQuote As String

    ' Define constants used for the ColumnType property
    ' of the DSO.Level object.
    ' Note that these constants are identical to
    ' those used in ADO in the DataTypeEnum enumeration.
    Const adDouble = 5

    ' Initialize variable for the database.
    strDBName = "TestDB"

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

    ' Ensure that the server has an existing database.
    If dsoServer.MDStores.Find(strDBName) = False Then
        MsgBox "Database " & strDBName & _
            " is not found."
    Else
        ' Retrieve the database from the server.
        Set dsoDB = dsoServer.MDStores(strDBName)

        ' Retrieve a data source from the database.
        Set dsoDS = dsoDB.DataSources("FoodMart")

        ' Get the delimiter characters from the data source.
        strLQuote = dsoDS.OpenQuoteChar
        strRQuote = dsoDS.CloseQuoteChar

        ' Create the new dimension in the Dimensions
        ' collection of the database object.
        Set dsoDim = dsoDB.Dimensions.AddNew("Store Size in SQFT")

        ' Set the description of the dimension.
        dsoDim.Description = "The Store Size in SQFT virtual dimension"

        ' Set the data source of the dimension.
        Set dsoDim.DataSource = dsoDS

        ' Set the dimension type, make it virtual,
        ' and identify its underlying source dimension.
        dsoDim.DimensionType = dimRegular
        dsoDim.IsVirtual = True
        dsoDim.DependsOnDimension = "Stores"

        ' Next, create the levels.
        ' Start with the (All) level.
        Set dsoLevel = dsoDim.Levels.AddNew("(All)")

        ' Set the level type.
        dsoLevel.LevelType = levAll

        ' Set the MemberKeyColumn of the (All) level to a constant
        ' that also acts as the name of the level's only member.
        dsoLevel.MemberKeyColumn = "(All Store Sizes)"

        ' Create the Store SQFT level. This holds the SQFT value.
        Set dsoLevel = dsoDim.Levels.AddNew("Store Size")

        ' Name the source column for this level.
        ' The format for this is "table_name"."column_name".
        ' Database-specific delimiter characters are required.
        dsoLevel.MemberKeyColumn = strLQuote & "store" & strRQuote & "." & _
                                   strLQuote & "store_sqft" & strRQuote

        ' Set the following properties to be identical to their
        ' counterparts in the member property object that provides
        ' this level with its data.
        dsoLevel.ColumnType = adDouble
        dsoLevel.ColumnSize = 4

        ' Check to see that you set the level and
        ' dimension properties correctly, and that the rest
        ' of the dimension structure is correct. If so,
        ' update the repository and exit the function.
        If dsoLevel.IsValid And dsoDim.IsValid Then
            ' Update the dimension.
            dsoDim.Update

            ' Inform the user.
            MsgBox "Virtual dimension has been added."
        End If
    End If

End Sub