Handle Multiple entries in Fact table in Data Cube

Question

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

Actually EmployeePersonalid was related to one EmployeeDisciplineIncidentId (i.e) E1(employeeId) has one discpline(Dis1) but in the cube it shows 3 as count for EmployeeDisciplineIncidentId but actually E1 was invloved in only one Dis1 . Our expected result was also one as that was the correct data. — selva, Jun 25 '15 at 06:11
We have no idea what is being asked... So we can't help. See [help/ask] for more information — Canadian Luke, Jun 29 '15 at 06:43

score 0 · Accepted Answer · answered Jul 20 '15 at 09:19

I have posted the same question in msdn forum. Those who came across with same problem please look into the below link which may help you.

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/4e39284f-341c-494d-96a2-74b700e6314c/handle-multiple-entries-in-fact-table-in-data-cube?forum=sqlanalysisservices#4e39284f-341c-494d-96a2-74b700e6314c

Handle Multiple entries in Fact table in Data Cube

1 Answers1