Oracle BI 12c: Build Repositories Ed 1

Course Introduction

• Lesson Agenda
• Instructor and Class Participants
• Training Site Information
• Course Audience
• Course Prerequisites
• Course Goal
• Course Objectives
• Course Methodology
• Course Materials
• Course Agenda
• Summary

Repository Basics

• Objectives
• Oracle BI Server Architecture
• Oracle BI Presentation Services
• Oracle BI Server
• Data Sources
• Oracle BI Repository
• Oracle BI Client Installer
• Oracle BI Administration Tool
• Physical Layer
• Objects in the Physical Layer
• Business Model and Mapping Layer
• Objects in the Business Model and Mapping Layer
• Business Model Mappings
• Measures
• Presentation Layer
• Presentation Layer Mappings
• Presentation Layer Defines the User Interface
• Repository Directory
• Repository Modes
• Uploading a Repository
• Reloading Server Metadata
• Sample Analysis Processing
• Uploading a Repository to BI Cloud Services
• Summary
• Practice 2-1 Overview: Exploring an Oracle BI Repository
• Quiz

Building the Physical Layer of a Repository

• Objectives
• Physical Layer
• Physical Layer Objects
• Database Object
• Database Object: General Properties
• Database Object: Features
• Connection Pool
• Schema Folder
• Physical Table
• Physical Table Properties
• Physical Column
• Key Column
• Physical Table: Alias
• Joins
• Sample Apps Scenario
• Implementation Steps
• Create a New Repository File
• Provide Repository Information
• Select the Data Source
• Select Metadata Types for Import
• Select Metadata Objects for Import
• Verify and Edit Connection Pool Properties
• Verify Objects for Import
• Verify the Import in the Physical Layer
• Verify Connectivity
• Create Alias Tables
• Define Keys and Joins
• Define Keys by Using the Physical Table Properties Dialog Box
• Open the Physical Diagram
• Define Foreign Key Joins
• Use the Joins Manager
• Print the Physical Diagram
• Design Tips for the Physical Layer
• Summary
• Practice 3-1 Overview: Sample Apps Business Scenario
• Practice 3-2 Overview: Creating a Repository and Importing a Data Source
• Practice 3-3 Overview: Creating Alias Tables
• Practice 3-4 Overview: Defining Keys and Joins
• Quiz

Building the Business Model and Mapping Layer of a Repository

• Objectives
• Business Model and Mapping (BMM) Layer
• Objects in the Business Model and Mapping Layer
• Business Model Mappings
• Objects in the Business Model and Mapping Layer
• Business Model
• Logical Tables
• Logical Table Sources
• Logical Table Source: Column Mappings
• Logical Columns
• Logical Primary Keys
• Logical Joins
• Measures
• BISAMPLE Example
• Implementation Steps
• 1. Create the Logical Business Model
• 2. Create the Logical Tables and Columns
• 3. Define the Logical Joins
• 4. Modify the Logical Tables and Columns
• 5. Define the Measures
• Printing the Business Model Diagram
• Design Tips
• Summary
• Practice 4-1 Overview: Creating the Business Model
• Practice 4-2 Overview: Creating Simple Measures
• Quiz

Building the Presentation Layer of a Repository

• Objectives
• Presentation Layer
• Subject Areas
• Presentation Tables
• Presentation Columns
• Presentation Layer Mappings
• Defining the User Interface in the Presentation Layer
• Nesting Presentation Tables
• Aliases
• Controlling Presentation Layer Object Visibility
• BISAMPLE Example
• Implementation Steps
• 1. Create a New Subject Area
• 2. Edit Presentation Layer Names
• 3. Reorder Tables
• 4. Delete Columns
• 5. Rename Columns
• 6. Reorder Columns
• Considerations
• Design Tips
• Summary
• Practice 5-1 Overview: Creating the Presentation Layer
• Quiz

Testing and Validating a Repository

