Creating Degenerated dimension

Degenerate Dimensions

In a data warehouse, a degenerate dimension is a dimension which is derived from the fact table and doesn't have its own dimension table. Degenerate dimensions are often used when a fact table's grain represents transactional level data and one wishes to maintain system specific identifiers such as order numbers, invoice numbers and the like without forcing their inclusion in their own dimension. The decision to use degenerate dimensions is often based on the desire to provide a direct reference back to a transactional system without the overhead of maintaining a separate dimension table.

Once a cube is created, we can add degenerate dimension to the cube.
Below are the steps to create Degenerate Dimension:

1. Click on Add Cube Dimension icon in Dimension Usage tab as shown below. It will open Add Cube Dimension dialogue box.

2. Click on New dimension… button in Add Cube Dimension dialogue box.

3. It will open Dimension Wizard. Click on Next to continue…
4. Select required data source view and click on Next to continue…

















5. Select Standard dimension option and click on Next to continue…
6. Select Main Dimension table and Key columns. Click on Next to continue…  

7. Select Dimension Attributes. Click on Next to continue…  

8. Click on Next to continue…  

9. Now we are at Completing the Wizard. Give proper dimension name click Finish to complete.

 















10. Newly created dimension is now available in Dimension Usage tab. At the intersection of Fact Sales measure group and the Sales Order Number dimension, click the ellipsis button (…) in the Item Description cell to review the fact relationship properties.   

The Define Relationship dialog box opens. Notice that you cannot configure any of the properties. The Selected relationship type should be Fact.

The following image shows the fact relationship properties in the Define Relationship dialog box