Home Forums Data Warehousing Strategies and Ideas Snowflaking Dimensions RE: Snowflaking Dimensions

  • February 9, 2011 at 1:06 pm

    #1284791

    PaulB-TheOneAndOnly (2/9/2011)

    1.a - I do agree a DimAgent dimension table is needed.

    1.b - How about having two FactPolicyTransaction columns named AgentPolicy and AgentTransaction.

    I assume FactPolicyTransaction table will be sliced and diced by a handful of dimensions including AgentPolicy and AgentTransaction so it seams like a reasonable solution.

    2 - You need two tables, one describing Brokers and one describing Agents.

    1a: I think you're saying DimPolicy should be snowflaked into DimAgent and DimPolicy, where DimPolicy contains the FK of DimAgent. I'm thinking the same thing. However, on to 1b.

    1b: Makes sense, as long as all queries would go through the Fact table. In many cases, users will need a list of DimAgent(s) and the DimPolicy(ies) for those agents. Using your proposed solution, you're basically have DimAgency have a FK in both DimPolicy as well as FactPolicyTransaction. As long as I'm not breaking any bad rules by having this triangle between DimAgency, DimPolicy, and FactPolicyTransaction, that works for me.

    2: Based on your suggestion, that would mean the new DimBroker table wouldn't have its key stored anywhere except for DimAgent, and DimAgent would have its key used in any appropriate Fact tables. Is that correct?

    Thanks!