Tuesday, October 2, 2012

Mapping Ceridian HR to OBIEE HR Analytics

The typical implementation of Oracle's Business Intelligence Human Resources Analytics Application is structured around either Peoplesoft or Oracle E-Business Suite data.   However, we recently completed a project where the source system was Ceridian's Human Capital Management application.

Because of the architecture of the BI applications, it is only necessary to configure the Source Dependent Extract (SDE) Informatica mappings and then allow the standard out-of-the-box Source Independent Load and Post Load Processing mappings to handle populating the Business Analytics Warehouse target tables.

For this project, we were able to successfully map standard HR dimensions like:

  • Employee (W_EMPLOYEE_D)
  • Job (W_JOB_D)
  • Department (W_INT_ORG_D)
  • Pay Type (W_PAY_TYPE_D)
  • Pay Grade (W_PAY_GRADE_D)
As well as two fact tables:

  • Workforce Event  (W_WRKFC_EVT_MONTH_F)   - Includes Events and Headcount Snapshots
  • Payroll (W_PAYROLL_F)
 For further information on mapping Ceridian HR to Oracle HR Analytics, contact us at hcminsight@hcminsight.com.



Sample  SQL used in the SDE mapping for Payroll which extracts data from the Ceridian source:

SELECT
'EARNINGS' as TYPE,
Ebflxid as EMPLOYEE_ID,
jbpaygrade as PAY_GRADE_ID,
EeCategory+'~'+ Eestatus+'~'+ jbflsa as EMPLOYMENT_ID,
EjCompany as COMPANY_ORG_ID,
EjJobcode as JOB_ID ,
EjLocation as EMP_LOC_ID,
EjDepartment as HR_BUSINESS_UNIT_ID,
EjCompany as EMP_HR_ORG_ID ,
CsePayTypeCode as PAY_TYPE_ID,
CsePayTypeLong,
null as EMP_POSTN_DH_ID,
CsuDateBeg as PAY_CHECK_DT,
CsuPayPeriod as PAY_PERIOD_START_DT,
null as PAY_PERIOD_END_DT,
null as PAY_EARN_START_DT,
null as PAY_EARN_END_DT,
'Y' as PAY_DETAIL_FLG,
CseCurrErngs as PAY_ITEM_AMT,
'N' as DELETE_FLG,
'N' as REJECT_FLG,
'USD' as DOC_CURR_CODE,
'USD' as LOC_CURR_CODE,
'1' as LOC_EXCH_RATE_TYPE,
'1' as LOC_EXCHANGE_RATE,
GETdate() as EXCHANGE_DT,
convert(varchar,csuflxideb) +'~'+ convert(varchar,CsuDateBeg)
+'~'+convert(varchar,CsePayTypeCode)+'~'+ convert(varchar,CsuCheckno)
 as INTEGRATION_ID,
CsuCheckno as X_CHECK_NUM,
CsuPayPeriod as X_PAY_PERIOD

FROM
Ebase, CheckSumm, CheckSummHE,Eemploy,Ejob,jobcode
WHERE
Ebflxid = ejflxideb and
Ebflxid = eeflxideb and
Ebflxid = csuflxideb and
CsuFlxid = CseFlxIDCsu and
EjJobcode=Jbjobcode and
ebflagemp='y'
--and  Ebflxid=55704
--and  CsuPayPeriod=2011200
-- and (csudatebeg BETWEEN '01/01/2012' and '01/31/2012')

and (
(EjDateBeg <= CsuDateBeg AND
(EjDateEnd >= CsuDateBeg OR EjDateEnd IS NULL))
AND(EeDateBeg <= CsuDateBeg AND
(EeDateEnd >= CsuDateBeg OR EeDateEnd IS NULL))
 and   EeStatus <> 'Terminated'
 OR
(eedatebeg<=CsuDateBeg and
(eedateend>=CsuDateBeg or eedateend is null)) and
Ejdateend = eetermdate)

union

