Almost every type of ETL tool has its extract and load strategy with its configurations. In general the user should be concentrated in full load (aka initial load) and incremental load options in whatever tools to utilize. Oracle BI Apps component provides straightforward options to let developer configure appropriate approaches to achieve data load in right time. But the extract technology is a little bit different for various sources – if the source is Siebel, we should configure table trigger and get records produced in an additional image table for insert, delete and update operations; if the source is Oracle EBS, we should utilize the extract query to compare last extract date in DAC with last update date in some specific source tables.
In the Oracle Data Warehouse Admin Console (DAC), we are able to control full or incremental load, as well as truncate target table by setting correct refresh date for related tables. Actually the administrator should keep below table in mind always.
| Scenario | Primary Source Refresh Date |
Primary Target Refresh Date |
Command to use | Truncate Target Table? |
| 1 | Null | Null | Full Load | Yes |
| 2 | Null | Not Null | Full Load | No |
| 3 | Not Null | Null | Full Load | Yes |
| 4 | Not Null | Not Null | Incremental Load | No |
But per our practice, there are several things that developer can pay attention to, this should help you to understand DAC control strategy better.
1.Monitor and reset the refresh date under Setup -> Physical Data Sources. Usually you just care about two connections: Data Warehouse and Source Connection (here we use ORA_EBS1213 as example). Make sure the Refresh date is populated or cleared in appropriate value. The other fields such as analyze date, execution plan can be skipped.
2.Primary Logical source/Target under tasks. In point #1 we know there are two connections so we should know where to bind to them in task design and execution plan after compilation. In the Design -> Tasks edit page, you should be ware that which connection to be used in Primary Logical Source and Primary Logical Target. Usually for SDE mappings, we would put DBConnection_OLTP for source, but for SIL mapping both of fields will be DBConnection_OLAP. This indicates that for SIL mapping we should only look into Refresh date under Data Warehouse connection in point #1. Even we clear out or set values on Refresh date under ORA_R1213, it don’t take effect really.
3.Multiple Primary Sources. When there are multiple sources and they have different last refresh dates, the DAC will pick the earliest one to feed as overall last refresh date. In the below example, the table which in type of Non-primary (lookup or auxiliary) will not be taken into considerations. It looks at only highlighted lists and searches the earliest date, but if any primary table was set Null in its refresh date, the execution plan will trigger a full load.
4.Prune Days. Sometimes even you set correct refresh date as ‘1/1/2000 00:00:00’ and you expect to extract since that date, but DAC does not behave like this. It’s because the Prune days are functioning so the last refresh date that is passed into Informatica mapping would be 5 days before Jan 1st 2000.
5.Preview your extract and load strategy script in Unit Test. After you set your refresh date along with other configurations, you want to know how DAC will trigger the execution plan. Just go to Execution Plan and select updated task, then click on Unit Test. The predefined script will tell you which way to do extract and load. Example:
————————————————————
-Source : INCREMENTAL Target : INCREMENTAL
————————————————————
All Task Batches
TASK:SILOS:SIL_InternalOrganizationDimensionHierarchy_VersionUpdate:1:(Source : INCREMENTAL Target : INCREMENTAL)
