October 8, 2013 at 5:01 am
Hello,
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!
Thanks
Lempster
October 8, 2013 at 10:27 am
You missed bridge table 😉
How often does an advisors qualifications change?
When an Advisor qualifies for a new code, is that an event?
October 9, 2013 at 3:09 am
Rarely and no, it wouldn't qualify as an event. I'm waiting to hear back from 'the Business' on whether they are interested in analysing data by individual qualification and their answer will dictate the solution I go with.
December 10, 2013 at 4:58 am
Thought I should update this post! I decided to go with a Bridge or 'Factless Fact' table to meet my requirement.
January 4, 2014 at 2:25 pm
Your final decision sounds like the appropriate one. As you may know, sometimes a table can act as both a fact and dimension. It all depends upon the business questions the users want to answer--just be careful with the bridge table if you are going to use Analysis Services with this model.
BIDSHELPER and this whitepaper can assist you with this should your solution require an Analysis Services layer:
http://www.microsoft.com/en-us/download/details.aspx?id=137
http://bidshelper.codeplex.com/wikipage?title=Many-to-Many%20Matrix%20Compression
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply