**Issue Description:** : OOTB Headcount calculations should exclude group of people who are called as non executive directors (NED). There are no indicators/ Flag in the source system to identify who is NED in a particular point in time. There are some CSV files (Outside source system) with the list of employee who they identified as NED between two dates
I am planning to use this CSV as lookup file for the incoming source data and stamp employee records with NED_IND (Y/N or 1/0) between two dates, however I have following questions
1. Should we stamp this NED_IND flag in Employee dimension (W_EMPLOYEE_D) which is designed as Type 2 SCD OOTB or
2. Should we stamp this in workforce fact table (W_WRKFC_EVT_MONTH_F) as a NED_IND indicator flag (1 or 0), so that this flag will be available as part of snapshot calculations
Considering the fact that OOTB Workforce fact process is reasonably complicated with series of mappings both in Full and Incremental load, would you suggest any of above mentioned solutions or any better solution so that we can reduce unnecessary customization of OOTB ETL and preserve existing logic?
1. If you are trying to alter the actual "Headcount" metric, then you need to edit the FACT tables. This includes the EVENT as well as the MONTHLY snapshot. You may also need to modify the logic of the "ACTIVE/INACTIVE" flag...as this is what is used to calculate the headcounts. Essentially, edit the SDE to bring in the NED FLAG, and then modify the logic for how OOTB "ACTIVE/INACTIVE" is calculated.
2. If you are running reports that use EMPLOYEE DImension, and you want to also see if the Employee is NED or NOT NED, then yes, you should also add this to the Employee Dim. Also, suppose that an employee can BECOME NED...if you need to capture this history, then you need to add the SCD logic. Also, if an existing employee can become NED..you may have to alter the incremental logic to modify both the DIM and the FACT.
What may be simpler is just to modify the SDE mapplet to use the LOOKUP for NED..and flag those as INACTIVE in the SDE itself...in which case you do not have to modify the downstream ETLs.
When you refer "What may be simpler is just to modify the SDE mapplet to use the LOOKUP for NED..and flag those as INACTIVE in the SDE itself...in which case you do not have to modify the downstream ETLs", are we referring to SDE of Dimension or of Fact?
At the moment, i only need these FLG as part of metric calculation, not as dimension attributes, so can i assume you were referring to SDE, SIL of Workforce fact process?
Thanks in advance,