Informatica Informat ica Power Center 9.0.1 Building Financial Financial Data Mode - Lab#32 Data Transformation Transformation Guide IV
Description: BISP is co BISP comm mmit itte ted d to pr prov ovid ide e BE BEST ST le lear arni ning ng ma mate teria riall to th the e begi be ginn nne ers an and d ad adva vanc nce e le lear arne ners rs.. In th the e sa same me se seri ries es,, we ha have ve prep pr epar ared ed a co comp mple lete te en endd-to to en end d Ha Hand ndss-on on Gu Guid ide e fo forr bui uild ldin ing g financial data model in Informatica. The document focuses on how the th e re real al wo worl rld d re requ quir irem emen entt sho should uld be int inter erpr pret eted ed.. The ma mapp ppin ing g docum do cumen entt te temp mpla late te wi with th ve very ry si simp mpli lifi fied ed st step eps s and sc scre reen en sh shot ots s makes the complete learning so easy. The document focuses on various data transformation required to while loading data to the transaction system. Join system. Join our professional training program and an d learn from experts.
History: Version 0.1 0.1
Description Change Initial Draft Review#1
www.bispsolutions.com Page 1
Author Upendra Upadhyay Amit Sharma
www.bisptrainigs.com
Publish Date 12th Aug 2011 18th Aug 2011
www.hyperionguru.com
Table of Contents Table of Contents.......................... ..................................................... ...................................................... ...................................................... ............................. .. 2 Objective:........................................................... ...................................................................................... ........................................... ............................ ............... ... 3 Summary:........................................................ Summary: ................................................................................... ............................................................. .................................. 3 Mapping Sheet ....... ............... ................ ............... ............... ................ ................ ............... ............... ................ ............... ............... ................ .............. .......... .... 4 Importing Importin g Source and Target Table........ ................ ............... ............... ................ ............... ............... ............ ........ ........ ........ ........ ........ .... 4 CREATE MAPPING....................... .................................................. .................................................................... ......................................... ............ .............. ..9 CREATE WORKFLOW ......................... .................................................... ...................................................... .............................................. ................... 36 Execute Execu te Workflow, Review Rev iew data and Check log File ........ ................ ................ ............... ............... ................ ............ ......42 ..................................................... .......................... ...................................................... ..................................................................... .......................................... ........... 45
www.bispsolutions.com Page 2
www.bisptrainigs.com
www.hyperionguru.com
Objective: This document is designed to demonstrate various Data Transformation and Data Quality check examples. We have used many real time scenarios to bridge the gap between theory concept and real life learning. This guide contents many transformation examples with lookup tables.
Summary: Source Table - STG_PAYMENT_PATTERN, Lookup Table -SRC_PAYMENT_TYPE_MLS, SRC_PATTERN_TYPE_MLS SRC_AMOUNT_TYPE_ML SRC_AMOUNT_TYPE_MLS, S, SRC_MONTHS_MLS, SRC_MONTHS_MLS, SRC_MULTIPLIER_MLS Target Table - TRG_PAYMENT_PATT TRG_PAYMENT_PATTERN ERN TRG_PAYMENT_PATTERN_EVENT
Process:
Launch Informatica Power Center Designer 9.0.1 Login to repository and open assign folder then Import source table in Sour Source ce analy analyzer zer windo window w and and Impor Importt target target table table in Targe Targett Desig Designer ner window in Informatica Power Center Designer. Click on Mapping Designer widow and create mapping and then create expression transformation to define the data quality check logic and then save this mapping. Create lookup transformation to get the value from lookup table. Launch Informatica Power Center Workflow Manager and create workflow in work let window and then assign session through create task. Create source and target relational connection (If source and target table is relational database) and define source and target connection in session. And then execute workflow. Check execution log file and review of data.
www.bispsolutions.com Page 3
www.bisptrainigs.com
www.hyperionguru.com
Mapping Sheet
Importing Source and Target Table Import data from source and create target table (There are two types for create target table, Manually or import from database). There are following step to importing source and target database (Table). Step-1 Laun Launch ch the Infor Informat matica ica Power Power Center Center Desig Designer ner,, Go to Start Start All Programs Informatica Informatica 9.0.1 Client Power Center Client Power Center Designer.
Step-2 Then Connect to Repositories Repositories in Informatica Informatica Power Center Designer, Designer, Right click on repositories name and click on Connect. Then sign into your assigned folder.
www.bispsolutions.com Page 4
www.bisptrainigs.com
www.hyperionguru.com
Right Click on
Step-3 Then go to Tools Menu in menu bar and click on Source Analyzer to import source table. Source Analyzer use for importing source definitions in Informatica Power Center or Updating s ource definitions either manually or by reimporting the definition.
Select Source Analyzer
Step-4 And then go to Sources Menu in Informatica Power Center Designer Menu bar and select Import from database (Here some other options available such as Import from Database(import source data from RDBMS), Import from File(i File(imp mpor ortt sourc source e data data from from Flatfi Flatfile le), ), Impor Importt from from Cobol Cobol File(i File(imp mpor ortt Cobol Cobol source), Import XML Definition(import source data from XML) etc.).
Click on Import from
Step-5 Select ODBC data source (If you need to create or modify an ODBC data source, click the Browse button to open the ODBC Administrator. Create the data source, and click on OK button. Then select the new ODBC data source) and then specify Username, Owner name and password after that click on connect button. Then select source table, if you want to select all table then click on Select All button to select All table and then click on OK Button.
Here also some other options available available such as search table name and show all owners. And if you got some
www.bispsolutions.com Page 5
www.bisptrainigs.com
www.hyperionguru.com
Step-6 Source Source table table in source source analyze analyzerr window window.. Then Then right right click click on Source Source table table and select select Preview Preview Data to view data. data. If you want to edit edit some column column and its relationship then you can do this from select Edit options.
www.bispsolutions.com Page 6
www.bisptrainigs.com
www.hyperionguru.com
Step-7 When you click on Preview Data, A new window appear. Select ODBC data source connection and then specify Username, Owner name and Password and then click on Connect button.
Step-8 Now Now clic click k on Targ Target et Desi Design gner er.. And And then then sele select ct Targe argett menu menu in Informatica Informatica Power Center Designer Menu bar and click on Import from database database to import target table. (Or create manually, In target menu click on create select database type and click OK. Then add column in Edit window. If target table stored in any RDBMS then select target table and then go to target menu and click on Generate/Execute SQL... Generate/Execute window appears. Then click on connect option and connect to database and specify information and then click on Generate and Execute ).
Step-9 Specify Username, Owner name, password and then click on connect and then select target table and click on OK.
www.bispsolutions.com Page 7
www.bisptrainigs.com
www.hyperionguru.com
Step-10 Target table in Target Designer tab. Here two target table in target designer window. First Target Table(TRG_ACCOUNT_DETAILS) - Store all account information. Second Target Table(Branch Grade) - Store Branch_CD and Grade.
www.bispsolutions.com Page 8
www.bisptrainigs.com
www.hyperionguru.com
CREATE MAPPING Mapping : Mappings represent the data flow b/w sources to targets. When the Informatica Power Center Server executes a session, it uses the instructions configured in the mapping to read, transform, and write data. Every mapping must contain the following components: Source definition: definition: It describes the characteristics of a source. Transformation: A transformation is a repository object which reads the data, modifies the data and passes the data. Transformations Transformations in a mapping represent represent the operations that the integration service performs on the data. Target definition: definition: It describes the target table. Step-1 Go to Mapping Mapping Designe Designerr and Create Create New Mapping Mapping for the Data Quality Check and then name of mapping and click OK button.
Step-2 Opens the folder and it shows you the subfolders associated with it.
Step-3 Then expand the sources and target subfolder and drag and drop source and target table into Mapping Designer Window from respective folder.
www.bispsolutions.com Page 9
www.bisptrainigs.com
www.hyperionguru.com
Step-4 Now create expression transformation for apply the business logic. EXPRESSION TRANSFORMATION - An Expression transformation is a connected and passive transformation used to calculate values on a single row.
Select Expression transformation in drop
Step-5 Now Now pass passes es all all Sour Source ce qual qualif ifie ierr tabl table e transformation table in Mapping Designer window.
www.bispsolutions.com Page 10
www.bisptrainigs.com
colu column mn into into expr expres essi sion on
www.hyperionguru.com
Step-6 Now create lookup transformation for look up data from SRC_MULTIPLIER_MLS table. Select Lookup in drop down and give name of lookup transformation. Lookup TRANSFORMATION - Lookup transformation is passive and Connected or Unconnected transformation ( Dynamic lookup is connected). Use a look up data in a flat Lookup file or a relational relational table, Select transformation in drop view, or synony m.
Step-7 Now click on From Relational Table in Import option.
Step-8 Now select ODBC data source and then give Username, Owner name and password and click on Connect button, select table as a lookup table and then click on OK button.
www.bispsolutions.com Page 11
www.bisptrainigs.com
www.hyperionguru.com
Step-9 Now pass the V_PAYMENT_EVENT_FREQ_MULT Source qualifier column into lookup table and then right click on lookup table and select Edit to give condition.
Step-10 Now go to Condition and click on Add button to give condition and select Lookup Table Column and select operator and select Transformation Port column and then click on APPLY button and click on OK button.
www.bispsolutions.com Page 12
www.bisptrainigs.com
www.hyperionguru.com
Step-11 Now create lookup transformation for look up data from SRC_MONTHS_MLS table. Select Lookup in drop down and give name of loLookup okup transformation. Select transformation in drop
Step-12 Now click on From Relational Table in Import option.
www.bispsolutions.com Page 13
www.bisptrainigs.com
www.hyperionguru.com
Step-13 Now select ODBC data source and then give Username, Owner name and password and click on Connect button, select table as a lookup table and then click on OK button.
Step-14 Now pass the V_PAYMENT_EVENT_MONTHS Source qualifier column into lookup table and then right click on lookup table and select Edit to give condition.
www.bispsolutions.com Page 14
www.bisptrainigs.com
www.hyperionguru.com
Step-15 Now go to Condition and click on Add button to give condition and select Lookup Table Column and select operator and select Transformation Port column and then click on APPLY button and click on OK button.
Step-16 Now create lookup transformation for look up data from SRC_PAYMENT_TYPE table. Select Lookup in drop down and give name of lookup transformation.
www.bispsolutions.com Page 15
www.bisptrainigs.com
www.hyperionguru.com
Select Lookup transformation in drop
Step-17 Now click on From Relational Table in Import option.
Step-18 Now select ODBC data source and then give Username, Owner name and password and click on Connect button, select table as a lookup table and then click on OK button.
www.bispsolutions.com Page 16
www.bisptrainigs.com
www.hyperionguru.com
Step-19 Now pass the V_TERM_TYPE Source qualifier column into lookup table and then right click on lookup table and select Edit to give condition.
Step-20 Now go to Condition and click on Add button to give condition and select Lookup Table Column and select operator and select Transformation Port column and then click on APPLY button and click on OK button.
www.bispsolutions.com Page 17
www.bisptrainigs.com
www.hyperionguru.com
www.bispsolutions.com Page 18
www.bisptrainigs.com
www.hyperionguru.com
Step-21 Now create lookup transformation for look up data from SRC_PATTERN_MLS table. Select Lookup in drop down and give name of loLookup okup transformation. Select transformation in drop
Step-22 Now click on From Relational Table in Import option.
Step-23 Now select ODBC data source and then give Username, Owner name and password and click on Connect button, select table as a lookup table and then click on OK button.
www.bispsolutions.com Page 19
www.bisptrainigs.com
www.hyperionguru.com
Step-24 Now pass the V_PATTERN_TYPE Source qualifier column into lookup table and then right click on lookup table and select Edit to give condition.
Step-25 Now go to Condition and click on Add button to give condition and select Lookup Table Column and select operator and select Transformation Port column and then click on APPLY button and click on OK button.
www.bispsolutions.com Page 20
www.bisptrainigs.com
www.hyperionguru.com
Step-26 Now create lookup transformation for look up data from SRC_AMOUNT_TYPE table
Select Lookup transformation in drop
Step-27 Now click on From Relational Table in Import option.
www.bispsolutions.com Page 21
www.bisptrainigs.com
www.hyperionguru.com
Step-28 Now select ODBC data source and then give Username, Owner name and password and click on Connect button, select table as a lookup table and then click on OK button.
Step-29 Now pass the V_AMOUNT_TYPE Source qualifier column into lookup table and then right click on lookup table and select Edit to give condition.
www.bispsolutions.com Page 22
www.bisptrainigs.com
www.hyperionguru.com
Step-30 Now go to Condition and click on Add button to give condition and select Lookup Table Column and select operator and select Transformation Port column and then click on APPLY button and click on OK button.
Step-31 Now pass all output lookup column into expression transformation table and then right click on Expression table and select Edit for apply data quality check query in Mapping designer window.
www.bispsolutions.com Page 23
www.bisptrainigs.com
www.hyperionguru.com
Step-32 Then go to ports tab, select V_PATTERN_TYPE V_PATTERN_TYPE COLUMN and create its dummy column, column, rename rename it, check only only output checkbox checkbox and input check check box for its source input column, Then go to expression editor and write SQL query.
Step-33 Then write SQL Query, Query, and then click click on Validate button button and then click on OK button. And
www.bispsolutions.com Page 24
www.bisptrainigs.com
www.hyperionguru.com
Step-34 Then, select V_PATTERN_EVENT_M V_PATTERN_EVENT_MONTH ONTH COLUMN and create its dummy column, column, rename rename it, check only only output checkbox checkbox and input check check box for its source input column, Then go to expression editor and write SQL query.
www.bispsolutions.com Page 25
www.bisptrainigs.com
www.hyperionguru.com
Step-35 Then write SQL Query, Query, and then click click on Validate button button and then click on OK button.
Step-36 Then, select N_PAYMENT_EVENT_FREQ N_PAYMENT_EVENT_FREQ COLUMN and create its dummy column, column, rename rename it, check only only output output checkbox checkbox and input input check box for its source input column, Then go to expression editor and write SQL query.
www.bispsolutions.com Page 26
www.bisptrainigs.com
www.hyperionguru.com
Step-37 Then write SQL Query, Query, and then click click on Validate button button and then click on OK button.
Step-38 Then, select N_PAYMENT_EVENT_R N_PAYMENT_EVENT_REPEAT_VALUE EPEAT_VALUE COLUMN and create its dummy column, rename it, check only output checkbox and input check box for its source input column, Then go to expression editor and write SQL query.
www.bispsolutions.com Page 27
www.bisptrainigs.com
www.hyperionguru.com
Step-39 Then write SQL Query, Query, and then click click on Validate button button and then click on OK button.
www.bispsolutions.com Page 28
www.bisptrainigs.com
www.hyperionguru.com
Step-40 Then, select N_AMOUNT COLUMN and create its dummy column, rename it, check only only output checkbox checkbox and input input check box for its source input input column, Then go to expression editor and write SQL query.
Step-41 Then write SQL Query, Query, and then click click on Validate button button and then click on OK button.
www.bispsolutions.com Page 29
www.bisptrainigs.com
www.hyperionguru.com
Step-42 Then, select V_PAYMENT_T V_PAYMENT_TYPE YPE COLUMN and create create its dummy column, column, rename it, check only only output checkbox checkbox and input input check box for its source input input column, Then go to expression editor and write SQL query.
Step-43 Then write SQL Query, Query, and then click click on Validate button button and then click on OK button. www.bispsolutions.com Page 30
www.bisptrainigs.com
www.hyperionguru.com
Step-44 Then click on add button and create two column, Rename both column, First as CREATED_BY and second as CREATED_DATE and then go to expression editor.
www.bispsolutions.com Page 31
www.bisptrainigs.com
www.hyperionguru.com
Step-45 Then write SQL Query, and then click on Validate button and then click on OK button.
Step-46 Then go to CREATED_DATE CREATED_DATE expression expression editor.
www.bispsolutions.com Page 32
www.bisptrainigs.com
www.hyperionguru.com
Step-47 And write sysdate and then click on Validate button and then click OK button.
Step-48 All expression in Expression editor .
www.bispsolutions.com Page 33
www.bisptrainigs.com
www.hyperionguru.com
Step-49 Then created created mapping. mapping.
Step-50 . Save this mapping.
www.bispsolutions.com Page 34
www.bisptrainigs.com
www.hyperionguru.com
v
www.bispsolutions.com Page 35
www.bisptrainigs.com
www.hyperionguru.com
CREATE WORKFLOW Workflow Manager: Workflow load the data between source to target b/w sequential manner. And also Define run-time properties for a mapping, known as sessions. Step-1 Open Informatica PowerCenter Workflow Manager and then go to go to workflow designer and click on workflow menu to create workflow. Specify name of workflow and click OK.
Step-2 Workflow Designer.
www.bispsolutions.com Page 36
www.bisptrainigs.com
www.hyperionguru.com
Step-3 Create Task and assign session, Click on Task menu and click on create and then name of session, click Create, select mapping and click OK. SESSION - A session is a set of instructions that tells the Power Center Server how and when to move data from sources to targets.
www.bispsolutions.com Page 37
www.bisptrainigs.com
www.hyperionguru.com
Step-4 Session in workflow designer windows.
Step-5 Now create flow B/W Workflow to Task. Select Line Task and link to Start to s_Data_Quality_Check. Link Task - Link task to determine the order of o f execution in the workflow
Step-6 Work Flow Designer Windows.
Step-7 Now Configure Connection to Target, Then Right Click on Expression and Edit, Then Click Mapping Mapping tab and configure configure connection connection for your Target table table schema in oracle 11g RDBMS. And then click OK.
www.bispsolutions.com Page 38
www.bisptrainigs.com
www.hyperionguru.com
Step-8 In Mapping. Specify Connection and property.
www.bispsolutions.com Page 39
www.bisptrainigs.com
www.hyperionguru.com
Step-9 Set These property for target table. • • • • • •
Insert: Check this option to insert a row in the target table. Delete: Check this option to delete a row in the target table. Truncate Table: check this option to truncate the target table before loading the data. Update as Update: Update the row in the target table. Update as Insert: Insert the row which is flagged as update. Update else Insert: If the row exists in the target table, then update the row. Otherwise, insert the row.
Set property for TRG_ACCOUNT_DETAILS.
www.bispsolutions.com Page 40
www.bisptrainigs.com
www.hyperionguru.com
Step-10 Set These Set property for Branch_Grade.
Step-11 Now save (ctrl+s) this workflow and check it.
www.bispsolutions.com Page 41
www.bisptrainigs.com
www.hyperionguru.com
Execute Workflow, Review data and Check log File Workflow monitor: Workflow monitor is helpful in monitoring and tracking the workflow created in Informatica power center. Step-1 Now Start Workflow, Right click on Workflow Designer Window and Click on Start Workflow.
Step-2 Check session in Informatica PowerCenter Workflow Monitor.
Session
successfully
Target table load. load. www.bispsolutions.com Page 42
www.bisptrainigs.com
www.hyperionguru.com
Step-3 Now view data into Oracle 11g..
www.bispsolutions.com Page 43
www.bisptrainigs.com
www.hyperionguru.com
Step-4 Session Log. Right click on session and select session log.
Target
Table
SQL Query.
www.bispsolutions.com Page 44
www.bisptrainigs.com
www.hyperionguru.com
Lookup Table.
Load Summary.
www.bispsolutions.com Page 45
www.bisptrainigs.com
www.hyperionguru.com