none
Fact Table Design Issue RRS feed

  • Question

  • Hi,

    I have a database with Projects and their Counties; a Project can be in multiple Counties.  Each Project can have one or more Counties associated with it.

    Like this (simplified):

    ProjectNumber

    CurrentAmount

    Year

    2149400000

    710000

    2012

    4444400105

    2485000

    2013

     

    CountyId

    County

    ProjectNumber

    131

    DuPage

    2149400000

    004

    Cook

    2149400000

    121

    Will

    4444400105

     

    Now I want to design a FactTable “FactProject” that aggregates the CurrentAmount with County dimension. Since CurrentAmount is aggregated at Project I am not sure how to resolve this kind of scenario.

    Monday, January 3, 2011 2:29 AM

Answers

  • You can deal with this situation in two ways

    1. As suresh mentioned, you can create a many to many dimension design. In this design, your county table will act as both dimension and a bridge table which hold many to many relationship between county and product. In BIDS, you will have to create two dimensions - county (based on county table ) and a fact dimension ( based on product fact table). Then you also have to create a measure group using the county table which can be even just row counter. Now in the dimension usage tab of cube solution, against county dimension for the main measure group ( which holds value fo sum (current amt)), select many to many relationship type and everything will be placed automatically if you have designed this correctly. Now in cube browser you can now put county name on rows and say sum ( current amt) measure on rows, it should give you correct answer

    2. Or you can design a name query and create one fact table by joining these two table. This will give you one table with current amt against all possible projects and thus against all possible counties. Now use the same county table to create a dimension and then you can apply sum (current amt) against county dimension to get amount by county

    I hope this helps.

    Amey

    • Marked as answer by kellog1 Wednesday, January 5, 2011 5:00 PM
    Wednesday, January 5, 2011 12:07 AM
  • Create a new Dimension Counties directly connected to the fact table.

    or

    Create a bridge table between the new dimension Counties and the dimension Project

    Regards,

    Pedro


    Visit My Business Intelligence Blog - If your question is answered, please mark as answered.
    • Marked as answer by kellog1 Wednesday, January 5, 2011 5:00 PM
    Wednesday, January 5, 2011 2:52 PM

All replies

  • I guess the dimension relationship through many to many type will resove it.It looks like the relationship type is similar to the case of a sales reason being related to multiple sales orders and vice versa. A project can be associated to one or more counties and A county may be related to multiple projects. A many to many relationship to resolve this type of scenario.

    Suresh M. Menon

    Monday, January 3, 2011 3:45 AM
  • Can you elaborate using an example.
    Monday, January 3, 2011 4:29 AM
  • You can deal with this situation in two ways

    1. As suresh mentioned, you can create a many to many dimension design. In this design, your county table will act as both dimension and a bridge table which hold many to many relationship between county and product. In BIDS, you will have to create two dimensions - county (based on county table ) and a fact dimension ( based on product fact table). Then you also have to create a measure group using the county table which can be even just row counter. Now in the dimension usage tab of cube solution, against county dimension for the main measure group ( which holds value fo sum (current amt)), select many to many relationship type and everything will be placed automatically if you have designed this correctly. Now in cube browser you can now put county name on rows and say sum ( current amt) measure on rows, it should give you correct answer

    2. Or you can design a name query and create one fact table by joining these two table. This will give you one table with current amt against all possible projects and thus against all possible counties. Now use the same county table to create a dimension and then you can apply sum (current amt) against county dimension to get amount by county

    I hope this helps.

    Amey

    • Marked as answer by kellog1 Wednesday, January 5, 2011 5:00 PM
    Wednesday, January 5, 2011 12:07 AM
  • Create a new Dimension Counties directly connected to the fact table.

    or

    Create a bridge table between the new dimension Counties and the dimension Project

    Regards,

    Pedro


    Visit My Business Intelligence Blog - If your question is answered, please mark as answered.
    • Marked as answer by kellog1 Wednesday, January 5, 2011 5:00 PM
    Wednesday, January 5, 2011 2:52 PM