I'm having an issue with summarizing data within my application that uses section access.
Right now I have implemented data restrictions based on the users Windows AD account. This is working well. What I would like to do is have each user be able to see our metrics summarized for the entire department compared to their own metric values. I've tried created a summary table that is an extension of the main fact table but that was producing incorrect results. Does anyone have experience with this sort of application?
My data model looks like this:
| Facts | Security | Employees |
|---|
MetricDate, MetricID, UserID, EmployeeAD, Department, Division, AppointmentStatus, NewRegistrationFlag, ManagerKey, DirectorKey, Numerator, Denominator | EmployeeAD, SECURITYID | UserID Name Address StartDate ManagerName |
SECURITYID is what drives the security. That field is located in the Section Access table along with the ACCESS and NTNAME fields. Basically I need each user to see a summary of their department (manager level) and organization (director level). I have a key for each of those in the Fact table. I tried creating summary tables for manager level and director level data using the AUTONUMBER function but those results were not correct.
Here is the syntax I used:
AutoNumber(MetricDate&MetricID&ManagerKey&Department&Division&AppointmentStatus&NewRegistrationFlag) as ManagerSumKey
Once I created the key I did this:
ManagerSummary:
Load
ManagerSumKey,
Sum(Numerator) as ManagerNumerator,
Sum(Denominator) as ManagerDenominator
Resident Facts
Group By ManagerSumKey;
Again, the results of this were not accurate. Any suggestions here would be greatly appreciated!!!
