DB2 OLAP Server Installation Guide

What's new in DB2 OLAP Integration Server 8.1

This section provides an overview of additional significant changes in DB2 OLAP Integration Server Version 8.1. These changes are specific to Release 6.5 of Hyperion Essbase Integration Services.

Hybrid Analysis

Relational databases can store several terabytes of data and thus they have nearly unlimited scalability. Hybrid Analysis, a new function in DB2 OLAP Integration Server V8.1, offers you a method of integrating your OLAP database with a relational database and thereby take advantage of the scalability of the relational database. With Hybrid Analysis, you can support both multidimensional databases and relational databases, operate with almost no limitation on outline sizes, and provide a rapid transfer of data between OLAP databases and relational databases.

How Hybrid Analysis Works:

• Hybrid Analysis integrates an OLAP database with a relational database by allowing a portion of an Essbase cube to reside in the relational database. This Essbase portion consists of the lowest level members of the dimensions defined for Hybrid Analysis and the associated data of these members.
• Using Essbase Integration Services Console, the data in the relational database is mapped to hierarchies in the Essbase database. Hybrid Analysis thus eliminates the need to load and store members and their data within the OLAP database itself. It is this feature which combines the efficiency of mass data scalability with the sophisticated data analysis and rapid reporting of DB2 OLAP Server.

Using Hybrid Analysis:

• When using Hybrid Analysis, you create combinations of multidimensional and relational data storages. You define your Hybrid Analysis data storage options through the Integration Services Console at the member level in the metaoutline.
• In the appropriate metaoutline dialog boxes, you perform the tasks necessary for using Hybrid Analysis:
  • Specifying the relational data source for the metaoutline
  • Defining the hierarchy for the applicable dimension
  • Building the metaoutline down to the appropriate level
  • Performing the member and data loads

When you save a metaoutline as Hybrid Analysis-enabled, the console warns you if the metaoutline contains dimensions or transformations that are likely to produce incorrect member names or data values within the Hybrid Analysis data storage.

You query the data from the relational database in much the same manner you query data from an OLAP database, that is, by using reporting tools such as Outline Editor, Essbase Spreadsheet Add-in, Report Writer, and third-party tools.

You can create a Hybrid Analysis storage using the TBC sample application.

Multiple Data Source Connectivity

You can access data from different data sources, primary and secondary, each with its own icon. Multiple data source connectivity gives you considerable flexibility in creating OLAP models and metaoutlines especially in the following functions:

• Creating fact tables and dimensions
• Building logical join relationships with fact tables
• Changing to another data source for a specific dimension
• Connecting to primary and secondary data sources
• Editing the properties of a data source
• Creating new models and metaoutlines
• Opening existing models and metaoutlines

Tables Grouped by Data Source and Owner

With multiple data source connectivity, relational tables are listed, categorized, and ordered by the name of the source or owner and by table type. In the left frame of the OLAP Model main window, tables with the same data source and owner are grouped together.

Verification of Formulas

DB2 OLAP Integration Server provides a rapid method of verifying and editing static formulas before using the formulas in DB2 OLAP Server. Formerly, when a static formula was entered in DB2 OLAP Integration Server, the formula was passed directly to DB2 OLAP Server without any verification. This often caused the formula to be rejected by DB2 OLAP Server because of simple errors such as misspellings or missing semicolons. Verifying the formula beforehand can save you significant amounts of time during member and data loads.

Formula verification takes place in the Formula tab of the Member Properties Dialog Box in the metaoutline. During verification, the cursor stops at the location of each error. After correcting the error, you can either start the verification process at the beginning of the formula or choose to find the next error in the formula.

Automatic Migration to Version 8.1

In DB2 OLAP Integration Server Version 8.1, when you update an OLAP metadata catalog you created using Version 7.1, DB2 OLAP Integration Server runs a set of scripts that migrate your 7.1 catalog to version 8.1.

Native Driver Support on Oracle

In addition to the Open Database Connectivity (ODBC) drivers already supported, DB2 OLAP Integration Server 8.1 supports Oracle native drivers. This support eliminates the need for database client software and thus significantly facilitates the installation of DB2 OLAP Integration Server on the UNIX operating system. Support of Oracle native drivers also enhances performance on UNIX platforms.

Automatic Configuration Settings for Command Line Parameters

In Version 8.1, you can store server configuration file settings for command line parameters in a single file called eis.cfg. The DB2 OLAP Integration Server runs eis.cfg which in turn automatically sets the configurations for you. This process eliminates the need for manually entering configurations each time you perform a member or data load.

