Contents

Title and Copyright Information

Preface

• Audience
• Documentation Accessibility
• Related Documentation and Other Resources
• Conventions

New Features for Oracle BI Metadata Repository Builders

• New Features for Oracle BI EE 11g Release 1 (11.1.1.6)
• New Features for Oracle BI EE 11g Release 1 (11.1.1.5)
• New Features for Oracle BI EE 11g Release 1 (11.1.1.3)

1 Introduction to Building Your Metadata Repository

• About Oracle BI Server and Oracle BI Repository Architecture
  • About Oracle BI Server Architecture
  • About Layers in the Oracle BI Repository
• Planning Your Business Model
  • Analyzing Your Business Model Requirements
  • Identifying the Content of the Business Model
    • Identifying Logical Fact Tables
    • Identifying Logical Dimension Tables
    • Identifying Dimensions
    • Identifying Lookup Tables
• Identifying the Data Source Content for the Physical Layer
  • About Types of Physical Schemas in Relational Data Sources
  • About Cubes in Multidimensional Data Sources
  • Identifying the Data Source Table Structure
• Guidelines for Designing a Repository
  • General Tips for Working on the Repository
  • Design Tips for the Physical Layer
  • Design Tips for the Business Model and Mapping Layer
    • Modeling Outer Joins
  • Design Tips for the Presentation Layer
• Topics of Interest in Other Guides
• System Requirements and Certification

2 Before You Begin

• About the Oracle BI Administration Tool
  • Opening the Administration Tool
  • About the Administration Tool Main Window
  • Setting Administration Tool Options
  • About Administration Tool Menus
    • File Menu
    • Edit Menu
    • View Menu
    • Manage Menu
    • Tools Menu
    • Diagram Menu
    • Window Menu
    • Help Menu
  • Using the Physical and Business Model Diagrams
  • Editing, Deleting, and Reordering Objects in the Repository
  • About Naming Requirements for Repository Objects
  • Using the Browse Dialog to Browse for Objects
  • Changing Icons for Repository Objects
  • Sorting Objects in the Administration Tool
  • About Features and Options for Oracle Marketing Segmentation
• About the Oracle BI Server Command-Line Utilities
  • Running bi-init to Launch a Shell Window Initialized to Your Oracle Instance
• About Options in Fusion Middleware Control and NQSConfig.INI
• About the SampleApp.rpd Demonstration Repository
• Using Online and Offline Repository Modes
  • Editing Repositories in Offline Mode
    • Opening Repositories in Offline Mode
    • Publishing Offline Changes
  • Editing Repositories in Online Mode
    • Opening Repositories in Online Mode
    • Publishing Online Changes
    • Guidelines for Using Online Mode
  • Checking Out Objects
  • Checking In Changes
  • About Read-Only Mode
• Checking the Consistency of a Repository or a Business Model
  • About the Consistency Check Manager
  • Checking the Consistency of Repository Objects
  • Using the validaterpd Utility to Check Repository Consistency
  • Common Consistency Check Messages

3 Setting Up and Using the Multiuser Development Environment

• About the Multiuser Development Environment
  • About the Multiuser Development Process
• Setting Up Projects
  • About Projects
    • About the Project Dialog
  • Creating Projects
  • About Converting Older Projects During Repository Upgrade
• Setting Up the Multiuser Development Directory
  • Identifying the Multiuser Development Directory
  • Copying the Master Repository to the Multiuser Development Directory
  • Setting Up a Pointer to the Multiuser Development Directory
• Making Changes in a Multiuser Development Environment
  • Checking Out Repository Projects
    • About Repository Project Checkout
    • Checking Out Projects
    • Using the extractprojects Utility to Extract Projects
  • About Changing and Testing Metadata
  • Refreshing Your Local Project Extract
  • About Multiuser Development Menu Options
• Publishing Changes to Multiuser Development Repository Projects
  • About the Multiuser Development Merge Process
    • How are Multiuser Merges Different from Standard Repository Merges?
  • Publishing to the Network
  • Enforcing Consistent Repositories When Publishing Changes
• Branching in Multiuser Development
  • About Branching
  • Using the Multi-Team, Multi-Release Model in Oracle Business Intelligence
  • Synchronizing RPD Branches
• Viewing and Deleting History for Multiuser Development
  • Viewing Multiuser Development History
  • Deleting Multiuser Development History
• Setting Multiuser Development Options

4 Using a Source Control Management System for Repository Development

• About Using a Source Control Management System with the Administration Tool
  • About MDS XML
