Tuesday, 27 November 2012

Business Intelligence Case Study

Oracle Business Intelligence Implementation Scenario

This post presents the case study followed in the book "Implement Oracle Business Intelligence". The Import department of  ABC Corporation is using an Imports Management and Information Software, The Import Manager, to manage its imports proceedings. The software produces several key reports to provide insight into the imports process, but individually. The Imports department head, Mr. Frank, wants to see all the information at a glance. In addition, he needs some specific reports and vigilant information that the existing software cannot deliver proactively. He discussed the problem with the IT head and put-up the following questions:

  • What are my future payments due to banks?
  • What is the status of in-transit shipments?
  • What is the import volume of each category?
  • What is the comparative imports volume?
  • What is the import volume of individual products?
  • What is the business volume with different suppliers?
  • How to avoid demurrage and government penalties?
  • How to evaluate the performance of fianance department?
  • How to create and schedule reports and deliver them automatically to multiple delivery destinations?
In reply to the above queries, the IT department suggested a dashboard that would display all the information on a single screen. 

ABC Corporation is using Oracle 11g database on the backend. So, the development team has decided to take advantage of the most robust tool, Oracle Business Intelligence, to prepare a professional looking dashboard for Mr. Frank. The team initiates the project by conducting an analysis on the existing application and corresponding database objects.

After analysis, the project team decided to use the following OBIEE components:



You query your organization's data through analysis. It is the main tool to answer your business questions. It provides you the ability to visually present data in tables, pivot tables, graphs and so on. You can add calculated items and enjoy the drill down functionality that enables you to browse detail information.

After creating analyses, you integrate them into Oracle BI dashboard. An embedded analysis executes automatically and displays the most current results every time the dashboard is accessed. Once processed, an analysis sends the request in the form of logical SQL statements to the Oracle BI Server. The Oracle BI Server contacts relevant data sources through its generated queries and fetches the results in a format not suitable for the end user. It performs calculations and filtration tasks on the fetched results and sends the data to Oracle BI Presentation Services to format the results in a way that is understandable and meaningful to the recipient. 


After setting the analysis criteria, when you click the Results tab, you view the output in a generic table comprising rows and columns. You can further enhance the presentation capabilities of analyses by adding a variety of views including pivot tables, graphs,tickers and so on. Some of these allow you to drill down to more detailed information (you can drill in attribute columns and hierarchical columns).



Agent is a very useful component in Oracle BI that automates business processes by providing event-driven alerting, scheduled content publishing, and conditional event-driven action execution. It dynamically detects information-based problems and opportunities, determines the concerned users to alert, and delivers information to them through a wide range of devices including email, phones, and more. You can perform the following tasks with an agent:
  • Select analysis that is to be distributed
  • Choose automatically executing Actions depending on a condition
  • Create schedule to run an Agent   
  • Set a condition that evaluates what the Agent is going to perform



An alert is a notification generated by an agent that is usually displayed on the main dashboard page. An agent automatically performs a specified catalog analysis on a defined schedule, and evaluates a specific problem or opportunity in the results. It triggers an alert upon detecting a specific problem or opportunity and delivers personalized and actionable content to specified recipients and to agent subscribers using the delivery options that are specified for each person.


Key Performance Indicators (KPIs): 

Larger organizational strategies are evolved through its business goals and objectives. In order to evaluate, monitor, and improve these strategies, special measurements such as KPIs are created. Key performance indicators are the detailed specifications used to track business objectives.

KPIs are created based on business objectives. A business objective is an executive statement of direction in support of a corporate strategy. The business objective is a high-level goal that is quantifiable, measurable, and results-oriented. The business objective is translated into a KPI that enables the organization to measure some aspect of the process against a target that they define.

A KPI is associated with a specific process and is generally represented by a numeric value. A KPI may have a target and allowable margins, or lower and upper limits, forming a range of performance that the process should achieve. A KPI can be thought of as a metric with a target.

A simple KPI example would be: As per historical records, average period taken to clear a shipment is four days. To reduce this period an organization sets the following business objectives:
  • Shipments must be cleared within three days compared to the current average of four days
  • Average duties payment period, to be taken by its finance department, should be two days

The business analyst of the company sets and monitors the following KPIs to evaluate if these two new processes are helping the organization to achieve its business objectives:

Shipment Clearance KPI: Track the shipment arrival date against the clearance date and monitor the average clearance time which should be within the range of three days target.

Duties Payment KPI: Track the average time taken by the finance department to hand over the duties pay order to the clearing agent. This interval is measured by evaluating the difference between shipment arrival date and duties payment date.

