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

Bridge solution for multi-valued attribute structure Expand / Collapse
Author
Message
Posted Friday, July 4, 2014 9:11 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Today @ 12:52 PM
Points: 48, Visits: 529
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?
Post #1589391
Posted Tuesday, July 15, 2014 7:44 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, August 29, 2014 7:37 AM
Points: 3,090, Visits: 783
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/
Post #1592562
Posted Tuesday, July 15, 2014 8:48 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Today @ 12:52 PM
Points: 48, Visits: 529
Thanks, Thomas. Trying the foreign key approach right now as it looks simpler to implement with ETL.
Post #1592623
Posted Tuesday, July 15, 2014 11:20 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, August 29, 2014 7:37 AM
Points: 3,090, Visits: 783
Sometimes that is the path to go on, easier ETL.

thomas


Thomas LeBlanc, MCITP DBA 2005, 2008 & MCDBA 2000
http://thesmilingdba.blogspot.com/
Post #1592698
Posted Tuesday, July 15, 2014 11:26 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Today @ 12:52 PM
Points: 48, Visits: 529
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.
Post #1592705
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse