This document discusses different approaches for data integration mappings. It describes:
1. A query override approach where transformations are performed at the database level and passed to expression mappings and target tables/files.
2. An ISD approach where business logic is implemented in expression mappings.
3. A split source query approach is not preferred as it would increase effort for analysis, development, testing and release due to dissecting complex source queries.
This document discusses different approaches for data integration mappings. It describes:
1. A query override approach where transformations are performed at the database level and passed to expression mappings and target tables/files.
2. An ISD approach where business logic is implemented in expression mappings.
3. A split source query approach is not preferred as it would increase effort for analysis, development, testing and release due to dissecting complex source queries.
This document discusses different approaches for data integration mappings. It describes:
1. A query override approach where transformations are performed at the database level and passed to expression mappings and target tables/files.
2. An ISD approach where business logic is implemented in expression mappings.
3. A split source query approach is not preferred as it would increase effort for analysis, development, testing and release due to dissecting complex source queries.
This document discusses different approaches for data integration mappings. It describes:
1. A query override approach where transformations are performed at the database level and passed to expression mappings and target tables/files.
2. An ISD approach where business logic is implemented in expression mappings.
3. A split source query approach is not preferred as it would increase effort for analysis, development, testing and release due to dissecting complex source queries.
Download as DOCX, PDF, TXT or read online from Scribd
Download as docx, pdf, or txt
You are on page 1/ 6
ISD Development Approach:
Data Synchronization Tasks:
When the integration step need to synchronize data between source and target with nil/minimal transformations / joins with out any complex left outer joins then these kind of mapping approach followd. Target can be either table and or flat file. Mapping Configuration Tasks: When the integration step involves source with minimal to complex transformations and with minimal to complex outer joins then this kind of mapping approach is followed. Target can be table or flat file with appended dates. In this approach we have researched on two kinds of approach: 1. Query Override Approach: In this approach we have made use of the power of sql server to perform all the transformations at the database end and passed the transformed values to expression transformation and to target tables/target files. This approach proves to be an advantage for support team to trouble shoot and fix when there arise any issue in future, Example: reyes_verifyx_export Mapping Configuration:
Mapping Design:
Source Query Override:
Source DML: SELECT RTRIM(ssn) [ssn] , RTRIM(firstname) [fname] , RTRIM(REPLACE(lastname,',','') ) [lname] , CASE eestatus WHEN 'A' THEN 'A' WHEN 'L' THEN 'A' WHEN 'R' THEN 'I' WHEN 'T' THEN 'I' ELSE 'I' END empstatus , CONVERT(CHAR(10),dateoflasthire, 101) lasthired , CASE WHEN dateoftermination IS NOT NULL THEN CONVERT(VARCHAR(10),dateoftermination, 101) ELSE '' END termdate , CASE WHEN RTRIM(jobdesc) like 'Conv%' THEN 'TERM' ELSE RTRIM(jobdesc) END jobtitle , CASE eetype WHEN 'REG' THEN 'F' ELSE 'P' END [emptype] , CAST(CASE WHEN salaryorhourly = 'H' THEN hourlyrate WHEN salaryorhourly = 'S' THEN annualsalary ELSE '' END as numeric(12,2)) salrate , salaryorhourly salmode , CASE WHEN left(payfrequency,1) = 'B' THEN 'BI' WHEN left(payfrequency,1) = 'W' THEN 'W' ELSE ''
END payfreq , '' column_l , ISNULL(CAST(current_year AS INT) ,YEAR(GETDATE())) current_year , ISNULL(CAST(cur_yr_base AS INT) ,0) cur_yr_base , ISNULL(CAST(cur_yr_ovt AS INT) ,0) cur_yr_ovt , ISNULL(CAST(cur_yr_bonus AS INT) ,0) cur_yr_bonus , ISNULL(CAST(cur_yr_gross AS INT),0) cur_yr_gross , ISNULL(CAST([previous_year] AS INT) , YEAR(GETDATE()) -1) [previous_year] , ISNULL(CAST([prev_yr_base] AS INT),0) [prev_yr_base] , ISNULL(CAST([prev_yr_ovt] AS INT),0) [prev_yr_ovt] , ISNULL(CAST([prev_yr_bonus] AS INT),0) [prev_yr_bonus] , ISNULL(CAST([prev_yr_gross] AS INT),0) [prev_yr_gross] , ISNULL(CAST([prev_year_2] AS INT),YEAR(GETDATE()) -2) [prev_year_2] , ISNULL(CAST([prev_yr_2_base] AS INT),0) [prev_yr_2_base] , ISNULL(CAST([prev_yr_2_ovt] AS INT),0) [prev_yr_2_ovt] , ISNULL(CAST([prev_yr_2_bonus] AS INT),0) [prev_yr_2_bonus] , ISNULL(CAST(prev_yr_2_gross AS INT),0) prev_yr_2_gross , '' block , '' column_ac , '' column_ad , CONVERT(CHAR(10),dateoforiginalhire, 101) orig_hiredate , RTRIM(eenumber) [empnum] , '' [svc_date] , '' column_ah , '' column_ai , '' column_aj , '' column_ak , CASE WHEN company = 'MB' THEN 'MAR1084' WHEN company = 'MBP' THEN 'MAR1101' WHEN company = 'MBN' THEN 'MAR1105' WHEN company IN ('RH','FWM','RHF') THEN 'REY1114' WHEN company = 'CBS' THEN 'CHI1089' WHEN company = 'HD' AND location IN ('114','115') THEN 'GAT1090' WHEN company = 'HD' AND location IN ('116') THEN 'MES1093' WHEN company = 'HD' AND location IN ('118','119') THEN 'ALL1118' WHEN company = 'HD' THEN 'HAR1091' WHEN company = 'HLD' THEN 'HEN1092' WHEN company = 'PDC' THEN 'PRE1094' WHEN company = 'PDM' THEN 'PRE1095' WHEN company = 'PVA' THEN 'PRE1096' WHEN company = 'CB' THEN 'CRE1103' WHEN company = 'RFS' THEN 'REI1104' WHEN company = 'RFL' THEN 'RFL1109' WHEN company = 'FDC' THEN 'FLO1108' WHEN company = 'CBY' THEN 'CHE1112' WHEN company = 'WCD' THEN 'WIN1113' WHEN company ='RSS' THEN 'REI1119' WHEN company ='FGC' THEN 'GOL1120' WHEN company ='GLCC' THEN 'GRE1121' ELSE ''
END vx_comp_code FROM v_ee v LEFT JOIN ( SELECT prgeeid , prgcoid , MAX(CASE WHEN pehpaydate = YEAR(GETDATE()) THEN [pehpaydate] END) current_year , MAX(CASE WHEN pehpaydate = YEAR(GETDATE()) THEN [Base Amount] END) cur_yr_base , MAX(CASE WHEN PehPaydate = YEAR(GETDATE()) THEN [Overtime Amount] END ) cur_yr_ovt , MAX(CASE WHEN PehPaydate = YEAR(GETDATE()) THEN [Bonus Amount] END ) cur_yr_bonus , MAX(CASE WHEN PehPaydate = YEAR(GETDATE()) THEN [Gross Amount] END) cur_yr_gross , MAX(CASE WHEN pehpaydate = YEAR(GETDATE()) - 1 THEN pehpaydate END) previous_year , MAX(CASE WHEN pehpaydate = YEAR(GETDATE()) - 1 THEN [Base Amount] END) [prev_yr_base] , MAX(CASE WHEN PehPaydate = YEAR(GETDATE()) - 1 THEN [Overtime Amount] END ) [prev_yr_ovt] , MAX(CASE WHEN PehPaydate = YEAR(GETDATE()) - 1 THEN [Bonus Amount] END ) [prev_yr_bonus] , MAX(CASE WHEN PehPaydate = YEAR(GETDATE()) - 1 THEN [Gross Amount] END) [prev_yr_gross] , MAX(CASE WHEN pehpaydate = YEAR(GETDATE()) - 2 THEN [pehpaydate] END) [prev_year_2] , MAX(CASE WHEN pehpaydate = YEAR(GETDATE()) - 2 THEN [Base Amount] END) [prev_yr_2_base] , MAX(CASE WHEN PehPaydate = YEAR(GETDATE()) - 2 THEN [Overtime Amount] END ) [prev_yr_2_ovt] , MAX(CASE WHEN PehPaydate = YEAR(GETDATE()) - 2 THEN [Bonus Amount] END ) [prev_yr_2_bonus] , MAX(CASE WHEN PehPaydate = YEAR(GETDATE()) - 2 THEN [Gross Amount] END) [prev_yr_2_gross] FROM ( SELECT YEAR(pehpaydate) PehPaydate , pehEEID prgeeid , pehCOID prgcoid , SUM( CASE WHEN RTRIM(pehearncode) = PayCode AND category = 'Base Amount' THEN ISNULL(pehcuramt,0) END ) [Base Amount] , SUM( CASE WHEN RTRIM(pehearncode) = PayCode AND category = 'overtime' THEN ISNULL(pehcuramt,0) END ) [Overtime Amount] , SUM( CASE WHEN RTRIM(pehearncode) = PayCode AND category = 'Bonus' THEN ISNULL(pehcuramt,0) END) [Bonus Amount] , SUM( CASE WHEN PehReportCategory <> 'NCH' AND PehUseDedOffSet = 'N' THEN ISNULL(pehcuramt,0) END ) [Gross Amount] FROM pearhist
INNER JOIN Reyes_Verifyx_Codes ON Paycode = pehearncode
WHERE YEAR(pehPayDate) IN (YEAR(GETDATE()), YEAR(GETDATE()) -1, YEAR(GETDATE()) - 2 ) GROUP BY YEAR(pehpaydate) , pehEEID , pehCOID )t GROUP BY prgeeid, prgcoid ) S ON s.prgeeid = v.eeid AND s.prgcoid = v.coid WHERE(dateoftermination IS NULL OR dateoftermination > DATEADD(yy,5,GETDATE())) AND company IN ('RH','MB','MBP','MBN','CBS','HD','HLD','PDC','PDM','PVA','CB','RFS','RFL','FDC','CBY',' FWM','RHF','WCD','RSS','FGC','GLCC') AND eenumber NOT IN ('R143','36321','R641','36338','36321','36338','18062','18199','18298','18297','180 89','31918','18299', '14322','22496','21536')
2. transformations at ISD Approach:
Mapping Configuration:
Implement Business logics in expression transformation in ISD:
3. Split Source Query Approach:
Dissecting complex source query with multiple joins would take Increased amount of efforts during the Analysis, development, testing and release phases. This approach was not prefered because this approach would take Increased amount of time in transition to support personal.
