Minidimension

Minidimension is described in Ralph Kimbal’s the Data Warehouse Toolkit book.

It takes the frequently analyzed or frequently changed attributes off the huge rapidly changing monster dimension and create the possible combination of the the attribute set as a separate dimension. If the attributes are typically analyzed together, put them as an attribute set in a single minidimension table.

Why create minidimensions?

It is mainly to improve the data warehouse performance.

The more attributes in a dimension table, the more changes per object can be expected. That means the more rows are created if you take the SCD type 2 approach. By removing the frequently changed attributes off the original dimension table or stop tracking the historical changes to these attributes in the original dimension table. The number of records in the original table can be reduced.

The steps to create/introduce a minidimension:

1. Identify the frequently analyzed or frequently changed attributes.

2. If any attribute contain the numeric continuous variables, convert them to discrete value or range value in the minidimension. For example, if you have the age column, convert the age column from the original dimension to the age group in the demography minidimension. Note: “Age” seems not a very frequent changed attribute, but it could be very frequently analyzed.

3. Create a new table to hold these attributes. The table should be very small with all the possible combination of the values. For example, if you have the 8 columns and each column has 10 possible values, the table will have 100,000,000 rows.

4. Add a new foreign key to the fact tables that may be analyzed by the attributes of the minidimension. The candidate fact tables are typically a periodic snapshot table.

5. Change the ETL to populate the new mindimesnion table. You need to design for both one time full load and incremental load.

6. Change the ETL to resolve the FK from the fact table to the new minidimension table. A full update to the fact tables as a one time change and change the logic of resolving the warehouse FK in your on going ETL.

7. Change the Analytics repository to use the minidimension table for analysis. Change the SQL in your reports to leverage the new minidimension.

Related Design Tips:

 Use Minidimension to handle the rapidly changing monster dimension