Junk Dimension Automation

Overview

The Junk Dimension contains a row for all distinct combinations of the Junk Dimension attributes along with a key that identifies the specific combination. The Junk dimension attribute fields can be removed from the fact-table and replaced with the single-field reference to the junk dimension table.

Example

In this example, the fact table contains 5 indicators or flags that are suited for a junk dimension (Confirmed, Delivered, Fragile, Delivery Method and Invoiced):

Based on these data, the Junk Dimension will contain the following unique combinations of the Junk Dimension Attributes:

After removal of the Junk Dimension Attributes and addition of the Junk dimension reference to the fact table, it looks like this:

Add a Junk Dimension

To build a Junk Dimension table for a table in a staging database, follow the steps below:

1. Right click a table, navigate to Advanced and click Add Junk Dimension Table.

   

2. The Add Junk Dimension dialog box appears. By default, the table name will be "Dim<SourceTableName>Info ". To give the table another name type it in the Name box.

3. Choose which fields to include in the Junk Dimension in the list under Map the fields to add to the Junk DimensionBy default, the Field Name in the Junk Dimension table will be the same as the chosen field. To use another name, click the field in the Field Name column and rename it.

   

4. Select Use Binary Key to use a binary key for unique identification of the Junk Dimension Table.

   Note: The Use Binary Key option is not supported by Analysis Services cubes.

About Junk Dimension Table Keys

By default, the key that identifies a specific Junk Dimension combination is a BigInt representing a hashed version of the Junk Dimension attributes. A BigInt is an 8-byte integer. This makes it possible, but unlikely, that two specific combinations can be assigned the same ID. If you select Use Binary Key , the key will be based on a 20-byte Varbinary datatype which significantly lowers the risk of getting the same value for two different combinations. However, Varbinary fields are not supported by Analysis Services cubes.

Add fields to a Junk Dimension Table

When the junk dimension table has been added, you can add additional fields to the Junk Dimension by right clicking on the junk dimension table (In this example DmFactTableInfo) and clicking on Edit Junk Dimension Table to bring up the dialog box for choosing fields.

Work with the Junk Dimension Table

When you have added a Junk Dimension Table, it appears in the project tree with a yellow table icon with a "J" on it. You can add fields, lookup fields and transformations to the Junk Dimension Table as well as custom data and data inserts.

When you have added a Junk Dimension Table to a table in a staging base, the next step is to add the table and corresponding Junk Dimension Table to the data warehouse. You do not need to add the fields on the table that are part of the Junk Dimension. This saves you storage space in the database.

You can add fields to the Junk Dimension Table in the data warehouse. For instance, in the example shown above, you could add a Delivery Instructions field to tell if the delivery should be handled with care. This could be accomplished with a field transformation with conditions that set the field value to "Fragile" if the value of the Fragile field is "Yes" and "Standard" if the value of the Fragile field is not "Yes".

When the Junk Dimension Table is executed, it will insert non-existing junk dimension combinations from the fact table. The Junk dimension table has no truncation of the Raw table.