Essbase® Analytic Services Database Administrator's Guide | | Update Contents | Previous | Next | Print | ? | |
Information Map | |
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 analytic capabilities. With Essbase Hybrid Analysis, you can integrate a relational database with an Essbase Analytic Services 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 Analytic Services database. This feature gives Analytic Services the ability to operate with almost no practical limitation on outline size and provides for rapid transfer of data between Analytic Services databases and relational databases.
This chapter helps you understand Hybrid Analysis and explains how you can take advantage of its capabilities.
Note: The information in this chapter is designed for block storage databases. Some of the information is not relevant to aggregate storage databases. For detailed information on the differences between aggregate and block storage, see Comparison of Aggregate and Block Storage.
The chapter includes the following topics:
Hybrid Analysis integrates a relational database with an Analytic Services multidimensional database so that applications and reporting tools can directly retrieve data from both databases. Figure 83 illustrates the hybrid analysis architecture:
Figure 83: Hybrid Analysis Architecture
The initial step in setting up Hybrid Analysis is to define the relational database as a hybrid analysis relational source (1 in Figure 83).
You define the hybrid analysis relational source in Essbase Integration Services Console. (The individual tasks are discussed in Defining Hybrid Analysis Relational Sources.) 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 83).
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 Analytic Services 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 Analytic Services outline (b in Figure 83). When the member load is complete, you run a data load to populate the Analytic Services database with data (c in Figure 83). At this point, the hybrid analysis architecture is in place:
Metadata is data that describes values within a database. The metadata that defines the hybrid analysis data resides in both the Analytic Services outline and in the Integration Services metaoutline on which the Analytic Services outline is based. Any changes that are made to hybrid analysis data in an OLAP model or metaoutline that is associated with an Analytic Services outline must be updated to the outline to ensure accuracy of the data reported in Analytic Services. See Managing Data Consistency for information on keeping data and metadata in sync.
Applications and reporting tools, such as spreadsheets and Report Writer interfaces, can directly retrieve data from both databases (2 in Figure 83). Using the dimension and member structure defined in the outline, Analytic Services determines the location of a member and then retrieves data from either the Analytic Services database or the hybrid analysis relational source. If the data resides in the hybrid analysis relational source, Analytic Services 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 Outline Editor in Administration Services to enable or disable dimensions for hybrid analysis on an as-needed basis. (3 in Figure 83). For information on using Outline Editor, see Using Outline Editor with Hybrid Analysis.
Hybrid Analysis has some guidelines with which you should be familiar:
A hybrid analysis relational source is defined in Integration Services Console. Detailed information and the specific procedures for performing the following steps is available in Integration Services online help.
To define a hybrid analysis relational source, perform the following steps:
Note: If you are using two servers during hybrid analysis, the Data Source Name (DSN) must be configured on both servers and the DSN must be the same.
The OLAP model star schema is created from tables and columns in the relational database. To build the model, Integration Services accesses the relational databases using the DSNs you configured in step 1.
Note: You can define any member of any dimension as enabled for hybrid analysis 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 Analytic Services database.
Note: For detailed information on the above steps, see the Essbase Integration Services Console online help.
In Hybrid Analysis, applications and reporting tools can directly retrieve data from both the relational and the Analytic Services databases by using the following tools:
Note: The Analytic Services 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 both the hybrid analysis relational source and the Analytic Services database when you perform calculations or generate reports, data retrieval time may increase with Hybrid Analysis; however, most capabilities of Analytic Services data retrieval operations are available with Hybrid Analysis, including pivot, drill-through, and other metadata-based methods.
Use the Enable Hybrid Analysis option in the Essbase Options dialog box in Essbase Spreadsheet Add-in and Essbase Spreadsheet Services to drill down to members in the hybrid analysis relational source. Refer to the Essbase Spreadsheet Add-in User's Guide and to Spreadsheet Add-in online help for more information about these functions.
Hybrid Analysis supports the following drill-down options in Spreadsheet Add-in:
For best performance, use children, bottom level, or siblings zoom-ins; avoid using a descendents zoom-in.
Hybrid Analysis supports the Parent drill-up option in Spreadsheet Add-in and Spreadsheet Services. However, the drill-up on a relational member always takes you to the leaf level member in the Analytic Services outline and not to the immediate parent of the relational member.
In Report Writer, two commands, enable and disable respectively, 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.
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 !
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.)
For optimal performance when retrieving hybrid analysis data with Hyperion Analyzer, keep in mind the following guidelines:
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 end user is unable to see and drill-through to the hybrid analysis data associated with the dimension; however, the members of the dimension are still visible in Outline Editor.
Figure 84 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 84: Example of Hybrid Analysis in Outline Editor
When you create a hybrid analysis relational source, the data and metadata are stored and managed in the relational database and in the Analytic Services database:
Because data and metadata exist in different locations, information may become out of sync.
Analytic Services depends upon the OLAP Metadata Catalog in Integration Services to access the hybrid analysis relational source. At Analytic Services database startup time, Analytic Server checks the number of dimensions and members of the Analytic Services outline against the related metaoutline.
Any changes made to the associated OLAP model or metaoutline during an Integration Services session are not detected by the Analytic Server until the Analytic Services database is started again. Undetected changes can cause data inconsistency between the Analytic Services 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 Analytic Services 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 Analytic Services database until the outline build and data load process is completed through Integration Services Console.
In Essbase 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 if the warnings reflect a threat to data consistency. To view the application log, see Viewing the Analytic Server and Application Logs.
The Analytic Services administrator has the responsibility to ensure that the Analytic Services multidimensional database, the relational database, and the Integration Services OLAP model and metaoutline remain in sync. Both Administration Services and Integration Services Console provide commands that enable the administrator to perform consistency checks and make the appropriate updates.
For a comprehensive discussion of maintaining data consistency, see Ensuring Data Integrity.
For a comprehensive discussion of restructuring a database, see Optimizing Database Restructuring.
The Analytic Services administrator determines access to the hybrid analysis relational source on an individual Analytic Services user level. Access for Hybrid Analysis is governed by the same factors that affect overall Analytic Services security:
If a security filter enables you to view only the relational children of the level 0 members that you have access to in Analytic Services, then you cannot view the relational children of the level 0 members that you do not have access to in Analytic Services.
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 Analytic Services security, see the following chapters:
Formulas used with members enabled for hybrid analysis are subject to the following limitations:
If a formula or member enabled for hybrid analysis contains one or more functions that are not supported by Hybrid Analysis, Analytic Services returns the following error message:
Error executing formula for member [member-name-to-which-formula-is-attached] (line [line# where the offending function appears inside the formula): function [Name of the offending function] cannot be used in Hybrid Analysis.
Hybrid Analysis does not support all Analytic Services functions. The following topics specify the categories of significant Analytic Services functions not supported by Hybrid Analysis.
Hybrid Analysis does not support functions that look up specific values in the database based on current cell location and a series of parameters. Some examples of these functions are given next.
Hybrid Analysis does not support functions used to specify member conditions. Some examples of these functions are listed next.
Hybrid Analysis does not support functions that use a range of members as arguments. Rather than return a single value, these functions calculate a series of values internally based on the range specified. Some examples of range functions that are not supported are listed next.
Hybrid Analysis does not support any Analytic Services functions that deal with attributes. Some examples of these functions are listed next.
@ATTRIBUTEVAL
@ATTRIBUTESVAL
@WITHATTR
Hybrid Analysis does not support the following functions used to determine whether the current member is the member being specified.
![]() |