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!!