Bridge solution for multi-valued attribute structure

  • 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?

  • 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

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

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

    thomas

    Thomas LeBlanc, MVP Data Platform Consultant

  • 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.

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

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