Forum Stats

  • 3,733,951 Users
  • 2,246,846 Discussions
  • 7,856,949 Comments

Discussions

How to extract the latest Previous Incumbent in the current design

User_A0WPE
User_A0WPE Member Posts: 7 Red Ribbon

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

Answers

Sign In or Register to comment.