Home / OBIEE

OBIEE Tutorial

Rating: 5.0Blog-star
Views: 15763
by Ravindra Savaram
Last modified: August 23rd 2021

OBIEE tutorial gives you an overview and talks about the fundamentals of OBIEE. Oracle business intelligence is a system that provides the single enterprise view of the business performance producing actionable insights across and deep down from top to bottom of the enterprise. This is most popularly known as Oracle business intelligence enterprise edition OBIEE.

Categories of OBIEE Tutorial

OBIEE Tutorial For Beginners

If you would like to Enrich your career with an OBIEE certified professional, then Enroll in the “OBIEE  Online Training”  Course. This course will help you to achieve excellence in this domain.

What Is OBIEE?

Today’s enterprise business scenarios are composed of multiple heterogeneous processes and systems that consist of transactional, ERP, decision support, analytical and custom homegrown desktop applications based on spreadsheets and Microsoft Access, etc. Data is distributed across the enterprise and integration among various systems to produce a single coherent view of the business performance of the enterprise is difficult.

Oracle business intelligence is a system that provides the single enterprise view of the business performance producing actionable insights across and deep down from top to bottom of the enterprise. This is most popularly known as Oracle business intelligence enterprise edition OBIEE.

Oracle business intelligence provides the following features as listed below:

  • Unified Enterprise Vie:  Enables a single consistent and logical view of enterprise across multiple heterogeneous sources such as ERP, data warehouse, and operational systems.
  • Unified Semantic View: Models complex business information and modes from multiple sources to a common enterprise logical model.
  • End-User Self Service: Business users can develop ad-hoc analytic reports based on their own needs and only be concerned about the business view of information: 
  • Real-Time Information Access: Technologies like federated access, micro ETL, direct integration with transactional systems as well as replication provides this
  • Performance Support By Oracle Appliance: Oracle Exalytics is OBIEE self-contained in an appliance. It is a hardware machine with preinstalled and configured OBIEE software
  • Open Scalable Architecture: Multi-tiered architecture enables it to scale vertically and horizontally in any tier.
  • Multiple Data Source Support: OBIEE supports multiple data sources ranging from ERP(EBS and Peoplesoft), Siebel CRM, data warehouses, OLAP cubes like Essbase and SSAS, spreadsheets, and text files. Oracle data integrator tool is the common hub for data integration that provides various adapters.
  • Built-In And Customizable Analytic Capabilities: OBIEE Applications are a suite of products on top of the platform consisting of built-in functionality that is extensible to Enterprise needs.
    1. Foundation For Integrating Multiple Analytic Applications
    2. Foundation for integrating multiple applications like Finance, procurement, HR, Transportation, etc.
    3. Publishing And Integration With Microsoft Excel
  • Integrated OLAP Sources Like Essbase: Data can be pulled and integrated with Essbase in a horizontal (drill through) or vertical (drill-down capability)"
  • Enterprise Mobile Capabilities: OBIEE has built-in and customizable mobile platform capabilities in a distributed disconnected model.
  • Data Source Abstraction To Business: OBIEE provides a logical business data modal based on the Common Information model that hides the data source from the business.
  • Highly Customizable And Modular: OBIEE is customizable in all tiers 1, e reports, repository. Middleware, ETL, and data layer. Tiered architecture and functional subject areas provide modularity.
    1. Interactive And Customizable Dashboards And Report
    2. KPI, Alerts, Delivery, And Automation
  • Common Data Integration:  Oracle data integrator provides the common data integration platform across transactional, OLAP, spreadsheet, and other external data sources

 KPI, Alerts, Delivery And Automation

MindMajix Youtube Channel

OBIEE  Architecture

A typical OBIEE implementation consists of multiple tiers as shown in the architecture diagram below. Information from the data source layer is transformed and integrated along the way and presented in a manner that is consumable effectively to the end business user.

 OBIEE  Architecture

