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. DB2 OLAP Integration Server V8.1 includes Hybrid Analysis which
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 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.
- 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.
Extended 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.
- Teradata support
- DB2 OLAP Integration Server, Version 8.1, now supports Teradata, a database
from NCR Corporation, using NT or UNIX operating systems. Teradata is supported
both as a relational data source and as an OLAP Metadata Catalog storage database.
- 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.inifile. 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 odbcconfigutility
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.