Learning SSAS with Me – Degenerate Dimension

Introduction

There is another good concept that we must understand is Degenerated Dimension. We must understand it properly. The concept of Degenerate Dimension is little hazy, but we try to describe it with an example that it will be easier to understand and interesting.

Hope the session will be informative.

What is Degenerate Dimension?

Before going to the Degenerate Dimension we must understand little about fact table. A facts table must contains some reference key (Foreign Key which is used to join with Primary key of Dimension Table) and measures.

The reference key is used to join with Dimension Table Primary key (Star schema).

[ Fact Table ]  =  [ Reference Key of Dimension Table ]   +  [ Calculative Measures ]

Imagine a columns in the Fact Table that is not a Part of any Reference key or Calculative measures.

We just want to mean that, this type of key don’t have any Related Dimension Table. This type of key has certain characteristic.

o   High Cardinality – Means Unique in nature

o   It is a Natural key – Means a Business key

o   Don’t have any Related Dimension Table

The Degenerate Dimension is sometimes called the Fact Dimension. As the Dimension is constructed from the attribute columns of Fact Table not from the attribute columns of Dimension Table.

Little confusing? Let’s take an example to understand it properly.

Example of Degenerate Dimension

Here we are taking an example of fact table to identify the Degenerate Dimension key.

Name of Fact Table:FactSalesOrder

Reference Key (Used to Join with Dimension Table)			Degenerated Dimension	Facts
OrderDateKey	ProductKey	CostomerKey	OrderNo	OrderAmount	VatAmount
20170101	101	1	SORDER-1	20,000.00	2,000.00
20170102	102	2	SORDER-2	30,000.00	3,000.00
20170103	101	1	SORDER-3	12,000.00	1,200.00
20170104	102	2	SORDER-4	56,000.00	5,600.00

If we look at the Fact Table, we can find that

OrderDateKeyà Is used to make join with à Dimension Table – Time Dimension
ProductKeyà Is used to make join with à Dimension Table – Product Dimension
CustomerKeyà Is used to make join with à Dimension Table – Customer Dimension

Here in this case the Order No is a good candidate to make Degenerated Dimension. There is no separate table for that. This dimension attribute exists is the Fact Table.

What the Use of Degenerate Dimension

It is generally used for drill through action purpose. To make a drill through action we need a dimension and hence this degenerate dimension comes into picture.

Why we are not going to Create separate Table

Question in mind why we are not going to make the separate table for Degenerated Dimension although it has High Cardinality and Natural in nature.

o   If we make the spate Dimension with this single columns the volume of data within this dimension is huge. We mean to say that the volume of the Dimension is same as the volume of fact table.

o   If we make separate dimension table it just duplicating the data and unnecessary make the multi-dimensional model complex.

o   Beside this we have to create a surrogate key to join with Fact table, increase the complexity.

o   With the increment of Fact in fact table the Degenerated Dimension table size must be increased.

Hope you understand it.

Posted by: MR. JOYDEEP DAS