The following changes are specific to Release 6.2 of Hyperion Essbase Integration Services:

New Client Console with Dynamic Intelligent Help Window

DB2 OLAP Integration Server introduces the new Essbase Integration Services Console and companion Intelligent Help window. The new dockable Intelligent Help window contains dynamic links to key program functions to automate the major operations necessary for moving data from a relational database to online analytical processing (OLAP). This convenient new feature provides the following enhanced installation and client component functionality:

• Automatically detects the OLAP model fact table, dimensions, and hierarchies to create an OLAP model.
• Automatically detects dimensions and hierarchies in an OLAP model to create a basic metaoutline.

OLAP Metadata Catalog and Sample Application Creation

When you first start DB2 OLAP Integration Server Console after installing DB2 OLAP Integration Server, the system displays a new OLAP Metadata Catalog Setup dialog box. For first-time installations, you can click a button in this dialog box to create the OLAP Metadata Catalog. For existing users, if the system detects a previous version of the catalog, your existing catalog is updated to the current version. With Version 8.1, no other migration procedures are required, other than to update your existing OLAP Metadata Catalog. You must have an open database connectivity (ODBC) connection defined for the OLAP metadata catalog before you begin the automatic catalog creation process.

To create the OLAP Metadata Catalog, you first enter the DB2 OLAP Integration Server name and then enter the ODBC Data Source Name (DSN) for the catalog that you want to create. The DSN defines the database where you want to create the catalog. You then enter your user name and password for the DSN and click the OK button. You can choose not to show the OLAP Metadata Catalog Setup dialog box at each startup by clicking a check box. If you disable the option to view the OLAP Metadata Catalog Setup dialog box each time that you open a new session of Integration Services Console, you can select Tools > Create Catalog to view the dialog box again.

After the OLAP Metadata Catalog has been automatically created or updated from a previous version, you can access a new Sample Application Setup dialog box by selecting Tools > Create Sample. This dialog box enables automatic loading of the TBC sample application, including the sample data, sample OLAP model, and sample metaoutline. You must define an ODBC connection for the TBC sample application before you begin the automatic creation process.

To create the sample application, you must first connect to the OLAP Metadata Catalog where you want to store the sample data source, OLAP model, and metaoutline. You then enter the appropriate ODBC Data Source Name and password for the DSN user name TBC (tbc for Informix). DB2 OLAP Integration Server then creates the sample application automatically. The system displays a warning message if it detects that duplicate tables exist from a previous sample application. If a previous sample application is detected, the system will delete and replace the existing tables, or you must provide a new data source name before continuing.

After the OLAP Metadata Catalog has been created and, optionally, the sample TBC application has been created, the system displays the current Login dialog box.

Automatic Detection of Fact Table and Dimensions

After you click the OLAP Model icon for first-time model creation and log in to the appropriate data source, you can choose to have the OLAP model fact table and all related dimensions created automatically.

A quick-access link to the automatic detection option for creating a fact table is provided in the inline text of the Intelligent Help window (see Intelligent Help on page 19). In addition, a Tools menu option (Tools > Create Fact Table) is available from the OLAP Model main window.

When you select the fact table automatic-detection option using either method, the system displays a Create Fact Table dialog box. This dialog box shows the fact tables detected in the current data source to which you are connected, along with any existing fact tables in the OLAP Metadata Catalog that are present in the current data source. You can select either from a list of fact tables in existing OLAP models or from a list of potential fact table candidates to define the fact table to use. Check boxes in the Create Fact Table dialog box enable you to create the Time and Accounts dimensions automatically. If you prefer, you can create the Time or Accounts dimensions manually at a later time.

For automatic detection of dimensions, you can choose the automatic-detection link in the Intelligent Help window or select Tools > Create Dimensions to initiate the dimension-creation process. The system begins by looking for all tables joined to the fact table with primary-foreign key relationships. Next, it searches for all tables joined to the previously detected tables and adds them to the OLAP model schema. In a database where no primary-foreign key relationships are defined, the system begins by searching for all tables joined to the fact table and then uses the column name and type to determine join keys. If two tables have the same column name and type, the system assumes that the two tables are joined on that column. To reduce the possibility of creating invalid dimensions, the system does not search for join keys in any tables that are not joined to the fact table.

Automatic Detection of Hierarchies

