Extracting and Loading Mechanism in DAC

DAC supports different kind of data extraction and loading combinations to give more flexibility over the kind of data transformation that happens.

Full Extract and Full Load: Loading of data warehouse first time happens in this way

Full Extract and Incremental Load: This comes in a situation like when you have to pull data from a new source and load it into the existing table in warehouse. It will have an additional logic whether to insert/update the record in cases of handling data integrity.

Incremental Extract and Incremental Load: This would be the regular process where the data loading happens every night which captures only the new or changed records.

DAC Process Cycle

DAC is used to design, execute, monitor and diagnose execution plans

Setup: Database connections, ETL setup in Informatica

Design: Define Tasks, Groups and Execution plans

Execute: Define parameters, schedule and run the execution plans

Monitor: Monitoring the run time executions

Diagnose: In case of task failure identifying the route cause and rerunning the task.

Components of DAC

DAC Client: User interface through which user performs the activities (configuration, administration, loading, monitoring) to manage the analytics schema.

DAC Server: Executes the instructions given from the DAC client for running the ETL routines.

DAC Repository: Stores the metadata used by DAC to manage this whole data warehouse process.

Why to use DAC?

Following are the reasons why we go for DAC

• Scheduling Tasks: It helps in scheduling an execution plan run. An execution plan can be scheduled for different frequencies or recurrences by defining multiple schedules. (An execution plan is defined based on business requirements for when the data warehouse needs to be loaded. Execution plans are scheduled in sequence, not in parallel)

• Automate Execution: It can be quite difficult and error prone to run different workflows manually and DAC helps us to automate execution of workflows according to our needs.

• Easy Flow Management: It helps easy management of workflow execution. Take a simple example I have created 5 different workflows 3rd should only be run after 4th and 5th workflow. Running them manually can always leave a possibility of error and result in wrong data load. DAC can help us define dependency and order of the workflows in which they should be run.

Also DAC manage the performance by dropping indexes, truncating stage tables, rebuilding the indexes, and analyzing the tables during the process

If you do not use DAC, you have to write your own custom change capture process and need to redesign from scratch an ETL method that allow the restart of the ETL process from point of failure at record level. The biggest saving is that DAC can survive during the upgrade , while your custom processes cannot.

OBIA Data warehouse full load using DAC

Here we will see how DAC loads the data using the execution plan into target tables.When you run an execution plan, data is extracted from one or more tables in the source system database, dumped into staging tables, and then loaded into tables in the data warehouse. The DAC supports the following extract and load combinations:

Full Load:

Here DAC does full extract of data from the source. All data is extracted from the source system and loaded into the data warehouse. The DAC performs a full extract for a task if the source and staging tables have null refresh dates. If the staging and target has null refresh dates then full load of data is done from staging to target. Its Just a full extract and full load

Full Extract and Incremental Load

Here as we said above the Full extract process is same i.e the whole data from source is loaded into the staging .In this case the source or staging table is null, the DAC invokes the full extract workflow.

Data is loaded from the staging table into the target table through an incremental load command. When the staging and target tables have refresh dates(incremental dates), the DAC invokes an incremental load command. This happens when data is loaded from new source

The incremental load process requires additional logic to determine whether a record should be inserted or updated which is done by the ETL part Therefore, if you add a new source connection to populate an existing data warehouse, you should expect the incremental load to be slower than when running a full load.

Incremental extract And Incremental Load

Here new or changed records are extracted from the source system by DAC and loaded into the data warehouse. Now DAC decides whether the records needs to be updated or inserted based on the incremental logic.The DAC performs an incremental extract for a task if the source and staging tables have refresh dates and performs an incremental load for a task if the staging and target table have refresh dates.

Read about Dac In Obia

What is OBIA? Oracle BI Applications and various Components of OBIA

Oracle BI Applications

The increasing volumes and complexity of data provide a strategic opportunity for organizations to turn information into business intelligence.This has resulted in OBIA. We will see what are the important features of OBIA(Latest version is  Oracle Business Intelligence Applications, v. 7.9.6.1).

Definition:

Oracle Business Intelligence Applications is a prebuilt business intelligence solution that deliver intelligence across an organization, empowering users at all levels from front line operational users to senior management with the key information they need to maximize effectiveness.

Sources for OBIA

EBS or Oracle E-Business Suite

Oracle’s Siebel Applications

Oracle’s PeopleSoft Applications

Oracle’s JD Edwards Applications

Non-Oracle sources such as SAP Applications.

Oracle Business Intelligence Application (OBIA) is composed of : 

INFORMATICA

+

DAC

+

OBIEE

Components of OBIA

