INF: The Impact of Using Changing Dimensions (303408)

MORE INFORMATION

First, remember that adding new members does not require changing dimensions. You can add new time periods, products, customers, and so forth without the use of the changing dimension feature. Changing dimensions allow you to move a member to a different parent. For example, a sales person may move from one territory to another. For more information about Changing Dimensions, see SQL Server 2000 Books Online.

Administrative Effect

You cannot use a changing dimension to set slices on partitions. When you create or edit a partition, you are given an option to select a data slice (that is, a subset of cube data) to store in the partition. You can pick a member from the selected dimension to be the data slice. For example, pick [Store].[All Stores].[USA].[CA].[Los Angeles].[Store 7]. The [Store] dimension is a changing dimension. If [Store 7] moves from [Los Angeles] to [San Diego], then the data slice you picked is no longer valid. That is why you cannot use a changing dimension to set the data slice on partitions. In fact, the Partition Wizard does not display changing dimensions in the list of available dimensions for the slice.

Processing Benefits and Effect

The major benefit of changing dimensions is that you do not have to re-process a cube when a member is moved. However, aggregations on the cube can be invalidated by such a change. If a member is moved, the system marks aggregations between the All level and the lowest level as "soft" aggregations, meaning that the "soft" aggregations can be dropped and re-computed when the dimension changes.

When the system detects a hierarchy change in a changing dimension, the "soft" aggregations on all cubes that are using that dimension are dropped and recomputed as a background activity. The computation begins after the dimension is processed, and continues in the background until all the cubes involved have been re-aggregated. The system does not lock the cubes during this time and users can still query the cubes.

The net effect is that cubes stay available for querying, and the cube data is not invalidated, so the Analysis Service does not have to re-process all the data from the relational database.

Query Benefits and Effect

The key benefit of using changing dimensions is that cubes that use the changing dimensions do not become unavailable when a dimension member is moved from one parent to another.

While a cube is still available for querying, one of the primary mechanisms for making OLAP queries fast is temporarily unavailable. The reason for this behavior is that after the "soft" aggregations are dropped and until they have been re-computed, queries do not benefit from pre-computed aggregations. A query that is requesting an aggregated data set, which might normally have been directly available or easily computable from another aggregation, must be computed from the fact level data. The effect is a substantial query performance penalty during the re-aggregation process.

Because of the re-aggregation process, even though cubes are fully available following a dimension change, it is still better to make such a change during the lightest use periods for the Analysis system.