Like the Create Fact Table and Create Dimensions options, a new option for automatic detection of hierarchies is provided with Version 8.1. Use the automatic-detection link to this option provided in the text of the Intelligent Help window (see Intelligent Help on page 19). Menu commands on the Tools menu in the OLAP Model and OLAP Metaoutline main windows also provide access to this option. From the OLAP Model main window, select Tools > Create Hierarchies; from the OLAP Metaoutline main window, select Tools > Get Existing Hierarchies. You can choose to create a single hierarchy or multiple hierarchies.

When you select the automatic hierarchy detection option, using either method, DB2 OLAP Integration Server searches for and detects hierarchies that exist in the OLAP Metadata Catalog. The system then displays the detected hierarchies in a hierarchical tree format that includes the dimension name, the hierarchy names, and the associated member names.

Automatic hierarchy detection does not detect any filters contained within the hierarchies. In addition, the system compares the hierarchical structure of the data source with that of the OLAP model dimensions and deletes any invalid columns from the detected hierarchies. For example, if the OLAP model Product dimension contains Category, Family, and SKU columns, but the data source does not contain a Category column, the hierarchy that is returned contains only Family and SKU columns.

Intelligent Help

This feature enhances user convenience and ease of operation. When you open the OLAP Model or OLAP Metaoutline main window to create or modify an OLAP model or a metaoutline, DB2 OLAP Integration Server Console provides a new option known as Intelligent Help to guide you through the process. Intelligent Help is displayed in a separate window that provides numbered procedures, along with links to new automatic detection options and frequently used functions.

Process buttons along the top of the window contain labels for each main group of related tasks. For example, the process buttons for the OLAP Model tasks groups contain the following labels:

• Fact
• Dimension
• Table and Column properties
• Hierarchy
• Finish

The operations that you need to perform to create an OLAP model fall into these main groupings.

Below the task-group process buttons, the Intelligent Help window consists of a right and left frame:

• The left frame lists the major tasks to be performed for each of the process groups.
• The right frame contains detailed, numbered procedures for performing the tasks itemized in the left frame. Links to automatic detection functions and to user interface dialog boxes are provided in this frame, where appropriate.

The Intelligent Help window is a dockable, relocatable window that you can move and resize as you prefer. Integration Services Console displays the Intelligent Help window automatically the first time that you create or open an OLAP model or metaoutline. You can click an icon to undock the Intelligent Help window and move it away from the console. You can click a check box to show or hide the Intelligent Help window at startup, and you can click a Close icon to close the window at any time during a console session.

Both the toolbar and the Tools menu in the OLAP Model and OLAP Metaoutline main windows provide options for selecting Intelligent Help. The system remembers whether the Intelligent Help window was open or closed during your last work session. If the Intelligent Help window is open when you close an OLAP model, the window is displayed automatically when you reopen the model to work on it at a later time.

As always, online help is available to provide in-depth conceptual information and dialog box entry details.

Load optimization

DB2 OLAP Version 8.1 provides five dynamic new load optimization options for improving member and data load performance:

Member load options

• Use Distinct Clause: If the leaf node of a given dimension should be distinct, you can choose to add the Distinct clause to the Select statement. During a member load, only the distinct combination of columns in the Select statement is returned, resulting in a quicker member load. You might want to use this option, for example, if you have created a Time dimension from the fact table.
• Use Outer Join: Use of this feature enables you to view all columns in your DB2 OLAP Server database, whether or not the columns between two joined tables are identical.

  The result of a natural (inner) join between two tables includes records where the joined columns match. In any row from Table A in which a join column does not exist in Table B, that row is dropped when data is loaded. In an outer join, however, when an unmatched column is encountered during a data load, the unmatched row is included in the results. The column data of the unmatched portion is replaced with a <NULL> value.

• Allow Duplicate Shared Members: Use of this feature enables the same member to be shared multiple times under the same parent, resulting in faster creation of the DB2 OLAP Server outline.

Data load options