Components	Description
DAC Repository metadata files:	Content includes repository objects such as tables, subject areas, execution plans, and tasks, and is contained in XML files.The DAC client and server were included until version 7.9.5. Since then it is a separate installer.
Embedded Informatica ETL tool and repository	ETL is a third party tool which includes mappings (wrapped in workflows) to extract data from the supported source systems (various versions of standard applications such as Siebel CRM - yes it was first - , Oracle eBusiness Suite, Peoplesoft, JDEdwards and SAP (BI Apps version 7.8.4) ) and load the data into the Oracle Business Analysis Warehouse.
Prebuilt metadata content	This metadata content is contained in the Oracle BI Applications repository file  (OracleBIAnalyticsApps.rpd). It maps the source physical tables to a generic business model and includes more than 100 presentation catalogs (aka subject areas) to allow queries and segmentation using Oracle BI clients such as Answers, Dashboards and Segment Designer
Prebuilt reports and dashboard content	Contains hundreds of requests and ready-to-use dashboards which enable tight integration between the source applications and the BI infrastructure
Oracle Business Analytics Warehouse	The prebuilt data warehouse that holds data extracted, transformed, and loaded from the  transactional database
Ad-hoc Analysis	Metadata layer serves as foundation to provide flexible and intuitive ad-hoc reporting and custom dashboard designs through Oracle BI Answers and Oracle BI Dashboard tools that are part of the OBIEE toolset.
Role-Based Dashboards	Delivered role-based dashboards are easily configurable to meet specific organizational needs and practices.

Oracle BI Applications includes the following modules:

• Oracle Financial Analytics (Manage financial performance across locations, customers, products, and territories, and receive real-time alerts on events that may impact financial condition)
• Oracle Human Resources Analytics (Correlate financial measures with key workforce metrics to demonstrate HR’s strategic value in the organization)
• Oracle Procurement and Spend Analytics (Creates visibility to spending patterns for both direct and indirect spend, enabling users to identify and realize the savings opportunities, with the ability to do the spend analysis by commodities, suppliers, supplier locations, buying locations, business units, cost centers, buyers and contract usage)
• Oracle Project Analytics
• Oracle Supply Chain and Order Management Analytics
• And more….

 You can also read more about What a DAC is and Full Load in DAC.

Tables Used by OBIA

Table	Description
Aggregate tables (_A)	These tables contain summed (aggregated) data.
Dimension tables (_D)	Dimension tables with descriptive data
Staging tables for Dimension(_DS)	These tables contain the data which is to be loaded to Target
Fact tables (_F)	Tables storing facts and measures
Dimension Hierarchy tables (_DH)	Tables that store the dimension's hierarchical structure
Fact Staging tables (_FS)	Staging tables used to hold the metrics being analyzed by dimensions that have not been through the final ETL transformations.

Internal Tables in Oracle Business Analytics Warehouse

Internal tables are used primarily by ETL mappings for data transformation and for controlling ETL runs. These tables are not queried by end users and are not directly managed by the Oracle Data Warehouse Administration Console (DAC).

Name	Purpose
W_DUAL_G	Used to generate records for the Day dimension.
W_COSTLST_G	Stores cost lists.
W_EXCH_RATE_G	Stores exchange rates.

Execution of ETL using DAC

1.When the Execution of an Execution Plan Fails

When an execution plan is executed, if a task fails, the status of the tasks that are dependent on the failed task is changed to Stopped. While tasks are still running, the execution plan’s status is Running. When all the tasks have been run, and if one or more tasks have failed, the execution plan’s status is changed to Failed. You can check the tasks that have failed in the Current Run tab of the Execute view, fix the problems,and then requeue the failed tasks by changing the status to Queued. You can then restart the ETL.

All the tasks will then be rerun. You can also manually run a task, change its status to Completed,and then restart the ETL. Tasks with a Completed status are skipped.To restart a Failed ETL, click Run Now from the Current Run tab of the Execute view.

2.In Case of Abnormal Termination of the DAC Server

If the DAC server fails during the execution of the ETL, the status of the ETL execution will remain as Running. When the DAC server is started again, it will automatically run the ETL if the Auto Restart ETL DAC system property is set to True. If the same system property is set to False, when the server restarts, it will set the correct status as Failed. In order to execute the ETL from the point of failure, submit the request to the server again. The DAC server will automatically terminate if it looses connection to the DAC repository.

What is DAC in OBIA

Data Warehouse Administration Console (DAC) is a centralized console for schema management as well as configuration, administration, loading, and monitoring of the Oracle Business Analytics Warehouse.You can also read about How to do Full load in DAC

The DAC provides a framework for the entire life cycle of data warehouse implementations. It allows you to create, configure, execute, and monitor modular data warehouse applications in a parallel, high-performing environment.

• It manages the following data warehouse load processes:
• Dynamic generation of subject areas and execution plans
• Dynamic settings for parallelism and load balancing
• Intelligent task queue engine based on user-defined and computed scores
• Index management for ETL and query performance
• Embedded high-performance OTLP change capture techniques
• Restart from any point of failure