• Setting Up Your System for Repository Development Under Source Control Management
  • Creating an SCM Configuration File
  • Creating an MDS XML Repository and Checking In Files to the SCM System
    • Saving an Existing Repository File in MDS XML Format
    • Creating a New Repository in MDS XML Format
    • Linking to Source Control Files to Convert Your Repository (Small Repositories Only)
• Using Source Control Management in Day to Day Repository Development
  • Updating, Saving, and Checking In Changes for Repositories Under Source Control
  • Handling Errors
  • Testing Repositories Under Source Control
  • Viewing the Source Control Log
• Using Source Control Management with MUD
  • Putting the MUD Master Repository and MUD Log File Under Source Control
  • Checking In New Versions of the MUD Master and MUD Log File to Source Control
    • Manually Checking In the Updated MUD Master Repository and Log File
    • Using a Script to Check In the Updated MUD Master Repository and Log File

5 Importing Metadata and Working with Data Sources

• About Importing Metadata and Working with Data Sources
• Creating a New Oracle BI Repository
• Performing Data Source Preconfiguration Tasks
  • Setting Up ODBC Data Source Names (DSNs)
  • Setting Up Oracle Database Data Sources
    • Additional Oracle Database Configuration for Client Installations
  • Setting Up Oracle OLAP Data Sources
    • Additional Oracle OLAP Configuration for Client Installations
  • Setting Up Oracle TimesTen In-Memory Database Data Sources
    • Configuring TimesTen Data Sources
    • Improving Use of System Memory Resources with TimesTen Data Sources
    • Ensuring that OPMN Can Access the TimesTen DLL on Windows
  • Setting Up Essbase Data Sources
    • Additional Essbase Configuration Steps for Client Installations
  • Setting Up Hyperion Financial Management Data Sources
    • Additional Hyperion Configuration for Client Installations
  • Setting Up SAP/BW Data Sources
  • Setting Up Oracle RPAS Data Sources
  • Setting Up Teradata Data Sources
    • Avoiding Spool Space Errors for Queries Against Teradata Data Sources
• Importing Metadata from Relational Data Sources
  • About the Map to Logical Model and Publish to Warehouse Screens
• Importing Metadata from Multidimensional Data Sources
  • About Importing Metadata from Oracle RPAS Data Sources
• Importing Metadata from XML Data Sources
  • About Using XML as a Data Source
  • Importing Metadata from XML Data Sources Using the XML Gateway
    • Examples of XML Documents Generated by the Oracle BI Server XML Gateway
    • Accessing HTML Tables
  • Importing Metadata from XML Data Sources Using XML ODBC
    • Example of an XML ODBC Data Source
  • Examples of XML Documents
• Using a Standby Database with Oracle Business Intelligence
  • About Using a Standby Database with Oracle Business Intelligence
  • Creating the Database Object for the Standby Database Configuration
  • Creating Connection Pools for the Standby Database Configuration
  • Updating Write-Back Scripts in a Standby Database Configuration
  • Setting Up Usage Tracking in a Standby Database Configuration
  • Setting Up Event Polling in a Standby Database Configuration
  • Setting Up Oracle BI Scheduler in a Standby Database Configuration

6 Working with ADF Data Sources

• What Are ADF Business Components?
  • About Operational Reporting with ADF Business Components
• What Happens During Import?
• About Specifying a SQL Bypass Database
• Setting Up ADF Data Sources
  • Creating a WebLogic Domain
  • Deploying OBIEEBroker as a Shared Library in Oracle WebLogic Server
  • Deploying the Application EAR File to Oracle WebLogic Server from JDeveloper
  • Setting Up a JDBC Data Source in the WebLogic Server
  • Setting the Logging Level for the Deployed Application in Oracle WebLogic Server
• Importing Metadata from ADF Data Sources
  • Performing an Initial Import from ADF Data Sources
  • Using Incremental Import to Propagate Flex Object Changes
  • Automatically Mapping Flex Object Changes to the Logical Model
    • Customizing the Mapping Behavior
    • Manually Mapping Flex Object Changes to the Logical Model
  • Automatically Mapping Flex Object Changes Using the biserverextender Utility
• Using the BI Extender to Propagate Flex Object Changes
  • About Propagating Changes to Flex Objects to the Data Warehouse
    • Use Cases for Propagating Flexfield Changes
  • Performing Preconfiguration Tasks for the BI Extender
    • Configuring the biextension.properties File
    • Obtaining the Informatica mapfwk.jar File
    • Configuring the JavaHost loaders.xml File
    • Configuring the opmn.xml File
    • Configuring the ContainerMapping.xml File
    • Updating NQSConfig.INI
    • Optionally Changing the Location of the BI Extender Files
  • Running the BI Extender in a Secured Environment
  • Publishing Changes to the Data Warehouse and Propagating Changes to the Repository
    • Running the BI Extender to Update Informatica, DAC, and the RPD
    • Using DAC to Propagate Flex Object Changes to the Data Warehouse Schema
  • Setting Up XSL Transform Files to Customize XML Output to the Oracle BI Extender
    • Sample XML Output
• Setting Up and Using ApplCore Grants for ADF Data Security
  • Setting Up Oracle Business Intelligence to Use ApplSession
    • Setting Up Database Objects and Connection Pools for ApplSession Integration
    • Setting Up Initialization Blocks for ApplSession Integration
    • About the Client Login Process in an ApplSession Integrated Environment
  • Setting Up Authentication for ApplSession Integration
    • Setting Up Security Constraints and Security-Related Servlet Filters in web.xml
    • Configuring Role-to-Principal Mapping in weblogic-application.xml
    • Configuring the Custom Identity Assertion Provider in Oracle WebLogic Server
    • Configuring One-Way SSL in Oracle WebLogic Server
    • Configuring Two-Way SSL in Oracle WebLogic Server
• Enabling the Ability to Pass Custom Parameters to the ADF Application
• Propagating Labels and Tooltips from ADF Data Sources
  • What are Labels and Tooltips?
  • About the Session Variable Naming Scheme for UI Hints
  • About Determining the Physical Column for a Presentation Column
  • Initializing Session Variables Automatically for Propagating UI Hints
  • Example of Using UI Hints From an Oracle ADF Data Source When Creating Analyses
  • Using XML Code in Initialization Blocks to Query UI Hints

7 Setting Up Database Objects and Connection Pools

• Setting Up Database Objects
  • About Database Types in the Physical Layer
  • Creating a Database Object Manually in the Physical Layer
    • When to Allow Direct Database Requests by Default
  • Specifying SQL Features Supported by a Data Source
  • Viewing Database Properties for Oracle ADF Data Sources
• About Connection Pools
  • About Connection Pools for Initialization Blocks
• Creating or Changing Connection Pools
  • Setting Connection Pool Properties in the General Tab
    • Common Connection Pool Properties in the General Tab
    • Multidimensional Connection Pool Properties in the General Tab
  • Setting Connection Pool Properties in the Connection Scripts Tab
  • Setting Connection Pool Properties in the XML Tab
  • Setting Connection Pool Properties in the Write Back Tab
  • Setting Connection Pool Properties in the Miscellaneous Tab
• Setting Up Persist Connection Pools
  • About Setting the Buffer Size and Transaction Boundary

8 Working with Physical Tables, Cubes, and Joins

• About Working with the Physical Layer
• Working with the Physical Diagram
• Creating Physical Layer Folders
  • Creating Physical Layer Catalogs and Schemas
    • Creating Catalogs
    • Creating Schemas
  • Using a Variable to Specify the Name of a Catalog or Schema
  • Setting Up Display Folders in the Physical Layer
• Working with Physical Tables
  • About Tables in the Physical Layer
  • About Physical Alias Tables
  • Creating and Managing Physical Tables and Physical Cube Tables
    • Creating or Editing Physical Tables
    • Creating Alias Tables
    • Setting Physical Table Properties for XML Data Sources
  • Creating and Managing Columns and Keys for Relational and Cube Tables
    • Creating and Editing a Column in a Physical Table
    • Specifying a Primary Key for a Physical Table
    • Deleting Physical Columns for All Data Sources
  • Viewing Data in Physical Tables or Columns
• Working with Multidimensional Sources in the Physical Layer
  • About Physical Cube Tables
  • About Measures in Multidimensional Data Sources
    • About Externally Aggregated Measures
  • Working with Physical Dimensions and Physical Hierarchies
    • Working with Physical Dimension Objects
    • Working with Physical Hierarchy Objects
  • Working with Cube Variables for SAP/BW Data Sources
  • Viewing Members in Physical Cube Tables
• Working with Essbase Data Sources
  • About Using Essbase Data Sources with Oracle Business Intelligence
    • About Incremental Import
  • Working with Essbase Alias Tables
    • Determining the Value to Use for Display
    • Explicitly Defining Columns for Each Alias
  • Modeling User-Defined Attributes
  • Associating Member Attributes to Dimensions and Levels
  • Modeling Alternate Hierarchies
  • Modeling Measure Hierarchies
  • Improving Performance by Using Unqualified Member Names
