Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Dimensional modelling help: 'outriggers' or not? Expand / Collapse
Author
Message
Posted Tuesday, October 08, 2013 5:01 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 9:12 AM
Points: 1,887, Visits: 1,179
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
Post #1502547
Posted Tuesday, October 08, 2013 10:27 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, April 07, 2014 11:04 AM
Points: 57, Visits: 613
You missed bridge table

How often does an advisors qualifications change?

When an Advisor qualifies for a new code, is that an event?
Post #1502711
Posted Wednesday, October 09, 2013 3:09 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 9:12 AM
Points: 1,887, Visits: 1,179
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.
Post #1502977
Posted Tuesday, December 10, 2013 4:58 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 9:12 AM
Points: 1,887, Visits: 1,179
Thought I should update this post! I decided to go with a Bridge or 'Factless Fact' table to meet my requirement.
Post #1521464
Posted Saturday, January 04, 2014 2:25 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, March 27, 2014 2:39 PM
Points: 2,141, Visits: 486
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

Post #1527865
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse