Thursday, October 28, 2010

Dimension has no attributes…Can I make it as Degenerated Dimension?

One of my friends recently came up with an interesting topic where he has faced an issue with his Data Warehouse design related to Insurance industry. He has come across a situation where he needs to build a dimension for Insurance Claim numbers that holds ONLY claim numbers. He has already build other related dimensions such as Customer and Product, and has built the Fact table too.

This particular Insurance Claim number is one of the attributes set at grain level of the fact. In addition to that, no common claim numbers, all are unique, and number of claim numbers are grown at the same rate that fact table grows. He wanted my opinion on this, thought degenerate the claim number without maintaining a separate dimension. The below image explains what I suggested;

As you see, first diagram has a dimension for maintaining claim numbers. The second has no dimension for claim numbers and claim number has been added to the Fact table. The best option I see is, making it as a degenerated dimension because;

  • Number of records in the ClaimNumber dimension grows at the same rate that Fact table grows.
  • Claim number has no other descriptive attributes.
  • Claim number does not exist outside the transaction (the claim).

I am facing a similar issue on Banking related DW, the Fact focuses on Deposits and the transaction related deposits contain Comments. Comments are not available for all transactions and they are not common among transactions too. I do not like to see NULLs in my Fact table but thought to degenerate into Fact table. I wish I could argue on this with someone……… :)

If you need to read more on Degenerated Dimension, read Kimballl Design Tip #46 at


Bineesh said...

Hi Dinesh,
I have a similar situation now. I did the same thing - removed claim dimension. Now I was thinking about to add a degenerate dimension for claim number , then I found your post with exactly same scenario.
The second case you mentioned, I would suggest to keep the comment column in the same fact table. Because in effect that would not make any difference if degenerated. We will never go back to comment table and search/dig back. You can create 2 different physical view/logical view /perspective when you are at BI front.

Dinesh Priyankara said...

Hi Bineesh,

Thanks for the comment and sharing info. Yes, I did the same for second scenario, so far, did not find any issue with that implementation :).