OWB Architecture, Designing Source and Targeted Database

Oracle Warehouse Builder Architecture and Components

Design Center: It provides visual user interface for designing, monitoring, scheduling and deploying ETL process for extracting and transforming data.

Control Center Manager: It resides in the Design Center and from which user can deploy and execute ETL processes. It is a comprehensive deployment console that enables user to monitor all aspects of deployment.

Target Schema: It is the target database where a user can load data and the objects that are designed by Design Center such as cubes, dimensions, views, and mappings. It contains OWB components such as synonyms which enables the ETL mappings to access the audit service packages in the repository. Warehouse builder Repository stores execution and deployment information to the target schema.

It is not a part of the Warehouse Builder but it is a component of the Oracle Database or any other database. User can associate multiple target schemas with a single Warehouse Builder repository.

Warehouse Builder Repository: Repository stores metadata definitions for all the sources, targets, and ETL processes which are a part of the design metadata. It also stores the runtime data generated by the Control Center Service and Control Center Manager. Repository Assistant helps us to configure repository in database.

Workspaces: User can create one or more workspaces based on a set of users working on relevant project in oracle warehouse Builder. User creates separate workspaces for easier and faster development, testing and production.
Repository Assistant also helps us to manage existing workspaces and in creating new ones.

Repository Browser: It provides a web browser interface for monitoring and reporting repositories. Using this Repository Browser, we can view the metadata, audit runtime operations, create reports and perform impact analysis, view control center-specific and design-specific information.

Control Center Service: It is the part of server, where we register our source and targeted database locations. It also provides deployment and execution of the ETL logic which we have designed in the Design Center like mappings and process flows.

Create the Oracle Design Center User and Repository

For logging in to an OWB Design Center, we need to create a OWB user first. We need to create a new workspace for development, testing and production, a new workspace user, and need to install the Warehouse Builder Repository.

Logging In to the Oracle Design Center

For logging into OWB Design Center using repository owner, we need to provide connection details of the repository. Design Center is divided into three parts: Project Explorer, Connection Explorer, and Global Explorer.

1. Project Explorer is a container to manage our design work.
2. In Global Explorer panel, we can define the metadata security strategies according to your implementation requirements.
3. In Connection Explorer, We can configure location of the source and targeted database, Files, Application, etc.

Working with Flat File Transactional Data

Flat files are a data source for warehouse. Now, we need to create a flat file source module. A flat file source module defines a connection to a folder that contains a number of sample flat files which we want to import into warehouse project.

a. Importing Flat File Metadata using the Flat File Sample Wizard

After creating a flat file module, we can import flat files sources into Warehouse Builder. Import Metadata Wizard helps us to import metadata objects. Every time we use the Import Metadata Wizard to import metadata from existing source flat files, the Import Metadata Wizard launches the Flat File Sample Wizard. Use this Wizard to check and document metadata for flat files. This wizard provides flat file extract functionalities.

b. Create module for Oracle Data source and Import Data from Oracle

We can create modules for various other sources, such as for Oracle-based relational table source metadata. We can also import our data from an Oracle source.

Designing the Relational Target Warehouse

Designing a relational target warehouse in Warehouse Builder constitutes modeling relational (from 3rd normal form to dimensional) schemas that provide high quality data into data warehouse. We can also design a multidimensional system through high-end analytics.

Oracle Warehouse Builder provides advanced relational and dimensional modeling system. It enables user to create Relational source objects such as Tables, External Tables, Views, Materialized Views and Sequences and Dimensional target objects such as Dimensions and Cubes.

Oracle Warehouse Builder explicitly differentiates dimensional design from physical implementation. We can choose a relational implementation or a multidimensional implementation for the dimensional objects using simple operations. That is the only reason, the same dimensional and multidimensional object design can be implemented as a relational target warehouse or a multidimensional warehouse which implemented in OLAP.

Defining a Target Warehouse Module in OWB

For configuring and designing of our target warehouse, we need to create target module in warehouse builder. Target modules contain the metadata definition of the target objects which we design. Target module must be mapped to a target user schema which is part of any targeted database like Oracle, Teradata etc.

Therefore, before creating a Warehouse Builder target module, we need to create a target schema that will physically store our target objects on deployment. So we need to set target schema by assigning proper targeted database location. To set up our target warehouse module, we need to perform the following steps:

1. Create a Target User and Target Location
2. Create a Target Module