Working with Virtual Cubes

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.

A cube object with a SubClassType of sbclsVirtual is a virtual cube. A virtual cube is used to encapsulate a subset of the measures, dimensions, and levels contained in one or more cubes. A virtual cube, like a view in a relational database, is a logical construct that itself contains no data. Just as a view is a join of multiple relations, a virtual cube is a join of multiple cubes.

The basic rule for using virtual cubes is that you add them to a database as a cube with the SubClassType parameter set to sbclsVirtual, and then add dimensions and measures to them as needed. However, the dimensions and measures are derived from previously defined cubes within the database rather than from a dimension table. Any levels associated with a dimension that has been added to a virtual cube automatically apply to the dimension in the virtual cube. Partitions and aggregations do not apply to virtual cubes.

If the structure for a virtual cube is changed, you must reprocess the virtual cube so that, just as with a regular cube, the data supporting the structure change can be reprocessed. The same holds true if you alter the structure of a regular cube used by a virtual cube; the regular cube needs to be reprocessed, and then the virtual cube also needs to be reprocessed.

Source Cubes and Source Dimensions

A virtual cube can contain any number of source cubes, including linked cubes, as long as they are from the same database.

Virtual cubes do not inherit the roles, calculated members, or actions that are assigned to their source cubes. After a virtual cube has been created, you must re-create these objects (or design different ones). The information needed to re-create the roles, calculated members or actions can be derived by reading the structures of the underlying regular cubes.

Other shared dimensions from the database but not from an included cube are also acceptable in a virtual cube. These dimensions require custom rollup expressions on their levels. Without the custom expressions, the server will not be able to find the dimension's data because the dimension references columns are not in the fact table.

Differences of Virtual Cubes

Because a virtual cube is based on the contents of one or more existing cubes, some of the properties and collections for a virtual cube object are not available, or they have a different meaning from their counterparts in a regular cube. An attempt to set or retrieve an unavailable property results in an error. The following table lists the properties and collections that are different for virtual cubes.

Property or collection	Description
AggregationPrefix	A virtual cube does not use aggregation prefixes.
Analyzer	A virtual cube does not have an analyzer object.
DataSources	A virtual cube does not have a DataSources collection.
EstimatedRows	For a virtual cube, this property is read-only and contains the number of rows in all underlying cubes.
FromClause	A virtual cube does not have a FROM clause.
JoinClause	A virtual cube does not have a JOIN clause.
MDStores	For a virtual cube, this collection contains the underlying cubes instead of the cube partitions.
OlapMode	A virtual cube does not use the OlapMode property.
SourceTable	A virtual cube does not have its own fact table.
SourceTableAlias	A virtual cube does not have its own fact table.
SourceTableFilter	A virtual cube does not have its own fact table.
Dimension.DataSource	The dimensions in a virtual cube do not have data sources.

Add a Virtual Cube

The process of adding a virtual cube is largely the same as the process of adding a regular cube. There are minor differences as noted in the table and as illustrated in the following code example.

Note

  The following example depends on the existence of the TestCube regular cube, created in previous code examples.

The following code example creates a new virtual cube, named VirtualCube, based on the TestCube cube in the TestDB database, created in earlier code examples:

Private Sub AddVirtualCube()
    Dim dsoServer As New DSO.Server
    Dim dsoDB As DSO.MDStore
    Dim dsoCube As DSO.MDStore
    Dim dsoDim As DSO.Dimension
    Dim dsoMea As DSO.Measure

    Dim strDBName As String
    Dim strCubeName As String

    ' Initialize variables for the database and
    ' virtual cube names.
    strDBName = "TestDB"
    strCubeName = "VirtualCube"

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

    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)

        ' Create the virtual cube in the MDStores collection
        ' of the database object.
        Set dsoCube = dsoDB.MDStores.AddNew(strCubeName, sbclsVirtual)

        ' Set the description for the virtual cube.
        dsoCube.Description = "The TestDB virtual cube"

        ' Set the source dimensions for the virtual cube by
        ' copying the dimensions from the underlying cube.
        For Each dsoDim In dsoDB.MDStores("TestCube").Dimensions
            dsoCube.Dimensions.AddNew dsoDim.Name
        Next

        ' Add measures to the virtual cube from the underlying cube.
        ' Measures for virtual cubes have the format
        ' [Cube Name].[Measure Name]

        ' Create the Unit Sales measure.
        Set dsoMea = dsoCube.Measures.AddNew("Unit Sales")
        dsoMea.SourceColumn = "[TestCube].[Unit Sales]"

        ' Create the Store Sales measure.
        Set dsoMea = dsoCube.Measures.AddNew("Store Sales")
        dsoMea.SourceColumn = "[TestCube].[Store Sales]"

        ' Save cube structure changes.
        dsoCube.Update

        ' Process the cube so that it can be used by client applications.
        dsoCube.Process

    End If

End Sub

See Also

Concepts

Working with Virtual Cubes
Working with Dimensions and Levels
Working with Cubes and Measures

Help and Information

Getting SQL Server 2005 Assistance