Accessing Relational Data with Hybrid Analysis

Because relational databases can store several terabytes of data, they offer nearly unlimited scalability. Multidimensional databases are generally smaller than relational databases but offer sophisticated analytical capabilities. With Hybrid Analysis, you can integrate a relational database with an Essbase database and thereby leverage the scalability of the relational database with the conceptual power of the multidimensional database.

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 limitation on outline sizes and provides for rapid transfer of data between Essbase databases and relational databases.

This chapter helps you understand Hybrid Analysis and explains how you can take advantage of its capabilities. The chapter includes the following sections:

Overview of Hybrid Analysis

Hybrid Analysis integrates a relational database with an Essbase multidimensional database so that applications and reporting tools can directly retrieve data from both databases. Figure 553, below, illustrates the Hybrid Analysis architecture:

Figure 553: 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 553).

You define the Hybrid Analysis relational source in Essbase Integration Services Console. (The individual tasks are discussed in Defining the Hybrid Analysis Relational Source.) 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 553).

Using the model, you define hierarchies and tag level members to be Hybrid Analysis-enabled. You then build the metaoutline, a template containing the structure and rules for creating the 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 outline (b in Figure 553). When the member load is complete, you run a data load to populate the Essbase database with data (c in Figure 553). At this point, the Hybrid Analysis architecture is in place:

Data Retrieval

Applications and reporting tools, such as spreadsheets and Report Writer interfaces, can directly retrieve data from both databases (2 in Figure 553). 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 the data through SQL commands. Data retrieval is discussed in Retrieving Hybrid Analysis Data.

If you want to modify the outline, you can use the Essbase Outline Editor to enable or disable dimensions for Hybrid Analysis on an as-needed basis. (3 in Figure 553). For information on using the Outline Editor, see Using the Outline Editor with Hybrid Analysis.

Hybrid Analysis Guidelines

Hybrid Analysis has some guidelines with which you should be familiar:

Defining the Hybrid Analysis Relational Source

The Hybrid Analysis relational source is defined in Essbase Integration Services Console. Detailed information and the specific procedures for performing the following steps is available in Integration Services online help.

To define the Hybrid Analysis relational source, perform the following steps:

  1. Specify the relational data source.
  2. The OLAP model is created from tables and columns in the relational database. To build the model, Integration Services accesses the star schema of the relational database.

  3. In the OLAP model, define the hierarchies that you will use for Hybrid Analysis.
  4. Note: You can define any member of any dimension as Hybrid Analysis-enabled except members in an accounts dimension. This restriction is necessary because all members of an accounts dimension, including lower-level members, must remain in the Essbase database.

  5. In the metaoutline, use the Build Multidimensional Down to Here command to select the level members that will reside in the Essbase multidimensional database.
  6. Use the Enable Hybrid Analysis Down to Here command to select the member levels that will remain in the relational database.
  7. Run a member load to add dimensions and members to the outline.
  8. Run a data load to populate the Essbase database with data.

Retrieving Hybrid Analysis Data

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

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

Because data is being accessed from both 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, all capabilities of Essbase data retrieval operations are available with Hybrid Analysis, including pivot, drill-through, and other metadata-based methods.

Retrieving Hybrid Analysis Data with Spreadsheet Add-in

Use the Span Hybrid Analysis option in the Essbase Options dialog box in the Essbase Spreadsheet Add-in to drill down to members in the Hybrid Analysis relational source. The following grid Essbase API functions can be used to enable or disable access to the Hybrid Analysis relational storage:

Refer to the Essbase Spreadsheet Add-in User's Guide and to Spreadsheet Add-in online help for more information.

Supported Drill-Down Options in Hybrid Analysis

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

Supported Drill-Up Option in Hybrid Analysis

Hybrid Analysis supports the following drill-up option in Spreadsheet Add-in: Parent

Retrieving Hybrid Analysis Data with Report Writer

In Report Writer, two commands enable/disable Hybrid Analysis:

The <ASYM and <SYM commands are not supported with Hybrid Analysis. If these commands 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. Refer to the Technical Reference in the docs directory for more information.

The following is a sample Report Writer script which 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 Hyperion Analyzer

When you use Hyperion Analyzer, the procedures for retrieving Hybrid Analysis data are the same as the procedures for retrieving data that is not defined for Hybrid Analysis. See the Hyperion Analyzer documentation for detailed information.

Using the Outline Editor with Hybrid Analysis

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

Note: When Hybrid Analysis is disabled for a dimension, the end user is unable to see and drill-through to the Hybrid Analysis data associated with the dimension; however, the members are still visible in the Outline Editor.

Figure 554, below, is an example of how a Hybrid Analysis-defined outline appears in the Outline Editor. Note that Hybrid Analysis-enabled dimensions are identified to distinguish them from dimensions that are not Hybrid Analysis enabled.

Figure 554: Example of Hybrid Analysis in Outline Editor

Managing Data Consistency

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

Because the 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 OLAP Server checks the number of dimensions and members of the Essbase outline with the related metaoutline. Any changes made to the associated OLAP model or metaoutline during an Integration Services session are not detected by the OLAP 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 an OLAP model or metaoutline, such changes can cause the Essbase outline to be out of sync with the metaoutline on which it 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 Application Manager, 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 file. You should decide if these warnings reflect a threat to your data consistency.

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. Both Application Manager and Integration Services Console provide commands that enable the administrator to perform consistency checks and make the appropriate updates.

For additional information on maintaining data consistency, see Ensuring Data Integrity.

For additional information on restructuring your database, see 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:

If a security filter allows 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, 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.

For detailed information on Essbase security, see the following chapters:

Using Formulas with Hybrid Analysis

Formulas used with Hybrid Analysis-enabled members are subject to the following limitations:

Unsupported Functions in Hybrid Analysis

Hybrid Analysis does not support all Essbase functions.

Generating Member Lists

Hybrid Analysis does not support the following functions used to generate member lists:

Specifying Member Conditions

Hybrid Analysis does not support the following functions used to specify member conditions:

Looking up Values

Hybrid Analysis does not support the following functions used to set a range for members:

Range Function

Hybrid Analysis does not support the following function used to look up specific values of members: @MDSHIFT.

Current Member

Hybrid Analysis does not support the following function used to determine whether the current member is the member being specified: @ISMBR.




© 2002 Hyperion Solutions Corporation. All rights reserved.
http://www.hyperion.com