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.
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.
- Foundation For Integrating Multiple Analytic Applications
- Foundation for integrating multiple applications like Finance, procurement, HR, Transportation, etc.
- 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.
- Interactive And Customizable Dashboards And Report
- 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
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 Tiered Architecture
- 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.
- Web Tier: This is a web catalog layer that stores the reports and dashboards developed by BI business users.
- Application Tier: Metadata layer called repository that develops SQL queries based on user requests
- Data Warehouse: This layer is the data warehouse that stores the historical data in a schema suitable for BI analysis
- ETL: Extract transform and load is the tool that transforms the transactional data into a format that is suitable for BI analysis.
- Data Source: The data source are the various kinds of transactional and spreadsheet data sources.
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.
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:
- Applications for common industry analytical processing such as Service Analytics, Sales Analytics, Marketing Analytics, and so on
- Prebuilt role-based dashboards to support the needs of line managers to chief executive officers
- A prebuilt database (Oracle Business Analytics Warehouse) designed for analytical processing with prebuilt routines to extract, load, and transform data from the transactional databases.
Is made up of five main components:
Provide access to bi information
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
- Uses metadata to direct processing
- Generates dynamic SQL to query data in the data sources
- Connects natively or via ODBC to the RDBMS
- Structures results to satisfy requests
- Repository file (.rpd)
- 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:
- Physical layer – represents the data sources
- Business and Mapping layer – models the data sources into facts and dimensions
- Presentation layer – specifies the users view of the model; rendered in Oracle BI Answers
- Speeds up request processing
- 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
- 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
- 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
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:
Used to schedule, monitor, configure, and customize OBAW extraction, transformation, and load
Accesses meta-data about ETL mappings and dependencies in the DAC repository
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
- Allows up to 16 Oracle BI Servers in a network domain to act as a single server
- 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:
- Monitors status of resources in a cluster and performs session assignment as resources change
- 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:
- Imports – metadata from databases and other data sources
- Simplifies and reorganizes the metadata into business models
- Structures the business model for presentation to users for requesting information
- Exposes the Oracle BI repository in three separate panes, called layers
- Physical layer
- Business Model and Mapping layer
- Presentation layer
Building Physical Layer of a Repository
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
- 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
- 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
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 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.