Retrieving Relational Data

In This Section:

Integrating Relational Databases with Essbase

Hybrid Analysis

Advanced Relational Access

SAP R/3

XOLAP Overview

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

Also see:

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 143, Hybrid Analysis Architecture illustrates the Hybrid Analysis architecture:

Figure 143. Hybrid Analysis Architecture

The image illustrates the Hybrid Analysis architecture, as described in the text following the image.

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 143, 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 143, 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 143, Hybrid Analysis Architecture). When the member load is complete, you run a data load to populate the Essbase database with data (c in Figure 143, 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 143, 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 143, 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 Online Help.

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

  • Spreadsheet Add-in

  • Smart View

  • Report Writer

  • Oracle's Hyperion® Web Analysis

  • Third-party applications

Note:

The Essbase database and the relational database must be registered to the same ODBC data sources, and Integration Services must use the same source name for both databases.

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 60 to enable and define Hybrid Analysis for an Essbase Server or specific applications and databases:

Table 60. Hybrid Analysis Retrieval Configuration Settings

Setting

Description

HAENABLE

Enables retrieval of members from a Hybrid Analysis relational source

HAMAXNUMCONNECTION

Sets the maximum number of connections per database that Essbase can keep active against the relational database

HASOURCEDSNOS390

Enables access to DB2 data sources on an OS/390 system

HAMAXNUMSQLQUERY

Sets the maximum number of SQL queries that can be issued against relational database fact tables per Essbase query session

HAMAXQUERYTIME

Sets the maximum time limit per query for SQL queries from a Hybrid Analysis Relational Source

HAMAXQUERYROWS

Sets the maximum number of rows that can be returned per SQL query issued on behalf of an Essbase query

HARETRIEVENUMROW

Sets the maximum number of rows resulting from an SQL query to process at one time

HARAGGEDHIERARCHY

Enables support of null values in columns of dimension tables that are used to create dimensions for Hybrid Analysis-enabled outlines

HAMEMORYCACHESIZE

Sets the amount of memory reserved to cache queried members from a Hybrid Analysis relational source

See the Oracle Essbase Technical Reference.

Retrieving Hybrid Analysis Data with Spreadsheet Add-in

Use the Enable Hybrid Analysis option in the Essbase Options dialog box in Spreadsheet Add-in to drill down to members in the Hybrid Analysis relational source.

See Oracle Essbase Spreadsheet Add-in User's Guide, Oracle Essbase Spreadsheet Add-in Online Help.

Hybrid Analysis supports the following options in Spreadsheet Add-in:

  • Drill-down:

    • Next Level (children)

    • All Levels (all descendants)

    • Bottom Level (level 0)

    • All Siblings (all members with common parent)

    For best performance, use children, bottom-level, or siblings zoom-ins; avoid descendents zoom-ins.

  • Drill-up: Parent drill-up

    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.

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 Hyperion Smart View for Office Online Help.

Retrieving Hybrid Analysis Data with Report Writer

In Report Writer, commands enable and disableHybrid 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 option 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 option button is not displayed on the toolbar.

Note:

When Hybrid Analysis is disabled for a dimension, the user is unable to see and drill through to the Hybrid Analysis data associated with the dimension; however, the members of the dimension remain visible in Outline Editor.

Figure 144, 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 144. Example of Hybrid Analysis in Outline Editor

The image shows an outline in which the Product dimension is labeled as Hybrid Analysis enabled.

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:

The image shows an outline of the Market dimension, as described in the text preceding the table.

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:

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 nonaccounts dimensions are automatically enabled for relational storage. Alternatively, you can enable relational storage on selected nonaccounts 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.

Note:

For information on enabling Advanced Relational Access, see the Oracle Essbase Integration Services Online Help and Oracle Essbase Integration Services System Administrator's Guide.

SAP R/3

Essbase provides an SAP R/3 adapter that helps SAP experts identify and integrate SAP R/3 data into Essbase and other Oracle Hyperion application products. When enabled, an SAP R/3 node is available in the Administration Services Console. After setting up the feature, you can open an SAP instance under the node and provide information from which Administration Services defines a logical schema that can be used by Integration Services to build an Essbase outline.

Working with SAP R/3 includes the following process:

  1. Install the SAP R/3 adapter (a onetime requirement).

    See the Oracle Hyperion Enterprise Performance Management System Installation and Configuration Guide.

  2. In Administration Services Console, per specific SAP R/3 database:

    • Configure the data source (see “Connecting to SAP Data Sources in the Oracle Essbase Administration Services Online Help).

    • Open the corresponding instance under the SAP R/3 node and log in.

    • Create logical tables and store logical table definitions into the relational database as logical schema.

    • Define the logical table joins, define filter criteria, save and populate logical tables from SAP tables, saving them as a logical schema.

  3. In Integration Services, from the tables in the saved logical schema, create an OLAP model and metaoutline, and perform a member load.

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 Oracle Essbase Studio.

For information on guidelines and restrictions in using XOLAP and how to designate models for XOLAP, see the Essbase Studio online help.

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, Oracle Essbase Visual Explorer, or another reporting tool which can access an Essbase database.

  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 Oracle 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