Bridge solution for multi-valued attribute structure

Question

Bridge solution for multi-valued attribute structure

emiranda 59653

Ten Centuries

Points: 1096
More actions
July 4, 2014 at 9:11 am

#397009

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?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Thomas LeBlanc SSCrazy Eights Points: 8085 More actions · Answer 1

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, MVP Data Platform Consultant

emiranda 59653 Ten Centuries Points: 1096 More actions · Answer 2

Thanks, Thomas. Trying the foreign key approach right now as it looks simpler to implement with ETL.

Thomas LeBlanc SSCrazy Eights Points: 8085 More actions · Answer 3

Sometimes that is the path to go on, easier ETL.

thomas

Thomas LeBlanc, MVP Data Platform Consultant

emiranda 59653 Ten Centuries Points: 1096 More actions · Answer 4

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.