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


Bridge solution for multi-valued attribute structure


Bridge solution for multi-valued attribute structure

Author
Message
emiranda 59653
emiranda 59653
SSC-Addicted
SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)

Group: General Forum Members
Points: 438 Visits: 800
I've been looking at using a bridge solution to implement multi-valued attributes for a dimension in my data model (education information for a faculty member). I've seen two approaches used to implement this:

1. Chris Anderson on his blog (http://blog.oaktonsoftware.com/2011/01/resolve-repeating-attributes-with.html) proposes having a foreign key on the dimension table pointing to the bridge table. Using this approach, my model (simplified) will look as follows:
DimFaculty(FacultyKey, FacultyName, EducationGroupKey)
BridgeFacultyEducation(EducationGroupKey, EducationInfoKey)
EducationInfo(EducationInfo, SchoolName)

2. Shaun Ryan (http://brokenninja.wordpress.com/2011/05/05/multi-valued-attributes-in-sql-services-analysis-services-ssas/) and
Vincent Rainardi (http://dwbi1.wordpress.com/2011/03/13/dimensions-with-multi-valued-attributes/) instead proposes designs where bridge table only contains foreign keys to both the dimension and outrigger so my model would look as follows:
DimFaculty(FacultyKey, FacultyName)
BridgeFacultyEducation(FacultyKey, EducationInfoKey)
EducationInfo(EducationInfo, SchoolName)

From an ETL and reporting viewpoint, which would be easier to implement?
Thomas LeBlanc
Thomas LeBlanc
SSCertifiable
SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)

Group: General Forum Members
Points: 5040 Visits: 919
For performance, this depends on the size of the related fact table and how many rows are going to be duplicated if the Fact table in the Bridge part contains a row for each row in the Fact table.

I have used the foreign key to the Group Bridge table in a health care dimensional model for multiple Diagnosis on a claim and when you can have multiple claim types on a claim.

The ETL is more difficult for the Group Bridge table as well as visualizing the data, but it reduces the size of the bridge fact table and processing the cube is faster.

The other note is not to place every combination of the many side in the Group Bridge table until you have cases for it.

If you look at the AdventureWorks DW from Code Plex, there is an example of the easy way with SalesOrderReason (factInternetSaleReason). This show fact rows in Sales Reason for almost every row in factInternetSales. This is the easy way, but causes problems when the fact tables get large.

Thomas

Thomas LeBlanc, MCITP DBA 2005, 2008 & MCDBA 2000
http://thesmilingdba.blogspot.com/
emiranda 59653
emiranda 59653
SSC-Addicted
SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)

Group: General Forum Members
Points: 438 Visits: 800
Thanks, Thomas. Trying the foreign key approach right now as it looks simpler to implement with ETL.
Thomas LeBlanc
Thomas LeBlanc
SSCertifiable
SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)

Group: General Forum Members
Points: 5040 Visits: 919
Sometimes that is the path to go on, easier ETL.

thomas

Thomas LeBlanc, MCITP DBA 2005, 2008 & MCDBA 2000
http://thesmilingdba.blogspot.com/
emiranda 59653
emiranda 59653
SSC-Addicted
SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)SSC-Addicted (438 reputation)

Group: General Forum Members
Points: 438 Visits: 800
Well it's my first DW project and I'm doing everything solo (from requirements gathering, data modeling, ETL, reporting, self-service BI with sharepoint, etc.) so I'm more interested in getting something done in a couple of months and just rebuilding/optimizing later on.
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