Sideout1972 (2/9/2011)
ssills (2/9/2011)
Looks to me as if there are two fact tables: policies and transactions, both have agent relationships. The former is SCD. The latter probably isn't. Whether broker may or may not be useful probably depends on whether that dimension has information unique from the agent dimension, or if there is a direct relationship between a fact and the broker in the source system. Broker, if it is tied to an agent, could also be SCD (hopefully, very slowly changing).An ERD would help answer some of these questions.
Policy wouldn't be a fact table - it would be a dimension. With that said, an option COULD be to have a factless fact table, which will join all of the dimensions together so there wouldn't be any triangular relationships between the dimensions, and that could solve the "list" type report queries.
As far as the ERD goes - are you talking about seeing the star schema or the OLTP data model? There are multiple source systems that are being pulled into staging tables, so there isn't an ERD to go by. It doesn't really matter how the source system databases are designed, since they do not reflect how we want the data to be reported.
ERD of your Data Warehouse - Star schema has to be based in something.
_____________________________________
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.