Hello!.
First of all, thanks a lot for your videos . I'm new in BI and they really helped me a lot, they are very clear and useful!.
I think that the following question is probably caused by the lack of experience that I have in this area. It would help me a lot to get your vision about the correct way to implement that. I’m using Microsoft Analysis Services.
I'm trying to get a KPI to track the hours reported by every employee in my company. If the employee doesn't report de expected hours, we should send an email to him, in order to remember him to complete his hour report.
I designed a warehouse with the following tables:
Fact Table_ReportedHours:employeeId,
roleId,
proyectId,
timeId, (the reported hours are for this date).
areaId,
taskTitle,
Hours, (the hours that the employee report for the given task in 1 day)
enteredDate(this indicates the moment in which the employee reported his hours).
-Dimensions:
Employees:
- EmployeeId,
-EmpNumber
-Name (+ surname... + other data for the employee)
- workingHours (to indicate if the employee’s working time is for 8 hours a day, or 6 hours a day).
Roles:
- RoleId
- RoleName
- PercentAsignment (sometimes an employee is not assigned 100% to a project, so to know "how many hours he needs to report")
Proyects:
- ProyectId
- BusinessCode
- Proyect Name
- ClientName
- (+ Others)
Areas:
-AreaId
-AreaName
-AreaAbbreviation
KPI Value Expression:
[Measures].[Hours]
The KPI Goal Expression is the following:
([Measures].[Fact Reported Hours Count],[Time].[Is Workable].&[True])* 8
Where “8” means the “employee’s Working Hours”, (that is the “expected reporting time”). Notice that I’m having the Working hours in the dimension time… but really don’t know how can I get this value to use in the KPI goal expression. Something similar happens to me with the “PercentAsignment” Value (I need to know if the employee is assigned in a 100% of his time to this particular project in order to calculate the goal of hours that needs to be reported by him.
I’m wondering if these two values need to be in the fact table, and not in dimension tables. Could you please give me an idea of the correct way to do this?.
Thanks in advance!, Soledad