Sideout1972 (2/9/2011)

---

Question 1: Building an insurance data warehouse, you have a DimPolicy table (for the insurance policy) and a FactPolicyTransaction table (for the transactions on that policy). There is an insurance agent that is responsible for the policy as a whole, and an insurance agent (which may be a different person) that is responsible for the transaction on that policy. There are a large number of attributes about the agent, which leads me to believe that putting all of the attributes in DimPolicy for the agent (where the agent is responsible for thousands of policies) doesn't make sense - therefore, you snowflake the agent, and create a DimAgent table which has it's PK as a FK within DimPolicy. Now you can tell which agent is responsible for the policy. Now, you also need a relationship between DimAgent and FactPolicyTransaction to denote which agent actually made the policy transaction. This creates a "triangle", since DimAgency has its key in both DimPolicy and FactPolicyTransaction, and DimPolicy also has its key in FactPolicyTransction. The question - is this "triangle" ok? It seems awkward to me, and against the philosophies of dimensional modeling.

Question 2: You have a company (brokerage), and an agent works for a company. There are 30+ attributes for the brokerage, and 20+ attributes for an agent. It seems funny to create a DimAgent dimension which includes all 30 of the brokerage's attributes repeated over and over again. By doing that, you will constantly need to run a "SELECT DISTINCT" on the DimAgent table to get a valid list of brokerages (since they are repeated over and over again). Is this a prime time to snowflake and create a DimBrokerage dimension as well?