Which of the following tools can be used to aggregate relational data from multiple sources?

The information in this chapter applies only to block storage databases and is not relevant to aggregate storage databases.

Integrating Relational Databases with Essbase

Because relational databases can store several terabytes of data, they offer nearly unlimited scalability. Multidimensional databases, generally smaller than relational databases, offer sophisticated analytic capabilities. By integrating a relational database with an Essbase database, you leverage the scalability of the relational database with the conceptual power of the multidimensional database.

By default, when Integration Server creates an Essbase outline, it loads all member levels specified in the metaoutline into a multidimensional database. You can, however, set Integration Server to build to a specified member level (Hybrid Analysis) or build only to the dimension level (Advanced Relational Access). Building down to a specified level produces a smaller multidimensional database and a smaller Essbase outline.

See Hybrid Analysis and Advanced Relational Access.

A source relational database can be integrated with an Essbase database by using XOLAP (extended online analytic processing). This a variation on the role of OLAP in business intelligence. Specifically, XOLAP is an Essbase multidimensional database that stores only the outline metadata and retrieves data from a relational database at query time. XOLAP thus integrates with an Essbase database, leveraging the scalability of the relational database with the more sophisticated analytic capabilities of a multidimensional database. Your business needs determine whether OLAP or XOLAP is best suited to your environment.

See XOLAP Overview.

Hybrid Analysis

Hybrid Analysis eliminates the need to load and store lower-level members and their data within the Essbase database. This feature gives Essbase the ability to operate with almost no practical limitation on outline size and provides for rapid transfer of data between Essbase databases and relational databases. Hybrid Analysis integrates a relational database with an Essbase multidimensional database so that applications and reporting tools can retrieve data directly from both databases. Figure 141, Hybrid Analysis Architecture illustrates the Hybrid Analysis architecture:

Figure 141. Hybrid Analysis Architecture

Hybrid Analysis Relational Source

The initial step in setting up Hybrid Analysis is to define the relational database as a Hybrid Analysis relational source (1 in Figure 141, Hybrid Analysis Architecture).

You define the Hybrid Analysis relational source in Integration Services Console. Through Integration Services Console, you first specify the relational data source for the OLAP model. The OLAP model is a schema that you create from tables and columns in the relational database. To build the model, Integration Services accesses the star schema of the relational database (a in Figure 141, Hybrid Analysis Architecture).

Using the model, you define hierarchies and tag levels whose members are to be enabled for Hybrid Analysis. You then build the metaoutline, a template containing the structure and rules for creating the Essbase outline, down to the desired Hybrid Analysis level. The information enabling Hybrid Analysis is stored in the OLAP Metadata Catalog, which describes the nature, source, location, and type of data in the Hybrid Analysis relational source.

Next, you perform a member load, which adds dimensions and members to the Essbase outline (b in Figure 141, Hybrid Analysis Architecture). When the member load is complete, you run a data load to populate the Essbase database with data (c in Figure 141, Hybrid Analysis Architecture). At this point, the Hybrid Analysis architecture is in place:

• The lower-level members and their associated data remain in the relational database.

• The data in the relational database is mapped to the Essbase outline that is defined by Hybrid Analysis.

• The outline resides in the Essbase database.

  Metadata is data that describes values within a database. The metadata that defines the Hybrid Analysis data resides in the Essbase outline and in the Integration Services metaoutline on which the Essbase outline is based. Any changes that are made to Hybrid Analysis data in an OLAP model or metaoutline that is associated with an Essbase outline must be updated in the outline to ensure accuracy of the data reported in Essbase. See Managing Data Consistency in Hybrid Analysis.

• Upper-level members and their associated data reside in the Essbase database.

Data Retrieval

Applications and reporting tools, such as spreadsheets and Report Writer interfaces, can retrieve data directly from both databases (2 in Figure 141, Hybrid Analysis Architecture). Using the dimension and member structure defined in the outline, Essbase determines the location of a member and then retrieves data from either the Essbase database or the Hybrid Analysis relational source. If the data resides in the Hybrid Analysis relational source, Essbase retrieves it through SQL commands. See Retrieving Hybrid Analysis Data.

To modify the outline, you can use Outline Editor in Administration Services to enable or disable dimensions for Hybrid Analysis on an as-needed basis. (3 in Figure 141, Hybrid Analysis Architecture). See Using Outline Editor with Hybrid Analysis and the Oracle Essbase Integration Services System Administrator's Guide.

Retrieving Hybrid Analysis Data

For information on defining a Hybrid Analysis relational source in Integration Services Console, see the Oracle Essbase Integration Services System Administrator's Guide.

In Hybrid Analysis, applications and reporting tools can retrieve data directly from relational and Essbase databases by using the following tools:

• Smart View

• Report Writer

• Oracle Hyperion Web Analysis

• Third-party applications

