Oracle BI 12c: Creating Repositories

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