SSAS 2012 : Filtering values of fact Tables by a common dimension table

1
votes

could you pleas help me with the following problem. I tried everything but can't get the desired values out of my cube. Basically i've got the following Facts/dimensions:

dimEmpolyeeGroup - employees grouped together by the HR department (M:N to department)
dimEmployee - the employee itself
dimDepartment - department structure (M:N to EmployeeGroup)
factAttendanceTime - time the staff was present and got payed for
factTimeTicket - time that was booked on a specific department

dimEmployeeGroup -> factAttendanceTime <- dimEmployee -> factTimeTicket <-dimDepartment

My Problem is to bring together those 2 factTables. How can I do this? I tried different combinations of many-to-many relationships between EmployeeGroup and Department but nothing worked for me. I either don't see values from factTimeTicket, factAttendanceTime or i get an cartesian product of Departments and EmployeeGroups.

The objective is to combine those 2 fact Tables. A user should be able (Excel Pivot) to filter by a EmployeeGroup to get the Attendance and TimeTicket-time for those Group (or the other way round, or filter by both,...)

What do i do wrong? Why isn't it automatically linked by dimEmployee?

1
pivotssasmdxolap-cube

1 Answers

0
votes

If you want to filter measures from table factTimeTicket by attributes/hierarchies from dimEmployeeGroup, then you should reference them. I would think this is a many-to-many relationship via the measure group derived from table factAttendanceTime.

And the relationship from the department dimension to the measure group based on factAttendanceTime would be many-to-many using the measure group factTimeTicket as many-to-many table.