DAC - EXECUTION PLAN IN DAC

Execution Plan 

An execution plan is the process of entire work carries over in DAC, which enables to organize, schedule and execute the ETL process.

An execution plan is the collection of subject areas give in DAC and its group of tasks.

Execution plan is based on subject areas and business requirements, according to the data warehouse need to be loaded. A process of data transformation includes subject areas and schedule time it depends on the type of loading the data whether full load or incremental loads.

Architecture of Execution Plans

Types of Execution Plans:

1.      Single Source Execution Plan

2.      Multi-Source Execution Plan

Single Source Execution Plan

A single-source execution plan extracts data from a single request of a single source system container, such as Oracle Ebiz or People soft or others.

Multi-Source Execution Plan

A multi – source execution plan extracts data from a single instance of a single source system container or multi source system containers as well as multi instance of a multiple source system containers.

Types of Multi – source Execution Plan

1.      Heterogeneous

 Heterogeneous execution plan which process the instance from various source systems in different time of scheduling the execution plan one after other. Even it extracts the data from single source system with different time of execution of subject areas from different place of locations.

2.    Homogeneous

 Homogeneous execution plan which process the instance from single source system in a particular times of schedule of the tasks as one after the other. It extracts the data from single source system with multiple instances at different location and time.

Extract and Load (Single)

It is the normal and simple plan of extraction and load into the target tables of data warehouse.

Having single instance of load and extract data from single source and move the data into staging, then load to Data warehouse.

Truncate process in DAC for single load and single extract condition, which truncate the tables, indexes and recreate the indexes and it will analyze the table.

Single Load and Multiple Extract

Multiple extract and single load is the process of extracting the data from single or various source systems at various time and location using the single staging and single data warehouse.

Here we have the heterogeneous and homogeneous multi source extraction, transformation and loading process will carry out.

Homogeneous:

Having one or more similar source system at different location and time loads to single end data warehouse.

 

Heterogeneous:

            Having one or more different data source system from different location and time loads data into the end data warehouse.

Truncate process in DAC for single load and multiple extract condition, which truncate the tables, indexes and recreate the indexes and it, will analyze the table. It will analysis the data source systems schedule timings and assign the priority to drop indices and analyze the tables.

Analyze Truncate Tables Only:

Purpose of “Analyze Truncate tables only”, when we running the task for a subject areas  we extract and load as “full load” as well as “Incremental Load”  it indicates the tables associated with the execution plans will be analyzed and run the tasks according to the refresh dates.

Drop/Create Indices:

Indices mean a combination of one or more indexes. It sounds plural of index.

An Index is the optional of created tables, purpose to access the data as much as quick and easy.

By creating one or more columns of the table can have ability to gain some distributed columns or set of rows of data from the original table. It is used to reduce the system heap space and disk space.Flexible in fetching the rows very speedy.

            Indexes of the tables associated with the execution plan will be dropped and re-created. When we using full load all the indexes tables will be dropped and re-created. When we using incremental load the tables where truncated will be dropped and recreated the indexes.

Analyze:

It is the task which is used to evaluate and analyze in the data warehouse after the process of truncation, drop indexes, informatica mapping and then recreation of indexes.

Full Load Always:

It indicates the ETL process to execute the data as a “full load” process.

Refresh Dates:

Refresh dates refer to the date of the last ETL process i.e., the last time data was extracted or loaded from tables in given database. DAC uses the refresh dates to determine whether to run the incremental load commands or to run full load commands and whether to truncate the target tables.

Micro ETL Execution

Micro ETL execution plans are ETL processes that you schedule at very regular intervals, such as hourly or half-hourly. They usually handle small subject areas or subsets of larger subject areas. 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.