Facts duplicating

[ntaulbee]

I have several tables in my DW. Here is what my model looks like:

Policy Dim - Policy Fact - Coverage Dim - Coverage Fact - Agent Dim

One Policy has an account value on the Policy Fact. That policy can have multiple coverage and agent records. If I pull Policy and Agent (not showing anything from the Coverage tables) I have account value duplicating for every coverage.

Example: Policy A has an account value of 2,000. My query displays Policy A, 4,000, Agent A. Its duplicating because the policy has 2 coverages. Even though I am not pulling anything from that table, its using it in the relationship to get to the agent table.

Any ideas?

[bus_pass_man]

Two ideas.

1. 
This sounds like a bridge table situation.

2.
From the information that you've supplied I'm assuming this:
Coverage Dim is conformed to Policy Fact and Coverage Fact. 
Policy Dim only has a relationship to policy fact.
Agent dim only has a relationship to coverage fact.

One problem is that you seem to indicate that there are relationships between agent and policy, which would make sense as someone has to sell them and it explains why you are bringing agent into the query.  Have you modeled that?  From the information you supplied it looks like you have not, which is why your query is going through coverage fact.

Hope that helps point you the way.

[ntaulbee]

You are correct, there is a bridge table I forgot to mention that links Agent Dim to Coverage Fact. The same bridge table links Agency Dim to Coverage Fact. There could be one agency and multiple agents-If I query and pull just agency, policy and face amount (policy fact) and there are 3 agents it is showing the face amount as 9,000 instead of 3,000 because of those 3 agents (even though I am not showing the agent number).

Would you alias the bridge table instead of having both agent and agency come off of the same bridge?
Thank you!

[bus_pass_man]

...there is a bridge table I forgot to mention that links Agent Dim to Coverage Fact.

links Agent Dim to Policy dim?


Yeah try aliasing the bridge table and move one of the relationships to use it.  I suspect that the re-use of the bridge table in two dimensions is the cause of the unwanted referencing of the fact table in the query.  It would not surprise me that you would need to have each bridge table to deal with only one role.  If that doesn't work at least we have narrowed down the solution space.

Just to confirm, all 3 dimensions are conformed to both your fact tables.  Is that a correct understanding?


Another thing that might need to taken into account is slow change -- the coverage might change etc. -- so keep that in mind.