• Remove Extra Joins: This feature removes extra joins whether or not a surrogate key is used for a data load. If a surrogate key is used in a join, the surrogate key is loaded into DB2 OLAP Server as an alias, and the join is eliminated in the data load.
• Use User-defined SQL: This option enables you to use an edited version of the standard SQL generated by DB2 OLAP Integration Server.

  A new OLAP metaoutline SQL Override dialog box displays standard data load Select statements. You can select the statements that you want to edit. The two-frame dialog box shows the current metaoutline in the left frame and three windows in the right frame. The top right-frame window lists the Current Selected SQL and is the window in which you edit Select statements. The center window displays the User-Defined SQL List that you create. The bottom window displays the Standard Data Load SQL generated by DB2 OLAP Integration Server.

  When you have completed editing of standard SQL statements, you can direct the system to use the edited version for a data load by checking the Use User-defined SQL for Data Load option in the OLAP Metaoutline Properties dialog box, Load Optimization tab. For rules and guidelines related to editing the standard data load SQL generated by DB2 OLAP Integration Server, click the Help button in the OLAP Metaoutline SQL Override dialog box.

Sort on alternate column

This feature enables you to sort on a column that resides in the relational data source, but which is not used in the OLAP metaoutline. Sorting on a nondisplayed column is enabled in both recursive builds and generation builds. The dialog boxes that you use to specify ordering and filtering display identical column lists for selection. To prevent multiple selections of the same column, a column that has been selected at any generation of ordering is no longer available for sort selection.

Loading of DB2 OLAP Server Properties from Database Columns

This new feature enables the following DB2 OLAP Server properties to be loaded from database columns in the same way that Consolidation Attributes and UDAs currently are loaded from DB2 OLAP Integration Server into DB2 OLAP Server:

• Two-pass calculation
• Data storage attribute
• Time balance
• Skip type
• Variance reporting
• Formulas

Inclusion of this feature follows the DB2 OLAP Server Administration Services Console model, enabling you to define major DB2 OLAP Server settings from within DB2 OLAP Integration Server. You define these member settings using the metaoutline Member Properties dialog box, Member Info tab. When property values are combined in one database column, you can separate them with a comma or a space (for example: +, Expense, Last, Both). When you use a word to define a property, only the first letter is used during a member load; for example, (O)nly or (E)xpense or (T)wo Pass Calc.

For details on allowable values for properties loaded from database columns, click the Help button in the Member Info tab of the metaoutline Member Properties dialog box.

XML Import/Export

DB2 OLAP Version 8.1 XML Import/Export enables you to import data into and export data from the OLAP Metadata Catalog. You can now quickly transfer OLAP models and metaoutlines from one OLAP Metadata Catalog to another through use of this feature. You can also use an XML-based editor to rename the OLAP model associated with a metaoutline and import the revised data into the OLAP Metadata Catalog. Be sure to use an XML editor that supports UTF 8 UNICODE.

Extensible Markup Language (XML) consists of two parts:

• Data Type Definition (DTD): This part describes the allowable structure of XML documents. A DTD defines the major elements within a document, the child elements, and related attributes. It can constrain the pieces of data that occur in a document, the hierarchy of the data that it includes, and the number of times each piece of data occurs. DB2 OLAP Integration Server provides a predefined DTD to ensure that you use the same data format when you create XML documents.
• XML documents: This part specifies how to use the tagged markup to indicate the meaning of data, somewhat like a card catalog. In DB2 OLAP Integration Server, the data in an XML document describes the dimensions, members, hierarchies, and settings in OLAP models and metaoutlines. This information is stored in tables in the OLAP Metadata Catalog.

To use the new XML Import/Export feature, DB2 OLAP Integration Server provides a new dialog box named the XML Import/Export dialog box. Select the File > XML Import/Export command from the OLAP Model or OLAP Metaoutline main window to access the dialog box.

You can then select the Import tab and enter the necessary information to open either an OLAP model or metaoutline from an XML file. You can also select the Export tab to save an OLAP model or metaoutline as an XML file. Click the Save As XML File button, enter an XML file name, and navigate to the directory where you want to save the file.

Incremental Update to Data

This feature enables you to add to and subtract from DB2 OLAP Server data during member loads and data loads. By default, DB2 OLAP Integration Server updates all dimensions and members. Incremental updating of an existing DB2 OLAP Server outline is faster than updating all dimensions and members. The Essbase Integration Services Console includes an Incremental Update dialog box to use for selecting update options.

For member load, you have the following options:

• Preserve all data (this is the default setting): preserves all existing data that applies to the changed outline when restructuring occurs.
• Preserve input data: preserves only those blocks containing data that is loaded.
• Preserve level 0 data: preserves data only for level zero members.
• Discard all data: clears all data from the database.

For data load, you have the following options:

• Overwrite (this is the default setting): replaces the values in the database with the values in the data source.
• Add: adds values in the data source to the existing values in the database.
• Subtract: subtracts the values in the data source from the existing values in the database.

