I'm after some help for the following scenario:
I'm creating a Adviser dimension table within the contect of a Financial Services DW and I can't decide whether to include the codes that any given Adviser is qualifed to advise on as attributes in the Adviser dimension or to create one (or more) 'outrigger' dimensions.
The advice that an Adviser is qualified to provide fall into a number of broad advice categories - General Insurance, Protection, Mortgage etc. - and within each of those categories are a number of codes that further breakdown the nature of advice that an Adviser may give, e.g. CodeAB500 could mean that an Adviser is qualified to provide Mortgage advice on properties that do not exceed $500K in value. So, any Adviser could have any number of codes across 1 or more of the advice categories.
So far I have considered the following options:
1.Treat each advice category as a separate column in the Adviser dimension table (which would be the source of separate attributes in the Adviser Dimension) and populate the column with a comma-separated list of codes.
2.Create a single 'AdviceCode' dimension table and include the PK of that table as an FK in the Adviser table. The problem with this approach as I see it is that the AdviceCode table would need to include a row for every possible combination of codes.
3.Create four different tables to hold the Advice Codes based on category, e.g. GIAdviceCode, MortageAdviceCode and then populate those tables with every possible combination of codes for the particular category. My Adviser dimension table would then contain a FK to each AdviceCode table.
4.Treat each Advice Code as a column (attribute) in its own right. This would result in a lot of blank or NULL columns in the Adviser dimension table; is that an issue?
5.Think of each Advice Code as similar to a Product in the AdventureWorks sample database and create one AdviceCode dimension table similar to option 2 above, but also create an Advice Category dimension table (analogous to the ProductCategory table) and include a FK to this table in the Advice Code table. The disadvantage of this option (I think) it that I would need to make the PK in the Adviser dimension table a composite key made up of the AdviserKey and the AdviceCodeKey and Kimball does not recommend that.
I'm currently leaning towards option 1 or option 4, but happy to have my opinion changed!