I.                   Objectives

This course is focused on creating the OBI 12c repository (modeling source data so that the tool can generate reports).

Upon completion of the course, the student will have sufficient knowledge to:

Explain the differences between the different relational models.

Develop a repository from relational sources (star, snowflake, and normalized models), including partitioning, summary tables, hierarchies (level and value, parent-child), derived measures, time series measures, and variables for dynamic reports.

Correctly define the keys for table joins and hierarchy levels.

Publish models and verify their correct operation.

Set basic security for users and roles, activate caching to improve performance, and multi-developer development with MUDE or model merging.

II. Requirements

SQL knowledge.

Optional: Knowledge of Data Warehouse and star model.

Advanced use of databases.

III. Duration

From 20 to 28 hours (1 or 2 modules).

IV. Methodology

The course is developed through theoretical presentation accompanied by practical demonstrations and explanations of the results obtained.

The student carries out the modeling of a dimensional data source (star model) with normalized tables (snowflake) and different complexities to test the characteristics of the product.

Resolution of doubts about the concepts presented.

V.                Content

1.                  Module I

Duration: 20 hours

Introduction to Oracle BI EE. Architecture. Administration Tool.

Data models. Normalized. Star.

Snowflake.

Project creation.

Physical layer creation. Physical layer objects. Database. Functionalities. Connection pool. Data source access parameters. Importing data sources. Loading tables and columns. Definition of keys and table joins. Physical model. SELECT type data sources. Deployment and redeployment. Use of object aliases.

Use of display folders and shortcuts.

Creating a logical layer (correspondences and business model).Creating a dimension and fact table.Renaming dimension attributes.Using logical sources and table correspondences.Logical joins.Determining aggregation function for measures.

Ordering data based on another column.

Creating a presentation layer.Preparing objects for the user.Renaming folders and columns.Using aliases.

Nested folders.

Model validation.

Requirements for a correct model.

Publishing the model.Using datamodel.cmd.Enabling query log. Reviewing log in Fusion Middleware, Presentation Service and server file.

Repository connection modes (offline and online).

Advanced modeling:Snowflake.Avoiding circular relationships.

Short paths in snowflakes.

Calculated measures:Creating derived measures.

Common functions (Rank, Rsum…).

Hierarchies:Level hierarchies:Defining level hierarchies.Defining and behaving primary logical level keys, display keys, level keys.Modeling time hierarchy.Modeling dimension hierarchies.Bifurcated hierarchies.Preferred paths for further analysis.Defining and using numerical values ​​for level elements.Parent-child hierarchies:Creating help table.Modeling hierarchy.Level measures:Creating level measures.Shared measures.

Presentation hierarchies.Automatic creation of presentation hierarchies.

Manual creation.Different data presentation paths.

Time series:Defining chronological keys.

Time series measures:

Aug, ToDate, and PeriodRolling.

Summary (aggregate) tables.Defining summary tables.Physical and logical modeling.

Creating logical sources.

Partitioning. Partitioning types.

Value-based partitioning modeling.

Variables. Variable types (repository and session). Using variables in reports to generate dynamic filters.

Using row-wise session variables (bulk loading of variables).

Usage tracking:

Description of system tables. Enabling usage tracking. Creating a model for usage analysis.

Testing the model.

Setting implicit fact column.

Determining implicit fact for models with multiple fact tables and reports that only use dimensions.

2. Module II

Duration: 8 hours

Security: OBI standard security layers. Creating Weblogic users and groups. Linking to application roles. Setting permissions on objects.

Row-level security.

Cache: Activate the cache system. Table purge configuration. Manual purge of cache entries. Check cache operation. Create agent to populate cache.

Cache management.

Multi-developer environment

I.                   Objectives

This course is focused on the installation and use of the main functionalities of Oracle Data Integrator 12c.

Upon completion of the course, the student will have sufficient knowledge to:

Install the development environment on a computer and configure an external agent.

Create mappings with different data integration methods, including user functions.

Use the most common knowledge modules in ETL processes.

Create procedures, packages, and load plans to carry out a complete data integration.

II. Requirements

SQL knowledge.

Optional:

Knowledge of other ETL tools.

III. Duration

25 hours.

IV.Methodology

The course is developed through theoretical presentation accompanied by practical demonstrations and explanations of the results obtained.

The student carries out the creation of objects (procedures, correspondences, models, topography, etc.) with different data sources, mainly relational (Oracle and MySQL).

Resolution of doubts about the concepts presented.

V. Content

Introduction to Oracle Data Integrator 12c, integration and administration. EL-T approach. Declarative design.

Knowledge modules.

Product installation. Prepare Java environment.

Installation of Middleware and ODI on Linux.

Create Master and Work repository. Using the RCU tool.

Using the ODI user interface and connection to the database.

Topology concepts. Description and creation of physical and logical architectures. Creation of contexts. Data servers and physical schemes. Access to source data and target schemas.Server connection parameters.Creation of logical schemas.Association of logical and physical objects through contexts.

Description and creation of physical and logical agents.Installation of Standalone Collocated Agent.

Topology configuration.

Projects.Project creation.Description of objects: Folders, packages, correspondences, procedures, variables, sequences, user functions, knowledge modules, bookmarks.Organization and scope of projects.Export and import of objects.

Introduction to knowledge modules.

Creation of models.Model folders.Datastore concept.Reverse engineering from relational models.Working with XML sources.Concepts of restrictions and indexes.Primary and foreign keys (references).NOT NULL or mandatory columns.Conditions (check type restrictions).Establish automatic distribution.Establish restriction checkpoint (flow, static).

Verify compliance with restrictions.

Mapping. Basic mappings. Use of different technologies (MySQL > Oracle). Use of Flow Control.

Use of Control Append.

Use of Incremental Update. Use of mapping components: Join, Aggregate, Filter, Lookup…

Specification of the staging area location.

Use of Operator Navigator. Check the execution log. See the sessions, steps and tasks carried out.

Review the code generated by ODI.

Sequences. Types of sequences.

Use of native sequences.

User functions. Scope of use. Implementation. Multiple technologies.

Creating a user function.

Procedures. Procedure description. Creating a procedure. Tasks and properties. Optional commands.

Modifying behavior using options.

Packages.Description.Creating a package.Package diagram panel and tools.Using variables in packages.Updating the value.Verifying and making decisions.

Controlling execution.

Version management.Creating versions in objects.

Comparing changes.

Scenarios.Description.Creating a scenario.

Managing versions in scenarios.

Load plans.Description.

Creating load plans with scenarios.

Change management. Change Data Capture.Description of the CDC system.Implementing and reviewing objects created in the database. Enabling the system.

Creating correspondence with CDC function.

1. Introduction to PL/SQL
2. Declaring Variables
3. Writing Executable Statements
4. Using SQL within PL/SQL
5. Control Structures
   5.1 Conditionals
   5.2 Loops
6. Using Composite Data Types
7. Using Explicit Cursors
8. Exception Handling
9. Procedures
10. Functions
11. Packages