Skip Headers
Oracle® Business Intelligence Concepts Guide
10g Release 2 (
  Go To Documentation Library
Go To Product List
Solution Area
Go To Table Of Contents
Go To Index


2 Technical Fundamentals for Implementation

Implementing a business intelligence solution involves a number of people across the organization, from information technology (IT) professionals to executives and line of business managers. Often, however, it falls to the IT professionals to design and implement the technical aspects of the BI solution. This chapter is addressed to these IT professionals.

This chapter describes how Oracle Business Intelligence supports the implementation of a business intelligence solution. It includes the following sections:

Consolidating Data for Analysis

While you can use Oracle Business Intelligence to report directly against a transactional system, most business intelligence requires historical data and data from diverse sources, and that data must be structured for analysis. A data warehouse is the foundation of a business intelligence solution.

OracleBI Warehouse Builder supports the process of designing and developing a data warehouse, from identifying source data to the creation of metadata for reporting. OracleBI Warehouse Builder takes advantage of the powerful data warehousing features in Oracle Database. In addition, the interface in OracleBI Warehouse Builder helps your team to consolidate data for reporting and to maintain the data warehouse after it has been deployed.

Data Sources

A data warehouse usually consolidates data from a number of sources.

Operational Data

Operational data is the main source of data for reporting. This data comes from your organization's business transactions, which are normally stored in one or more relational systems. Your transactional data may be in Oracle Database, as well as other systems. For example, your current data might come from Oracle Database, but perhaps you have historical data in a legacy system. Or perhaps your organization has recently acquired a company whose data is stored in a non-Oracle database.

OracleBI Warehouse Builder can read metadata from a number of different database sources, including:

  • Oracle databases

  • SAP R/3

  • DB2, Sybase, Informix, SQL Server, and others, through Oracle Transparent Gateways

  • ODBC

  • Mainframe systems

External Data

Your business intelligence system is likely to need data other than your own operational data. For example, to support the kind of analysis you want, you might have demographic data from a third party vendor. This data might come through ODBC, in flat files, or in spreadsheet files.

In OracleBI Warehouse Builder, you can use external tables to represent flat files in relational form. External tables are read-only Oracle Database tables that behave like regular source tables in OracleBI Warehouse Builder. The metadata for these tables is stored in the OracleBI Warehouse Builder repository and can be used when you design the transformation of the source data. OracleBI Warehouse Builder also allows you to incorporate spreadsheet data in your data warehouse.

Extraction, Transformation, and Loading

As you combine data from disparate sources, you design and implement the extraction, transformation, and loading (ETL) of the source data into a target data warehouse.

During extraction, the desired data is identified and extracted from various sources. Often you extract more than you will eventually need, because it is hard to identify precisely the data that you will actually use. As you extract data, database scalability is an issue. Because data warehouses store historical data, they grow very fast. Oracle Data Warehousing includes a number of scalability features, and OracleBI Warehouse Builder leverages these features.

Because a data warehouse consolidates data from disparate sources, it must be transformed to provide accurate, consistent data for reporting. OracleBI Warehouse Builder provides a graphical environment for modeling transformations, making it easy to map source data to target data. OracleBI Warehouse Builder includes a solution for name and address cleansing. This solution automatically corrects common errors and inconsistencies in name and address data. OracleBI Warehouse Builder also includes a match merge operator that uses business rules to consolidate duplicate records in the source data.

Extracted and transformed data is loaded into the target data warehouse. You can create target warehouse objects in OracleBI Warehouse Builder. The target objects can be relational or dimensional, as described in "Metadata Models for Data Access".

In OracleBI Warehouse Builder, a mapping describes a series of ETL operations. You define the operations when you create mappings. You can use the Mapping Editor in OracleBI Warehouse Builder to define these operations graphically, or you can use a scripting interface to create mappings.

Figure 2-1 shows the Mapping Editor canvas, which defines how the target Sales table will be populated with the joined and transformed data from two source tables.

Figure 2-1 Mapping Editor in OracleBI Warehouse Builder

Description of Figure 2-1  follows
Description of "Figure 2-1 Mapping Editor in OracleBI Warehouse Builder"

Along with mappings, you create process flows in OracleBI Warehouse Builder. Process flows specify when mappings run and the actions that happen after a mapping runs successfully or fails. For example, you can send e-mail notifications and run FTP commands and operating system executables.

More Information

For more information about data warehousing, see the Oracle Data Warehousing Guide. For more information about OracleBI Warehouse Builder features, see the Oracle Warehouse Builder User's Guide.

Data Models

The systems that store transactional data are designed for collecting data. Business intelligence is designed for using and understanding that data. The data model for BI is different from source model data. This section contrasts the two models.

Transactional Model

The transactional model, which is used in online transaction processing (OLTP) systems, is built around relational tables, usually in third normal form (3NF).

OLTP systems are tuned for storing and processing transactions. These systems need to be able to handle easily the addition, removal, and changing of individual records. The system is normalized, to ensure that the database can be updated efficiently and accurately. The transactional model is for collecting data.

Dimensional Model

The dimensional model is designed not for collecting data, but for retrieving data in a meaningful way. The goal of the dimensional model is to make the data understandable to business users. There is a dimensional aspect to the way users think about data. For example, a sales manager might say, "I want to see sales revenues by product, region, and quarter." The dimensional model seeks mimic the way that users think about business.

Dimensional data can be stored in relational tables or in multidimensional cubes, as described in the "Data Structures" section, but the concepts are the same.

Facts or Measures

The data that users want to examine and analyze, such as Sales, Costs, and Profit, is presented as facts or measures, depending on the physical structure of the data. This data is usually numeric.


Dimensions are categorizations of data, in the way business users think of it. When users say they want to see numbers "by" something or "over" something, they are identifying the dimensions of the data. Common dimensions are Geography, Product, Customer, and Time.

Individual geographical locations, products, customers, and times are called dimension values or dimension members.


Dimensions are often structured hierarchically, so that some values are grouped "under" other values. For example, cities can be grouped under countries, and countries under continents. A dimension can have more than one hierarchy. For example, a Time dimension might have a calendar hierarchy and a fiscal hierarchy.

Hierarchies can define how data is aggregated and how users can navigate data in an interactive report. For example, a hierarchy in a Time dimension can specify whether users can drill down from years to quarters or from years to months.


Hierarchies often have several levels. For example, a Product hierarchy might have a total level, a category level, a subcategory level, a family level, and an item level. A Time hierarchy might have levels for days, weeks, months, quarters, and years.

Levels are more significant in some hierarchies than in others. For example, in an Employee dimension, some individual contributors might have only one or two layers of management, while others might have several layers. There is not a strong grouping of all employees at a certain level.

Data Structures

Oracle Business Intelligence supports different data structures for reporting. You can report against transactional data, but analysis usually involves dimensional data. This section describes structures that are used to store transactional and dimensional data.

Relational Tables in Third Normal Form

Transactional data is stored in relational tables. Each table represents a relational entity, such as customers, products, cash registers, or transactions. The attributes of the entities are stored in table columns, and rows store individual records. For example, a customer table would have the customer name and the customer phone number in separate columns. Each row stores data for a different customer.

Key attributes make it possible to join data from separate tables. For example, a customer ID can be used to join data from the customer table with data from a transaction table.

Online transactional processing (OLTP) systems store data in these relational tables. Data warehouses can include cleansed versions of transactional data as base tables, in third normal form (3NF). These tables store detail data.

This detail data is not updated directly in the data warehouse. Instead, changes to the detail data are incorporated through ETL code. The changes appear when the warehouse is refreshed from the OLTP system.

Third normal form is a good form for ensuring data integrity for a transactional system, where data is updated often. However, 3NF is not well suited for data that is frequently queried or queried in a complex way. Because business intelligence systems are tuned for data retrieval, they often have a number of denormalized tables or views. Denormalized tables combine information that is most often needed together, to reduce the number of joins that are required for a query.

Star Schemas

A star schema is a relational implementation of the dimensional model. Dimension tables hold dimension values, with attributes in each column of the table. A fact table contains fact data, such as Sales and Cost data. It has a foreign key for each dimension, and the composite of all the foreign keys is the primary key of the fact table. Figure 2-2 shows the structure of a star schema.

Fact tables often contain detailed data. Summary data is often stored in denormalized summary tables or materialized views. As with any relational data, indexes provide faster access into the tables.

Star schemas are sometimes called data marts, and building star schemas is a common task in the creation of a data warehouse.

You can create star schemas in OracleBI Warehouse Builder.

Analytic Workspaces

An analytic workspace structures a multidimensional implementation of the dimensional model. In this multidimensional structure, dimensions form the edges of a cube, which contains the data as measures. Figure 2-3 shows the structure of a cube.

Cubes store detailed data and summary data, so they are said to be presolved or precalculated. A cube is a set of measures that have the same dimensions and aggregation plan.

In an analytic workspace, dimensions act as indexes into the measures, so there is no need to build separate indexes into the data. Because dimensions and measures are in the same structure, there is no need for joins in an analytic workspace.

You use Analytic Workspace Manager or OracleBI Warehouse Builder to create analytic workspaces. You define the structure of cubes, and you specify the source data for the cubes. You can use star schemas as the source for analytic workspaces, but you can also use other relational tables, flat files, and other sources to populate analytic workspaces.

Data Structure and Preparation for Data Mining

When you use Oracle Data Mining to find hidden patterns in data and to perform predictive analytics, you need a single relational table or view that has all of the attributes that will be considered in data mining analysis. You can use OracleBI Warehouse Builder to create this table, as you would any other table.

In the data mining table, each case is represented by one row in the table, and each attribute is represented by a column in the table. The data in this table must have one of the data types supported by Oracle Data Mining. Supported types include nested columns and text, as well as standard data types such as INTEGER, FLOAT, NUMBER, VARCHAR32, and CHAR.

In addition, data mining algorithms often require data preparation before the algorithm is invoked. Oracle Data Mining includes transformations that perform the following data-mining-specific transformations:

  • Managing extreme values (such as the top 5% of the values), either by setting them all to a less extreme value (such as the value at the 95th percentile) or by removing them

  • Grouping related values, to reduce the number of distinct values for an attribute

  • Normalization to convert attribute values to a common range, so that values do not receive inappropriate weighting because of different ranges

The preparation necessary for a data mining application depends on the kinds of models that will be applied to the data.

For more information about preparing data for data mining, see Oracle Data Mining Concepts or the Oracle Data Mining Application Developer's Guide.

Metadata Models for Data Access

It is not enough to gather the data that you need in a data warehouse. You also need to prepare the data for reporting, by providing metadata that allows users to understand and navigate the data, and by summarizing the data.

Metadata management is a crucial activity in BI development. If report builders cannot understand and navigate the data, then they cannot query the data appropriately. Even if the data warehouse contains clean, high-quality data, without good metadata, it is very difficult to know which data structures to use for queries. Two different business users can ask the same question of the data and come up with different answers.

Business intelligence uses summary, or aggregate, data, to provide optimal query performance for business users. You decide which data to summarize and how to store the summarized data. The needs of the business users should determine which data you decide to summarize.

The structure of the summarized data depends on the metadata model that you use for a report. When you use a relational data warehouse, you can store summary data in materialized views, usually separate from the detail data. When you use Oracle OLAP, you can store summary data in an analytic workspace, which also includes the data that it summarizes.

OracleBI Warehouse Builder provides support for creating and maintaining metadata and for creating summaries. Also, Analytic Workspace Manager supports the creation of analytic workspaces, which include summary data. For relational reporting, OracleBI Discoverer Administrator supports the creation of metadata and the management of summary data.

Relational Metadata

When you use OracleBI Discoverer Plus Relational or OracleBI Discoverer Viewer against relational data, you use relational metadata to access the data, which can be stored in star schemas, 3NF tables, or other relational tables. A Discoverer manager creates an End User Layer (EUL) for this metadata.

The EUL insulates users from database complexity. It contains business areas, which group folders for particular sets of users. Folders in the business areas represent result sets for building reports. A folder can represent a table, a set of tables, or a query. Items in the folders represent the columns in the result set.

The EUL may also contain:

  • Hierarchies for drilling navigation

  • Joins that connect tables or views

  • Conditions that filter data for reports

  • Calculations created for business users

You use OracleBI Discoverer Administrator to create and maintain EULs and to control access to different business areas. You can also create relational metadata in OracleBI Warehouse Builder and export it to an EUL that OracleBI Discoverer Administrator can read.

For more information about creating and maintaining EULs, see the Oracle Business Intelligence Discoverer Administration Guide.

OLAP Metadata

When you use OracleBI Discoverer Plus OLAP, the OracleBI Spreadsheet Add-In, or a custom application that includes OracleBI Beans, you use OLAP metadata to access dimensional data, which is normally stored in analytic workspaces. In addition to the dimensional data, analytic workspaces store OLAP metadata, where you define the dimensions and the measures that are available to users. A cube is a set of measures that have the same dimensions and aggregation plan.

OLAP metadata can also include:

  • Hierarchies, both for aggregation and for drill navigation

  • Saved selections that filter the data in a report

  • Calculations for business users

As noted in "Data Structures", analytic workspaces do not require joins.

You use OracleBI Warehouse Builder or Analytic Workspace Manager to create analytic workspaces.

When dimensional data is in a star schema, you can store OLAP metadata in the OLAP Catalog. You use OracleBI Warehouse Builder to create this metadata. Most business intelligence systems that use OLAP metadata use analytic workspaces, both for better performance and for more analytic capability.

For more information about analytic workspaces and the OLAP catalog, see the Oracle OLAP Application Developer's Guide.

Key Differences Between Relational and OLAP Metadata Models

Oracle Business Intelligence enables access to data through relational metadata and through OLAP metadata. The model that you choose depends on a number of factors. This section highlights basic differences between the two models and some of the implications of those differences.


In the relational model, hierarchies are very flexible. Any attribute in a relational table or view can serve as any level in a hierarchy. Values in the hierarchy can have more than one parent, so it is possible to have a many-to-many (M:M) relationship between hierarchy levels. For example, you can allow users to drill from a year to quarter as shown in Figure 2-4.

Figure 2-4 Years Drilled to Quarters

Description of Figure 2-4  follows
Description of "Figure 2-4 Years Drilled to Quarters"

And, in a relational hierarchy, you can also allow users to drill from quarters to years, as shown in Figure 2-5.

Figure 2-5 Quarters Drilled to Years

Description of Figure 2-5  follows
Description of "Figure 2-5 Quarters Drilled to Years"

This hierarchy flexibility allows you to place attributes anywhere in a crosstab report. For example, you can create a report like that in Figure 2-6.

Figure 2-6 Example of Relational Hierarchy Display

Description of Figure 2-6  follows
Description of "Figure 2-6 Example of Relational Hierarchy Display"

In the OLAP model, hierarchies are much more tightly structured. In an OLAP hierarchy, all levels must be in the same dimension, and each dimension value can have only one parent in a hierarchy, though you can define more than one hierarchy for a dimension. A parent represents an aggregation of its children. OLAP hierarchies allow only one-to-many (1:M) relationships.

The structured hierarchies in the OLAP model restrict the placement of hierarchy levels. All Time levels, for example, must be on one edge of a crosstab. You can create a report like Figure 2-4, but you cannot create reports like those shown in Figure 2-5 and Figure 2-6 when you use the OLAP model. And you cannot drill from a Time level to a Geography level and then to another Time level.

Strict OLAP hierarchies enable the OLAP engine to perform powerful analytic functions efficiently. OLAP hierarchies are especially well suited for time-series analysis and share calculations.


Data aggregation can occur as part of a query, or data can be pre-aggregated. Because aggregation takes time, where the aggregation happens has effects on load time and on query time. Pre-aggregation lengthens the time it takes to populate data structures, but it reduces the time it takes for a query to return data. Pre-aggregation also requires space for storing aggregated data.

In the relational model, pre-aggregated data is stored in summary tables and materialized views. Because relational hierarchies are very flexible, the potential for different kinds of summarization is nearly limitless. In practice, a pre-aggregated summary table or materialized view often supports a particular query or group of queries. You can arrange for automatic summary management, through Oracle Database or through OracleBI Discoverer Administrator. With automatic summary management, summaries are automatically created, based on the most frequently run queries.

In the OLAP model, where hierarchies are less flexible, there are fewer potential summarizations, and these potential summarizations are all built into the analytic workspace. In the aggregation plan, you determine how much aggregation is stored in the analytic workspace and how much will be performed at query time. To the reporting tool, the analytic workspace appears to be pre-aggregated. Most of the time, OLAP implementations use fully pre-aggregated analytic workspaces.

Oracle continues to work to improve both load performance and query performance, so you might want to try different configurations and test them to see what the performance trade-offs are.

The other effect of aggregation differences is less obvious. It has to do with the display of hierarchical data in a report. In the relational model, because aggregation happens as part of the query, you can filter the data and then aggregate the results based only on filtered data.

Table 2-1 shows the results of a query in which data is filtered and then aggregated. Sales totals for selected cities are aggregated into region values. The Eastern US Sales value ($35,000) is the sum of the Sales values of the selected cities in the Eastern US region: New York City ($20,000) and Atlanta ($15,000). Likewise, the Central US Sales value ($40,000) is the sum of the Sales values for selected cities in the Central US region: Chicago ($18,000), Houston ($9,000), and Kansas City ($13,000).

Table 2-1 Example of Filtering Before Aggregation

Region and City Sales

Eastern US


 New York City




Central US






 Kansas City


With pre-aggregated data in the OLAP model, a data value at a certain level in a hierarchy is always the total of the values for all of the children at that level, regardless of which children appear in the report. Table 2-2 shows the result of the same kind of query on pre-aggregated data. In this table, Eastern US Sales is pre-aggregated as the total of Sales for all cities in the region, including, for example, Boston, Philadelphia, Baltimore, and so on. Likewise, the total for Central US also includes Sales values for cities that are not displayed in the report.

Table 2-2 Example of Aggregation Before Filtering

Region and City Sales

Eastern US


 New York City




Central US






 Kansas City


For a report in OracleBI Discoverer Plus Relational, it is possible to write a SQL query so that data is aggregated first and then filtered, for a static report, where users will not drill or sort the data. For a report in OracleBI Discoverer Plus OLAP, you can add rows or columns for subtotals of only the dimension values that appear in the report, and users can rotate the report and drill in it.


In the relational model, calculations, like aggregation, are defined in the query. You can store the result of a calculation in a summary table or materialized view. A Discoverer manager can create calculations in the EUL, to make calculations available to multiple users. Those who build reports can also create calculations in OracleBI Discoverer Plus Relational.

In the OLAP model, a calculation is a measure and is treated like any other measure. You create calculations as you would other measures. Those who build reports can also create calculations in OracleBI Discoverer Plus OLAP, in OracleBI Spreadsheet Add-In, and in applications that are built with OracleBI Beans.

Advanced calculations, such as time-series analysis and share calculations, especially, are simpler to create in the OLAP model and faster to execute. In the relational model, these calculations require multiple passes through the data.

The OLAP engine also supports sophisticated modeling, for what-if analysis, forecasting, and allocations.

Models for Data Mining

Oracle Data Mining lets you analyze large amounts of data to find hidden patterns in the data and to gain new insights from the data. With Oracle Data Mining, your business users can find characteristics of the most profitable customers, detect fraud, predict which customers are likely to churn, and so on.

With Oracle Data Mining, data analysts and application developers can create data mining models to perform particular analyses. Different analyses may require different algorithms, which produce different data mining models. These models contain patterns that were found in data or that predict likely outcomes for data.

Developing a model is an iterative process. Even after the model is deployed, it may need to be rebuilt with new or additional data.

Oracle Data Mining supports the following data mining functions:

Oracle Data Miner makes it easier to create data models and data mining applications. With Oracle Data Miner, wizards provide a guide through data preparation, model creation and evaluation, and model scoring. Oracle Data Miner relieves analysts from having to learn SQL or Java in order to create data mining models. Mining activity guides walk analysts through the data mining process.

Tools for Creating Reports

The choice of tools for creating reports has a big impact on the success of a business intelligence implementation. You must have tools that are powerful enough to make the most sophisticated reports that business users need. You also want tools that make it possible for business users to create reports for themselves.

Oracle Business Intelligence includes a variety of tools for creating reports, from the most sophisticated enterprise reports to simple worksheets that anyone can do. This section describes these tools.

Oracle Reports

Oracle Reports is a powerful enterprise reporting tool that enables you to develop and deploy high quality reports, from a number of data sources and to a variety of destinations. You can use Oracle Reports to create a wide range of reports for the full range of business activities. For example, you can use Oracle Reports to create invoices, form letters, packing slips, shipping labels, financial statements, sales reports, and high quality presentations such as annual reports.

Reports Builder is the development component of Oracle Reports. It allows report specialists fine control over report format and output, and it supports a wide variety of data sources as input. Reports Builder includes access to virtually any data format, through pluggable data sources (PDSs), such as JDBC and XML.

Reports Builder provides many report objects, such as tables in different formats, graphs, text, images, bar codes, and so on. An editor allows report specialists to modify paper report layouts in a WYSIWYG model. Wizards and default report templates enable quick report creation, yet the templates are customizable, for maximum flexibility.

Oracle Reports supports many output formats, including HTML, HTMLCSS, XML, PDF, PCL, PostScript, and ASCII, so you can deliver reports however users want them.

Who Uses Oracle Reports

Oracle Reports is a powerful reporting tool. Most often, a report specialist in the IT department is responsible for creating and maintaining reports with Oracle Reports and for delivering them to business users.

Source Data for Oracle Reports

Oracle Reports can display data from any data source. Although Oracle Reports started with relational data, because of its pluggable data sources (PDSs), Oracle Reports can access data from different sources, including OLAP, JDBC, and XML. For data that requires special access mechanisms, you can create your own pluggable data source.

Data Mining results are stored in a relational table, so they are easily displayed in Oracle Reports.

More Information

For more information about building reports with Oracle Reports, see Oracle Reports Building Reports.

OracleBI Discoverer Plus

OracleBI Discoverer Plus is an intuitive data analysis and reporting tool. OracleBI Discoverer Plus makes it easy for users to analyze their data and share it with others in the organization. Reports that are created with OracleBI Discoverer Plus can easily be viewed in a Web browser, either in OracleBI Discoverer Viewer or as a portlet in an OracleAS Portal dashboard. OracleBI Discoverer Plus reports can also be easily printed or exported to Microsoft Excel.

OracleBI Discoverer Plus reports, also known as worksheets, are organized in workbooks, which users can share. Worksheets can include tables or crosstabs and graphs, as well as titles and text areas for explanation.

An interactive model allows direct manipulation of the data, for changing the layout or the contents of a report. Report builders and recipients can drill, pivot, and sort the data, enabling the slice and dice capability that users expect from business intelligence. Wizards guide new users in query creation and report building tasks.

OracleBI Discoverer Plus includes powerful analytic features, such as the ability to apply conditional formatting to the data, including stoplight formats. With OracleBI Discoverer Plus, you can display data numerically and graphically in the same worksheet. A table and a graph, or a crosstab and a graph, share the same query. Alternately, you can choose to display only the numeric form or only the graph.

In OracleBI Discoverer Plus Relational, report specialists can add parameters to reports, so the same report can be used by different customers, who set parameters to choose data of interest to them.

In OracleBI Discoverer Plus OLAP, users have access to the OLAP engine, which supports sophisticated time-series analysis, share calculations, and so on.

The user interface is very similar, whether you use OracleBI Discoverer Plus Relational or OracleBI Discoverer Plus OLAP.

Figure 2-7 shows a report generated by Discoverer Plus Relational. This report uses a pie graph and a table, side by side.

Figure 2-7 Sample Table and Pie Graph in Discoverer Plus Relational

Description of Figure 2-7  follows
Description of "Figure 2-7 Sample Table and Pie Graph in Discoverer Plus Relational"

Figure 2-8 shows a sample report generated by Discoverer Plus OLAP. This report has a crosstab with a graph below it.

Figure 2-8 Sample Crosstab and Bar Graph in Discoverer Plus OLAP

Description of Figure 2-8  follows
Description of "Figure 2-8 Sample Crosstab and Bar Graph in Discoverer Plus OLAP"

Who Uses OracleBI Discoverer Plus

OracleBI Discoverer Plus is powerful enough for report specialists to use to create sophisticated analytic reports for others, but it is also easy to use, so business users can create their own reports as well. An intuitive user interface allows business users to select and arrange data and to apply conditional formatting, including stoplight reports.

Source Data for OracleBI Discoverer Plus

OracleBI Discoverer Plus Relational uses metadata provided in an End User Layer (EUL) to display data from relational tables, including star schemas.

OracleBI Discoverer Plus OLAP normally displays data from an analytic workspace, though it can also display data from star schemas, through metadata in an OLAP Catalog.

More Information

For more information about OracleBI Discoverer Plus, see the Oracle Business Intelligence Discoverer Plus User's Guide.

OracleBI Spreadsheet Add-In

OracleBI Spreadsheet Add-In enables analysts to work with dimensional data in Microsoft Excel. The Add-In fetches data through an active connection to an analytic workspace and displays the data in a spreadsheet. Analysts can use the Add-In to perform OLAP operations such as drilling, rotation, and data selection.

With OracleBI Spreadsheet Add-In, calculations can be defined in the analytic workspace and shared across user communities. OLAP calculations are performed quickly and efficiently in the database, and they do not require massive downloads of data to Excel.

Figure 2-9 shows Oracle data in an Excel spreadsheet. Notice the addition of the OracleBI menu to the menu bar.

Figure 2-9 Oracle Data Displayed in an Excel Spreadsheet

Description of Figure 2-9  follows
Description of "Figure 2-9 Oracle Data Displayed in an Excel Spreadsheet"

OracleBI Spreadsheet Add-In uses the same Query Wizard and Calculation Wizard as OracleBI Discoverer Plus OLAP. Analysts can use these wizards to select data from lists or to select exception data. They can also create custom measures.

Analysts can also treat Oracle data like regular spreadsheet data. For example, they can create formulas and graphs in Excel, thereby combining the powerful analytic capabilities of Oracle OLAP with standard Excel calculations and formatting.

Who Uses OracleBI Spreadsheet Add-In

OracleBI Spreadsheet Add-In is easy to use by anyone who is familiar with Excel.

Source Data for OracleBI Spreadsheet Add-In

OracleBI Spreadsheet Add-In displays data from analytic workspaces. It requires Oracle OLAP.

More Information

For more information, see OracleBI Spreadsheet Add-In Help from within Excel.

Oracle Spreadsheet Add-In for Predictive Analytics

Oracle Spreadsheet Add-In for Predictive Analytics provides analysts with automated data mining in Microsoft Excel, through a simplified user interface. Business users can mine Oracle or Excel data, using simple Predict and Explain features. Predictive analytics provides automated methodologies that simplify data mining.

The Explain feature allows users to understand their data better, by identifying attributes that have the greatest influence on a target attribute, such as "high value customers." Explain searches the data for these attributes, ranks them in order, and displays the results in Excel. Explain can be used to identify attributes most closely associated with customers of high value, for example, or customers who switch to a competitor.

The Predict feature allows users to make predictions based on observed data and hidden patterns in the data. Predict digs through data, builds a predictive model, and applies that model to the data. Predict makes predictions about whether a record in a table or Excel spreadsheet belongs to a certain class, such as customers who are likely to purchase a product. Predict could also be used to predict which customers are likely to have the highest lifetime value to an organization, for example, or which people are most likely to commit fraud.

Who Uses Oracle Spreadsheet Add-In for Predictive Analytics

Oracle Spreadsheet Add-In for Predictive Analytics is generally used by business analysts.

Data Sources for Oracle Spreadsheet Add-In for Predictive Analytics

Oracle Spreadsheet Add-In for Predictive Analytics analyzes data in relational tables or in Excel spreadsheets. It requires the Oracle Data Mining option.

More Information

The documentation for Oracle Spreadsheet Add-In for Predictive Analytics is included in the product download from Oracle Technology Network.

Custom Application Development Tool

The report-building tools in Oracle Business Intelligence cover a wide variety of reports. You might, however, have special circumstances that require a custom application. For example, you might need reports that comply with the corporate look and feel in a Web application. Or you might need access to advanced OLAP features that are not exposed in OracleBI Discoverer Plus OLAP, such as forecasting, what-if analysis, or allocation.

OracleBI Beans is a set of standards-based JavaBeans, integrated into Oracle JDeveloper for the development of Java-based and Web-based applications. The application development environment includes a rich set of tags for Java Server Pages (JSPs), making it easy to build analytic Web applications. These applications can include advanced features such as interactive user interfaces and drill-to-detail reports, as well as Oracle OLAP analytic features.

Figure 2-10 shows a custom dashboard application that was built using OracleBI Beans. The application presents two graphs:

Figure 2-10 OracleBI Beans Custom Application

Description of Figure 2-10  follows
Description of "Figure 2-10 OracleBI Beans Custom Application"

Web applications built with OracleBI Beans can be deployed to a number of Web servers, such as IBM WebSphere. You can also build Java applications with OracleBI Beans.

OracleBI Beans is seamlessly integrated into Oracle JDeveloper, providing a productive development environment for building custom business intelligence applications. Using JDeveloper and BI Beans, application developers can build Internet applications quickly and easily.

Data Source for BI Beans

OracleBI Beans displays data from analytic workspaces. It can also display data from star schemas, with metadata in an OLAP Catalog,

More Information

For more information, see the BI Beans Help from within JDeveloper.

Deploying Oracle Business Intelligence

Oracle Business Intelligence supports a variety of methods for deploying your BI solution. You can distribute reports in print, over the Web, or through e-mail. You can create a customizable portal page that displays BI data from multiple sources, along with data from other sources, such as stock prices.

This section describes different options that Oracle Business Intelligence offers for distributing BI reports.

OracleAS Portal and OracleBI Discoverer Portlet Provider

OracleAS Portal is a component of Oracle Application Server that allows you to build, deploy, and maintain integrated enterprise portals. OracleAS Portal allows for self-service content management and publishing, wizard-based development, and deploying, publishing, and consuming Web services in an extensible framework.

OracleBI Discoverer Portlet Provider makes it easy to distribute OracleBI Discoverer reports in a dashboard in OracleAS Portal. Using OracleBI Discoverer Portlet Provider, you can display worksheets that you create in OracleBI Discoverer Plus. You can also display data as gauges through OracleBI Discoverer Portlet Provider.

OracleBI Discoverer Portlet Provider provides portlets to OracleAS Portal. It provides three types of portlets:

  • The List of Worksheets portlet provides a list of links to OracleBI Discoverer workbooks and worksheets. When users click a worksheet link, the worksheet appears in OracleBI Discoverer Viewer for interactive analysis.

  • The Worksheet portlet places the actual content of a worksheet on a portal page. A Worksheet portlet can contain numeric data (a table or a crosstab), a graph, or both. Users can click an Analyze link to open the worksheet in OracleBI Discoverer Viewer for interactive analysis.

  • The Gauges portlet allows you to display gauges in a portal page. Gauges are a quick way to show where key performance indicators fall within set ranges. The Analyze link is also available for gauges.

Figure 2-11w shows a sample dashboard in OracleAS Portal, containing worksheets and gauges.

Figure 2-11 OracleAS Portal Dashboard with OracleBI Discoverer Portlets

Description of Figure 2-11  follows
Description of "Figure 2-11 OracleAS Portal Dashboard with OracleBI Discoverer Portlets"

More Information

For more information about OracleBI Discoverer Portlet Provider, see Oracle Business Intelligence Discoverer Publishing Workbooks in Oracle Application Server Portal.

OracleBI Discoverer Viewer

OracleBI Discoverer Viewer is an HTML Web application that gives business users interactive access to OracleBI Discoverer workbooks, without having to download plug-ins or applet code.

In OracleBI Discoverer Viewer, business users can interact with reports that others have created for them in OracleBI Discoverer Plus. They can drill on crosstabs and graphs to view and analyze the underlying data, thereby identifying trends and anomalies in their business. They can change the graph type used in a report, pivot the data, and add stoplight formatting to spot trends. They can also customize reports that contain parameters, such as changing the selection of City in a report that shows the top 5 products for a particular city (such as New York).

OracleBI Discoverer Viewer allows business users to share the results of their analysis by exporting their reports in Excel, HTML, PDF, or other popular file formats, and sending these files as e-mail attachments from within OracleBI Discoverer Viewer.

OracleBI Discoverer Viewer is integrated with OracleAS Portal, allowing users easy access from a OracleBI Discoverer portlet. From the dashboard, they can drill down to OracleBI Discoverer Viewer to perform further analysis.

Figure 2-12 shows a crosstab from the dashboard in Figure 2-11, which is now displayed in OracleBI Discoverer Viewer.

Figure 2-12 OracleBI Discoverer Viewer Report wit User-Settable Parameters

Description of Figure 2-12  follows
Description of "Figure 2-12 OracleBI Discoverer Viewer Report wit User-Settable Parameters"

More Information

For more information about OracleBI Discoverer Viewer, see Oracle Business Intelligence Discoverer Viewer User's Guide .

Oracle Reports Output Options

You can use Oracle Reports to distribute reports in any format. Of all the Oracle Business Intelligence tools, Oracle Reports has the richest set of output formats. You can create high quality printed reports and high quality Web reports. Oracle Reports supports standard output formats such as HTML, HTMLCSS, XML, PDF, PCL, PostScript, and ASCII.

Oracle Reports enables you to distribute the same report in multiple output formats, and you can distribute a report to multiple destinations from a single run of the report. You can create distributions for an entire report and for individual sections of the report. For example, in a single run of a report, you can generate HTML output, send a PostScript version to the printer, and also e-mail any or all sections of the report to a distribution list.

You can define the distribution for a report using XML, using a dialog in Oracle Reports, or through a command line interface.

More Information

For more information about Oracle Reports report formats and distributing reports, see Oracle Reports Building Reports and Oracle Application Server Reports Services Publishing Reports to the Web.