- 3,733,951 Users
- 2,246,846 Discussions
- 7,856,949 Comments
- 17.8K All Categories
- 3.3K Industry Applications
- 3.2K Intelligent Advisor
- 57 Insurance
- 533.2K On-Premises Infrastructure
- 137.3K Analytics Software
- 38.5K Application Development Software
- 5.1K Cloud Platform
- 109K Database Software
- 17.5K Enterprise Manager
- 8.8K Hardware
- 70.7K Infrastructure Software
- 105K Integration
- 41.5K Security Software
How to extract the latest Previous Incumbent in the current design
Technologies: OBIA 184.108.40.206.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