How to create Fact Dimension or Degenerate Dimension

Sometimes additional data in fact table might useful for users such as invoice number, purchase order numbers, some text fields like comments and complaints. When we define dimension based on a fact table item, the dimension is called a fact dimension and also known as degenerate dimension. The reason that we keep the data in fact table instead of having in separate dimension table is the dimension table would grow at the same rate as the fact table, and would just create duplicate data and unnecessary complexity.

For example: In adventure work data base FactInternetSales table has Sales Order Number. User might need to get the sales amount based on SalesOrderNumber.

Let see how to create Fact Dimension for this scenario. Create new dimension.

In Main table select InternetSales, and under key columns add “SalesOrderNumber” and “SalesOrderLineNumber”, under Name column Select SalesOrderLineNumber.

Unchecked all the Related tables

Select Sales Order Number, if you need some more details such as Customer PO Number.

One you create dimension, rename the attribute name as you want. Here I renamed Sales order Number as Item Details, and under name column select the EnglishProductName from product table.

Add the salesOrderNumber again into attribute panel, and set the name as Order Number and Order by Key.

You can choose the storage mode based on the requirement.

Add this new dimension in the cube .

Make sure that this dimension and the Measure Group relationship type as Fact under Dimension usage.

Now you can test the dimension as below.

Cheers!