Oracle OBIEE 11g — Detailed Course Notes

Comprehensive notes covering architecture, repository development, dashboards, security, performance tuning, and deployment for Oracle Business Intelligence Enterprise Edition 11g.

Week 1 — Architecture & Repository Basics Foundations

Overview of Oracle BI Enterprise Edition (OBIEE) 11g Architecture

Oracle Business Intelligence Enterprise Edition (OBIEE) 11g is a comprehensive business intelligence platform that provides a unified environment for reporting, analysis, dashboards, and ad-hoc querying. Its architecture is composed of several key components that work together to deliver scalable and secure BI solutions.

Key Components:

• BI Server (Oracle Business Intelligence Server) — the core engine that processes queries, manages the repository, and enforces security. It interprets logical SQL, generates optimized physical SQL, and executes queries against data sources.
• Presentation Server (Oracle Business Intelligence Presentation Server) — the web-based interface that delivers dashboards, analyses, and reports to end-users. It handles user interaction, rendering, and session management.
• Scheduler (Oracle Business Intelligence Scheduler) — manages scheduled jobs such as iBots (Intelligent Business Objects), agent execution, and cache seeding. It ensures timely delivery of reports and updates.
• WebLogic Server (WLS) — the application server that hosts OBIEE components, providing J2EE container services, clustering, and failover capabilities.
• Repository (.rpd file) — the metadata layer that contains all business rules, mappings, security settings, and data source definitions. It is the heart of OBIEE, defining how data is accessed and presented.

Understanding the BI Server, Presentation Server, and Scheduler

BI Server is the query engine. It receives requests from the Presentation Server, parses logical SQL, consults the repository for business rules, and generates optimized physical SQL for each data source. It also handles caching, aggregation, and multi-source queries.

Presentation Server is the user-facing component. It hosts dashboards, analyses, and the Answers and Dashboards interfaces. It handles user authentication, session management, and rendering of visualizations.

Scheduler manages background tasks. You can schedule reports to be delivered via email, update caches, and run agents that trigger alerts based on business conditions.

Introduction to the Administration Tool and Repository (.rpd) Files

The Administration Tool is the primary development environment for OBIEE. It is a Windows-based application used to create and maintain the repository (.rpd) file. The repository is the metadata layer that maps physical data sources to logical business models.

Key features of the Administration Tool:

• Repository Editor — view and modify the three layers: Physical, Business Model, and Presentation.
• Business Model Editor — define logical tables, joins, hierarchies, and measures.
• Security Manager — configure user and group permissions.
• Variables Manager — define session, repository, and initialization variables.
• Consistency Checker — validate repository integrity and identify issues.

Setting Up the Development Environment and Connecting to Data Sources

Before you can build a repository, you need to set up your development environment:

• Install OBIEE 11g — including the Administration Tool and BI Server.
• Install Database Clients — appropriate database drivers (ODBC, JDBC) for your data sources.
• Configure Data Sources — set up ODBC DSNs or JDBC connections for each source system.
• Launch Administration Tool — connect to the repository and begin development.

-- Example: Setting up an ODBC connection for Oracle Database
-- 1. Open ODBC Data Source Administrator.
-- 2. Add a System DSN for Oracle.
-- 3. Specify the TNS Service Name, Server, Port, and Service Name.
-- 4. Test the connection.

Creating a New Repository and Importing Database Metadata

Steps to create a new repository:

1. Open the Administration Tool and select File → New → Repository.
2. Choose the repository type (typically "Oracle BI Server Repository").
3. Enter a name and location for the .rpd file.
4. Select the data sources you want to import. You can import from database tables, views, or synonyms.
5. Choose the physical tables, columns, and relationships to include.
6. Click Import to create the Physical Layer.

-- Importing metadata from Oracle:
-- 1. In the Administration Tool, select File → Import Metadata.
-- 2. Select the ODBC DSN for your Oracle database.
-- 3. Enter username and password.
-- 4. Select tables, views, or schemas to import.
-- 5. Choose "Import" to bring the metadata into the Physical Layer.

Working with Physical Tables, Aliases, and Foreign Key Relationships

In the Physical Layer, tables represent database objects. You can create aliases to represent the same physical table multiple times for different purposes (e.g., self-joins). Foreign key relationships define how tables are joined. You can create complex joins (inner, outer, full) based on the relationships in your database.