Each KPI can have an associated target with margins, or lower and upper limits. The limits and margins indicate a range that the KPI should remain within.

Following are some of the specific uses of KPIs in Oracle BI:
  • Evaluate metrics against their targets and alert the appropriate users via agents when targets are not met.
  • You can create an analysis from a KPI and add it to a dashboard. Such analyses will allow you to drill into different levels of details based on the KPI’s dimensions. For example, to a KPI called Product Imports, you can add the Country and Fiscal dimensions to view Product Imports figures by country and time periods such as year and month. This enables you to gather multi-dimensional subsets of data and evaluate the performance of objectives and initiatives that the KPI measures against the different target values of the dimensional metrics.
  • You can also use KPIs in Oracle Scorecard and Strategy Management to evaluate and monitor the performance of the objectives that form the strategy and the initiatives that are needed to meet the organization's objectives. For example, use KPIs for "Average Customer Satisfaction Survey Score" and "Repeat Orders" to measure the performance of an "Improved Customer Satisfaction" objective.

You can determine a KPI's status by comparing its actual value against the defined thresholds. For visual presentation, you assign status icons to each range that represents the performance status of KPI. For example, to monitor shipment clearance duration, in which low values are desirable, you can define the ranges that are described in the following table.

 Scorecard and Strategy Management: 

Oracle Scorecard and Strategy Management is a performance management tool that lets you describe and communicate your business strategy. You can drive and assess your corporate strategy and performance at all levels of your organization. You use this tool to define objectives and initiatives such as "Decrease Operational Cost" and "Establish Cost Minimization Team" respectively. You then assign KPIs to measure progress and performance. Besides setting targeted numeric ranges to evaluate the values of KPIs, you are able to graphically communicate your organization's strategy using strategy maps, cause & effect maps, custom views, and strategy trees. You can also discuss the implementation and status of objectives and initiatives by adding, reading, and replying to comments. Because you use KPIs in scorecards to measure progress and performance over time, best practice is to include appropriate time dimensions when defining KPIs to be used within scorecards.

Oracle BI Publisher: 

A strategic enterprise reporting product that provides the ability to create and manage highly formatted reports from a wide range of data sources. Oracle BI Publisher is available as part of the Oracle BI Enterprise Edition Suite and also as a standalone reporting tool. Oracle BI Publisher report formats can be designed using Microsoft Word or Adobe Acrobat, the tools that are easy to use, and most users are already familiar with. Oracle BI Publisher also allows you to create reports from different types of data sources such as Oracle Database, Files, BI EE, Web services, and Discoverer.

The latest release of Oracle BI Publisher has enhanced, easy- to-use user interface (UI), many enhanced features, and newly introduced salient features such as - Data Model Editor - a graphical user interface for building data models within the BI Publisher interface; and Layout Editor - a design tool that enables you to create report layouts within the BI Publisher interface. BI Publisher also enables you to schedule reports and deliver the reports to multiple delivery destinations required by your business. Following is the list of tasks that you can perform with BI Publisher:
  • Run and view reports from the BI Publisher reports repository
  • Schedule reports to run at selected intervals and to be delivered via any channel set up by your administrator
  • Create an ad hoc analysis of report data using BI Publisher's Analyzer feature
  • Open and manipulate reports in Microsoft Excel using BI Publisher's Analyzer for Microsoft Excel

Monday, 26 November 2012

Application Development Process in Oracle Business Intelligence Enterprise Edition

Oracle Business Intelligence Enterprise Edition Process Flow

This blog post briefs how applications are developed in Oracle Business Intelligence Enterprise Edition.

Oracle Business Intelligence Enterprise Edition (OBIEE) is one of the best business intelligence solution available on the market today. From simple analyses to professional dashboard creation and from scheduled reporting to proactive alerts, OBIEE offers all the required components that help organizations assess their business proceedings proactively and in real time.

The following illustration depicts OBIEE application development process:

Step 1: You need a SQL client tool such as SQL Developer to create database objects. In the book, Implement Oracle Business Intelligence, we used this tool to create sample tables needed for demo exercises.

Step 2: In this step we connect BI Administration tool to Oracle database. The Administration Tool is a Windows client application that is used to create and modify Oracle BI repository.

Step 3: Here, we create the three layers (Physical, Business Model and Mapping, and Presentation). The Physical layer defines the objects and relationships that is needed by OBIEE server to write native queries against the physical data source. The BMM layer is created to determine the analytic behavior. The Presentation layer carries one or more Subject Areas that presents personalized and secure views of business model to users according to their roles.

Step 4: After creating the desired Subject Areas in the Presentation layer, the repository is saved and uploaded to the BI Server through Oracle Enterprise Manager interface (http://localhost:7001/em). The repository must be uploaded after modification to reflect the changes.

Step 5: Once a repository is uploaded, you can connect to BI Enterprise Edition interface using url http://localhost:9704/analytics. You can use many OBIEE components such as analyses, dashboards, agents, graphs, tables etc. to present data either in generic tables or in graphical formats.

Step 6: In this step you create professional looking reports in Oracle BI Publisher after connecting the data source (in our scenario Oracle Database). Oracle BI Publisher, now available as part of Oracle BI, is a strategic enterprise reporting product to create and manage highly formatted reports from a wide range of data sources including JDBC (Oracle & other databases), BIEE, XML Files, OLAP, JNDI, Fusion Application, HTTP, Web Services, and Discoverer.

BI Publisher has a salient feature called Data Model Editor, a graphical user interface, which enables you to build data models for your reports. It also provides a web-based design tool named Layout Editor to create report layouts within the BI Publisher interface. After creating reports, you have the option to schedule these reports and deliver them to multiple delivery destinations.

Saturday, 3 November 2012

Oracle Business Intelligence Enterprise Edition Book

Table of Content

Chapter 1 - Oracle Business Intelligence - Introduction

1.1 Data and Information
1.2 SQL and its Limitation
1.3 What is Business Intelligence?
1.4 Why Use Business Intelligence?
1.5 What are the Benefits?
1.6 Oracle Business Intelligence
1.7 Oracle BI Repository and BI Server
1.8 Measures, Dimensions and  BI Repository Layers
        1.8.1 Logical Fact Tables
        1.8.2 Logical Dimension Tables
        1.8.3 Identifying Dimensions
        1.8.4 Physical Layer
        1.8.5 Business Model and Mapping Layer
        1.8.6 Presentation Layer
1.9 The Expression Builder
1.10 Oracle BI Interface
1.11 Oracle BI Presentation Catalog
1.12 Oracle BI Components
        1.12.1 Analyses
        1.12.2 Subject Areas
        1.12.4 Dashboards
        1.12.5 Agents
        1.12.6 Alerts
        1.12.7 Conditions
        1.12.8 Actions
        1.12.9 Key Performance Indications (KPI)
        1.12.10 KPI Watchlist
        1.12.11 Scorecard and Strategy Management
        1.12.12 Oracle BI Publisher
1.13 Summary

Chapter 2 - Setup Oracle BI Environment

2.1 Case Study
2.2 Download Software
        2.2.1 Download Oracle Database 11g
        2.2.2 Download Oracle Business Intelligence & RCU
2.3 Install Oracle Database 11g
2.4 Launch Repository Creation Utility (RCU)
2.5 Install Oracle Business Intelligence
2.6 Test Installation
2.7 Create User
2.8 Summary

Chapter 3 - Create Dashboard

3.1 What a dashboard is?
3.2 Components and Features
        3.2.1 SQL Developer
        3.2.2 Oracle BI Server
        3.2.3 BI Administration Tool
        3.2.4 Oracle BI Repository
        3.2.5 Analyses
        3.2.6 Title View
        3.2.7 Table View
        3.2.8 Ticker View
        3.2.9 Graph View
        3.2.10 Gauge View
        3.2.11 Filters
        3.2.12 Dashboard
3.3 Problem Scenario – What are my future payments?
        3.3.1 Create Database User and Connection
        3.3.2 Create Database Tables
        3.3.3 Create Keys and Relationship
        3.3.4 Insert Data
        3.3.5 Create Physical Layer
        3.3.6 Create Business Model & Mapping Layer (BMM)
        3.3.7 Create Presentation Layer
        3.3.8 Load the Repository
        3.3.9 Create Analysis – Payment Maturities
3.4 Problem Scenario - What is the status of in-transit shipments?
        3.4.1 Create Database Tables
        3.4.2 Create Keys and Relationship
        3.4.3 Insert Data
        3.4.4 Update Physical Layer
        3.4.5 Update Business Model & Mapping Layer
        3.4.6 Update Presentation Layer
        3.4.7 Load the Repository
        3.4.8 Create Analysis – Shipment Tracking
3.5 Problem Scenario - What is the Product Category Volume?
        3.5.1 Create Database Tables
        3.5.2 Create Keys and Relationship
        3.5.3 Insert Data
        3.5.4 Incorporate new tables in the Physical Layer
        3.5.5 Move tables to the Business Model & Mapping Layer
        3.5.6 Add tables to the Presentation Layer
        3.5.7 Load the Repository
        3.5.8 Create Analysis – Product Category Volume
3.6 Problem Scenario – What is the Imports Volume?
        3.6.1 Create Analysis – Import Volume
3.7 Problem Scenario – What is the Product Volume?
        3.7.1 Add Data
        3.7.2 Modify the BMM Layer
        3.7.3 Modify the Presentation Layer
        3.7.4 Create Analysis – Product Volume
3.8 Create Dashboard’s Main Page
3.9 Add new dashboard page
        3.9.1 Create Analysis – Shipment Tracking Data
        3.9.2 Create Analysis – Product Category Volume Data
        3.9.3 Create Analysis – Import Volume Data
        3.9.4 Create Analysis – Product Volume Data
        3.9.5 Create a new dashboard page and add analyses
        3.9.6 Create Action Links
3.10 Summary

Chapter 4 - Business Analysis

4.1 About Analyzing Business
4.2 Problem Scenario – What is the business volume with different suppliers?
4.3 Components and Features
        4.3.1 Pivot Table View
        4.3.2 Duplicate Layer
        4.3.3 View Selector
        4.3.4 Conditions
        4.3.5 Report Links
        4.3.6 Preferences
4.4 Update Database
        4.4.1 Add a new column
        4.4.2 Create Keys and Relationship
        4.4.3 Insert Data
4.5 Update Physical Layer
4.6 Update Business Model and Mapping Layer
4.7 Update Presentation Layer
4.8 Load the Repository
4.9 Create Analyses
        4.9.1 Summarized Volume Analysis
        4.9.2 Detail Volume Analysis using Pivot Table
4.10 Add Analyses to a new dashboard page
        4.10.1 Create Condition
        4.10.2 Report Links
        4.10.3 Set Preferences
4.11 Summary

Chapter 5 - Delivering Contents

5.1 About Content Delivery
5.2 Problem Scenario – How to avoid demurrage?
5.3 Components and Features
        5.3.1 Agents
        5.3.2 Alerts
        5.3.3 Calculated Items
        5.3.4 Conditional Formatting
        5.3.5 Email Delivery
5.4 Required Analyses
5.5 Update Table Data
5.6 Recent Arrival Analysis
5.7 Create Agent
        5.7.1 Test the Agent
5.8 Incorporate Data
        5.8.1 Create Cost Table
        5.8.2 Create Keys and Relationship
5.9 Update and Load the Repository
5.10 Previous Arrival Analysis
5.11 Demurrage Paid Analysis
5.12 Send Alert through Email
        5.12.1 Configure SMTP Server
        5.12.2 Configure Email Account
        5.12.3 Add Email Delivery Channel to Agent
5.13 Summary

Chapter 6 - Performance Management

6.1 About Performance Management
6.2 Key Performance Indicators (KPI)
        6.2.1 KPI Editor
6.3 Problem Scenarios
        6.3.1 Shipment Clearance
        6.3.2 Duties Payment
        6.3.3 Product Unit Cost by Supplier
6.4 Update Database Table
6.5 Update Repository
6.6 Cost Comparison KPI – The Pinned Approach
6.7 Cost Comparison KPI – The Not Pinned Approach
6.8 Creating Scorecard
6.9 Shipment Clearance KPI
        6.9.1 Update Database Table
6.10 Duties Payment KPI
6.11 Visual result presentation using Strategy Tree
6.12 Summary

Chapter 7 - Publish Reports

7.1 Significance of Internal Reporting
7.2 Oracle BI Publisher
7.3 Problem Scenario
7.4 Oracle BI Publisher Interface
7.5 Establish Connectivity with Data Source
7.6 Create Data Model
7.7 Create Data Set
7.8 Get XML Output and Save Sample Data
7.9 Generate Report
7.10 Working with Layout Editor
7.11 Report Scheduling
7.12 Add Pivot Table
7.13 Create Template in MS Word
       7.13.1 Install BI Publisher Desktop
        7.13.2 Create Report Template
        7.13.3 Format Report
        7.13.4 Conditional Formatting
        7.13.5 Summary Calculation
        7.13.6 Add a Summary Chart
        7.13.7 Upload Template
        7.13.8 Create Pivot Table
7.14 Generate Purchase Order
        7.14.1 Update Database Tables
        7.14.2 Create Data Model
        7.14.3 Create Purchase Order Template
        7.14.4 Setup Schedule
7.15 Summary


About The Author