Example of slowly moving dimensions

Slowly moving dimensions are dimensions that are not frequently updated.

Slowly moving dimensions are updated in two phases:

  • The first phase handles transactions in the source with members that are unknown to the target. For example, this is typically the result of an added product, company, or period in the source.
  • The second phase updates one or many dimensions in the target.

For the system-created aggregation nodes, the naming convention @Aggr can be changed in the FAP client.

This example shows the changes in the source database and target cube during the different phases of an update.

In the following figure, A1, A2, B1, and B2 are different products that belong to different product groups.

Figure 1. Example of dimensions in source and cube
In the source products A1 and A2 belong to product group A. Products B1 and B2 belong to group B. In the target, leaf members are aggregated.

In the source, if product group C and product C1 are added, and product B2 is moved, this change is initially not reflected in the target. After transactions that correspond to the new product members (C and C1) are detected, the new members are added to the target.

These new members are not connected in the hierarchy, but are collected under a "not connected" node. The only dimensional operation that occurs during this stage is to add the new leaf members. The IBM® Cognos® Controller Financial Analytics Publisher server tracks all valid leaf members in all dimensions to detect the new ones. A new leaf node is added before the corresponding transaction is imported into the target.

In the following figure, the new source product groups and products that are detected in the source, are added as new, but unconnected leaf members in the target.

Figure 2. Transformation of slowly moving dimensions, phase 1
In source, group C and product C1 are added. Product B2 is moved to group C. In the target, leaf members C and C1 are added, but not connected. B2 still connected to @Aggr.B node.

The second phase of the slowly moving dimension transformation addresses the inconsistency between the source and the target. In the IBM Cognos Controller Financial Analytics Publisher client, when you detect that there are pending source dimension changes, you can choose to update the TM1® dimension. This updating process is manually triggered for the following reasons:

  • It is not specified how an automated detection of a dimension update in the target should be updated from the source.
  • There is a disadvantage with automatic updating because every small change in the source results in a costly operation in the target. For example, it is best to not trigger an update after each change if you make significant changes to the product hierarchy.
  • Even if performance is not a concern, the user might not want the update to be triggered automatically. A dimensional update can invalidate reports and it might be best to wait until a certain time, for example, after the closing process is finished.

After you manually trigger dimensional updating, the target is updated to contain new members, a restructured aggregation hierarchy, and changed names (aliases on the target side). The following figure shows this second phase of transformation.

Figure 3. Transformation of slowly moving dimensions, phase 2
The updated target shows leaf members C, C1, and B2 are now connected to new @Aggr.C node.