Implement Oracle Business Intelligence

By Riaz Ahmed

Analyze the Past – Streamline the Present – Control the Future

In today's fast paced technology-driven world, successful businesses depend heavily on driving intelligent decisions based on latest information. More informed decisions mean more revenue, less risk, decreased cost, and improved operational control. Up-to-date, secure, and highly visual information is an obligatory in the modern business world to make faster and smarter decisions.

Oracle Business Intelligence helps top management and department heads to harness the advantage of change to create competitive advantages, achieve corporate objectives, and make better and quicker decisions. It delivers a comprehensive set of capabilities that span ad-hoc query and analysis, OLAP analysis, interactive dashboards, scorecards, reporting, proactive intelligence and alerts, mobile analytics, and more.

This book is aimed at giving you insights into running your organization in a more intelligent fashion. It not only presents the BI concepts, but also guides you to implement Oracle Business Intelligence using real world scenarios. It covers:

• Oracle Business Intelligence Introduction
• BI Environment setup
• Practical scenarios to answer business questions using Dashboard, Analysis, Tables, Pivot Table, Graphs and many more BI tools.
• Delivering Contents using Agent and Alert features
• Performance Management using KPI and Scorecard
• Create professional reports in BI Publisher

Oracle Business Intelligence increases speed-to-insight by enabling comparisons of historical, real time, and forward looking information. It thus provides the highest value and lowest TCO than any other BI solution available today.

• Language: English
• Publisher: Riaz Ahmed
• Release date: Oct 20, 2012
• ISBN: 9781301941636

Riaz Ahmed

Riaz Ahmed is an IT professional with over twenty years of hard-earned experience. He started his career as a programmer in early 90's and is currently working as the head of IT with a reputed group of companies. He has been working in Oracle technologies since 1996. Besides Oracle database, his areas of interest are Oracle's web-based development technologies including APEX, JDeveloper, Business Intelligence, and JHeadstart. He has authored couple of books on Oracle technologies. These books are taught in U.S community colleges and IT institutes. His first book, Create Rapid Web Applications Using Oracle Application Express, is listed on Oracle Corporation's web site. The second one, Implement Oracle Business Intelligence (Volume 1), is a hands-on guide written on OBIEE.

Book preview

Implement Oracle Business Intelligence - Volume I

Learn how to dig intelligence from the data mine to take informed decisions using Oracle BIEE

By

Riaz Ahmed

SMASHWORDS EDITION

*****

PUBLISHED BY:

Riaz Ahmed on Smashwords

Implement Oracle Business Intelligence - Volume I

ISBN: 9781301941636

Copyright 2012 Riaz Ahmed

This ebook is licensed for your personal enjoyment only. This eBook may not be re-sold or given away to other people. If you would like to share this book with another person, please purchase an additional copy for each recipient. If you’re reading this book and did not purchase it, or it was not purchased for your use only, then please return to Smashwords.com and purchase your own copy. Thank you for respecting the hard work of this author.

Table Of Contents

Title

Copyright

Preface

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.3 Views

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

Conclusion

About The Author

PREFACE

Analyze the Past – Streamline the Present – Control the Future

In today's fast paced technology-driven world, successful businesses depend heavily on driving intelligent decisions based on latest information. More informed decisions mean more revenue, less risk, decreased cost, and improved operational control. Up-to-date, secure, and highly visual information is an obligatory in the modern business world to make faster and smarter decisions.

Oracle Business Intelligence helps top management and department heads to harness the advantage of change to create competitive advantages, achieve corporate objectives, and make better and quicker decisions. It delivers a comprehensive set of capabilities that span ad-hoc query and analysis, OLAP analysis, interactive dashboards, scorecards, reporting, proactive intelligence and alerts, mobile analytics, and more.

This book is aimed at giving you insights into running your organization in a more intelligent fashion. It not only presents the BI concepts, but also guides you to implement Oracle Business Intelligence using real world scenarios. It covers:

- Oracle Business Intelligence Introduction

- BI Environment setup

- Practical scenarios to answer business questions using Dashboard, Analysis, Tables, - Pivot Table, Graphs and many more BI tools.

- Delivering Contents using Agent and Alert features

- Performance Management using KPI and Scorecard

- Create professional reports in BI Publisher

Oracle Business Intelligence increases speed-to-insight by enabling comparisons of historical, real time, and forward looking information. It thus provides the highest value and lowest TCO than any other BI solution available today.

Chapter 1 - Oracle Business Intelligence - Introduction

1.1 Data and Information

The words data and information are very frequently used in our daily lives and are often used interchangeably. However, there are many differences between the meanings and use of these two words.

Data are facts that people collect through their observations and experiences while information are interpreted facts based on the gathered data. Data refers to the lowest abstract or a raw input which when processed or arranged generates information. Information is obtained through analysis, communication, or investigation. As an example, if you have a list on the top ten travel sites, that is data; if you have a book giving details about each site, it is information.

Any kind of understanding or knowledge that can be exchanged with people is information. It can be about facts, things, concepts, or anything relevant to the topic concerned.

Data can be in the form of numbers, characters, symbols, or even pictures. When it exists in this raw form, the data has no significance. It gains importance when it is arranged into something meaningful. A collection of these data which conveys some meaningful idea is information. It may provide answers to questions like who, which, when, why, what, and how.

1.2 SQL and its Limitations

Structured Query Language (SQL), is a specialized language for managing data in relational database management systems (RDBMS).

You can use simple SQL queries to extract information from the data source. In complicated situations where data is stored in different sources and in different formats, you need to issue complex analytical SQL queries to extort the information.

This primary tool has been the main source for extracting information from data for decades. But when it comes to answer business analysis questions, SQL comes up with the following drawbacks:

1. Writing SQL statements is too hard to review business processes

2. It is not designed to access unstructured or semi-structured data.

Business Intelligence tools emerged on the market due to the above mentioned SQL limitations. These tools enable better decision-making by simplifying the process of business analysis and data publishing stored in any source and in any format.

To explain further, suppose your company imports goods from abroad and the Imports department head wants to know the number of orders placed in the previous year, this query might be as simple as the following:

Select count(*) from Orders_Master where extract(year from order_date)=2012

For simple questions, you do not need any Business Intelligence tools. By issuing a simple SQL query like the one above, you can derive the required information with ease. But not all business questions can be answered that easily. In situations where you have to deal with large dataset and disparate data sources, you have to get assistance from a mature Business Intelligence tool such as Oracle Business Intelligence.

For example, to find the top three salespersons for a given year may require you to write a complex SQL query like this one:

SELECT emp.ename salesperson, top_emp_orders.sales sales FROM

(SELECT all_orders.sales_empno empno, all_orders.total_sales FROM

(SELECT sales_empno, SUM(ord_total) total_sales, RANK() OVER (ORDER BY SUM(ord_total) DESC) sal_rank FROM orders

WHERE EXTRACT(YEAR FROM ord_dt) = 2012

GROUP BY sales_empno)all_orders

WHERE all_orders.sal_rank <= 3) top_emp_orders, employees emp

WHERE top_emp_orders.empno = emp.empno

ORDER BY sales DESC;

The above query might produce the following output:

SALESPERSON - SALES

Kevin Peterson - 450000

Matt Prior - 350000

Andrew Strauss - 250000

Writing and debugging these types of SQL queries can take several hours and probably days if you have many of them. The above example clearly indicates that it is often not feasible to get business answers using SQL queries. But, even then, SQL has its own significance in Business Intelligence where it provides some advanced features to summarize data over multiple tables and is used behind the scenes totally transparent to users.

1.3 What is Business Intelligence?

Business intelligence can simply be defined as transformation of data into information and then into intelligence. It provides you the power to get significant data in time, and in an efficient manner. The main objective of business intelligence is to allow you to make high quality decisions to achieve more opportunities. Many factors, such as internal business processes, economic environment, customers, business partners and competitors help in making such decisions. These major assessments, not only directs a company towards the success milestone but also helps a lot in improving its performance. It is business intelligence that will provide you a competitive edge over your competitors and will allow you get an insight into your company’s processes to evaluate whether it needs improvements.

Today, businesses use BI tools and systems in their strategic planning to analyze their operations. The major advantage of using these solutions is that they provide a single source to analyze a company’s disparate data sources without any technical assistance.

Common business intelligence functions include, but are not limited to, the following:

- Understand your business by identifying areas of strength and weaknesses

- Exploit areas of opportunity

- Address underperforming regions

- Comprehend results of a business decision

- Identify areas of business growth

1.4 Why Use Business Intelligence?

The following list provides answer to the above question.

- Data analysis

- Verify the organization’s health by checking its strength and weaknesses

- Provides an atmosphere of collaboration to share knowledge

- Quickly assess the performance of a scheme and take quick decisions

- Provides key performance indicators (KPI)

- Enhances competitive advantage

1.5 What are the Benefits?

- Get instant information through high performance queries

- Obtain interactive analysis over large volumes of data

- Drill down on your information

- Fetch information from multiple data sources

- No need to be a reporting guru

- No impact on the performance of live applications

1.6 Oracle Business Intelligence

Oracle Business Intelligence is a comprehensive set of enterprise business intelligence tools and infrastructure, including a scalable and efficient query and analysis server, an ad-hoc query and analysis tool, interactive dashboards, proactive intelligence and alerts, real-time predictive intelligence, and an enterprise reporting engine. Oracle Business Intelligence is designed to bring greater business visibility and insight to a wide variety of users. It provides a full range of business intelligence capabilities that allow you to:

- Collect up-to-date data from your organization

- Present the data in easy-to-understand formats (such as tables and graphs)

- Deliver data in a timely fashion to the employees in your organization

These capabilities enable your organization to make better decisions, take informed actions, and implement more-efficient business processes.

1.7 Oracle BI Repository and BI

[tmaora-1 · Rating: 5 out of 5 stars]

Analyze the Past – Streamline the Present – Control the FutureIn today's fast paced technology-driven world, successful businesses depend heavily on driving intelligent decisions based on latest information. More informed decisions mean more revenue, less risk, decreased cost, and improved operational control. Up-to-date, secure, and highly visual information is an obligatory in the modern business world to make faster and smarter decisions.Oracle Business Intelligence helps top management and department heads to harness the advantage of change to create competitive advantages, achieve corporate objectives, and make better and quicker decisions. It delivers a comprehensive set of capabilities that span ad-hoc query and analysis, OLAP analysis, interactive dashboards, scorecards, reporting, proactive intelligence and alerts, mobile analytics, and more.This book is aimed at giving you insights into running your organization in a more intelligent fashion. It not only presents the BI concepts, but also guides you to implement Oracle Business Intelligence using real world scenarios. It covers:•Oracle Business Intelligence Introduction•BI Environment setup•Practical scenarios to answer business questions using Dashboard, Analysis, Tables, Pivot Table, Graphs and many more BI tools. •Delivering Contents using Agent and Alert features•Performance Management using KPI and Scorecard•Create professional reports in BI PublisherOracle Business Intelligence increases speed-to-insight by enabling comparisons of historical, real time, and forward looking information. It thus provides the highest value and lowest TCO than any other BI solution available today.