OWB - Oracle Warehouse Builder

Oracle Warehouse Builder (OWB)

ETL: The process of extracting data from its source location, transforming it as defined in a mapping, and loading it into target objects (or schemas). ETL stands for extract, transform, and load.

Oracle Warehouse Builder (OWB) is an ETL tool produced by Oracle that offers a graphical environment to build, manage and maintain data integration processes in BI Systems.

Major Components in OWB Architecture:

1. Design Center

2. Control Center Manager

3. Target Schema  

4. Warehouse Builder Repository

5. Control Center Service.

Mapping: An object that contains operations for extraction, transformation, and loading (ETL) that moves data from sources to targets. 

Design Center: Is a client GUI to design and develop ETL mapping.

To create a mapping, just import the source tables (right click at tables and import) and define the loading type of a mapping insert, update, insert update and verify column data types; Target load order in below screen, will give the objects execution order. Click f4 to compile / validate and generate for generating PLSQL package. In above palette, there are several components.

Sequence   – Generates values for artificial l keys

Expression – SQL expression

Aggregator (group by) – Aggregates data, e.g. sum, average

Filter (where) – Removes rows based on a filter condition

Sorter – Sorts rows

Joiner (joining multiple tables with multiple input/output groups) – Joins sets of rows

Key Lookup (it’s like validation if it exists then process) – Returns a key from a table, dimension or view

Splitter – Splits the dataflow based on a condition

Deduplicator (distinct) – Removes duplicates from a set of rows

Constant – Constant value (like creation_date, last_update_date).

Data Generator – Generates values, e.g. record count, system date

External Process – Initiates an external process, e.g. an operating system executable

Input/output Parameter – Reads or writes a parameter

Pre/Post-Mapping Process – Initiates a process before/after the mapping executes

Set Operation – Performs set operations e.g. intersect, minus, and union

Cast: is like converting one type into different type.

Pluggable mapping is like re-usable mapping (execution status).

View Operator: Placeholder for a view.

Control Center Manager: The graphical console of the Control Center Service for centrally viewing and managing all aspects of deployment and execution. Tools à Control Center Manager to compile and execute the objects. Deployment action can be None, Create, Drop and Replace.

To execute the mapping either any scheduler (like TIDAL or Oracle Applications or Batch program).

-- To execute

DECLARE

v varchar2(32767);

BEGIN

v:=exec_mapping('TEST_MAP','TARGET_SCHEMA');

END;

-- To Find mapping errors in OWB Audit tables

SELECT err.creation_date,

       to_char(err.creation_date,'HH24:MI') hr,rta_lob_name,

       rte_dest_table,

       rte_statement,

       rte_sqlerrm

  FROM owb_owner.wb_rt_audit rt, owb_owner.wb_rt_errors err

 WHERE rt.rta_iid = err.rta_iid ORDER BY 1 DESC;

Control Center Service: A service that runs outside the database, which can monitor and execute things that cannot be run directly in the database, such as: PL*SQL scripts, SQL*Loader, and shell scripts. Enables deployment of Oracle Warehouse Builder mappings and processes to targets (databases, and so on), and the execution of these mappings and processes.

Target schema: A schema that contains the data objects that store your data warehouse data. You can design a relational target schema or a dimensional target schema.

Workspace: Oracle Warehouse Builder structure that contains all the related projects and their objects. Graphically displayed as the canvas in the Design Center where Oracle Warehouse Builder windows, navigators, wizards, and dialog boxes are laid out to create a work environment that one or more users log in to.

OMB plus is a flexible, high-level command line metadata access tool for Oracle
Warehouse Builder. We use OMB Plus to create, modify, delete, and retrieve object metadata in Warehouse Builder design and runtime repositories.

OMB Plus is a scripting language used to manipulate object and runtime repositories of OWB.OMB Plus is an extension TCL Programming language and hence has variable constructs, looping and control structures. .TCL Scripts (It has OWB Deployments) can be executed thru OMBPlus.

OMBPlus can be launched at Unix Prompt by using: OWB_HOME/owb/bin/unix/OMBPlus.sh and then connect to database with OMBCONNECT.

OWB Repository: The single, unified repository for the database instance, which is pre-seeded with a schema and database objects. The run time environment and the design environment reside in this single repository. The repository schema, named OWBSYS, gets created when you install Oracle Database.

Pluggable mapping: A reusable grouping of mapping operators that works as a single operator. Similar in concept to a function in a programming language.

Project: The highest-level and largest object in Oracle Warehouse Builder workspace. Each project contains the metadata and definitions for objects in the data system that contains the sources and targets.

Dimension: A dimension table typically has two types of columns, primary keys to fact tables and textual\descriptive data. Example is Time, Customer.

Types of Dimensions: 

1.    Slowly Changing Dimensions (Type1, losing history when update).

2.    Type 2

3.    Type 3

1.     Type 1 Dim Attributes of a dimension that would undergo changes over time. It depends on the business requirement whether particular attribute history of changes should be preserved in the data warehouse. This is called a slowly changing attribute and a dimension containing such an attribute is called a slowly changing dimension.

2.    Type 2 Dim stores all the history (with effective from and to dates, it increase rows).

3.     Type 3 Dim stores only last history (It increases the columns).

Fact Tables:

1.     Snapshot

2.     Cumulative

3.     Factless Fact Table

Snapshot: This type of fact table describes the state of things in a particular instance of time, and usually includes more semi-additive and non-additive facts. The second example presented here is a snapshot fact table. Eg: Daily balances fact can be summed up through the customers dimension but not through the time dimension.

Cumulative: This type of fact table describes what has happened over a period of time. For example, this fact table may describe the total sales by product by store by day. The facts for this type of fact tables are mostly additive facts. The first example presented here is a cumulative fact table. Eg: Sales fact

Factless Fact Table: In the real world, it is possible to have a fact table that contains no measures or facts. These tables are called “Factless Fact tables”. Eg: A fact table which has only product key and date key is a factless fact. There are no measures in this table. But still you can get the number products sold over a period of time.

Debugging a map can be done thru above audit tables or it can be done thru intermediate query generator by selecting that Ingroup and intermediate query as shown in below screens.

Few screens about operators..

Lookup..

With Transformational operator, we can use oracle procedures, functions, packages in OWB map pings. There is import option in transformations, with that we can import from database directly.

We can pass the input parameters thru constants into transformation operator. We can generate the files, send emails, alerts and run the reports.

Refer the below transformation operator..

Above, CURr_COnv_prc is the procedure with 4 parameter from input parameter.

Splitter operator splits the data from one source into multiple targets with where conditions.

 

1