Technologies: OBIA 11.1.1.10.1 with ODI 11g and PSFT 9.2 as source
Have a requirement to add the previous Incumbent in a vacant position in the Vacant Position OBIEE report.
The current customized design is - one ODI interface to populate the customized Position Event Month Fact table for filled position from W_HR_POSITION_D and W_WRKFC_EVT_MONTH_F with some other dimensions like W_JOB_D, W_INT_ORG_D,W_LOCATION_D to populate corresponding WID columns.
Another interface similar to the above one for vacant positions. But here the W_WRKFC_EVT_MONTH_F is not used any more and Assignment IDs are hard coded as 0~0.
The new requirement is to add previous Incumbent in a position. For filled positions its easy as we are using LAG function and selecting the previous Assignment ID in a position from W_WRKFC_EVT_MONTH_F based on the Effective_from_dt.
For vacant positions, now when I am trying to add the fact table W_WRKFC_EVT_MONTH_F it is breaking the current design even if I select the latest ones with max of Effective_from_dt.
Can anyone please advise how can I extract the last one in a position when the position is vacant.
let me know of any further clarification