• Working with Hyperion Financial Management Data Sources
  • About Query Support for Hyperion Financial Management Data Sources
• Working with Oracle OLAP Data Sources
  • About Importing Metadata from Oracle OLAP Data Sources
  • Working with Oracle OLAP Analytic Workspace (AW) Objects
  • Working with Oracle OLAP Dimensions, Hierarchies, and Levels
  • Working with Oracle OLAP Cubes and Columns
• Working with Physical Foreign Keys and Joins
  • About Physical Joins
    • About Primary Key and Foreign Key Relationships
    • About Complex Joins
    • About Multi-Database Joins
    • About Fragmented Data
  • Defining Physical Joins with the Physical Diagram
  • Defining Physical Joins with the Joins Manager
• Deploying Opaque Views
  • About Deploying Opaque Views
  • Deploying Opaque View Objects
    • Using the Create View SELECT Statement
  • Undeploying a Deployed View
  • When to Delete Opaque Views or Deployed Views
  • When to Redeploy Opaque Views
• Using Hints
  • How to Use Oracle Hints
    • About the Index Hint
    • About the Leading Hint
  • About Performance Considerations for Hints
  • Creating Hints
• Displaying and Updating Row Counts for Physical Tables and Columns

9 Working with Logical Tables, Joins, and Columns

• About Working with the Business Model and Mapping Layer
• Creating the Business Model and Mapping Layer
  • Creating Business Models
  • Automatically Creating Business Model Objects
    • Automatically Creating Business Model Objects for Multidimensional Data Sources
  • Duplicating a Business Model and Subject Area
• Working with the Business Model Diagram
• Creating and Managing Logical Tables
  • Creating Logical Tables
    • Creating and Managing Logical Table Sources
  • Specifying a Primary Key in a Logical Table
  • Reviewing Foreign Keys for a Logical Table
• Defining Logical Joins
  • Defining Logical Joins with the Business Model Diagram
  • Defining Logical Joins with the Joins Manager
    • Creating Logical Joins with the Joins Manager
    • Creating Logical Foreign Key Joins with the Joins Manager
  • Specifying a Driving Table
  • Identifying Physical Tables That Map to Logical Objects
• Creating and Managing Logical Columns
  • Creating Logical Columns
  • Basing the Sort for a Logical Column on a Different Column
  • Enabling Double Column Support by Assigning a Descriptor ID Column
  • Creating Derived Columns
    • Configuring Logical Columns for Multicurrency Support
  • Setting Default Levels of Aggregation for Measure Columns
    • Setting Up Dimension-Specific Aggregate Rules for Logical Columns
    • Defining Aggregation Rules for Multidimensional Data Sources
  • Associating an Attribute with a Logical Level in Dimension Tables
  • Moving or Copying Logical Columns
• Enabling Write Back On Columns
• Setting Up Display Folders in the Business Model and Mapping Layer
• Modeling Bridge Tables
  • Creating Joins in the Physical Layer for Bridge and Associated Dimension Tables
  • Modeling the Associated Dimension Tables in a Single Dimension
  • Modeling the Associated Dimension Tables in Separate Dimensions

10 Working with Logical Dimensions

• About Working with Logical Dimensions
• Creating and Managing Dimensions with Level-Based Hierarchies
  • About Level-Based Hierarchies
    • Using Dimension Hierarchy Levels in Level-Based Hierarchies
  • Manually Creating Dimensions, Levels, and Keys with Level-Based Hierarchies
    • Creating Dimensions in Level-Based Hierarchies
    • Creating Logical Levels in a Dimension
    • Associating a Logical Column and Its Table with a Dimension Level
    • Identifying the Primary Key for a Dimension Level
    • Selecting and Sorting Chronological Keys in a Time Dimension
    • Adding a Dimension Level to the Preferred Drill Path
  • Automatically Creating Dimensions with Level-Based Hierarchies
  • Populating Logical Level Counts Automatically
• Creating and Managing Dimensions with Parent-Child Hierarchies
  • About Parent-Child Hierarchies
    • About Levels and Distances in Parent-Child Hierarchies
    • About Parent-Child Relationship Tables
    • About Parent-Child Hierarchies Populated with Preaggregated Data
  • Creating Dimensions with Parent-Child Hierarchies
  • Defining Parent-Child Relationship Tables
    • Adding the Parent-Child Relationship Table to the Model
    • Maintaining Parent-Child Hierarchies Based on Relational Tables
