July 1, 2011 at 6:47 am
Hi,
I am developing a SSRS report where I am calculating Salaries of Employees as per organisational levels for a particular month. Something as below. Salary below is sum of all employees in that Department.
Organisation Level Salary
Department A 500,000
Department B 600,000
Above salary figures are of all employees in Dept A and Dep B respectively.
Now, in a particular year-month, say 201104 few employees from Dep A goes somewhere, say Dep B or even Dep C. I still need to capture the salary figures for those employees. But In parameter Selection when I say 201104, those employees are not part of Dep A(as they moved to Dep B), hence their figures dont appear in my report.
Request to pour in your ideas about what can be done here. Thanks.
July 1, 2011 at 7:07 am
You need to add some temporal awareness to your database and queries, and fractionalize monthly salaries into daily salaries.
If Employee 1 is in Dept A from 20110401 through 20110412 and then moves to Dept C for the balance of the month, you need to be able to calculate their salary for 12 days and attribute that to Dept A and then give the balance to Dept C.
From my point of view, whether or not you choose to do this in a cube or not, you need a Type 2 Slowly Changing Dimension. Slowly Changing Dimensions are the subject of multiple chapters in books on data warehousing, and many blogs, but way beyond the scope of a quick forum response.
Good luck.
July 1, 2011 at 7:08 am
I would say you need some sort of employment history table,
with the following details:
Department_Identifier, Employee_Identifier, Start_Date, End_Date
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply