Handle Multiple entries in Fact table in Data Cube RRS feed

  • General discussion

  • We have been constructing data cube for our project in which we are facing a problem like 1 to many relationship in the dimension tables and making entries in fact table (duplication).

        EmployeePersonalInfo Table:                                                    
        EmployeePersonalID    Name
        E1                  Steve(row1)
        E2                    Mark(row2)
        EmployeeDiscipline IncidentInfo Table:                                      
        EmployeePersonalID    EmployeeDisciplineIncidentID
        E1                     Dis1(row1)
        E2                    Dis5(row2)
        EmployeeDepartmentInfo Table:
        EmployeeDepInfoID    EmployeePersonalID
        Dep1                E1(row1)
        Dep2                E1(row2)
        Dep3                E1(row3)
        Dep1                E2(row4)
        Dep2                E2(row5)
        Dep3                E2(row6)

    While creating a Fact table with the above entries comes as,

        FactDiscipline Table:
        EmployeePersonalD    EmployeeDepInfoID    EmployeeDisciplineIncidentID
        E1                    Dep1                   Dis1(row1)
        E1                    Dep2                Dis1(row2)
        E1                    Dep3                Dis1(row3)
        E2                    Dep1                Dis5(row4)
        E2                    Dep2                Dis5(row5)
        E2                    Dep3                   Dis5(row6)

    Actually Employee E1 in associated with discipline incident Dis1. Since EmployeeDisciplineInfo is joined with it , 3 entries will be there for the same Employee E1.  But Employee E1 involved with only one Incident Dis1. In Fact table, it measures the incident count as 3 for Employee E1.

    Your help is appreciated!!
    • Edited by selva173 Wednesday, June 24, 2015 7:29 AM
    • Changed type selva173 Wednesday, June 24, 2015 9:45 AM
    Wednesday, June 24, 2015 4:37 AM

All replies