SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Dimensional modelling help: 'outriggers' or not?


Dimensional modelling help: 'outriggers' or not?

Author
Message
Lempster
Lempster
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3218 Visits: 1657
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
larskandersen
larskandersen
SSC-Enthusiastic
SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)

Group: General Forum Members
Points: 140 Visits: 1032
You missed bridge table ;-)

How often does an advisors qualifications change?

When an Advisor qualifies for a new code, is that an event?
Lempster
Lempster
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3218 Visits: 1657
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.
Lempster
Lempster
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3218 Visits: 1657
Thought I should update this post! I decided to go with a Bridge or 'Factless Fact' table to meet my requirement.
sneumersky
sneumersky
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2494 Visits: 487
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search