Commission Rate Fact or Dimension

View previous topic View next topic Go down

Commission Rate Fact or Dimension

Post  Guest on Wed Oct 16, 2013 3:26 pm

Hi,
In our system, we have details of agents, policies and clients.
Commission Percentage is allocated to agents against the policies and is predefined for a period (between StartDate and EndDate).

agentId | PolicyId | CommissionRate | StartDate | EndDate
Agent1 | Policy1 | 1.5 | 2013-01-01 | 2020-12-31
Agent1 | Policy2 | 2.5 | 2013-01-01 | 2030-12-31

As mentioned in http://kimballgroup.forumotion.net/t1670-unit-price-fact-table, I understand that, 'Unit Price' should go to fact table as it changes everyday, but I am not sure if 'Commission Rate' goes to fact or dimension (as this is predefined for a period and not transactional data). I can use the Dimension key in other fact tables if I keep this data in dimension table (as mentioned by 'hang' in the same post).

Kindly advice.

Guest
Guest


Back to top Go down

Re: Commission Rate Fact or Dimension

Post  ngalemmo on Wed Oct 16, 2013 7:37 pm

Since it is at an intersection of three dimensions (agent, policy, and time) it is actually a snapshot fact. So, it should be maintained in a fact table.

To avoid the need to reference that table every time the rate is needed, it may also be prudent to reference that table during ETL when loading other facts and populate the rate in that fact as well. Wither this would work depends on how reliably this data is maintained. If it is subject to retroactive corrections, then it may not be a good idea to propagate the value and just leave it in it's snapshot fact and join to it when needed. It depends...
avatar
ngalemmo

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

View user profile http://aginity.com

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