SELECT
'DEDUCTIONS' as TYPE,
Ebflxid as EMPLOYEE_ID,
jbpaygrade as PAY_GRADE_ID,
EeCategory+'~'+ Eestatus+'~'+ jbflsa as EMPLOYMENT_ID,
EjCompany as COMPANY_ORG_ID,
EjJobcode as JOB_ID ,
EjLocation as EMP_LOC_ID,
EjDepartment as HR_BUSINESS_UNIT_ID,
EjCompany as EMP_HR_ORG_ID ,
CsdDedCode as PAY_TYPE_ID,
CsdDedDesc,
null as EMP_POSTN_DH_ID,
CsuDateBeg as PAY_CHECK_DT,
CsuPayPeriod as PAY_PERIOD_START_DT,
null as PAY_PERIOD_END_DT,
null as PAY_EARN_START_DT,
null as PAY_EARN_END_DT,
'Y' as PAY_DETAIL_FLG,
CsdDedCurr as PAY_ITEM_AMT,
'N' as DELETE_FLG,
'N' as REJECT_FLG,
'USD' as DOC_CURR_CODE,
'USD' as LOC_CURR_CODE,
'1' as LOC_EXCH_RATE_TYPE,
'1' as LOC_EXCHANGE_RATE,
GETdate() as EXCHANGE_DT,
convert(varchar,csuflxideb) +'~'+ convert(varchar,CsuDateBeg) +'~'
+convert(varchar,CsdDedCode)+'~'+ convert(varchar,CsuCheckno)

as INTEGRATION_ID,
CsuCheckno as X_CHECK_NUM,
CsuPayPeriod as X_PAY_PERIOD
FROM
Ebase, CheckSumm, CheckSummDed,Eemploy,Ejob,jobcode
WHERE
Ebflxid = ejflxideb and
Ebflxid = eeflxideb and
Ebflxid = csuflxideb and
CsuFlxid = CsdFlxIDCsu and
EjJobcode=Jbjobcode and
ebflagemp='y'
--and  Ebflxid=55704 
--and  CsuPayPeriod=2011200
-- and (csudatebeg BETWEEN '01/01/2012' and '01/31/2012')
and (
(EjDateBeg <= CsuDateBeg AND
(EjDateEnd >= CsuDateBeg OR EjDateEnd IS NULL))
AND(EeDateBeg <= CsuDateBeg AND
(EeDateEnd >= CsuDateBeg OR EeDateEnd IS NULL))
 and   EeStatus <> 'Terminated'
 OR
(eedatebeg<=CsuDateBeg and
(eedateend>=CsuDateBeg or eedateend is null)) and
Ejdateend = eetermdate)

UNION

SELECT
'TAX' as TYPE,
Ebflxid as EMPLOYEE_ID,
jbpaygrade as PAY_GRADE_ID,
EeCategory+'~'+ Eestatus+'~'+ jbflsa as EMPLOYMENT_ID,
EjCompany as COMPANY_ORG_ID,
EjJobcode as JOB_ID ,
EjLocation as EMP_LOC_ID,
EjDepartment as HR_BUSINESS_UNIT_ID,
EjCompany as EMP_HR_ORG_ID ,
CsxshortDesc as PAY_TYPE_ID,
CsxshortDesc,
null as EMP_POSTN_DH_ID,
CsuDateBeg as PAY_CHECK_DT,
CsuPayPeriod as PAY_PERIOD_START_DT,
null as PAY_PERIOD_END_DT,
null as PAY_EARN_START_DT,
null as PAY_EARN_END_DT,
'Y' as PAY_DETAIL_FLG,
CsxCurrTax as PAY_ITEM_AMT,
'N' as DELETE_FLG,
'N' as REJECT_FLG,
'USD' as DOC_CURR_CODE,
'USD' as LOC_CURR_CODE,
'1' as LOC_EXCH_RATE_TYPE,
'1' as LOC_EXCHANGE_RATE,
GETdate() as EXCHANGE_DT,
convert(varchar,csuflxideb) +'~'+ convert(varchar,CsuDateBeg)
+'~'+convert(varchar,CsxshortDesc)+'~'+ convert(varchar,CsuCheckno)
 as INTEGRATION_ID,
CsuCheckno as X_CHECK_NUM,
CsuPayPeriod as X_PAY_PERIOD
FROM
Ebase, CheckSumm, CheckSummTax,Eemploy,Ejob,jobcode
WHERE
Ebflxid = ejflxideb and
Ebflxid = eeflxideb and
Ebflxid = csuflxideb and
CsuFlxid = CsxFlxIDCsu and
EjJobcode=Jbjobcode and
ebflagemp='y'
--and  Ebflxid=55704 
--and  CsuPayPeriod=2011200
-- and (csudatebeg BETWEEN '01/01/2012' and '01/31/2012')
and (
(EjDateBeg <= CsuDateBeg AND
(EjDateEnd >= CsuDateBeg OR EjDateEnd IS NULL))
AND(EeDateBeg <= CsuDateBeg AND
(EeDateEnd >= CsuDateBeg OR EeDateEnd IS NULL))
 and   EeStatus <> 'Terminated'
 OR
(eedatebeg<=CsuDateBeg and
(eedateend>=CsuDateBeg or eedateend is null)) and
Ejdateend = eetermdate)


No comments:

Post a Comment