For both member and data loads, you have the following Attribute Dimension Restriction options:

• Delete all dimensions (this is the default setting): deletes all attribute dimensions associated with any base dimension selected for the incremental update.
• No update to existing dimensions: keeps attribute dimensions unchanged while updating other areas.
• Update existing dimensions: updates all changes to attributes.

Member Load and Data Load Filter Options

This feature enables you to limit filters to a member load or a data load, or to apply filters to both member and data loads.

The Essbase Integration Services Console includes a revised DB2 OLAP Server Application and Database dialog box with a simple drop-down list from which to select or deselect filters.

Improved SQL Drill-Through

This feature takes advantage of the new data load optimizations by including join key optimization in the SQL for drill-through. In join key optimization, the join between a fact table and a dimension table is removed if the join column is the same as the column selected. This results in faster drill-through queries.

Windows Service Utility Support

This new utility adds the Windows NT and Windows 2000 service for DB2 OLAP Integration Server, accepting parameters supported by the olapisvr command. These parameters enable you to:

• View a list of available switches for starting DB2 OLAP Integration Server.
• Set the buffer size that DB2 OLAP Integration Server uses when transferring rows from the data source to the DB2 OLAP Server database.
• Set the number of records that DB2 OLAP Integration Server commits to DB2 OLAP Server during a data load.
• Give the log file a different name.
• Set the level of detail in the messages logged by DB2 OLAP Integration Server.
• Set the DB2 OLAP Server message database file to a different name.
• Specify the number of threads for executing SQL, retrieving data, transforming data, and performing a data load.
• Set the TCP port number at which DB2 OLAP Integration Server connects with a client.
• Set the number of network listeners that DB2 OLAP Integration Server starts.

Improved Access to the Server Log File

To assist users in analyzing and debugging member and data loads, DB2 OLAP Integration Server now provides two methods of accessing the Server log file:

• During a member or data load, or at any other time, you can select Tools > View Log File to view the Server log file.
• At the completion of a member or data load, a View Log File option button is enabled that you can select to view the Server log file.

In both cases, DB2 OLAP Integration Server retrieves the log data and displays it in the Server Log File window with a date and time stamp for each activity that has occurred during the load. For very large member or data loads, the server retrieves only the last 1 MB of the log file. You can copy the log file and paste it to any text editor, such as Notepad, to print it out for review and analysis.

New Sample Application

DB2 OLAP Integration Server provides a new sample application based on a fictitious company named The Beverage Company (TBC). The TBC sample application demonstrates creating a Measures dimension recursively, using a sort on an alternate column in the relational data source, and loading metadata from database columns. Additionally, the sample application includes UDAs and Date Time Series data, additional years, new drill-through paths, and a new Time table to demonstrate data load incremental updates.

The TBC sample application includes the following components:

• Sample OLAP Metadata Catalog (TBC_MD)
• Sample data source (TBC)
• Sample OLAP model (TBC Model) and sample OLAP metaoutline (TBC Metaoutline)

For the first time, the sample application OLAP model and OLAP metaoutline are provided in Java-supported XML format.

The Essbase Integration Services Console provides a new Sample Application Setup dialog box that is displayed automatically after installation of DB2 OLAP Integration Server. With the completion of a few simple entries in the dialog box, you can now load the sample application automatically.

This automated feature enables you to create a functional sample application that is ready to use within minutes. You must set up the database for the OLAP Metadata Catalog and the TBC data source, and define open database connectivity (ODBC) connections beforehand. When setting up ODBC connections, the user name that you use when defining the Data Source Name (DSN) for the data source must be TBC (tbc for Informix) because the sample tables were created using this name.

UNIX ODBC Configuration Utility

DB2 OLAP Integration Server provides a new utility for UNIX users to verify, add, or delete ODBC connections. The odbcconfig utility enables you to edit ODBC configuration information in the odbc.ini file. This new utility also displays current environment settings related to relational database management system (RDBMS) access. You can use the odbcconfig utility for diagnostic testing of environment settings.

The odbcconfig utility provides the following menu options to aid you in configuring and troubleshooting ODBC connections:

• List: Use this option to list all defined DSNs for the OLAP Metadata Catalog and source databases and verify that they are correct.
• Add: Use this option to add a DSN for the OLAP Metadata Catalog and source databases.
• Delete: Use this option to delete a DSN for the OLAP Metadata Catalog and source databases.
• Test: Use this option to test that the ODBC connections that you created are valid.