Validating and Checking Consistency of the Repository

After making changes to the repository, it's essential to validate it to ensure integrity and correctness. Use the Consistency Checker (Tools → Consistency Checker) to identify issues such as broken joins, missing keys, or invalid column references.

Best Practices for Repository Version Control and Backup

• Version your .rpd files — use source control (Git, SVN) with meaningful commit messages.
• Backup regularly — keep copies of the .rpd file before major changes.
• Use naming conventions — standardize names for tables, columns, and business models.
• Document changes — maintain a change log for the repository.
• Test in a development environment — never make direct changes in production.

---

Week 2 — Physical, Business Model, and Presentation Layers Repository

Deep Dive into the Three-Tier Repository Architecture

OBIEE's repository has three distinct layers, each serving a specific purpose:

• Physical Layer — defines the actual data sources (tables, views, columns, joins, connection pools).
• Business Model and Mapping (BMM) Layer — defines logical business models, mapping physical sources to logical objects. This is where you define hierarchies, measures, and business rules.
• Presentation Layer — defines the Subject Areas that end-users see in the Answers and Dashboards interface. It controls which columns and measures are visible to users.

This separation allows you to abstract the physical complexity from the business users, providing a simplified, business-friendly view of the data.

Physical Layer: Setting Up Connection Pools, Schemas, and Native Database Joins

The Physical Layer contains Connection Pools that define how the BI Server connects to data sources. Each connection pool includes connection details, credentials, and connection pooling parameters. You can create multiple connection pools for different databases.

Native database joins are defined directly in the Physical Layer. These joins are used when the BI Server generates SQL queries. You can define inner, left outer, right outer, and full outer joins.

Business Model and Mapping (BMM) Layer: Creating Logical Business Models

The BMM Layer is where you create Logical Tables and Logical Joins. Logical tables map to one or more physical tables. You can combine multiple physical tables into a single logical table using logical table sources (LTS).

In the BMM Layer, you also define:

• Dimensional Hierarchies — parent-child relationships (e.g., Year → Quarter → Month).
• Aggregation Rules — how measures are aggregated (SUM, COUNT, AVG, etc.).
• Calculated Columns — derived columns using logical expressions.

-- Example: Creating a Logical Table Source (LTS)
-- 1. In BMM Layer, create a new Logical Table (e.g., "Sales").
-- 2. Add a Logical Table Source that maps to a Physical Table.
-- 3. Define the mapping: which physical columns map to which logical columns.
-- 4. Add multiple LTSs for different granularities (e.g., daily, monthly).

Defining Logical Joins, Dimensional Hierarchies, and Aggregation Rules

Logical Joins define how logical tables are related. You can define joins between fact tables and dimension tables. Dimensional Hierarchies define the levels within a dimension (e.g., Country → Region → City). Aggregation Rules specify how measures should be aggregated when data is rolled up.

Presentation Layer: Designing Subject Areas for End-User Reporting

The Presentation Layer defines Subject Areas, which are the top-level folders that end-users see in Answers and Dashboards. Each Subject Area contains logical tables and columns. You can hide columns that are not relevant to business users, rename columns to business-friendly names, and organize columns into logical folders.

Hiding and Organizing Columns for Intuitive Dashboard Creation

In the Presentation Layer, you can:

• Hide columns — if they are not needed for reporting.
• Rename columns — to business-friendly names (e.g., "EMP_ID" → "Employee ID").
• Organize columns — into folders (e.g., "Customer Information", "Financial Metrics").

Importing and Merging Changes from Multiple Data Sources

When you have changes in your database schema (new tables, columns), you can merge them into your repository. The Administration Tool's import feature allows you to add new objects and update existing ones.

Handling Complex Data Modelling Scenarios and Star Schemas

OBIEE supports Star Schemas (fact tables surrounded by dimension tables) and Snowflake Schemas (normalized dimension tables). You can model complex relationships, including many-to-many joins, by creating appropriate logical table sources and join definitions.

---

Week 3 — Measures, Hierarchies & Repository Variables Logic

Creating Logical Measures, Calculated Columns, and Aggregation Rules