• Objectives
• Validating a Repository
• BISAMPLE Example
• Consistency Check
• Checking Consistency
• Consistency Check Manager
• Using the validaterpd Utility to Check Repository Consistency
• Marking a Business Model Available for Queries
• Confirming a Consistent Repository
• Uploading a Repository
• Query Logging
• Setting a Logging Level
• Logging Levels
• Validating by Using the Analysis Editor
• Inspecting the Query Log
• Using Session Monitor to Inspect the Query Log
• Using nqlogviewer to Inspect the Query Log
• Opening obis1-query.log to Inspect the Query Log
• Oracle BI SELECT Statement: Syntax
• Oracle BI SELECT Statement Compared with Standard SQL
• Summary
• Practice 6-1 Overview: Testing the Repository
• Quiz

Managing Logical Table Sources

• Objectives
• Table Structures
• Business Challenge
• Business Solution
• BISAMPLE Example: Adding Multiple Sources to a Logical Table Source (LTS)
• Importing Additional Dimension Tables
• 1. Import Additional Tables
• 2. Create Aliases
• 3. Define Keys and Joins
• 4. Identify Physical Columns for Mappings
• Adding a New Mapping to an Existing Logical Table Source
• 1. Create a New Logical Column
• 2. Map a New Physical Source
• 3. Create the Column Mapping
• Adding a New Mapping to an Existing Logical Table Source: Result
• Adding a New Logical Table Source
• 1. Identify a Table that Stores Additional Information
• 2. Add a New Logical Table Source
• 3. Rename the New Logical Table Source
• 4. Verify Column Mappings
• 5. Rename the Logical Columns
• 6. Add the Columns to the Presentation layer
• 7. Verify Your Work
• Modeling for OLTP Systems
• Summary
• Practice 7-1 Overview: Importing Additional Dimension Tables
• Practice 7-2 Overview: Adding a New Mapping to an Existing
• Logical Table Source
• Practice 7-3 Overview: Adding a New Logical Table Source
• Quiz

Adding Calculations to a Fact

• Objectives
• Business Problem
• Business Solution
• Creating Calculation Measures by Using Existing Logical Columns
• 1. Create a New Logical Column
• 2. Specify Logical Columns as the Source
• 3. Build a Formula
• Creating Calculation Measures by Using Physical Columns
• 1. Create a New Logical Column
• 2. Map the New Column
• 3. Build the Formula
• 4. Specify an Aggregation Rule
• Steps for Using the Calculation Wizard
• 1. Open the Calculation Wizard
• 2. Choose the Columns for Comparison
• 3. Select the Calculations
• 4. Confirm the Calculation Measures
• 5. New Calculation Measures Are Added
• Adding New Measures to the Presentation Layer
• Examining a Query by Using Logical Columns
• Example: Using Logical Columns
• Examining a Query by Using Physical Columns
• Example: Using Physical Columns
• Examining a Query by Using the Calculation Wizard
• Using Functions to Create Expressions
• Summary
• Practice 8-1 Overview: Creating Calculation Measures
• Practice 8-2 Overview: Creating Calculation Measures by
• Using the Calculation Wizard
• Practice 8-3 Overview: Creating a RANK Measure
• Practice 8-4 Overview: Testing and Validating the Repository Measures
• Quiz

Working with Logical Dimensions

• Objectives
• Logical Dimensions
• Logical Dimensions: Types
• Level-Based Measures
• Share Measures
• Logical Dimension: Example
• BISAMPLE Example
• Creating a Level-Based Logical Dimension
• 1. Create a Logical Dimension Object
• 2. Add a Parent-Level Object
• 3. Add Child-Level Objects
• 4. Specify Level Columns
• 5. Create Level Keys
• 6. Set the Preferred Drill Path
• 7. Create Level-Based Measures
• 8. Create Additional Level-Based Measures
• 9. Create Share Measures
• 10. Add Measures to the Presentation Layer
• 11. Create Presentation Hierarchies
• 12. Test Measures and Hierarchies
• Summary
• Practice 9-1 Overview: Creating Logical Dimension Hierarchies
• Practice 9-2 Overview: Creating Level-Based Measures
• Practice 9-3 Overview: Creating Share Measures
• Practice 9-4 Overview: Creating Dimension-Specific Aggregation Rules
• Practice 9-5 Overview: Creating Presentation Hierarchies
• Quiz

Working with Logical Dimension Hierarchies