• Modeling Time Series Data
  • About Time Series Functions
    • About the AGO Function
    • About the TODATE Function
    • About the PERIODROLLING Function
  • Creating Logical Time Dimensions
    • Selecting the Time Option in the Logical Dimension Dialog
    • Setting Chronological Keys for Each Level
  • Creating AGO, TODATE, and PERIODROLLING Measures

11 Managing Logical Table Sources (Mappings)

• About Logical Table Sources
• Creating Logical Table Sources
• Setting Priority Group Numbers for Logical Table Sources
• Defining Physical to Logical Table Source Mappings and Creating Calculated Items
  • Unmapping a Logical Column from Its Source
• Defining Content of Logical Table Sources
  • Verifying that Joins Exist from Dimension Tables to Fact Table
  • About WHERE Clause Filters
• Working with Parent-Child Settings in the Logical Table Source
• Setting Up Aggregate Navigation by Creating Sources for Aggregated Fact Data
• Setting Up Fragmentation Content for Aggregate Navigation
  • Specifying Fragmentation Content for Single Column, Value-Based Predicates
  • Specifying Fragmentation Content for Single Column, Range-Based Predicates
    • Specifying Multicolumn Content Descriptions
    • Specifying Parallel Content Descriptions
    • Specifying Unbalanced Parallel Content Descriptions
  • Specifying Fragmentation Content for Aggregate Table Fragments
    • Specifying the Aggregate Table Content
    • Defining a Physical Layer Table with a Select Statement to Complete the Domain
    • Specifying the SQL Virtual Table Content
    • Creating Physical Joins for the Virtual Table

12 Creating and Maintaining the Presentation Layer

• About the Presentation Layer
• Creating and Customizing the Presentation Layer
  • Creating Subject Areas
    • Automatically Creating Subject Areas Based on Logical Stars and Snowflakes
  • Removing Unneeded or Unwanted Columns
  • Renaming Presentation Columns to User-Friendly Names
  • Exporting Logical Keys in the Subject Area
  • Setting an Implicit Fact Column in the Subject Area
  • Maintaining the Presentation Layer
• Working with Subject Areas
• Working with Presentation Tables and Columns
  • Creating and Managing Presentation Tables
  • Creating and Managing Presentation Columns
  • Nesting Folders in Answers and BI Composer
• Working with Presentation Hierarchies and Levels
  • Creating and Managing Presentation Hierarchies
    • Modeling Dimensions with Multiple Hierarchies in the Presentation Layer
    • Editing Presentation Hierarchy Objects
  • Creating and Managing Presentation Levels
• Setting Permissions for Presentation Layer Objects
  • Generating a Permission Report for Presentation Layer Objects
  • Sorting Columns in the Permissions Dialog
• Creating Aliases (Synonyms) for Presentation Layer Objects
• Controlling Presentation Object Visibility

13 Creating and Persisting Aggregates for Oracle BI Server Queries

• About Aggregate Persistence in Oracle Business Intelligence
• Identifying Query Candidates for Aggregation
• Using Oracle BI Summary Advisor to Identify Query Candidates for Aggregation
  • About Oracle BI Summary Advisor
    • Gathering Summary Advisor Statistics
    • Generating and Using Summary Advisor Recommendations
  • Setting Up the Statistics Database
  • Turning On Usage Tracking
  • Turning On Summary Advisor Logging
  • Using the Oracle BI Summary Advisor Wizard
    • Using the Oracle BI Summary Advisor Statistics Generator to Generate Statistics
• Using the Aggregate Persistence Wizard to Generate the Aggregate Specification
• Using Model Check Manager to Check for Modeling Problems
  • About Model Check Manager
  • Running Model Check Manager
  • Using the Model Check Manager Dialog
• Writing the Create Aggregates Specification Manually
  • What Constraints Are Imposed During the Create Process?
  • How to Write the Create Aggregates Specification
  • Adding Surrogate Keys to Dimension Aggregate Tables
    • About the Create/Prepare Aggregates Syntax
    • About Surrogate Key Output from Create/Prepare Aggregates
• Running the Aggregate Specification Against the Oracle BI Server
• Life Cycle Use Cases for Aggregate Persistence
• Using Double Buffering to Refresh Highly Available Aggregates
• Troubleshooting Aggregate Persistence
  • About Aggregate Persistence Errors
  • Avoiding Aggregate Persistence Errors
  • Tips for Creating Aggregates on TimesTen Sources

14 Applying Data Access Security to Repository Objects

• About Data Access Security
  • Where Do I Find Information About Security Tasks?
• Setting Up Row-Level Security
  • Setting Up Row-Level Security (Data Filters) in the Repository
  • Setting Up Row-Level Security in the Database