OBIEE Tiered Architecture

  1. Client Tier: This consists of the browser and the URL for accessing the OVIEE system from a laptop or a desktop or across the internet or mobile devices.
  2. Web Tier: This is a web catalog layer that stores the reports and dashboards developed by BI business users.
  3. Application Tier: Metadata layer called repository that develops SQL queries based on user requests
  4. Data Warehouse: This layer is the data warehouse that stores the historical data in a schema suitable for BI analysis
  5. ETL: Extract transform and load is the tool that transforms the transactional data into a format that is suitable for BI analysis.
  6. Data Source: The data source are the various kinds of transactional and spreadsheet data sources.

Oracle BI Domain

The diagram above shows the OBIEE application layer architecture. The Oracle application layer or popularly known as the Fusion Middleware layer is built upon a common domain-based architecture that can support a set of common core functionality (like security, scalability, etc) across various Oracle products. This is implemented with the Weblogic domain as shown in the diagram (Oracle BI domain).

The Oracle BI domain consists of two components

  • Weblogic Domain: The Weblogic domain consists of the logical division of applications in the applications we server layer with a common architecture to administer them, called Admin servers and managed servers to perform the web applications work performed by the applications.
  • Admin Server: This consists of a set of admin servers to manage the processes running in the domain.
  • Managed Server: These are product-specific processes that run within Weblogic. The core architecture is designed such that multiple managed servers can be started across multiple machines for load balancing as BI usage increases. The managed server consists of different application-specific services like BI publisher (responsible for generating pixel-perfect reports), BI Office (integration with MS office components like excel and ppt), Action service (responsible for navigation based on the action defined for a data cell in the reports).
  • Oracle BI System Components: The Oracle BI systems components are the server-side components of the WebLogic application layer domain that performs a specific set of work. The system components are managed in an integrated fashion by a central process Oracle process manager (OPMN)
  • Oracle BI Server: Performs generation of SQL queries based on the Oracle BI repository metadata and the user requests. The SQL queries are then directed to the data source or the data warehouse to produce the desired results.
  • Oracle Presentation Server: This is responsible for rendering the report catalog with the data retrieved from the SQL queries to the dashboard.
  • Oracle BI Scheduler: Responsible for executing Oracle BI requests and in a scheduled automated manner and delivery of BI content such as generated reports to business users' email. Responsible for monitoring the different business alerts set in the BI system.
  • Oracle BI Cluster Controller: Responsible for cluster control in high availability and failover environment in conjunction with Oracle cluster ware. Process failures detected in one node will results in automatic failover to the other nodes.
  • Oracle BI Java Host: This is responsible for enabling the BI Presentation server to support various Java tasks for BI Scheduler, Publisher, and graphs.
Related Article - OBIEE Administration Interview Questions

Oracle BI Enterprise Edition:

  • Is a comprehensive suite of enterprise BI products that delivers a full range of analysis and reporting capabilities

  • It has the following components:

  • Oracle BI Server – Common enterprise business model and abstraction layer

  • Oracle BI Answers – Ad-hoc query and reporting

  • Oracle BI Interactive Dashboards – Highly interactive dashboards for accessing business intelligence and applications  content

  • Oracle BI Delivers – Proactive business activity monitoring and alerting

  • Oracle BI Disconnected Analytics – Full analytical functionality for the mobile professionals

  • Oracle BI Publisher – Enterprise reporting and distribution of “pixel-perfect” reports

  • Oracle BI Briefing Books – Snapshots of dashboard pages to view and share in offline mode

Oracle BI Applications:

Are complete, prebuilt BI solutions that deliver intuitive, role-based intelligence for everyone in an organization

Oracle BI Applications are built on the Oracle Business Intelligence Enterprise Edition

Provides all that the standalone application does, plus:

  1. Applications for common industry analytical processing such as Service Analytics, Sales Analytics, Marketing Analytics, and so on
  2. Prebuilt role-based dashboards to support the needs of line managers to chief executive officers
  3. A prebuilt database (Oracle Business Analytics Warehouse) designed for analytical processing with prebuilt routines to extract, load, and transform data from the transactional databases.