• Objectives
• More Logical Dimensions Types
• Parent-Child Logical Dimensions
• Parent-Child Hierarchy: Example
• Parent-Child Logical Table
• Parent-Child Relationship Table
• Creating a Parent-Child Logical Dimension
• 1. Create a Logical Dimension Object
• 2. Set the Member Key
• 3. Set the Parent Column
• 4. Open the Parent-Child Relationship Table Settings Dialog Box
• 5. Enter Parent-Child Relationship Table Script Information
• 6. Enter Parent-Child Relationship Table Details
• 7. Preview Scripts
• 8. Confirm Parent-Child Relationship Table Settings
• 9. Confirm Changes to the BMM Layer
• 10. Confirm Changes to the Physical Layer
• 11. Modify the Physical Layer
• 12. Modify the BMM Layer
• 13. Create the Presentation Hierarchy
• 14. Verify Your Work
• Ragged and Skipped-Level Dimensions
• Creating Ragged and Skipped-Level Dimensions
• Summary
• Practice 10-1 Overview: Creating Parent-Child Hierarchies
• Practice 10-2 Overview: Creating Logical Dimensions with
• Ragged and Skipped-Level Hierarchies
• Quiz

Managing Usage Tracking

• Objectives
• Business Challenges
• Business Solution: Oracle BI Usage Tracking
• BISAMPLE Example
• Steps to Enable Usage Tracking
• 1. Create the Usage-Tracking Tables
• 2. Import the Usage-Tracking Table
• 3. Build a Usage-Tracking Business Model
• 4. Stop Oracle BI Components
• 5. Set Direct Insertion Parameters in NQSConfig.ini
• 6. Start Oracle BI Components
• 7. Test the Results
• Analyzing Usage-Tracking Data
• Summary
• Practice 11-1 Overview: Setting Up Usage Tracking
• Quiz

Using Model Checker and Aggregates

• Objectives
• Business Challenge
• Business Solution: Aggregate Tables
• Model Check Manager
• Model Check Manager Dialog Box
• Oracle BI Aggregate Navigation
• Aggregated Facts
• Modeling Aggregates
• BISAMPLE Example
• Steps to Implement Aggregate Navigation
• 1. Import Tables and Create Aliases
• 2. Create Joins
• 3. Create the Fact Logical Table Source and Mappings
• 4. Specify the Fact Aggregation Content
• 5. Specify Content for the Fact Detail Source
• 6. Create the Dimension Logical Table Source and Mappings
• 7. Specify the Dimension Aggregation Content
• 8. Specify Content for the Dimension Detail Source
• 9. Test Results for Levels Stored in Aggregates
• 10. Test Results for Data That Is Above or Below Levels
• Aggregate Persistence Wizard
• Model Checker
• Aggregate Persistence Wizard: Steps
• Aggregate Persistence Wizard Steps
• 1. Open the Aggregate Persistence Wizard
• 2. Specify a File Name and Location
• 3. Select the Business Model and Measures
• 4. Select Dimensions and Levels
• 5. Select the Connection Pool, Container, and Name
• 6. Review the Aggregate Definition
• 7. View the Complete Aggregate Script
• 8. Verify that the Script was Created
• 9. Run the Aggregate Persistence Script
• 10. Verify Aggregates in the Physical Layer
• 11. Verify That Aggregates Are Populated
• 12. Verify Aggregates in the BMM Layer
• 13. Test Results for Levels Stored in Aggregates
• Setting the Number of Elements
• Enable Aggregates
• Generate Fragmented Aggregates in Aggregate Persistence
• Troubleshooting Aggregate Navigation
• Considerations
• Summary
• Practice 12-1 Overview: Using Aggregate Tables
• Practice 12-2 Overview: Using the Aggregate Persistence Wizard
• Quiz

Using Partitions and Fragments

• Objectives
• Business Challenge
• Business Solution: Oracle BI Server
• Partition
• Partitioning by Fact
• Partitioning by Value
• Partitioning by Level
• Complex Partitioning
• BISAMPLE Example: Value-Based (Order Date)
• Implementation Steps
• Specify Fragmentation Content
• Summary
• Practice 13-1 Overview: Modeling a Value-Based Partition
• Quiz