• Setting Up Object Permissions
  • About Permission Inheritance for Users and Application Roles
• Setting Query Limits
  • Accessing the Query Limits Functionality in the Administration Tool
  • Limiting Queries By the Number of Rows Received
  • Limiting Queries By Maximum Run Time and Restricting to Particular Time Periods
  • Allowing or Disallowing Direct Database Requests
  • Allowing or Disallowing the Populate Privilege
• About Applying Data Access Security in Offline Mode
  • Setting Up Placeholder Application Roles for Offline Repository Development
• About the List of Users in the Administration Tool

15 Completing Oracle BI Repository Setup

• Configuring the Repository for Oracle Scorecard and Strategy Management
  • Configuring the Repository for Comments and Status Overrides
• Saving the Repository and Checking Consistency
• Using nqcmd to Test and Refine the Repository
• Making the Repository Available for Queries
• Creating Data Source Connections to the Oracle BI Server for Client Applications
• Publishing to the User Community

16 Setting Up Data Sources on Linux and UNIX

• About Setting Up Data Sources on Linux and UNIX
• Configuring Data Source Connections Using Native Gateways
  • Troubleshooting OCI Connections
  • About Updating Row Counts in Native Databases
• Using DataDirect Connect ODBC Drivers on Linux and UNIX
  • Configuring the DataDirect Connect ODBC Driver for Microsoft SQL Server Database
  • Configuring the DataDirect Connect ODBC Driver for Sybase ASE Database
  • Configuring the DataDirect Connect ODBC Driver for Informix Database
• Configuring Database Connections Using Native ODBC Drivers
• Setting Up Oracle TimesTen In-Memory Database on Linux and UNIX
• Configuring Oracle RPAS ODBC Data Sources on AIX UNIX
• Configuring Essbase Data Sources on Linux and UNIX
• Configuring DB2 Connect on IBM z/OS and s/390 Platforms

17 Managing Oracle BI Repository Files

• Comparing Repositories
  • Comparing Repositories Using the Compare Dialog
  • Comparing Repositories Using comparerpd
  • Turning Off Compare Mode
• Equalizing Objects
  • About Equalizing Objects
  • Using the Equalize Objects Dialog
  • Using the equalizerpds Utility
    • About Values for TypeName
• Merging Repositories
  • Performing Full Repository Merges
    • About Full Repository Merges
    • Performing Full Repository Merges With a Common Parent
    • Performing Full Repository Merges Without a Common Parent
  • Performing Patch Merges
    • About Patch Merges
    • Generating a Repository Patch
    • Applying a Repository Patch
• Querying and Managing Repository Metadata
  • Querying the Repository
    • Constructing a Filter for Query Results
  • Querying Related Objects
• Changing the Repository Password

18 Using Expression Builder and Other Utilities

• Using Expression Builder
  • About the Expression Builder Dialogs
  • About the Expression Builder Toolbar
  • About the Categories in the Category Pane
  • Setting Up an Expression
    • Navigating Within Expression Builder
    • Building an Expression
    • About the INDEXCOL Conversion Function
• Using Administration Tool Utilities
  • Using the Replace Column or Table Wizard
  • Using the Oracle BI Event Tables Utility
  • Using the Externalize Strings Utility
  • Using the Rename Wizard
  • Using the Update Physical Layer Wizard
  • Generating Documentation of Repository Mappings
  • Generating a Metadata Dictionary
  • Removing Unused Physical Objects
  • Persisting Aggregates
  • Using the Oracle BI Summary Advisor Wizard
  • Using the Upgrade Presentation Foldering Utility
• Using the Calculation Wizard

19 Using Variables in the Oracle BI Repository

• Working with Repository Variables
  • About Repository Variables
    • About Static Repository Variables
    • About Dynamic Repository Variables
  • Creating Repository Variables
    • Using Repository Variables in Expression Builder
• Working with Session Variables
  • About Session Variables
    • About System Session Variables
    • About Nonsystem Session Variables
  • Creating Session Variables
• Working with Initialization Blocks
  • About Using Initialization Blocks with Variables
    • Initializing Dynamic Repository Variables
    • Initializing Session Variables
    • About Row-Wise Initialization
  • Creating Initialization Blocks
    • Assigning a Name and Schedule to Initialization Blocks
    • Selecting and Testing the Data Source and Connection Pool
  • Associating Variables with Initialization Blocks
  • Establishing Execution Precedence
  • When Execution of Session Variable Initialization Blocks Cannot Be Deferred
  • Enabling and Disabling Initialization Blocks

