A Dimension Key is one which is present in Fact table and doesn’t have any related Attributes because all its attributes will be placed as measures. It cannot be joined to any Dimension table.
Purpose:
To avoid duplication and unnecessary data
Performance optimization
Effective and efficient way of carrying forwarding control numbers (such as invoice number, POS transaction number, etc) from OLTP to Data Warehouse.
When to use:
If Suppose there is a situation to create Separate Dimension Table which contains a single Column with a separate row for each transaction details and creating a Foreign Key in Fact table to join the Dimension table will result in creating same data twice which is of no use, to avoid these situation we go for Degenerate Dimension to move the Attribute field into Fact table.
Example:
If you have a dimension that only has ‘Order Number’ and ‘Order Line Number’, you would have a 1:1 relationship with the Fact table. Do you want to have two tables with a billion rows or one table with a billion rows. Therefore, this would be a degenerated dimension and Order Number and Order Line Number would be stored in the Fact table.
How to Achieve:
- 1. Under Manage Sources in Define source Tab select the data source and check Measure and Analyze Measure for Target Column
- 2. Under Grains Tab select the appropriate grain and click Save and Process Data.
- 3. Once Process Data is completed Successfully, Go to Data Flow under Define source Tab and Right click Dimension level and select Properties
The Physical Table Name should be like [DW_SF_] where SF (Snapshot Fact) indicates that it’s a Fact table. If the Physical table Name begins with [DW_DM_] then it’s normal Dimensional table indicating it did not set as a degenerate dimension.