Measures are quantitative columns used in analyses. You define measures in the BMM Layer, specifying the aggregation rule (e.g., SUM, COUNT, AVG, MIN, MAX). Calculated Columns are derived using expressions.

-- Example: Creating a calculated column in BMM
-- Logical Column: Profit Margin
-- Expression: Revenue - Cost / Revenue
-- Aggregation: SUM (for fact tables)
-- Use the Expression Editor to define the formula.

Implementing Time Series Functions (Ago, ToDate, Period Rolling)

OBIEE provides built-in time series functions for comparisons over time:

• Ago — compares a measure to a previous period (e.g., this month vs. last month).
• ToDate — compares a measure to the same period in a previous time range.
• Period Rolling — calculates running totals or moving averages.

Level-Based Measures and Dimensional Context Awareness

Level-based measures are aggregated at specific dimensional levels. OBIEE provides context-aware calculations using the RSUM (Rolling SUM) and other functions.

Building Parent-Child Hierarchies and Level-Based Hierarchies

Parent-Child Hierarchies are defined by self-referencing tables (e.g., employee to manager). Level-Based Hierarchies have fixed levels (e.g., Country → Region → City).

Understanding Repository Variables (Session, Static, System, and Dynamic)

• Session Variables — set per user session (e.g., USER, LANGUAGE).
• Static Variables — fixed values set in the repository.
• System Variables — predefined variables for OBIEE.
• Dynamic Variables — values obtained from an initialization block (e.g., lookup from a database).

Using Initialization Blocks to Populate Variables Dynamically

Initialization Blocks are SQL queries that run when the BI Server starts or when a user logs in. They populate variables dynamically, enabling features like row-level security and user-specific settings.

Implementing Multi-Language and Multi-Currency Support

OBIEE supports multiple languages by storing translations in the repository. Multi-currency support can be implemented using currency conversion tables and variables.

Performance Tuning of Logical SQL Generated by OBIEE

• Optimize logical SQL — use aggregate aware features to push aggregation to the database.
• Use Fragmentation — to split queries across multiple data sources.
• Leverage Query Cache — to reduce repeated executions.

---

Week 4 — Interactive Dashboards & Advanced Analyses Dashboards

Creating Analyses Using Criteria, Columns, and Filters

In OBIEE Answers, you create Analyses by dragging columns from Subject Areas into the Criteria tab. You can add Filters to restrict data, and Prompts to allow users to interact with the analysis.

Advanced View Types: Pivot Tables, Charts, and Gauges

OBIEE supports various view types:

• Pivot Tables — for multi-dimensional data analysis.
• Charts — bar, line, pie, scatter, area, and combination charts.
• Gauges — for KPI dashboards.
• Funnel, Bubble, and Radar charts — for advanced visualizations.

Creating Column Selectors, View Selectors, and Action Links

• Column Selectors — allow users to switch columns dynamically.
• View Selectors — allow users to switch between different view types (e.g., table to chart).
• Action Links — navigate to other dashboards or external URLs.

Dashboard Creation, Dashboard Prompts, and Guided Navigation

Dashboards are collections of analyses, prompts, and other content. Dashboard Prompts enable users to filter data across multiple analyses. Guided Navigation directs users through a sequence of steps.

Leveraging Variables and Dynamic Content Within Dashboards

You can use Dashboard Variables to create dynamic content. For example, a dashboard can show different data based on the user's department.

Implementing Drill-Downs and Navigating Between Reports

Drill-downs allow users to click on a data point and navigate to a more detailed analysis. This can be configured in the analysis properties.

Adding Custom JavaScript and CSS to Dashboards

OBIEE allows you to embed custom JavaScript and CSS to enhance the user experience. Use the "Static Text" view to add custom code.

Best Practices for Dashboard Layout and User Experience Design

• Use consistent formatting — fonts, colors, and styles.
• Keep it simple — avoid cluttered dashboards.
• Use meaningful titles — and labels.
• Test with end-users — gather feedback.

---

Week 5 — Security, Cache Management & Performance Tuning Security

Understanding OBIEE Security Architecture and WebLogic Integration

OBIEE security is integrated with WebLogic Server, enabling authentication via LDAP, Active Directory, or database authentication. Authorization is managed through Application Roles and Privileges.