A Managing the Repository Lifecycle in a Multiuser Development Environment

• Planning Your Multiuser Development Deployment
  • About Business Organization and Governance Process Best Practices
  • About Technical Team Roles and Responsibilities
• Multiuser Development Architecture
  • About Multiuser Development Concepts
  • About Multiuser Development Styles
  • Multiuser Development Sandbox Architecture
  • Multiuser Development and Lifecycle Management Architecture
• Understanding the Multiuser Development Environment
  • About Multiuser Development Environment Task Flow
  • About Multiuser Development Projects
  • How to Create Branches
    • How to Create a Main Branch
    • How to Create a Side Branch
    • How to Create a Delegated Administration Branch
  • Which Merge Utility Should I Use?
• MUD Tips and Best Practices
  • Best Practices for Branching
  • Best Practices for Setting Up Projects
  • Best Practices for Three-Way Merges
  • Best Practices for MUD Merges
  • Best Practices for Two-Way Merges
  • Best Practices for Production Migration
  • Best Practices for Application Roles and Users
• Troubleshooting Multiuser Development

B MUD Case Study: Eden Corporation

• About the Eden Corporation Fictional Case Study
• Phase I - Initiating Multiuser Development (MUD)
  • Starting Initiative S
  • Setting Up MUD Projects
  • First Developer Checks Out
  • Second Developer Checks Out
  • First Developer Publishes Changes
  • Second Developer Publishes Changes
  • MUD Administrator Test Migration Activities
  • Phase I Testing
  • Phase I Migration to Production
  • Phase I Summary
• Phase II - Branching, Fixing, and Patching
  • Setting Up the Second Branch
  • Developers Check Out Projects
  • Patch Fix for the Main Branch
  • Finishing and Merging Phase II Branch
  • Phase II Summary
• Phase III - Independent Semantic Model Development
  • Security Considerations for Multiple Independent Semantic Models
  • Sales Semantic Model Developers Check Out
  • HR Semantic Model Developer Builds Content
  • Phase III Summary

C Logical SQL Reference

• About Logical SQL in Oracle Business Intelligence
• SQL Syntax and Semantics
  • Syntax and Usage Notes for the SELECT Statement
    • Basic Syntax for the SELECT Statement
    • Usage Notes
    • Subquery Support
    • SELECT List Syntax
    • FROM Clause Syntax
    • WHERE Clause Syntax
    • GROUP BY Clause Syntax
    • ORDER BY Clause Syntax
  • Syntax and Usage Notes for SELECT_PHYSICAL
    • Syntax for the SELECT_PHYSICAL Statement
    • Aggregate Functions Not Supported in SELECT_PHYSICAL Queries
    • Queries Supported by SELECT_PHYSICAL
    • Using the NATURAL_JOIN Keyword
    • Special Usages of SELECT_PHYSICAL
  • Limiting and Offsetting Rows Returned
    • Limitations of the FETCH and OFFSET Clauses
  • Rules for Queries with Aggregate Functions
    • Computing Aggregates of Baseline Columns
    • Computing Aggregates of Measure Columns
    • Display Function Reset Behavior
    • Alternative Syntax
    • Using FILTER to Compute a Conditional Aggregate
  • Operators
    • SQL Logical Operators
    • Mathematical Operators
  • Conditional Expressions
    • CASE (Switch)
    • CASE (If)
  • Expressing Literals
    • Character Literals
    • Datetime Literals
    • Numeric Literals
  • Calculated Members
    • CALCULATEDMEMBER Syntax
    • Rules for the CALCULATEDMEMBER Expression
    • Using Solve Order to Control Formula Evaluation Sequence
    • Examples of Calculated Members in Queries
  • Variables
• Aggregate, Running Aggregate, and Time Series Functions
  • Aggregate Functions
    • AGGREGATE AT
    • AVG
    • AVGDISTINCT
    • BOTTOMN
    • COUNT
    • COUNTDISTINCT
    • COUNT(*)
    • FIRST
    • FIRST_PERIOD
    • GROUPBYCOLUMN
    • GROUPBYLEVEL
    • LAST
    • LAST_PERIOD
    • MAX
    • MEDIAN
    • MIN
    • NTILE
    • PERCENTILE
    • RANK
    • STDDEV
    • STDDEV_POP
    • SUM
    • SUMDISTINCT
    • TOPN
  • Running Aggregate Functions
    • MAVG
    • MSUM
    • RSUM
    • RCOUNT
    • RMAX
    • RMIN
  • Time Series Functions
    • AGO
    • PERIODROLLING
    • TODATE