Because data is being accessed from the Hybrid Analysis relational source and the Essbase database when you perform calculations or generate reports, data retrieval time may increase with Hybrid Analysis; however, most capabilities of Essbase data retrieval operations are available with Hybrid Analysis, including pivot, drill-through, and other metadata-based methods.

Defining the Hybrid Analysis Retrieval Environment

Use the configuration settings listed in Table 119 to enable and define Hybrid Analysis for an Essbase Server or specific applications and databases:

Table 119. Hybrid Analysis Retrieval Configuration Settings

See the Oracle Essbase Technical Reference.

Retrieving Hybrid Analysis Data with Smart View

Hybrid Analysis is automatically enabled in Smart View.

Hybrid Analysis supports the Parent drill-up option in Smart View. The drill-up on a relational member always takes you to the leaf-level member in the Essbase outline and not to the immediate parent of the relational member.

See the Oracle Smart View for Office User's Guide.

Retrieving Hybrid Analysis Data with Report Writer

In Report Writer, commands enable and disable Hybrid Analysis:

• <HYBRIDANALYSISON enables a report script to retrieve the members of a dimension that is enabled for Hybrid Analysis.

• <HYBRIDANALYSISOFF prevents a report script from retrieving the members of a dimension that is enabled for Hybrid Analysis.

The <ASYM and <SYM commands are not supported with Hybrid Analysis. If they are present in a report, errors may result. The <SPARSE command is ignored in reports retrieving data from a Hybrid Analysis relational source and does not generate errors.

The following is a sample Report Writer script that uses the IDESCENDANTS command to return Hybrid Analysis data:

<PAGE (Accounts, Scenario, Market)
Sales
Actual
<Column (Time)
<CHILDREN Time
<Row (Product)
<IDESCENDANTS 100-10
!

Retrieving Hybrid Analysis Data with Web Analysis

When you use Web Analysis, the procedures for retrieving Hybrid Analysis data are the same as those for retrieving data that is not defined for Hybrid Analysis. (See the Web Analysis documentation.)

For optimal performance when retrieving Hybrid Analysis data with Web Analysis, consider the following guidelines:

• Place dimensions that are enabled for Hybrid Analysis along the rows when constructing queries in Web Analysis.

• Avoid using sort options.

• Use the children operator when drilling down to a hierarchy; do not use the descendants operator.

• Additional processing in the form of restrictions or Top/Bottom retrievals may cause slower query times.

Using Outline Editor with Hybrid Analysis

In Outline Editor, you can toggle the Hybrid Analysis button to enable or disable Hybrid Analysis for each dimension that is defined for Hybrid Analysis in Integration Services Console. If you open an outline that is not defined for Hybrid Analysis, the Hybrid Analysis button is not displayed on the toolbar.

Figure 142, Example of Hybrid Analysis in Outline Editor is an example of how an outline defined for Hybrid Analysis appears in Outline Editor. Note that dimensions that are enabled for Hybrid Analysis are identified to distinguish them from dimensions that are not enabled for Hybrid Analysis.

Figure 142. Example of Hybrid Analysis in Outline Editor

Managing Data Consistency in Hybrid Analysis

When you create a Hybrid Analysis relational source, the data and metadata are stored and managed in the relational database and in the Essbase database:

• Lower-level members and their associated data remain in the relational database.

• Data in the relational database is mapped to the Essbase outline that is defined for Hybrid Analysis.

• The outline resides in the Essbase database.

• Upper-level members and their associated data reside in the Essbase database.

Because data and metadata exist in different locations, information may become out of sync.

Essbase depends upon the OLAP Metadata Catalog in Integration Services to access the Hybrid Analysis relational source. At Essbase database startup time, Essbase Server checks the number of dimensions and members of the Essbase outline against the related metaoutline.

Changes made to the associated OLAP model or metaoutline during an Integration Services session are not detected by the Essbase Server until the Essbase database is started again. Undetected changes can cause data inconsistency between the Essbase database and the Hybrid Analysis relational source.

If changes are made in the Hybrid Analysis relational source, and members are added or deleted in the OLAP model or metaoutline, such changes can cause the Essbase outline to be out of sync with the metaoutline on which it is based. These types of changes and their effect on the hierarchical structure of a dimension are not reflected in the Essbase database until the outline build and data load process is completed through Integration Services Console.

In Administration Services, the Restructure Database dialog box has a check box that enables a warning whenever a restructuring affects an outline containing a Hybrid Analysis relational source. Such a problem occurs, for example, if members with relational children are moved or deleted.

Warnings are listed in the application log. You should decide whether the warnings reflect a threat to data consistency. To view the application log, see Viewing the Essbase Server and Application Logs.

The Essbase administrator has the responsibility to ensure that the Essbase multidimensional database, the relational database, and the Integration Services OLAP model and metaoutline remain in sync. Administration Services and Integration Services Console provide commands that enable the administrator to perform consistency checks and make the appropriate updates.