Managing Users, Groups, and Application Roles in the Enterprise Manager

You can manage users and groups in the WebLogic Console or Oracle Enterprise Manager. Application Roles define what users can do (e.g., "BIAdministrator", "BIAuthor", "BIConsumer").

Implementing Row-Level and Column-Level Data Security Using Filters

Row-level security restricts which rows a user can see. Column-level security restricts access to specific columns. This is implemented using Data Filters and Session Variables.

-- Example: Row-level security using a session variable
-- 1. Create a session variable: USER_DEPT
-- 2. Set the variable using an initialization block.
-- 3. Add a data filter in the repository: "Department = USER_DEPT"

Query Cache Mechanism and Cache Management Strategies

OBIEE maintains a Query Cache to store query results. This improves performance for repeated queries. You can manage the cache by purging, seeding, or scheduling updates.

Manual Cache Seeding, Purging, and Scheduling Cache Updates

• Seeding — pre-populate the cache with frequent queries.
• Purging — remove outdated cache entries.
• Scheduling — use iBots to refresh the cache.

Monitoring and Tuning Query Performance Using Usage Tracking Logs

Usage Tracking logs all queries executed in OBIEE. You can analyze these logs to identify slow-running queries, frequent queries, and performance bottlenecks.

Aggregate Persistence (Materialized Views) for Accelerated Reporting

Aggregate Persistence allows OBIEE to create and maintain materialized views in the database. This offloads aggregation from the BI Server and accelerates reporting.

Identifying and Resolving Slow-Running Analyses

• Analyze the logical SQL — generated by OBIEE.
• Check physical SQL — ensure it is optimized.
• Use aggregate tables — to reduce query complexity.
• Enable query logging — to identify bottlenecks.

---

Week 6 — Deployment, Integration & Capstone Project Deployment

Migrating Repositories, Web Catalogs, and Security Configurations

Migration involves moving the Repository (.rpd), Web Catalog (dashboards, analyses), and Security Configuration from development to production. Use the Migration Utility or manual copy.

Using BAR (BI Application Archive) Files and the Migration Utility

A BAR file (BI Application Archive) packages the repository and web catalog for deployment. The Migration Utility (part of the OBIEE Administration Tools) is used to create and deploy BAR files.

-- Creating a BAR file:
-- 1. Open the Administration Tool.
-- 2. Select File → Migration → Create BAR Archive.
-- 3. Choose the repository and web catalog to include.
-- 4. Generate the BAR file.
-- Deploying a BAR file:
-- 1. Open the Administration Tool.
-- 2. Select File → Migration → Deploy BAR Archive.

Integrating OBIEE with Oracle BI Publisher for Pixel-Perfect Reporting

Oracle BI Publisher can be integrated with OBIEE to create pixel-perfect, template-based reports. You can schedule BI Publisher reports and embed them in OBIEE dashboards.

Exposing OBIEE Reports via Web Services and SOAP APIs

OBIEE provides Web Services (SOAP) and REST APIs for programmatic access. You can integrate OBIEE reports with external applications, enabling them to call analyses and retrieve results.

Embedding OBIEE Dashboards into External Applications (iframe/SSO)

You can embed OBIEE dashboards in external applications using iFrames or Single Sign-On (SSO) integration. This allows seamless access to OBIEE content from other portals.

Scheduling iBots, Agents, and Delivering Reports via Email

iBots (Intelligent Business Objects) are scheduled agents that deliver reports, update caches, or trigger alerts. You can schedule iBots to run daily, weekly, or on-demand.

Disaster Recovery and High-Availability Best Practices

• Backup — regularly backup repository and web catalog.
• Clustering — deploy OBIEE in a clustered environment for failover.
• Monitoring — use Oracle Enterprise Manager to monitor system health.

Capstone Project

Project Objective: Design and deploy a complete enterprise BI solution from scratch for a real-world business domain.

Deliverables:

• Requirements gathering — understand business needs and KPIs.
• Data modelling — design star schema and create repository.
• Development — create analyses, dashboards, and prompts.
• Security — implement row-level and column-level security.
• Performance tuning — optimize queries and configure cache.
• Deployment — migrate to production and schedule reports.
• Documentation — comprehensive documentation of the solution.

---