About Controlling HR-Future-Data Transaction Data Visibility

In most HR systems it is common to enter transactions in advance. These are often termed Future-Dated transactions. Which roles can see future-date transactions is often tied to role based access control. In the BI Applications Human Resources a number of facts are secured in such a way as to limit access to future-dated transactions. This is achieved via some Session level OBIEE Initialization Blocks and associated variables that returns a certain date value. This date value dictates how far into the future the user gets access to data, if at all.

The purpose of this task is to set this date.

Optional or Mandatory

By default, no future data access is provided for users. If users need future data access, then this task is mandatory.

Initialization Blocks

There are five Session variables, and two related Initialization Blocks:

Session Variable Name Initialization Block used Configuration Needed (Y/N)

HR_WRKFC_MAX_EFFECTIVE_DT

HR_WRKFC_MAX_EFFECTIVE_DT

Y

HR_WRKFC_MAX_EFFECTIVE_DT_WID

HR Workforce Max Effective Dates

N

HR_WRKFC_MAX_EFFECTIVE_DT_MONTH_WID

HR Workforce Max Effective Dates

N

HR_WRKFC_MAX_EFFECTIVE_DT_QTR_WID

HR Workforce Max Effective Dates

N

HR_WRKFC_MAX_EFFECTIVE_DT_YEAR_WID

HR Workforce Max Effective Dates

N

You need to configure only the first Initialization Block. The default delivered behaviour of the Initialization Block 'HR_WRKFC_MAX_EFFECTIVE_DT' is to always return today's date, and the SQL statement is:

select DAY_DT
from "Oracle Data Warehouse"."Catalog"."dbo"."Dim_W_DAY_D_Common"
where DAY_DT = CURRENT_DATE

If your requirement is to allow future data access up until one year ahead of 'today', then you need to change the SQL to:

select CAST(TIMESTAMPADD(SQL_TSI_YEAR, 1, DAY_DT) AS DATE) 
from "Oracle Data Warehouse"."Catalog"."dbo"."Dim_W_DAY_D_Common" 
where DAY_DT = CURRENT_DATE

If you wish to change the default behaviour of the Initialization Block based on the authenticated user, then you will need to change the delivered SQL for Initialization Block HR_WRKFC_MAX_EFFECTIVE_DT to something like this:

select case when ':USER' in ('A','B','C') then 
      CAST(TIMESTAMPADD(SQL_TSI_YEAR, 1, DAY_DT) AS DATE) 
     else 
      DAY_DT 
     end 
from "Oracle Data Warehouse"."Catalog"."dbo"."Dim_W_DAY_D_Common"
where DAY_DT = CURRENT_DATE

The above represents a use case where users A, B and C gets future data access up until one year ahead from today, whereas all other users have no future data access.

Regardless of the use cases, depending on what the variable HR_WRKFC_MAX_EFFECTIVE_DT returns, the second Initialization Block HR - Future-dated Data Date (WIDs) returns the appropriate values for the dependent four variables. The SQL goes (this is for your information only; no change is needed):

select
CAST(ROW_WID AS INT)        AS HR_WRKFC_MAX_EFFECTIVE_DT_WID,
CAST(CAL_MONTH_WID AS INT)  AS HR_WRKFC_MAX_EFFECTIVE_DT_MWID,
CAST(CAL_QTR_WID AS INT)    AS HR_WRKFC_MAX_EFFECTIVE_DT_QWID,
CAST(CAL_YEAR_WID AS INT)   AS HR_WRKFC_MAX_EFFECTIVE_DT_YWID
from "Oracle Data Warehouse"."Catalog"."dbo"."Dim_W_DAY_D_Common"
where DAY_DT = CAST(VALUEOF(NQ_SESSION.HR_WRKFC_MAX_EFFECTIVE_DT) AS DATE

The exact SQL will depend on the user/role requirements.

Note: Future-dated security is applied only to HR facts, not dimensions, at the time of writing.

Dependency

Future data restriction is available for the following Logical Facts:

  • Fact - HR - Workforce - Balance Information

  • Fact - HR - Workforce - Event Information

  • Fact - HR - Recruitment Event Information

  • Fact - HR - Workforce Gains and Losses - Event Information

  • Fact - HR - Workforce Gains and Losses - Balance Information

  • Fact - HR - Payroll Balance Detail

  • Fact - HR - Payroll Balance Summary

  • Fact - HR - Accrual Transactions - Balance Information

  • Fact - HR - Accrual Transactions - Event Information