Using Repository Variables

• Objectives
• Variables
• Variable Manager
• Types of Variables
• Repository Variables
• Static Repository Variables
• Dynamic Repository Variables
• Session Variables
• System Session Variables
• Nonsystem Session Variables
• Initialization Blocks
• Initialization Block: Example
• Initialization Block Example: Edit Data Source
• Initialization Block Example: Edit Data Target
• BISAMPLE Example
• Implementation Steps
• 1. Create a Dedicated Connection Pool
• 2. Open the Variable Manager
• 3. Create an Initialization Block
• 4. Edit the Data Source
• 5. Edit the Data Target
• 6. Test the Initialization Block Query
• 7. Use the Variable to Determine Content
• 8. Verify Your Work
• Summary
• Practice 14-1 Overview: Creating Dynamic Repository Variables
• Practice 14-2 Overview: Using Dynamic Repository Variables as Filters
• Quiz

Modeling Time Series Data

• Objectives
• Time Comparisons
• Business Challenge: Time Comparisons
• Oracle BI Solution: Model Time Comparisons
• Time Dimensions
• Time Series Functions
• Time Series Grains
• BISAMPLE Example
• Steps to Model Time Series Data
• 1. Identify a Time Dimension and Chronological Keys
• 2. Create a Measure by Using the AGO Function
• 3. Use a Column with the AGO Function to Create Additional Measures
• 4. Create a Measure by Using the TODATE Function
• 5. Create a Measure by Using the PERIODROLLING Function
• 6. Add New Measures to the Presentation Layer
• 7. Test the Results
• Summary
• Practice 15-1 Overview: Creating Time Series Calculation Measures
• Quiz

Modeling Many-to-Many Relationships

• Objective
• Business Challenge and Solution
• Bridge Table
• BISAMPLE Example
• Steps to Model a Bridge Table
• 1. Import Tables
• 2. Create the Physical Model
• 3. Create the Logical Model
• 4. Map the Bridge Table for Dimensions
• 5. Map the Bridge Table for Fact
• 6. Create a Calculation Measure
• 7. Map Objects to the Presentation Layer
• 8. Verify the Results
• Summary
• Practice 16-1 Overview: Modeling a Bridge Table
• Quiz

Setting an Implicit Fact Column

• Objectives
• Business Challenge: Dimension-Only Queries
• Business Solution: Implicit Fact Column
• BISAMPLE Example
• Steps to Configure an Implicit Fact Column
• 1. Set an Implicit Fact Column
• 2. Verify the Results
• 3. Clear the Implicit Fact Column
• Summary
• Practice 17-1 Overview: Setting an Implicit Fact Column
• Quiz

Importing Metadata from Multidimensional Data Sources

• Objective
• Overview
• Multidimensional Versus Relational Data Sources
• Overview: Importing Multidimensional Data Sources
• BISAMPLE Example
• Creating a Multidimensional Business Model
• 1. Import Metadata
• 2. Verify the Import
• 3. Choose Options to Control the Model
• 4. View Members and Update the Member Count
• 5. Determine the Member Display Value
• 6. Create the Business Model
• 7. Create the Presentation Layer
• 8. Verify the Results
• Horizontal Federation
• Vertical Federation: Viewing in Reports
• Vertical Federation in BMM Layer
• Summary
• Quiz

Security

• Objectives
• Business Challenge: Security Strategy
• Business Solution: Oracle BI Security
• Managing Oracle BI Security
• Oracle BI Default Security Model
• Default Security Realm
• Default Authentication Providers
• Default Users
• Default Groups
• Default Application Roles
• Default Application Policies
• Default Presentation Services Privileges
• Default Application Role Hierarchy: Example
• BISAMPLE Example
• Create Groups
• Create Group Hierarchies
• Create Users
• Assign Users to Groups
• Create Application Roles
• Map Application Roles
• Application Role Hierarchies
• Verify Security Settings in Oracle BI
• Verify Security Settings in the Repository
• Set Up Object Permissions
• Set Up Object Permissions and Verify
• Permission Inheritance
• Permission Inheritance: Example
• Set Row-Level Security (Data Filters)
• Set Query Limits
• Set Timing Restrictions
• Summary
• Practice 19-1 Overview: Exploring Default Security Settings
• Practice 19-2 Overview: Creating Users and Groups
• Practice 19-3 Overview: Creating Application Roles
• Practice 19-4 Overview: Setting Up Object Permissions
• Practice 19-5 Overview: Setting Row-Level Security (Data Filters)
• Practice 19-6 Overview: Setting Query Limits and Timing Restrictions
• Quiz

