use of degenerate dimension to physically join two logically related fact tables

I am looking for a sanity check on a design decision.

Here is the scenario:

The business domain is insurance, specifically life and annuity.

I have two fact tables - factPolicyTransaction and factCommissionTransaction.

Policy transactions, usually in the form of a premium payment, always result in one or more commission transactions. For example, a premium payment of $100 made by Widow Jones will land in factPolicyTransaction. In turn, the insurance company will make three commission payments: one payment of $1.50 to the writing agent, the second and third payments in the amount of $.50 to the two agents upline from the writing agent. Obviously, these transactions are captured in factCommissionTransaction. Errors are corrected by offsetting records.

The requirement is that business users must be able to drill thru from the premium payment to see the commission payments associated with it, and visa-versa.

From my research (which has included the related discussions on this board), I understand that using a degenerate dimension to bridge the two fact tables is appropriate, as in the following (simplified) model:

factPolicyTransaction

• PolicyTransactionId
  
• PolicyId
  
• PolicyTransactionAmount

dimPolicyTransaction

• PolicyTransactionId

factCommissionTransaction

• PolicyTransactionId
  
• AgentId
  
• CommissionTransactionAmount

I believe this to be a reasonable approach, but wanted to validate with others that this is the case.

Regards,
tsnider

Is dimPolicyTransaction an actual table? In which case where is your degenerate dimension?

I am assuming you mean to use PolicyTransactionId as the degnerate dimension. That's fine. You simply store the transction id in the fact table. Degenerate dimensions are just natural keys in the fact. They do not have dimension tables.

Let me see if I got this straight. You have 2 reports - one on premium another on commission. You want to be able to drill from one report to the other. You should be able to do that by having the reports send the PolicyTransactionId to one another during the drill down/up process. Drilling from one report to another usually involves sending a relevant filter information (PolicyTransactionId ) fom one report to the other.

If you want the Policy information on the Commission fact table, then put the Policy ID on the Commission Fact.

Bridge tables are usually used when the relationships between fact and dimension are wierd. Such as in medical when an office visit can result in multiple diagnosis and multiple treatments (seperate fact tables) and there is a need to link the 2. There is a many to many relationship between the diagnosis and treatment facts and therefore a bridge table is needed.

The only way you would need a bridge table is if a premium resulted in many commissions and a commission was linked to many premiums. 1 Commission was paid for multiple premiums.

Now, it could be the case where commissions were delayed so instead of a premium generating an immediate commission, the commission was bundled with 2 or more premiums and this would require a bridge table.