Use Minidimension to handle the rapidly changing monster dimension

The concept of Minidimension was discussed in Margy Ross and Ralph Kimball’s article

 Slowly Changing Dimensions Are Not Always as Easy as 1, 2, 3, published in Intelligent Enterprise.

Here is design tip I got:

You should consider creating a minidimension if you would like to analyze a few attributes on a dimension, which has the following characteristics:

• The dimension has a huge number of members
• The attribute you want to analyze are frequently changed
• You need to point to the historical state from the fact (SCD type 2). The fact need to be analyzed by the historical values

By creating a separate minidimension table, you can change the attributes in the primary dimension from SCD type 2 columns to type 1 columns. Basically only the “current” state is stored in the dimension table and thus won’t result in multiple records in the dimensions for tracking the changes.

The facts table will then be added a new FK to the mini dimension table which will include the set of attributes in effective when the fact event takes places. So the history is carried by the mini-dimension FK.

Periodical Snapshot

There is another possible usage of mini-dimension. You can create a mini-dimension and make it as the dimension for the aggregated tables.

The mini-dimension under this case is very similar to the key flexfield concept existing in Oracle EBS. So the original dimension may or may not hold the historical changes on the original dimension. The aggregated data will hold the original image for a given point in time.

F or example, the aggregated sales by week fact includes the total sales by customer region and salesperson, both are the attributes of the customer dimension. The customer region and salesperson can be type 1 column and the sales fact has a FK to the customer dimension. The aggregated sales by week can be populated by summarizing the all the sales during the week using the customer attributes to lookup the minidimension FK. Even when the customer moves or reassigned to other sales region or salesperson at a later time, the aggregated snapshot data won’t be affected.

Dimension Attribute or Separate Dimension?

I feel that the criteria may also help to determine when you would like to create a dimension instead of capturing data as a dimension attribute.

A project manager of a project?  Is the project manager a dimension on itself  or it is simply an attribute of the project dimension?

If the project manager can  change during the lifecycle of a project and you do not want to thus make the project dimension as a type 2, create project manager as a separate dimension.