Cache Management

• Objective
• Business Challenge
• Business Solution: Oracle BI Server Query Cache
• Advantages of Caching
• Costs of Caching
• Query Cache: Architecture
• Monitoring and Managing the Cache
• Cache Management Techniques
• Using Fusion Middleware Control to Configure Caching
• Using NQSConfig.ini to Manually Edit Cache Parameters
• Setting Caching and Cache Persistence for Tables
• Using the Cache Manager
• Inspecting SQL for Cache Entries
• Modifying the Cache Manager Column Display
• Inspecting Cache Reports
• Purging Cache Entries Manually Using the Cache Manager
• Purging Cache Entries Automatically
• Using Event Polling Tables
• Seeding the Cache
• Cache Hit Conditions
• Summary
• Practice 20-1 Overview: Enabling Query Caching
• Practice 20-2 Overview: Modifying Cache Parameters
• Practice 20-3 Overview: Seeding the Cache
• Quiz

Setting Up and Using the Multiuser Development Environment

• Objectives
• Business Challenge
• Business Solution: Oracle BI Multiuser Development Environment (MUDE)
• Oracle BI Repository Development Process
• SCM Three-Way Merge Process
• Oracle BI Repository Three-Way Merge Process
• Multiuser Development Projects
• Overview: Oracle BI Multiuser Development
• BISAMPLE Example
• Steps to Set Up an Oracle BI MUDE
• 1. Create Projects
• 2. Edit Projects
• 3. Set Up a Shared Network Directory
• 4. Copy the Master Repository to the Shared Directory
• 5. Set Up a Pointer to the Multiuser Directory
• Making Changes in an Oracle BI MUDE
• 1. Check Out Projects
• 2. Administration Tool Tasks During Checkout
• 3. Modify Metadata
• 4. Multiuser Options During Development
• 5. Publish Local Changes to Master Repository
• 6. Define Merge Strategy
• 7. Track Project History
• History Menu Options
• Deleting History Items
• Source Control Management System
• MDS XML Repository Storage
• Summary
• Practice 21-1 Overview: Setting Up a Multiuser Development Environment
• Practice 21-2 Overview: Using a Multiuser Development Environment
• Quiz

Performing a Patch Merge

• Objectives
• Patch Merge
• Creating a Patch
• Applying a Patch
• Steps to Perform a Patch Merge
• 1. Compare Current and Original Repositories
• 2. Equalize Objects
• 3. Create a Patch File
• 4. Apply the Patch
• 5. Make Merge Decisions
• 6. Verify Your Work
• Summary
• Practice 22-1 Overview: Performing a Patch Merge
• Quiz

Managing Metadata and Working with Service Instances

• Objectives
• Singleton Data Dictionary
• BI Service Instance
• SDD and Service Instance Directory Structure
• BI Application Archive (BAR) Files
• Using BAR Files to Move Between Environments
• Exporting a Service Instance into a BAR file
• Example: Exporting a Service Instance into a BAR file
• Importing a BAR File into a Service Instance
• Example: Importing a BAR file into a Service Instance
• Managing Service Instances
• Quiz
• Summary
• A Using Administration Tool Utilities
• Objectives A-2
• Wizards and Utilities A-3
• Accessing Wizards and Utilities A-4
• Managing Sessions A-5
• Querying Repository Metadata A-6
• Replacing Columns or Tables A-7
• Documenting a Repository A-8
• Generating a Metadata Dictionary A-9
• Creating an Event Table A-10
• Updating the Physical Layer A-11
• Removing Unused Physical Objects A-12
• Oracle BI Server XML API A-13
• Using the Oracle BI Server XML API A-14
• Summary A-15
• Quiz A-17