The diagram shows an example of the Conduits for Connect process using database tables as the source.
- You load data from your original databases into tables created especially to
hold data that you want to use in your Connect system. Use extract scripts to
pull your data from your original databases and place it in one set of tables:
CUSTOMER_MASTER, CUST_SUBSCRIPTION, and CUST_ATTRIBUTES.
You extract data from those tables, transform it using Informatica mappings, and load it into the Connect ETL staging tables.
- You use Informatica to load records into the Connect ETL staging tables. Use
Informatica’s Job Control process to facilitate incremental data extraction.
When data is written to the staging tables, a Date/Time stamp must be appended to each record. The JOB_CONTROL table stores a record containing the beginning (FROM_DATE) and end (TO_DATE) Date/Time stamps that are used to identify staging table records that have not been extracted during previous data loads.
For example, assume that all records written to the Staging Tables were loaded on Dec 5, 2000 and each record is stamped with this Date/Time. When the Phase-1 mappings are executed on Dec 6, 2000, the JOB_CONTROL table is automatically updated and shows the following entry:
SESSION_NAME FROM_DATE TO_DATE CLIENT_STAGING1 05-DEC-00 06-DEC-00 This tells Informatica to take only the staging table records whose Date/Time stamp falls between these dates.
After the completion of the first data load, additional records are inserted into the Staging Tables on Dec 6, 2000. If the Phase-1 mappings are executed on Dec 7, 2000, the mappings update the FROM_DATE with the first data load’s TO_DATE, and then overwrite the first data load’s TO_DATE with the database sysdate (current date/time). The entry then looks as follows:
SESSION_NAME FROM_DATE TO_DATE CLIENT_STAGING1 06-DEC-00 07-DEC-00 Now the Phase 1 mappings will take only those records that were inserted into the staging tables since the completion of the first data load.
The Informatica Server Manager can be used either to schedule or to manually execute the Phase 1 mappings. An Informatica post-session command, configured in the session that executes the Phase 1 mappings, can be used to execute the Phase 2 mappings automatically upon completion of these Phase 1 mappings.
- An Informatica post-session command automatically extracts data from the Connect ETL staging tables, transforms it using Informatica mappings, and loads it into the Connect tables. Phase 2 mappings (contained in the black box) validate the data from the Connect ETL staging tables and load records into the Connect database tables. Software developed the Phase 2 mappings and does not recommend or support any customer changes to these mappings.