1 .com
|Mapping Name |Name of the ETL PowerMart mapping (m_ hrispos) |
|Business Requirement |Name of the Business Requirements Specification that is met by this Mapping Specification |
|Information | |
|Source System |Name of the Source Database (HRPROD) |Target System |Name of the Target Database (HRODS) |
|Initial Rows |Estimated number of rows pulled from the |Rows/Load |Estimated number of rows to be loaded to the |
| |source table | |target table |
|Short Description |Description that describes the purpose of this mapping – what will this session accomplish? |
|Refresh Frequency |How often will this session run? Is this a table rebuild or refresh? Is this a daily, weekly, monthly, yearly, or hourly|
| |refresh? |
|Preprocessing |Preprocessing can occur within a mapping or outside of the mapping before a session runs. What sessions must run before |
| |this session? Indexes to be dropped before session runs? Files to be ftp’d or moved to a specific place before session|
| |can run? |
|Post Processing |Post processing can occur within a mapping or outside of the mapping before a session runs. What sessions/mappings must|
| |follow this session/mapping? Indexes that need to be put in place after this session/mapping runs? Emails to be sent on|
| |success/failure? |
|Error Strategy |Error trapping –Document error functions used in input/output port default values, input values for input/output ports. |
| |The Informatica Server skips the row when it encounters the ERROR function. It aborts the session when it encounters the |
| |ABORT function. Examples: |
| |Replace the null value with a constant value or constant expression. |
| |Skip the null value with an ERROR function. |
| |Abort the session with the ABORT function. |
| |You may also document what kinds of session errors cause the session to fail, as well as session errors that do not cause|
| |a session to fail. You need to know for rework or reloads. |
| |Document what errors are acceptable, not acceptable and solutions for resolving |
|Reload Strategy |Restart/recovery procedure if this session/mapping fails. Can this session run independent of other sessions even though|
| |it is in a batch? Must you start the batch over from the first session? |
|Unique Source Fields |Don’t use this field |
Sources
|Tables |
|Table Name |Schema/Owner |Selection/Filter |
|Name of the source table |Who owns the table? (ADMIN) |Do you use the sql query section in the source qualifier to |
| | |limit what you pull from the source table? Document what |
| | |limits are needed here |
| | | |
| | | |
|Files |
|File Name |File Owner |Unique Key |
|Name of the source file |Machine file resides on, path, and owner of |Key (column or field) that makes a row in this file unique |
| |file (Holiday, /usr/local…., SISOPER) | |
| | | |
| | | |
Targets
|Tables |Schema Owner |Who owns the target table (ADMIN) |
|Table Name |Insert |Update as Update |Update as Insert |Update else insert|Delete |Truncate Table |Unique Key |
| | | | | | | | |
| | | | | | | | |
|Filter/SQL|This can be used to override the default UPDATE statement for the target. | |
|Override |For instance, we use this to NULL fields based on certain conditions. | |
| |Include the SQL limits or conditions for the target SQL override here. | |
|Files |
|File Name |File Owner |Unique Key |
|Name of the target file |Machine file resides on, path, and owner of |Key (column or field) that makes a row in this file unique |
| |file (Holiday, /usr/local…., SISOPER) | |
| | | |
| | | |
Lookups
|Lookup Name |Name given to the lookup transformation object (LKP_tablename) |
|Table |Name of the lookup table |Location |Database name (SISQA) |
|Match Condition(s) |Similar to where clause in sql statement – (person.uniqueid = student.customer_uniqueid). How would you find the row|
| |that you need? |
|Filter/SQL Override |Can use a sql override for multiple match conditions. ( last_effective_date is null, role_type = ‘prospect’) |
| |Document sql override statements here. |
|Lookup Name | |
|Table | |Location | |
|Match Condition(s) | |
|Filter/SQL Override | |
|Lookup Name | |
|Table | |Location | |
|Match Condition(s) | |
|Filter/SQL Override | |
Process Overview
To be used as a visual representation of the mapping
Processing Description (Detail)
Describe processing logic contained in mapping, including a description of the logic for each transformation object.
Example:
This mapping will update the target person table with the most recently attended, official high school experience information.
The source qualifier only pulls the rows where the session id = the current session id (Where
ETL_HIGH_SCHOOL_EXPERIENCE.SESSION_ID = (Select max(Session_ID)
From ETL_SESSION) order by last_modified_time, last_db_action).
For each row pulled back from the source qualifier, the ed institution uniqueid is passed to a lookup on the high school table to get the high school code (ceeb code). In addition to the ceeb code, the uniqueid of the high school is also passed back.
The first expression object sets the Hs core 40 completion flag, Hs Official Flag, Hs transcript received flag, Hs Diploma earned flag, and the Hs invalidated flag in the person table after evaluating these flags in the etl_high_school_experience table. If the flag in the etl_high_school_experience table = ‘T’, the flag in the person table is set to ‘YES’. If the flag in the etl_high_school_experience table = ‘F’, the flag in the person table is set to ‘NO’
The first expression object also evaluates the last db action along with other criteria, to determine if the row in the target table should be updated or nullled. If the last db action is = ‘I’ or ‘U’ and the high school experience is official and most recent, the high school fields in the target table are updated. If the last db action is = ‘X’ , the high school experience fields in the target table are nulled.
iif(LAST_DB_ACTION = 'I' and OFFICIAL = 'T' and MOST_RECENTLY_ATTENDED = 'T', 'I',
iif(LAST_DB_ACTION = 'U' and OFFICIAL = 'T' and MOST_RECENTLY_ATTENDED = 'T', 'U',
iif(LAST_DB_ACTION = 'X', 'X') ))
The second expression object passes the uniqueid of the high school to a lookup for each row pulled back in the source qualifier to get the long name of the high school.
The update strategy for this mapping flags all rows for update.
IIF(LAST_DB_ACTION = 'I' or LAST_DB_ACTION = 'U' or LAST_DB_ACTION = 'X',DD_UPDATE,DD_REJECT)
The target table sql override will determine whether the person high school experience fields get updated or nulled. The field that is used is the last db action field (this field was used/updated in the previous expression object).
The logic for the sql override on the high school experience is:
UPDATE PERSON SET HS_MOST_RECENTLY_ATTENDED_FLAG =
decode(:TU.last_db_action,'U',:TU.HS_MOST_RECENTLY_ATTENDED_FLAG,'I',:TU.HS_MOST_RECENTLY_ATTENDED_FLAG,'X',null),
HS_LONG_NAME =
decode(:TU.last_db_action,'U',:TU.HS_LONG_NAME,'I',:TU.HS_LONG_NAME,'X',null),
HS_CEEB_CODE =
decode(:TU.last_db_action,'U',:TU.HS_CEEB_CODE ,'I',:TU.HS_CEEB_CODE ,'X',null),
HS_SOURCE =
decode(:TU.last_db_action,'U',:TU.HS_SOURCE ,'I',:TU.HS_SOURCE ,'X',null),
HS_ED_LEVEL =
decode(:TU.last_db_action,'U',:TU.HS_ED_LEVEL,'I',:TU.HS_ED_LEVEL ,'X',null),
HS_AS_OF_DATE =
decode(:TU.last_db_action,'U',:TU.HS_AS_OF_DATE,'I',:TU.HS_AS_OF_DATE ,'X',null),
HS_END_DATE =
decode(:TU.last_db_action,'U',:TU.HS_END_DATE,'I',:TU.HS_END_DATE ,'X',null),
HS_GRADUATION_DATE =
decode(:TU.last_db_action,'U',:TU.HS_GRADUATION_DATE,'I',:TU.HS_GRADUATION_DATE ,'X',null),
HS_CUMULATIVE_GPA =
decode(:TU.last_db_action,'U',:TU.HS_CUMULATIVE_GPA,'I',:TU.HS_CUMULATIVE_GPA ,'X',null),
HS_CLASS_RANK =
decode(:TU.last_db_action,'U',:TU.HS_CLASS_RANK,'I',:TU.HS_CLASS_RANK ,'X',null),
HS_CLASS_SIZE =
decode(:TU.last_db_action,'U',:TU.HS_CLASS_SIZE,'I',:TU.HS_CLASS_SIZE ,'X',null),
HS_PERCENTILE_LOW =
decode(:TU.last_db_action,'U',:TU.HS_PERCENTILE_LOW,'I',:TU.HS_PERCENTILE_LOW ,'X',null),
HS_PERCENTILE_HIGH =
decode(:TU.last_db_action,'U',:TU.HS_PERCENTILE_HIGH,'I',:TU.HS_PERCENTILE_HIGH ,'X',null),
HS_DIPLOMA_TYPE =
decode(:TU.last_db_action,'U',:TU.HS_DIPLOMA_TYPE,'I',:TU.HS_DIPLOMA_TYPE,'X',null),
HS_CORE_40_COMPLETION_FLAG =
decode(:TU.last_db_action,'U',:TU.HS_CORE_40_COMPLETION_FLAG,'I',:TU.HS_CORE_40_COMPLETION_FLAG,'X',null),
HS_OFFICIAL_FLAG =
decode(:TU.last_db_action,'U',:TU.HS_OFFICIAL_FLAG,'I',:TU.HS_OFFICIAL_FLAG,'X',null),
HS_TRANSCRIPT_RECEIVED_FLAG =
decode(:TU.last_db_action,'U',:TU.HS_TRANSCRIPT_RECEIVED_FLAG,'I',:TU.HS_TRANSCRIPT_RECEIVED_FLAG,'X',null),
HS_DIPLOMA_EARNED_FLAG =
decode(:TU.last_db_action,'U',:TU.HS_DIPLOMA_EARNED_FLAG,'I',:TU.HS_DIPLOMA_EARNED_FLAG,'X',null),
HS_INVALIDATED_FLAG =
decode(:TU.last_db_action,'U',:TU.HS_INVALIDATED_FLAG,'I',:TU.HS_INVALIDATED_FLAG,'X',null),
SESSION_ID =:TU.SESSION_ID,
last_db_action =:TU.last_db_action,
last_modified_by =:TU.last_modified_by,
last_modified_time =:TU.last_modified_time
WHERE UNIQUEID = :TU.UNIQUEID
The session settings for the update strategy are as follows:
Source To Target Field Matrix
|Target Table |Target Column |Data-type |Source |Source Column |Data-type |Expression |Default |Data Issues/Quality/Comments |
| | | |Table | | | |Value if | |
| | | | | | | |Null | |
| | | | | | | | | |
| | | | | | | | | |
| | | | | | | | | |
| | | | | | | | | |
| | | | | | | | | |
| | | | | | | | | |
| | | | | | | | | |
| | | | | | | | | |
| | | | | | | | | |
| | | | | | | | | |
| | | | | | | | | |
| | | | | | | | | |
| | | | | | | | | |
| | | | | | | | | |
................
................
In order to avoid copyright disputes, this page is only a partial summary.
To fulfill the demand for quickly locating and searching documents.
It is intelligent file search solution for home and business.
Related download
- december 2006 spald item 1 attachment 2b information
- chapter 9 code tables veterans affairs
- u s army human resources command
- epims data handbook v 9 0
- chapter 4 quiz time management
- documentation
- feasibility study template hud u s
- name that nerd pc mac
- determining veteran status and eligibility for benefits u
Related searches
- accounting chapter 1 b com 1st year
- 1 or 2 374 374 1 0 0 0 1 168 1 1 default username and password
- 1 or 3 374 374 1 0 0 0 1 168 1 1 default username and password
- 1 or 2 711 711 1 0 0 0 1 168 1 1 default username and password
- 1 or 3 711 711 1 0 0 0 1 168 1 1 default username and password
- 1 or 2 693 693 1 0 0 0 1 168 1 1 default username and password
- 1 or 3 693 693 1 0 0 0 1 168 1 1 default username and password
- 1 or 2 593 593 1 0 0 0 1 or 2dvchrbu 168 1 1 default username and password
- 1 or 3 593 593 1 0 0 0 1 or 2dvchrbu 168 1 1 default username and password
- 1 or 2 910 910 1 0 0 0 1 168 1 1 default username and password
- 1 or 3 910 910 1 0 0 0 1 168 1 1 default username and password
- 192 1 or 2 33 33 1 0 0 0 1 1 1 default username and password