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

View previous topic View next topic Go down

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

Post  tsnider on Thu Feb 17, 2011 2:42 pm

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



tsnider

Posts : 1
Join date : 2011-02-16

View user profile

Back to top Go down

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

Post  ngalemmo on Thu Feb 17, 2011 6:43 pm

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

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

Post  Jeff Smith on Fri Feb 18, 2011 1:23 pm

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.

Jeff Smith

Posts : 471
Join date : 2009-02-03

View user profile

Back to top Go down

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

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum