A question that I often receive is: “What should I do to learn how to develop ETL mappings using Informatica PowerCenter?” Here are the steps that I would take to learn how to do extract, transform, and load with Informatica PowerCenter. I assume that you already have knowledge of SQL. If you don’t know SQL or need to learn more about SQL, I would recommend that you buy Learning SQL by Alan Beaulieu....
There are situations where you need to keep a source and target in sync. One method to do this is to truncate and reload. However this method is not that efficient for a table with millions of rows of data. You really only want to: insert rows from the source that don’t exist in the target update rows that have changed delete rows from the target that no longer exist in the source Can you do this efficiently in a single informatica mapping?...
If you need to denormalize or flatten PeopleSoft summer trees (trees with leaves) in your data warehouse, here is an ETL process for you to follow. Typical PeopleSoft summer tree structure SQL to flatten the PeopleSoft summer tree Substitute TREE_NAME with the name(s) of your summer tree(s) on line 54 below. 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 SELECT SQ4....
If you are attempting to insert millions of rows into an oracle database, you may want to temporarily disable indexes and constraints to improve the speed of the load. Here are 5 steps to follow that will disable constraints and make indexes unusable. You need to substitute the OWNER and TABLE on line 5 and 6 respectively in each statement. 1. Disable Constraints 1 2 3 4 5 6 7 8 9 10 11 BEGIN FOR cur IN (SELECT OWNER, CONSTRAINT_NAME , TABLE_NAME FROM all_constraints WHERE OWNER = 'OWNER' AND TABLE_NAME = 'TABLE') LOOP EXECUTE IMMEDIATE 'ALTER TABLE '||cur....