Incremental Updates
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.
Incremental updates allow you to keep the contents of a cube current without requiring you to reprocess the cube in full when you add new data. An incremental update involves creating a temporary partition, filling it with updated source data, processing the temporary partition, and then merging it into another partition in the cube.
Data to be added to a cube can come from the original fact table or from a separate fact table with a structure identical to the original. If you add data from the original fact table, take care not to duplicate data that already exists in the cube. Set the SourceTableFilter property before processing the created temporary partition to restrict the data that is imported from the fact table. Temporary partitions created for this purpose are indicated by a tilde (~) character preceding the name of the temporary partition.
If data to be added to a cube comes from the fact table from which the cube was originally created, a risk of duplicate aggregation occurs. The cube uses the SourceTableFilter property to screen incoming data from a fact table; when performing an incremental update, it adds the aggregations computed from the fact table to the aggregations stored by the cube. If the same table is run twice, once to construct the original aggregations, and again as part of an incremental update, you will receive the same data twice, added together in the cube. The SourceTableFilter property can be used to screen out existing, already aggregated data in the fact table, preventing duplicate aggregation.
If you add data from a fact table that includes new members of a dimension, you must also reprocess the affected dimension using the processRefreshData for the Process method of the dimension object.
Perform an Incremental Update
The following code example shows how to do an incremental update using a temporary partition based on a separate fact table.
The following code example performs an incremental update on the TestCube cube of the TestDB database by creating a temporary partition, adding a new table, and combining the partitions:
Private Sub IncrementalUpdate()
Dim dsoServer As New DSO.Server
Dim dsoDB As DSO.MDStore
Dim dsoCube As DSO.MDStore
Dim dsoPartition As DSO.MDStore
Dim dsoClonePartition As DSO.MDStore
Dim dsoMeasure As DSO.Measure
' Connect to the local server.
dsoServer.Connect "LocalHost"
' Set up the MDStore objects:
' database, cube, and partition.
Set dsoDB = dsoServer.MDStores.Item("TestDB")
Set dsoCube = dsoDB.MDStores.Item("TestCube")
Set dsoPartition = dsoCube.MDStores.Item("TestCube")
' Create a temporary partition to store the new data.
' Use the tilde character to indicate to the server
' that the partition is not permanent.
Set dsoClonePartition = dsoCube.MDStores.AddNew("~New TestCube Data")
' Clone the main partition to the temporary one.
dsoPartition.Clone dsoClonePartition, cloneMinorChildren
' Because this partition uses a different source table,
' the properties that identify the table must be changed
' (sales_fact_1997 becomes sales_fact_1998).
' If this update involved the original fact table, these
' properties would remain unchanged, but the SourceTableFilter
' property would have to be updated to prevent duplicate data
' from being imported during processing.
dsoClonePartition.SourceTable = "sales_fact_1998"
dsoClonePartition.FromClause = _
Replace(dsoClonePartition.FromClause, "1997", "1998")
dsoClonePartition.JoinClause = _
Replace(dsoClonePartition.JoinClause, "1997", "1998")
' It is also necessary to update the measures in the
' partition, because they reference the old fact table, too.
For Each dsoMeasure In dsoClonePartition.Measures
dsoMeasure.SourceColumn = _
Replace(dsoMeasure.SourceColumn, "1997", "1998")
Next
' Although this is not the case in this example, if the
' MemberKeyColumn or MemberNameColumn properties of any of the
' dimension levels are based on the fact table, they too must
' be updated.
' Process the temporary partition.
dsoClonePartition.Process processDefault
' Merge the two partitions.
dsoPartition.Merge "~New TestCube Data"
' Close the server object and exit the subroutine.
dsoServer.CloseServer
End Sub