Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

Commission Rate Fact or Dimension

Go down

Commission Rate Fact or Dimension Empty Commission Rate Fact or Dimension

Post  Guest Wed Oct 16, 2013 3:26 pm

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, 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.


Back to top Go down

Commission Rate Fact or Dimension Empty Re: Commission Rate Fact or Dimension

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

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

Back to top Go down

Back to top

- Similar topics

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