In This Section:
The information in this chapter applies only to block storage databases and is not relevant to aggregate storage databases.
Oracle Essbase Integration Services System Administrator's Guide
Oracle Essbase Administration Services Online Help
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.
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 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:
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:
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.
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.
For information on defining a Hybrid Analysis relational source in Integration Services Console, see the Oracle Essbase Integration Services System Administrator's Guide.
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.
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
Enables retrieval of members from a Hybrid Analysis relational source
Sets the maximum number of connections per database that Essbase can keep active against the relational database
Enables access to DB2 data sources on an OS/390 system
Sets the maximum number of SQL queries that can be issued against relational database fact tables per Essbase query session
Sets the maximum time limit per query for SQL queries from a Hybrid Analysis Relational Source
Sets the maximum number of rows that can be returned per SQL query issued on behalf of an Essbase query
Sets the maximum number of rows resulting from an SQL query to process simultaneously
Enables support of null values in columns of dimension tables that are used to create dimensions for Hybrid Analysis-enabled outlines
Sets the amount of memory reserved to cache queried members from a Hybrid Analysis relational source
See the Oracle Essbase Technical Reference.
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.
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.
<PAGE (Accounts, Scenario, Market) Sales Actual <Column (Time) <CHILDREN Time <Row (Product) <IDESCENDANTS 100-10 !
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.
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 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.
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.
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 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.
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.
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 (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.
The workflow of data retrieval in an XOLAP environment is much like that of a non-XOLAP environment:
The model is designated as XOLAP-enabled in Essbase Studio.
The cube is deployed in Essbase Studio; however, no data is loaded at that time.
The Essbase database is queried using Smart View or other reporting tools that can access Essbase databases.
Essbase dynamically generates the required SQL to retrieve the data from the source relational database.
XOLAP has several restrictions. There are also several usages not supported in 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.
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.