Related Article - OBIEE Interview Questions

OBIEE Architecture

Is made up of five main  components:

  • Web Clients

  • Oracle BI Presentation Server

  • Oracle BI Server

  • Oracle BI Scheduler

  • Data Sources

Web Clients

Provide access to bi information

  • Web browser

  • Enterprise application

  • Corporate portals

Oracle BI Presentation Server

  • Provides the processing to visualize the information for client consumption -1. Is implemented as an extension to a web server 2.Uses the presentation catalog to store aspects of the  application
  • Receives data from the Oracle BI Server and provides it to the client that requested it

Oracle BI Presentation Catalog

  •  Stores the application dashboards request definitions and information about scheduling reports
  •  Contains information regarding permissions and accessibility of the dashboards by groups and users
  •  Is created when the Oracle BI Presentation Server  starts
  •  Is administered using Oracle BI Catalog Manager

Oracle BI Delivers

  • A client application that:

         1.Is used to create iBots

         2. Deliver alerts to subscribed users

         3. Is integrated with Dashboards and Answers

  • Job identifies what information to filter when it should run, and who to send alerts to

Oracle BI Presentation Services Administration

  • Is used to access administrative functions of Oracle BI presentation services and view information about the currently installed  system

Oracle BI Server 

  • Provides efficient processing to intelligently access the physical data sources and structures the information

    1. Uses metadata to direct processing
    2. Generates dynamic SQL to query data in the data sources
    3. Connects natively or via ODBC to the RDBMS
    4. Structures results to satisfy requests
  • Provides the data to the Oracle BI Presentation Server

  • Several important components are used by the Oracle BI   Server:

    1. Repository file (.rpd)
    2. Cache
    3. NQSConfig.ini
    4. DBFeatures.ini
    5. Log files
1. Repository file (.rpd)
  • Contains metadata that represents the analytical  model
  • It is created using the Oracle BI Administration Tool
  • It is divided into three layers:
    1. Physical layer – represents the data sources
    2. Business and Mapping layer – models the data sources into facts and dimensions
    3. Presentation layer – specifies the users view of the model; rendered in Oracle BI Answers
2.CACHE :
  • Contains results of queries

  • Is used to eliminate redundant queries to the database:

    1. Speeds up request processing
3.  NQSConfig.INI 
  • Is a configuration file used by the Oracle BI Server at  startupSpecifies values that control processing, such  as:

       1. Defining the repository (.rpd) to load

       2. Enabling or disabling caching of results

4.  DBFeatures.INI
  •  Is a configuration file used by the Oracle BI Server
  •  Specifies values that control SQL generation:

          1. The features supported by each  database

5. Log Files
  •  NQServer.log records Oracle BI Server messages
  •  NQQuery.log records information about query requests

Oracle BI Scheduler:

  • Manages and executes jobs requesting data  analytics
  • Schedules reports being delivered to users at specified  times
  • In windows, the scheduler runs as a service

Data Sources:

  • Contain the business data users want to analyze
  • Are accessed by the Oracle BI Server

     Can be in any format, such as

         1. Relational databases

         2. Online Analytical Processing (OLAP) databases

         3. Flat files

         4. Spreadsheets

Oracle Business Analytics Warehouse

  • It is a predefined data source to support the analytical requirements of Oracle Business Intelligence Applications:1. Relevant data structures support Oracle BI Applications
  • It is  in a star schema format
  • It is  included with Oracle BI Applications (not available with standalone OBIEE purchases)

Data Warehouse Administration Console

  •    Data Warehouse Application Console (DAC)  Client:
    1.  Used to schedule, monitor, configure, and customize OBAW extraction, transformation, and  load

    2.  Accesses meta-data about ETL mappings and dependencies in the DAC repository

  • DAC Server:

     1. Organizes ETL requests for processing

  • Third-party Informatica Server populates the OBAW from the transactional  Database (PeopleSoft database):

     1. Uses extract, transform, and load (ETL) routines