See Ensuring Data Integrity and Optimizing Database Restructuring.

Managing Security in Hybrid Analysis

The Essbase administrator determines access to the Hybrid Analysis relational source on an individual Essbase user level. Access for Hybrid Analysis is governed by the same factors that affect overall Essbase security:

• Permissions and access levels for individual users and groups of users

• Permissions and access levels for the server, application, or database

• Specific database access levels for particular database members

If a security filter enables you to view only the relational children of the level 0 members that you have access to in Essbase, then you cannot view the relational children of the level 0 members that you do not have access to in Essbase.

Assume that you have the following outline of the Market dimension, where San Francisco and San Jose are relational children of California, and Miami and Orlando are relational children of Florida:

In this example, if a filter allows you to view only level 0 member California and its descendants, you can view California and its relational children, San Francisco and San Jose; however, you cannot view the children of level 0 member Florida.

See the following chapters:

• User Management and Security in EPM System Security Mode

• Controlling Access to Database Cells Using Security Filters

• Security Examples in Native Security Mode

Advanced Relational Access

Integration Services uses Advanced Relational Access to give Essbase users direct access to data from relational databases or data warehouses. In Integration Services Console, Advanced Relational Storage is enabled at the metaoutline level. When the Relational Storage option is selected, all members of all non-accounts dimensions are automatically enabled for relational storage. Alternatively, you can enable relational storage on selected non-accounts dimensions.

When a metaoutline is enabled for Advanced Relational Access, users are able to query directly on relationally stored members. Queries to the database are made using multidimensional expressions (MDX), which are translated into SQL statements. Dimension members are accessed directly from the relational data source.

XOLAP Overview

XOLAP (extended online analytic processing) is a variation on the role of OLAP in business intelligence. Specifically, XOLAP is an Essbase multidimensional database that stores only the outline metadata and retrieves data from a relational database at query time. XOLAP thus integrates a source relational database with an Essbase database, leveraging the scalability of the relational database with the more sophisticated analytic capabilities of a multidimensional database. Your business needs determine whether OLAP or XOLAP is best suited to your environment.

OLAP and XOLAP store the metadata outline and the underlying data in different locations:

• In OLAP, the metadata is located in the Essbase database, and the underlying data is also located in the Essbase database.

• In XOLAP, the metadata is located in the Essbase database while the underlying data remains in your source relational database.

The differences in the locations of the metadata and data are key to understanding how XOLAP can be of benefit because these differences affect the functionality of OLAP and XOLAP.

OLAP lends itself to traditional relational data storage and data analysis. XOLAP lends itself to operations supported in mixed or “hybrid” environments such as Hybrid Analysis and Advanced Relational Access (familiar to users of Essbase and Integration Services). Many of the basic concepts of Hybrid Analysis and Advanced Relational Access have been folded into the functionality of XOLAP cubes in Essbase Studio.

For information on guidelines and restrictions in using XOLAP and how to designate models for XOLAP, see the Oracle Essbase Studio User's Guide.

XOLAP Workflow

The workflow of data retrieval in an XOLAP environment is much like that of a non-XOLAP environment:

1. The model is designated as XOLAP-enabled in Essbase Studio.

2. The cube is deployed in Essbase Studio; however, no data is loaded at that time.

3. The Essbase database is queried using Smart View or other reporting tools that can access Essbase databases.

4. Essbase dynamically generates the required SQL to retrieve the data from the source relational database.

Guidelines for Using XOLAP

XOLAP has several restrictions. There are also several usages not supported in XOLAP.

Restrictions For XOLAP

XOLAP has the following restrictions:

• No editing of an XOLAP cube is allowed. If you wish to modify an outline, you must, instead, create a new outline in Essbase Studio. XOLAP operations will not automatically incorporate any changes in the structures and the contents of the dimension tables after an outline is created.

• When derived text measures are used in cube schemas to build an Essbase model, XOLAP is not available for the model.

• XOLAP can be used only with aggregate storage. The database is automatically duplicate-member enabled.

• XOLAP supports dimensions that do not have a corresponding schema-mapping in the catalog; however, in such dimensions, only one member can be a stored member.

Usages Not Supported in XOLAP

XOLAP does not support use of the following

• Flat files

• Ragged hierarchies

• Alternate hierarchies

• Recursive hierarchies

• Calendar hierarchies

• Filters

• Typed measures

• User defined members at the leaf level

• Multiple relational data sources

Optimizing XOLAP

Use the following Essbase configuration settings to optimize XOLAP performance:

• SSOPTIMIZEDGRIDPROCESSING: Specifies whether optimized grid processing, which cuts the input grid into symmetric grids to create fewer symmetric queries, is enabled for spreadsheet operations.

• SSBULKGRIDPROCESSING: Optimizes asymmetric Grid API queries for XOLAP.