UNIT 6: Lookups and Reusable Transformations
1. Sources - FlatFile - employees_layout 가져오기
2. Target - Imfort database - TDBU07 로 연결 후 STG_EMPLOYEES 가져오기
3. Mapping - m_Stage_Customer_Contacts_07 열어서
exp_Format_Name_Gender_Phone - Make Reusable 체크
4. Transformation Developer
Transformations 에서 exp_ForMat_Name_Gender_Phone 가져오기
edit - Rename - re_ 앞에 붙이기
port 탭에서
OUT_CUST_NAME -> OUT_NAME
OUT_CUST_PHONE -> OUT_PHONE
위와 같이 수정 후 적용 후 확인
5. Mapping 만들기
m_STG_EMPLOYEES_07
소스, 타겟 가져오기
6. Lookup 만들기
from flat file - salaries.txt 가져오기
first line, comma, delimiters, no quotes Next
Finish
SQ_Shortcut 에서 lkp_salaries 로 employee_id 연결
Edit - rename - lkp_salaries
port 탭 - EMPOLYEE_ID1 을 IN_EMPLOYEE_ID 로 변경 후 output 은 언체크
condtion 탭 - add new 클릭 EMPLOYEE_ID = IN_EMPOLOYEE_ID
ok
저장
7. LINK TRANSFORMATIONS
from SQ_employees_layout to the STG_EMPLOYEES target:
EMPLOYEE_ID J EMPLOYEE_ID
ADDRESS J EMPLOYEE_ADDRESS
CITY J EMPLOYEE_CITY
STATE J EMPLOYEE_STATE
ZIP_CODE J EMPLOYEE_ZIP_CODE
COUNTRY J EMPLOYEE_COUNTRY
FAX_NUMBER J EMPLOYEE_FAX_NMBR
EMAIL J EMPLOYEE_EMAIL
NATIVE_LANGUAGE J NATIVE_LANG_DESC
SECOND_LANGUAGE J SEC_LANG_DESC
THIRD_LANGUAGE J TER_LANG_DESC
POSITION_TYPE J POSITION_TYPE
DEALERSHIP_ID J DEALERSHIP_ID
REGIONAL_MANAGER J REGIONAL_MANAGER
DEALERSHIP_MANAGER J DEALERSHIP_MANAGER
HIRE_DATE J HIRE_DATE
DATE_ENTERED J DATE_ENTERED
저장
from lkp_SALARY to STG_EMPLOYEES:
SALARY J EMPLOYEE_SALARY
from SQ_employees_layout to re_exp_Format_Name_Gender_Phone_Load_Date:
FIRSTNAME J IN_FIRSTNAME
LASTNAME J IN_LASTNAME
PHONE_NUMBER J IN_PHONE_NUMBER
GENDER J IN_GENDER
AGE J AGE
re_exp_Format_Name_Gender_Phone_Load_Date to
STG_EMPLOYEES:
OUT_NAME J EMPLOYEE_NAME
OUT_PHONE J EMPLOYEE_PHONE_NMBR
OUT_GENDER J EMPLOYEE_GENDER
OUT_AGE_GROUP J AGE_GROUP
저장
8. Create and Run Workflow
wkf_STG_EMPLOYEES_07
session - m_STG_EMPLOYEES_07
링크 걸고
Edit
Mapping 탭에서
Source file
filename - employees_list.txt 로
filetype - Indirect
Target
TDBU 로 수정, Truncate 체크
lkp_salaries 파일명을 salaries.txt 입력
저장 후 start the workflow
109건 적재 확인