Degenerate dimension with example in data warehouse

What is a Degenerated dimension?

Degenerated dimension is a dimension table which is being derived from fact table columns.

In a case, a fact table contains more than one Boolean column. Group all Boolean columns into a single table along with the surrogate key. This new dimension table is called degenerated dimension.

We can call this as a junk dimension since it has only Boolean columns

Structure of  the table:

Typically it has surrogate key and other columns,

Surrogate key

Column1

.

.

ColumnN

Advantages of having dimension type:

As we are grouping all Boolean data’s into a single table most of the data’s would be reusable only. Hence the data space usage will be very less.

Example:

Consider a schema which contains a fact table called “Amount” and it has two Boolean columns “Is_veg” and “Is_domestic”.

Based on the junk dimension concept we can remove these two Boolean columns from the amount fact table and can be created a new dimension with the name Flag dimension.

For maintaining the referential integrity a surrogate key will be created in Flag dimension and the reference key will be linked with Amount fact table.

If the schema type is star schema, then the new Flag dimension reference directly mapped to the fact table. The mapping would be,

For snowflake schema type, Flag dimension reference will be mapped to another dimension (product dimension).