question

SQLvis avatar image
SQLvis asked

LastNonEmpty Question

I have a fact table that tracks employees and their headcounts by day. Here's a sample record of the the table. The Employee dimension is a type I SCD whose primary key is EmployeeID and Salary is the annual salary of that employee. DateIDDeparmentIDEmployeeIDSalary1110050,00001330040,00001340020,00002110050,00002330042,00002340020,0000 I'm trying to get the last salary that an employee had and then sum this up in SSAS browser using the "LastNonEmpty" aggregation feature for an SSAS measure, so that when they slice by Year the latest salaries are summed up together. The date table looks like this: DateIDDateValueYear12010-1-1201012010-1-22010
I'm trying to get to this result if you slice by Year=2010 DepartmentSalary150,000362,0000
Basically the most recent salary from the employee is then summed up. Does anyone know how I would implement this?
sql-server-2008ssas
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Something doesn't seem right here, if the Employee dimension is type I SCD on EmployeeID and Salary why are there more than one eg (100, 50000) Looks like the grain is DateID, EmployeeID
0 Likes 0 ·
The grain is supposed to represent an Employee's HR information on daily basis. For example, an employee can have certain deductions for today but tomorrow it may change. Users want to see the latest salary for an employee. Note that I'm using surrogate keys here in my following example: DateIDDeparmentIDEmployeeIDDeductionIDSalary11100150,000011100250,000011100350,0000
0 Likes 0 ·
Let's say DateID 1 is for the date '2010-1-1' so if users sliced by 2010 and by Department 1 then they'd want to see Salary of 50,0000. Now let's take two employees as an example in two different dates - DateIDDeparmentIDEmployeeIDDeductionIDSalary11100150,000011100350,000011100350,00001001200170,00001001200270,00001001200370,0000
Let's say DateID = 100 represents "2010-6-1" and so now when users slice on just 2010 data by this same department, they'd expect to see 70,000+50,0000 = 130,000. I hope I'm making sense! I've been trying to use the LastNonEmpty feature, but not sure how to get just one latest salary per employee to aggregate on. Any ideas?
0 Likes 0 ·

0 Answers

·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.