Sample Request Processing

  • User views a Dashboard or submits an Answers  request
  • The Oracle BI Presentation Server makes a request to the Oracle BI Server to retrieve the requested data
  • The Oracle BI Server, using the repository file, optimizes functions to request the data from the data sources
  • The Oracle BI Server receives the data from the data sources and processes as necessary
  • The Oracle BI Server passes the data to the Oracle BI Presentation  Server
  • The Oracle BI Presentation Server formats the data and sends it to the client

REAL-TIME Implementation :

  • Oracle BI components are often implemented across several computers on the network

Clustering Oracle BI Servers

  • Cluster Server Feature:
    1. Allows up to 16 Oracle BI Servers in a network domain to act as a single server
    2. Servers in cluster share requests from multiple Oracle BI clients, including Oracle BI Answers and Oracle BI Delivers
  • Cluster Controller is a primary component of the Cluster Server feature:
    1. Monitors status of resources in a cluster and performs session assignment as resources change
    2. Supports detection of server failures and failover for ODBC clients of failed servers

OBIEE Repository Basics

  • Stores the metadata used by the OBIEE Server in a file format with.RPD as an extension
  • Is accessed and exposed using the Administration Tool
  • Is created by the server architect who:
    1. Imports – metadata from databases and other data sources     
    2. Simplifies and reorganizes the metadata into business models
    3. Structures the business model for presentation to users for requesting information
  • Exposes the Oracle BI repository in three separate panes, called layers
    1. Physical layer
    2. Business Model and Mapping layer
    3. Presentation layer

Building Physical Layer of a Repository

Objective:

After completing this chapter you will be able to:

  • Identify the objects in the Physical layer of the repository
  • Define a System Data Source Name (DSN) for a data source
  • Build the Physical layer Why you need to know:
  • Enables you to perform the first step of creating a repository, building the Physical layer
  • Provides the access to the data sources against which users will generate reports

Physical layer

  • Is the metadata that describes the source of the analytical data
  • Defines what the data is, how the data relates, and how to access the data
  • Is used by the OBIEE Server to generate SQL to access the business data to provide answers to business questions 

Connection Pool

  • Specifies the ODBC or native data source name
  • Defines how the OBIEE Server connects to the data source
  • Allows multiple users to share a pool of database connections
  • May create multiple connection pools to improve performance for a group of users

Table Types: 

Tables within the physical schema could be a:

  • Physical table – table existing in the database
  • Alias table – an alias of the physical table imported to the physical layer
  • Select Statement – forms the Opaque view in the physical layer of the repository
  • Stored Procedure

Foreign Key Joins

  • Represent the Primary Key – Foreign Key relationship between the tables in the Physical layer
  • Used to formulate the join when building the SQL

Complex Joins

  •  Complex joins are used to express relationships that do not involve a Primary Key – Foreign Key relationship

Import Data Source Schemas (Continued)

  • Select the tables and columns required to support the business model
  • Limit to tables and columns required to support the users’ analytical requirements

Building Physical Layer – Best Practices: 

Import only those tables that are likely to be used in the business models

  • Avoid importing joins from the database as it can lead to circular joins while defining custom join conditions in the physical layer
  • Import the database views, synonyms, and system tables only when required
  • Importing unnecessary database objects adds unnecessary complexity and increases the size of the repository
  • After importing the tables from the database, define/re-define the primary keys for every table.
  • Use complex joins to join the physical tables on non-primary keys or to specify an expression.

About Author

author
NameRavindra Savaram
Author Bio

 

Ravindra Savaram is a Content Lead at Mindmajix.com. His passion lies in writing articles on the most popular IT platforms including Machine learning, DevOps, Data Science, Artificial Intelligence, RPA, Deep Learning, and so on. You can stay up to date on all these technologies by following him on LinkedIn and Twitter.