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 http://www.rkimball.com/html/designtipsPDF/DesignTips2003/KimballDT46AnotherLook.pdf.