• String Functions
  • ASCII
  • BIT_LENGTH
  • CHAR
  • CHAR_LENGTH
  • CONCAT
  • INSERT
  • LEFT
  • LENGTH
  • LOCATE
  • LOWER
  • OCTET_LENGTH
  • POSITION
  • REPEAT
  • REPLACE
  • RIGHT
  • SPACE
  • SUBSTRING
  • TRIMBOTH
  • TRIMLEADING
  • TRIMTRAILING
  • UPPER
• Math Functions
  • ABS
  • ACOS
  • ASIN
  • ATAN
  • ATAN2
  • CEILING
  • COS
  • COT
  • DEGREES
  • EXP
  • EXTRACTBIT
  • FLOOR
  • LOG
  • LOG10
  • MOD
  • PI
  • POWER
  • RADIANs
  • RAND
  • RANDFROMSEED
  • ROUND
  • SIGN
  • SIN
  • SQRT
  • TAN
  • TRUNCATE
• Calendar Date/Time Functions
  • CURRENT_DATE
  • CURRENT_TIME
  • CURRENT_TIMESTAMP
  • DAY_OF_QUARTER
  • DAYNAME
  • DAYOFMONTH
  • DAYOFWEEK
  • DAYOFYEAR
  • HOUR
  • MINUTE
  • MONTH
  • MONTH_OF_QUARTER
  • MONTHNAME
  • NOW
  • QUARTER_OF_YEAR
  • SECOND
  • TIMESTAMPADD
  • TIMESTAMPDIFF
  • WEEK_OF_QUARTER
  • WEEK_OF_YEAR
  • YEAR
• Conversion Functions
  • CAST
  • CHOOSE
  • IFNULL
  • INDEXCOL
    • Example With Hierarchy Levels
  • TO_DATETIME
  • VALUEOF
• Database Functions
  • EVALUATE
  • EVALUATE_ANALYTIC
  • EVALUATE_AGGR
  • EVALUATE_PREDICATE
• Hierarchy Navigation Functions
  • DEPTH
  • IDOF
  • ISANCESTOR
  • ISCHILD
  • ISDESCENDANT
  • ISLEAF
  • ISPARENT
  • ISROOT
  • ISSIBLING
  • PARENT
• System Functions
  • USER
  • DATABASE

D Merge Rules

• About the Merge Process
• Merge Rules and Behavior for Full Merges
  • Special Merge Algorithms for Logical Table Sources and Other Objects
    • Merging Objects that Use the Vector Merge Algorithm
    • Merging Logical Table Sources
    • Merging Security Filters
    • Inferring the Use Logical Column Property for Presentation Columns
    • Merging Aliases
• Merge Rules and Behavior for Multiuser Development Merges
• Merge Rules and Behavior for Patch Merges
  • Using Patchrpd to Automate the Patch Process

E Deleting Unwanted Objects from the Repository

• About the Object Pruning Utility
• Using the Object Pruning Utility
  • Creating the Input File
  • Running the prunerpd Utility
• Deletion Rules for the Object Pruning Utility

F Exchanging Metadata with Databases to Enhance Query Performance

• About Exchanging Metadata with Databases
• Generating the Import File
  • Running the Generator
  • About the Metadata Input File
  • About the Output Files
  • Troubleshooting Errors from the Generator
  • Metadata Conversion Rules and Error Messages
    • Conversion Rules for Oracle Databases
    • Conversion Rules for IBM DB2 Databases
• Using Materialized Views in the Oracle Database with Oracle Business Intelligence
  • About Using the SQL Access Advisor with Materialized Views
  • Deploying Metadata for Oracle Database
    • Executing the SQL File for Oracle Database
    • Defining Constraints for the Existence of Joins
    • Creating the Query Workload
    • Creating Materialized Views
• Using IBM DB2 Cube Views with Oracle Business Intelligence
  • About Using IBM DB2 Cube Views with Oracle Business Intelligence
  • Deploying Cube Metadata
    • Executing the Alias-SQL File for IBM Cube Views
    • Importing the XML File
    • Guidelines for Creating Materialized Query Tables (MQTs)

G XML Schema Files for ADF Mapping Customizations

• app_segment_rule.xsd XML Schema File
• app_segment_rules_*.xml Example
• mapping_rules.xsd XML Schema File
• mapping_rules_*.xml Example

H Administration Tool Keyboard Shortcuts

• Menu Keyboard Shortcuts
• Dialog Keyboard Shortcuts
• Physical Diagram and Business Model Diagram Keyboard Shortcuts

Glossary

Index