Challenging Scenario with multi valued dimensions...

We have a following scenario which We need to model to track the application process in insurance industry. Following are the tables in the source system.

Agent
Agent Agreement
Agent Agreement Status - (Tracks the life cycle of the status like Pending, Active and Termainated with start and end dates)
Agent Role - (Tracks the role of an agent over time like Agent, Asst Manager, Division Manager etc.. with start and end dates)
Org Hierarchy - Tells us Agent's organisation hierarchy

Agent Agreeement is 1 to Many with Agent Agreeement Status and also 1 to Many with Agent Role. We were thinking of creating one dimension table for this structure but it brings up challenge as we have multiple life cycle items in one table. Question is what kind of dates would we use for Type-2 tracking for the history records. Even for daily loads when we try to put all these together we get kind of cartesian products as the status and role both changed on the same day.

So I am thinking of splitting these into three dimension tables and connect them through fact table.

1. Agent Agreement - This will have Agent, Agreement and his agreement Status
2. Agent Role - This table will have Agent's Role only
3. Organisation Hierarchy - This will have Organisation hierarchy of every Org.

Now, the challenge is that, one application can have multiple agents involved in it and we need a bridge table for that and each of those agent can be a part of different org's and each one will have a role also. How do we model this out ?

Thanks for your time...

Model it as an accumulating snapshot. Make agent and status dimensions as well and include effective period dates in the fact. Create a fact row whenever anything changes and expire the one it supersedes. No need for multi-valued dimensions.

Thanks for your reply...

We have designed it as an accumulating snapshot as this is tracking life cycle but I did not get what you are suggesting. We do have lot more of the dimensions attached to the fact table than agent and org hierarchy. We would like to know who are the agents worked on that application along with other details which is why we are planning to create a a bridge table. Agent will have a bridge table but what would happen to his org ?

You mentioned "Create a fact row whenever anything changes and expire the one it supersedes. "...
but in Accumulating snapshot we don't expire rows and create new ones. we just need to be update the existing record when process progresses through life cycle. Am I correct ?

Dimensions -

1) Application
2) Agent with org and role details, Org & role being Type 2 columns

Fact will have -- Application SK,App+Agent bridge SK , Application status columns with dates ( it is cumulative fact) & measures.

App+Agent bridge --> App SK, AgentGroup code ( 1-1 relationship b/w App SK and AgentGroup code)
AgentGroup table -- > AgentGroup Code,Agent ID (1-n relationship b/w AgentGroup code & Agent)

kaps wrote:Thanks for your reply...

We have designed it as an accumulating snapshot as this is tracking life cycle but I did not get what you are suggesting. We do have lot more of the dimensions attached to the fact table than agent and org hierarchy. We would like to know who are the agents worked on that application along with other details which is why we are planning to create a a bridge table. Agent will have a bridge table but what would happen to his org ?

It sounds like there could be multiple bridges. One way to avoid it is to increase the grain of the fact. If the grain included one row per agent per application, you don't need a bridge for the agent.

You mentioned "Create a fact row whenever anything changes and expire the one it supersedes. "...
but in Accumulating snapshot we don't expire rows and create new ones. we just need to be update the existing record when process progresses through life cycle. Am I correct ?

Depends on wither you need change history or not. Either way is fine.