Commission Rate Fact or Dimension
Page 1 of 1
Commission Rate Fact or Dimension
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 https://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.
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 https://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
Re: Commission Rate Fact or Dimension
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...
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...
Similar topics
» Store the tax rate as the attribute of product in product dimension table?
» interest rate basis (variable rate products)
» Rate Card as minidimension, attribute or fact
» Conformed Dimension for Transaction Fact and Accumulating Snapshot Fact Table
» interest rate basis (variable rate products)
» Rate Card as minidimension, attribute or fact
» Conformed Dimension for Transaction Fact and Accumulating Snapshot Fact Table
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum