Skip Headers

Oracle® Business Intelligence Concepts Guide 10g Release 2 (10.1.2.1) B16378-01 |
| Library | Product | Contents | Index

Previous | Next |

# 3 Example Implementations

This chapter provides two scenarios to show you how to craft a business intelligence system. One scenario uses OLAP for advanced analytics, and the other scenario reports against transactional data.

This chapter contains the following topics:

- Implementing a Business Intelligence Solution

- Global Enterprises: Ad-Hoc Reporting and Advanced Analytics

- Acme Corporation: Web-Based Access to Transactional Data

## Implementing a Business Intelligence Solution

This chapter describes two fictitious companies, with two different data sets and two different sets of requirements. It presents an Oracle Business Intelligence solution for each one, using a set of Oracle Business Intelligence components.

The Global example is based on the Global schema, which is available on the Oracle Business Intelligence Web site at

`http://www.oracle.com/technology/bi/olap/olap.html`

The Acme example is based on the sample schemas provided with Oracle Database. Both solutions follow the same basic steps, which are listed in the next topic.

### Planning a Business Intelligence System

Implementing a business intelligence system requires careful planning to assure that it meets expectations. These are the basic steps:

1. Identify End-User Requirements

2. Identify the Data Sources

3. Design the Data Model

4. Create the Data Store

5. Generate the Summary Data

6. Prepare the Data for Client Access

7. Grant Access Rights

8. Distribute the Client Software and Documentation

9. Create and Distribute Reports

### Identify End-User Requirements

It is important to anticipate how end users will analyze the data. By interviewing key users, you can identify the questions that the business intelligence system needs to answer.

You can ask questions such as:

- What information do you have now?

- What additional information do you need?

- How do you want the information presented?

Business requirements can be generated at all levels of your organization. The following are examples of the requirements you might need to address:

- Board of Directors

  - Competitive analysis

  - Key indicator tracking

  - Trend analysis

  - Exception reporting

- Administrative Analysis and Planning

  - Investment and acquisitions assessment

  - Reorganization analysis

  - Long-range planning

  - Resource allocation

  - Capacity planning

  - Human resource planning

- Finance Department

  - Budgeting

  - Consolidation

  - Variance analysis

  - Financial modeling

  - Cash management

  - Asset liability modeling

  - Activity-based management

- Sales and Marketing Department

  - Product profitability

  - Customer profiling

  - Distribution analysis

  - Sales performance and effectiveness

You can find out about the reports and data sources currently available, and what users like and dislike about their current information system. You may also discover their expectations about run-time performance.

### Identify the Data Sources

From the types of questions that end users want answered, you can identify the sources of the data that can provide the answers. The data can be distributed among numerous locations, such as transactional databases and flat files. If the data is not available within your company, then you should discuss whether it is possible to acquire the data or whether end users must modify their expectations.

### Design the Data Model

The logical data model must support the needs and expectations of your end users. The logical data model presents the data in business terms so that users can quickly identify the data they need to use.

For OLAP tools, you define dimensions, measures, and so forth. Then you can map the metadata objects to the physical data sources.

For relational tools, you define items, calculations, joins, and so forth using any existing relational data source.

### Create the Data Store

You must deploy the data model as physical objects in the database and load the data from its sources.

For OLAP tools, the data store is an analytic workspace.

For relational tools, the data store may be the current OLTP system or a star schema in a data warehouse.

### Generate the Summary Data

Business intelligence data is essentially hierarchical, so that data can be summarized at various levels. For performance, some of this data (ideally the data most frequently queried) is summarized and stored as a data maintenance procedure.

In analytic workspaces, summary data is stored in the same analytic workspace objects as the base-level data. In relational schemas, summary data is stored in materialized views.

### Prepare the Data for Client Access

The client tools query the metadata to find out what data is available, where to get it, and how to present it.

### Grant Access Rights

Users must have database access rights granted to them so that they can view and manipulate the data.

### Distribute the Client Software and Documentation

After the data store is ready for client access, you can distribute the software and provide documentation to your end users.

### Create and Distribute Reports

Report developers can develop reports and share them with the user community. When you use OracleAS Portal, you can easily create dashboards where reports can be published.

## Global Enterprises: Ad-Hoc Reporting and Advanced Analytics

Global Enterprises sells computer hardware and software in a variety of outlets. While they have been industry leaders for many years, the price of hardware has fallen dramatically in the last few years. They have little room for error if they are going to remain profitable.

To create a data warehouse, they need to consolidate information from disparate sources from around the world. This will provide them with the data to answer the following business analysis questions:

- What products are profitable?

- Who are our customers, and what and how are they buying?

- What accounts are most profitable?

- What is the performance of each distribution channel?

- Is there a seasonal variance to the business?

Identifying trends in the data that will answer these questions requires ad-hoc analysis and sophisticated analytic computations, such as:

Change in sales from prior period
Percent change in sales from prior period
Change in sales from prior year
Percent change in sales from prior year
Product share
Channel share
Market share
Extended cost
Extended margin
Extended margin change from prior period
Extended margin percent change from prior period

Global Enterprises uses OracleBI Warehouse Builder to generate a star schema for their data warehouse. Their current problems arise not in the data itself, but in the tools to manipulate that data. They believe that a choice of OracleBI Discoverer Plus OLAP and OracleBI Spreadsheet Add-In will satisfy their sales managers. However, they are also planning to have their IT department take a close look at OracleBI Beans to explore custom solutions to their most aggressive requirements, such as forecasting and what-if analysis.

The applications development team at Global Enterprises will use Analytic Workspace Manager for developing analytic workspaces. After they finish designing the logical model, they plan to turn over responsibility for managing the analytic workspaces to the IT department, who will use OracleBI Warehouse Builder. Until then, the IT department will continue to deploy star schemas.

### Software Requirements

Global Enterprises will use a number of components of Oracle Business Intelligence to implement their BI solution.

#### Components for Delivering Reports

OracleBI Discoverer Viewer
OracleAS Portal with OracleBI Discoverer Portlet Provider
Custom Java applications

#### Components for Creating Reports

OracleBI Discoverer Plus
OracleBI Spreadsheet Add-In

#### Components for Preparing Data

OracleBI Warehouse Builder
Analytic Workspace Manager

#### Component for Developing Custom Applications

JDeveloper with OracleBI Beans

#### Database

Oracle Database 10g Enterprise Edition with the OLAP option

### Getting Started With Analytic Workspaces

Prerequisite: Install the software identified under "Software Requirements".

Global Enterprises has already identified its end-user requirements and the data sources, as described previously. To create an analytic workspace for Global Enterprises, take these remaining steps, which are described in more detail below:

1. Design the Data Model

2. Create the Data Store

3. Generate the Summary Data

4. Prepare the Data for Client Access

5. Grant Access Rights

6. Distribute the Client Software and Documentation

7. Define Advanced Analytics

8. Deploy Custom Applications

#### Design the Data Model

Open the Model View of Analytic Workspace Manager, and define these objects:

- Analytic workspace

- Dimensions

- Levels

- Attributes

- Hierarchies

- Cubes

- Measures

Analytic Workspace Manager stores the logical model as standard form metadata in the analytic workspace, and it creates all of the objects needed to instantiate the model at the same time.

When the source data is in a star or snowflake schema, you can quickly define a logical multidimensional model. The dimension tables contain columns for values at various levels, and their attributes. For example, a Time dimension table might have surrogate keys for weeks, quarters, and years; they are the levels of a hierarchy, which you might name the Calendar hierarchy. The display names, end date, and time span columns are attributes of the Time dimension. Each fact table is a cube, and the columns containing facts are the measures. Other types of schemas require individual analysis.

#### Create the Data Store

To map the logical objects to their data sources, choose the Mapping folder in the Model View Navigator of Analytic Workspace Manager. You can drag-and-drop the source tables onto the mapping canvas, then draw connectors between the logical objects and the appropriate columns. The Maintenance Wizard loads data from relational data sources into the analytic workspace.

#### Generate the Summary Data

You can define the aggregation rules for each cube. The rules identify the aggregation operator for each dimension and the portion of data that you want to presummarize and store. This plan provides the default summarization rules for all measures in the cube. The Maintenance Wizard executes the aggregation rules and generates the stored aggregates.

#### Prepare the Data for Client Access

Using the Calculation Wizard, you can easily define the derived measures for all users, thus adding a wealth of information to your analytic workspace.

OracleBI Discoverer Plus OLAP and OracleBI Spreadsheet Add-In enable users to define additional calculations (custom measures) and saved selections, so they do not have to be defined entirely in the analytic workspace. Discoverer Plus OLAP stores them in the Discoverer Catalog, where they can be accessed by other users with the appropriate permissions.

#### Grant Access Rights

For users to access the data, they require the following database privileges:

`CONNECT`
`QUERY REWRITE`
`SELECT` on the table in which the analytic workspace is stored

You can use Oracle Enterprise Manager or SQL to define users and groups, and to assign these privileges.

#### Distribute the Client Software and Documentation

After the analytic workspace is ready for use, you can make OracleBI Spreadsheet Add-In and OracleBI Discoverer Plus available for installation. You can also set up a dashboard with Discoverer portals so that power users can publish their reports.

#### Define Advanced Analytics

Advanced analytics take development time. You can define forecasts, models, and what-if scenarios in the analytic workspace by using OracleBI Beans. Meanwhile, analysts can take advantage of the rich analytics already available through custom measures.

Check the Oracle Technology Network at `http://www.oracle.com/technology` for new versions of Analytic Workspace Manager Release 2, which will support these definitions in a graphical interface.

#### Deploy Custom Applications

The IT department plans to develop custom Java applications using OracleBI Beans in JDeveloper, in order to tailor the user interface to the specific needs of their user community.

## Acme Corporation: Web-Based Access to Transactional Data

Acme Corporation operates worldwide to fill orders for several different products. The company has several divisions:

- Human Resources tracks information on company employees and facilities.

- Order Entry tracks product inventories and sales of company products through various channels.

- Product Media maintains descriptions and detailed information on each product sold by the company.

- Information Exchange manages shipping through business-to-business (B2B) applications.

Acme wants to provide a flexible, out-of-the-box query and analysis tool to their executives and managers, who are distributed across the globe. The tool must enable them to query vast amounts of data that is stored in their Oracle databases, regardless of the product edition or the schema designs. They expect the queries to range from analysis of individual transactions on a daily basis to historical expenditure profiles for the whole organization. They do not have any requirements for extensive or ad-hoc analysis.

Acme chooses OracleBI Discoverer for its query and analysis tool. The majority of users need only a Web browser to retrieve and review their data in numerous graphical formats, and they can drill and pivot through the data.

### Software Requirements

OracleBI Discoverer provides all of the components needed to satisfy Acme's business intelligence requirements. Since they already have their data stored in Oracle databases, and their immediate goal is analyzing "as is" data, they have no need for an ETL tool at this time. At a later date, they may want to use OracleBI Warehouse Builder to create a true data warehouse containing cleaned, transformed, and aggregated data in a star schema.

#### Components for Delivering Reports

OracleBI Discoverer Viewer
OracleAS Portal with OracleBI Discoverer Portlet Provider

#### Components for Creating Reports

OracleBI Discoverer Plus

#### Component for Preparing Data

OracleBI Discoverer Administrator

#### Database

Oracle Database 10g Enterprise or Standard Edition

### Getting Started With Discoverer

Acme Corporation has already identified its end-user requirements and the data store already exists, as described previously. To implement a Discoverer system for Acme Corporation, you must take these additional steps, which are described in more detail below.

Prerequisite: Install the software identified under "Software Requirements".

1. Design the Data Model

2. Generate the Summary Data

3. Prepare the Data for Client Access

4. Grant Access Rights

5. Distribute the Client Software and Documentation

See Also: Oracle Business Intelligence Discoverer Administration Guide for detailed instructions for performing each of these steps.

#### Design the Data Model

The EUL contains the metadata that defines one or more business areas. A business area is a conceptual grouping of tables and views that apply to a user's specific data requirements. Business areas can be set up to reflect the needs of the user or group of users accessing the EUL.

After identifying the requirements of your users, you should have a good idea of the information different user groups need to access. For example, one group of users might want to access sales information, another group might want to access manufacturing information, and so on.

In OracleBI Discoverer Administrator, you define a business area for classifying information with a common business purpose. Then you specify which database tables and views hold that class of information. You also load metadata and other information about the tables and views into the business area.

#### Generate the Summary Data

Use OracleBI Discoverer Administrator to create summary tables, which will optimize query performance. You also have the option of using OracleBI Discoverer automated summary management or registering existing summary tables.

#### Prepare the Data for Client Access

The default settings and contents of a business area are sufficient to enable users to access and analyze data. However, OracleBI Discoverer Administrator provides you with a number of features to enhance the default analysis capabilities so that users can view the data in the most flexible and understandable way.

These are some of the steps you can take to refine business areas:

- Create optional and mandatory conditions to restrict the number of rows returned in a folder.

- Create calculated items so that users do not need to formulate complex calculations themselves.

- Create joins to combine folders that were not joined when tables were loaded from the database.

- Combine folders into complex folders to completely hide joins and relational structures from users.

- Create custom folders to represent a result set returned by SQL.

- Edit item names, descriptions, and other formatting information to make data easier to understand.

- Create item classes to support lists of values, alternative sorts, and drill to detail.

- Create hierarchies to simplify drill-down operations.

#### Grant Access Rights

You can grant access to business areas based on user requirements for accessing data.

OracleBI Discoverer does not compromise database security. Users cannot see information in OracleBI Discoverer that they do not have database privileges to access. All OracleBI Discoverer security and privileges are imposed in addition to database security.

#### Distribute the Client Software and Documentation

After the EUL has been created and access rights have been granted, you can make OracleBI Discoverer Plus available to users. You can also set up an OracleAS Portal dashboard where power users can publish their reports through OracleBI Discoverer Portlet Provider.