SQL SERVER 2005 / 2008 ANALYSIS SERVICES
I have 3 tables (more I have to consider later) and all 3 contain approx. 50 million rows. The first table contains details of tranactions (and will be a fact table) and the other two tables are dimension tables called 'Client' and 'Policy'. 'Client' and 'Policy' both contain around 50 columns. Many of the columns contain codes of a sort which suit the use of look-up tables, for example Client contains codes for 'occupation category' and 'employer occupation category', etc.
Would it be better to:
a. simply create dimensions based upon the Client and Policy tables as they are, or
b. create separate dimension tables attached to the Client and Policy tables for all the different codes (i.e. look-ups for their descriptions) and therefore create dimensions for these tables as well ?
(This would equate to a snow-flake design in which the Client and Policy tables have lots of lookups hanging off them).
Also, there is a direct relation (one to many) between the Client and Policy tables. Would I also set this relation to each other (i.e between the Client and Policy tables) as well as the relation of Client and Policy to the fact table ?