Saturday, December 10, 2011

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


  1. Hi,

    Good Example,

    But still i am confusing about the above example and degenerated dimension..

    Why we deriving from fact table?
    We have already attributes in our fact but again we are deriving from fact table? Confusing
    Please provide simple excution?

    Thnaks In Advance


  2. I have One more query Hari,

    Recently microsoft launched SQL BI 2012 with BIGDATA(Hadoop).

    May i know what is mean by Hadoop? Is it data warehousing tool?
    Why we need Hadoop?
    Is it open source tool?


  3. Hello,

    I don't have the "New Dimension" button on the "Add Cube Dimension" dialog.
    Do I need to setup the cube in a particular way in order to have it appear?

    Thanks very much,


Here are few FREE resources you may find helpful.