Sideout1972 (2/9/2011)
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?
I'm not convinced a snowflake is needed. Seeing ER model wouldn't hurt.
In a Data Warehouse environment the data users want to see is the data stored in FACT tables, that data will be sliced and diced using the DIMension tables of the proper datamart.
FACT table in this case will contain both an AgentPolicy and an AgentTransaction columns, each one pointing to the rigth DIMension table for further description.
The use of Data Warehouse to get a "list of brokers" is not what Data Warehouse is meant for, that information should be provided by the OLTP system, nobody analyzes a list of brokers. Having said that, proposed structure even provides that.
Last but not least, DimBroker and DimAgent look to me like slowly changing dimensions - how are you planning to address them? would they be treated as Type 2 SCD?
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.