Oracle^® Business Intelligence Data Warehouse Administration Console Guide > Customizing, Designing, Executing and Monitoring ETL Processes >

Creating a Micro ETL Execution Plan

Micro ETL execution plans are ETL processes that you schedule at very frequent intervals, such as hourly or half-hourly. They usually handle small subject areas or subsets of larger subject areas. The DAC tracks refresh dates for tables in micro ETL execution plans separately from other execution plans and uses these refresh dates in the change capture process.

After a micro ETL execution plan runs, the DAC populates refresh date values in the Refresh Dates child tab of the Execution Plans tab. If a subject area is used in a regular execution plan (an execution plan with the Keep Separate Refresh Dates option not selected) as well as a micro ETL execution plan, the DAC maintains refresh dates for the tables in the regular execution plan in the Refresh Dates child tab of the Physical Data Sources tab (Setup view).

In cases of a subject area being used in both a regular and micro ETL execution plan and the micro ETL execution plan is suspended for a few days but the regular execution plan runs nightly, the DAC automatically detects the last refresh date for the tables common to both execution plans and intelligently extracts only the most recent records for the micro ETL execution plan.

CAUTION:  Micro ETL processes can cause issues with data inconsistencies, data availability, and additional load on the transactional database. Therefore, you should consider the following factors before implementing a micro ETL process:

• For related star schemas, if one schema is omitted from a micro ETL execution plan, the cross-star reports may be inaccurate. For example, if the Person fact table is refreshed more frequently than the Revenue fact table, a report that spans the Person and Revenue dimensional schemas may produce inconsistent results.
• If you omit dimension tables from a micro ETL execution plan, the foreign keys for the fact tables will point to Unspecified rows for the new dimension records. The foreign key references will be resolved when the Complete ETL execution plan is run, but users of the reports should be aware of such inconsistencies.
• If you do not include aggregate tables in micro ETL execution plans, the reports that use data from these tables will be inconsistent with the reports that use data from the detailed fact tables. However, if aggregate tables are included in the micro ETL execution plan, the aggregate calculations are performed for each ETL process, which will take a constant amount of time and may be inefficient to perform at such frequent intervals.
• Hierarchy tables are rebuilt during every ETL execution plan by querying the base dimension tables. This operation takes a constant amount of time. If the base tables are big, this operation may take a long time and may be inefficient if the micro ETL execution plan runs several times a day. However, if you avoid populating the hierarchy tables during micro ETL processes, data inconsistencies will occur.
• With micro ETL execution plans, caching will occur more frequently, which may have performance implications.
• Micro ETL execution plans will put more load on the transactional database because of the frequent extracts.

To create a micro ETL execution plan

1. In the DAC toolbar, select the appropriate source system container from the drop-down list in the toolbar.
2. In the Menu bar, click Views > Design > Subject Areas.
3. In the Subject Areas tab, assemble a small subject area.
4. In the Tasks child tab, inactivate all tasks that are not required for the execution plan.
5. Create a new execution plan.
   1. Navigate to Execute > Execution Plans.
   2. Enter a name for the execution plan
   3. Select the Keep Separate Refresh Dates check box.
   4. Click Save.
6. Associate one or more subject areas with the execution plan.
   1. Click the Subject Areas child tab.
   2. Click Add/Remove in the bottom pane toolbar.
   3. In the Choose Subject Areas dialog box, select the appropriate source system container.
   4. Query for the subject area you want to associate with the execution plan.
   5. Select the subject area and click Add.

      You can associate multiple subject areas with an execution plan, but all the subject areas must be from the same source system container.

   6. Click OK to close the window.
7. Generate the runtime parameters.
   1. Click the Parameters child tab.
   2. Click Generate in the bottom pane toolbar.
   3. For each Datasource type, enter the appropriate name in the Value field.
8. In the top pane of the Execution Plans tab, make sure the new execution plan is highlighted, and click Build.

   The DAC builds the execution plan.

9. Navigate to the Ordered Tasks child tab and verify the sequence of tasks.

   The execution plan is now ready to run as a micro ETL execution plan.

10. Create a schedule for the micro ETL execution plan. For instructions, see Scheduling an Execution Plan.