Data Warehouse Administration Console (DAC) Part 1

Following my posts on Informatica let us look into other integral component of BI Applications which is Data warehouse Administration Console. Before starting with DAC let’s have an introduction to BI applications which will help us to understand the big picture.

Oracle BI Applications

This is pre packaged BI applications across many modules like Financials, SCM, CRM, HCM etc which comes with a Central Data warehouse which holds all these data and set of pre built ETL routines which loads data into warehouse which is sourced from ERP systems like Oracle Ebiz, Peoplesoft & Siebel CRM applications.

Components of BI Applications Architecture:

As you see from the below diagram Informatica is the ETL tool used in BI Applications (Oracle Data Integrator also can be used as ETL tool). DAC is the console which is used to manage analytics schema, configuration, administration, loading and monitoring the analytics warehouse.

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.

Terms to know about DAC:

Source System Containers: This is like schemas in database. Objects in the DAC repository belongs to a particular source system container and objects can reference across containers. DAC repository comes with the predefined source system containers which can be copied and customized to implement our solution. We cannot modify the predefined containers and they are like templates.

Subject Areas: It’s just a grouping of activities related to one functional area. For example within HCM there can be subject areas like Talent Management, Compensation etc.

Table Types: There can be different table types like Fact, Dimension, Hierarchy, Aggregate etc

Tasks & Task Groups: Task is unit of work required to load one or more tables. It contains information like source, target tables and other instructions used for loading. Task groups are the group of tasks which will be done in a sequence.

Execution Plan: This is defined on subject areas which will help in loading the tables in that area. It includes the tasks involved and parameters used for executing it.

Schedules: This indicates the frequency in which execution plan should run as per the business requirement.

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.

Extracting and Loading Mechanism’s:

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.

In the next posts let see how to setup DAC for the first time, performing full load and user management.