Working with Linked Cubes

Working with Linked Cubes

A linked cube is an MDStore cube object with a SubClassType of sbclsLinked. The contents of a linked cube are based on another cube that is defined and stored on a different Analysis server. Unlike a virtual cube, which can contain portions of one or more cubes, a linked cube references the entire contents of a single cube.

A subscribing server is an Analysis server that contains a linked cube. A publishing server contains the source cube upon which the linked cube is based. To be a subscribing server, the Analysis server service (MSSQLServerOLAPService) must run under an account that has query permissions on each publishing server to which it connects. This account can be an account that belongs to the OLAP Administrators group on the publishing server, or an account that has query permissions established by a role on each source cube to which the subscribing server needs access. There are no requirements an Analysis server has to meet in order to become a publisher. Any processed cube on the publishing server can be made available for linking, subject to network and cube security; the cube must be available for use by the subscribing server as if the subscribing server were a client querying the cube on the publishing server.

All dimensions in a linked cube are treated as private dimensions on the subscribing server. This means that other regular cubes in the subscribing database cannot use these dimensions. A linked cube can be included in a virtual cube.

Linked cubes cannot be created from regular cubes that employ shared or private ROLAP dimensions.

Differences of Linked Cubes

Because a linked cube is based on the contents of an existing cube, some of the properties for the linked cube object are not supported, or they have a meaning that is different from their counterparts in a regular cube. An attempt to set or retrieve an unsupported property results in an error. The properties that are different for linked cubes are listed in the following table.

Add a Linked Cube

The following code example illustrates the steps needed to create a linked cube.

Note  This procedure must involve two different servers: a publishing server and a subscribing server. Attempting to create a link to a cube on the same server causes an error.

Example

The following code example creates a linked cube by joining the Sales cube from the FoodMart 2000 database on the publishing server to a new cube, named LinkedCube, to the TestDB database on the subscribing server:

Private Sub AddLinkedCube()
    Dim dsoServer As New DSO.Server
    Dim dsoDB As DSO.MDStore
    Dim dsoCube As DSO.MDStore
    Dim dsoLDS As DSO.DataSource

    Dim strDBName As String
    Dim strCubeName As String
    Dim strServerName As String

    ' Initialize variables for the database and
    ' linked cube names.
    strDBName = "TestDB"
    strCubeName = "LinkedCube"

    ' The following variable should be set to the name
    ' of the publishing server.
    strServerName = "servername"

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

    If dsoServer.MDStores.Find(strDBName) = False Then
        MsgBox "Database " & strDBName & _
            " is not found."
    Else
        ' Get a reference for the database that
        ' will contain the linked cube.
        Set dsoDB = dsoServer.MDStores(strDBName)

        ' Create a new data source for the linked cube.
        Set dsoLDS = dsoDB.DataSources.AddNew("PublishingServer")

        ' Set the connection string, so that the data source points
        ' to an Analysis server running SQL Server 2000 Analysis Services 
        ' or later, installed with the FoodMart 2000 database.
        dsoLDS.ConnectionString = "Provider=MSOLAP;" & _
            "Data Source=" & strServerName & ";" & _
            "Initial Catalog=FoodMart 2000;"

        ' Save this data source in the repository.
        dsoLDS.Update

        ' Create a new cube on the local server, mark it as linked.
        Set dsoCube = dsoDB.MDStores.AddNew(strCubeName, sbclsLinked)

        ' Add dsoLDS to the DataSources collection of the linked cube.
        dsoCube.DataSources.Add dsoLDS

        ' Use the name of the published cube as the
        ' source table for the subscribed cube.
        dsoCube.SourceTable = """" & "Sales" & """"

        ' Update the cube. This creates the link.
        dsoCube.Update

        ' Commit the changes to the subscribing server.
        dsoCube.Process processFull
    End If

End Sub