Learn how to export metadata from Oracle Business Intelligence into IBM DB2 using the DB2 Cube Views Generator.
This section contains the following topics:
Learn how this enhances the data warehouse performance and functionality of a database.
The term IBM DB2 Cube Views is a registered trademark of IBM. See System Requirements and Certification.
It enables the DB2 database to store metadata about the logical relationships of the data residing in the database. Additionally, it accelerates data warehouse queries by using more efficient DB2 materialized query tables (MQTs). These MQTs preaggregate the relational data and improve query performance.
When processing queries, the DB2 Query Rewrite functionality routes queries to the MQTs when possible. Because these tables are smaller than the underlying base tables and the data has been pre aggregated, the queries that are rerouted to them might run faster.
DB2 Cube Views Generator works as a metadata bridge to convert the Oracle BI proprietary metadata into an IBM Cube Views XML file. After converting metadata into an XML file, you use IBM Cube Views to import the translated metadata into the DB2 database and store it in IBM Cube Views metadata catalog tables. After importing the metadata, you use the IBM Optimization Advisor to generate scripts to create materialized query tables (MQT) and their indexes. The deployed MQTs are used by the DB2 Query Reroute Engine to optimize incoming application queries.
DB2 provides an API, implemented as a stored procedure, that passes XML documents as arguments to create, modify, delete, or read the metadata objects.
The alias-SQL file generated by the DB2 Cube Views Generator should be executed before importing the XML file.
The XML file generated by the DB2 Cube Views Generator contains the cube metadata in XML format. After importing the XML file into your DB2 database, you must create materialized query tables.
Note:
It is strongly recommended that you become familiar with IBM Cube Views and its tools before attempting to import the XML file. See the IBM documentation.
Ensure that you complete the steps in Running the Generator before deploying metadata. To deploy cube metadata, perform the tasks described in the following sections:
You must execute the alias-SQL file before you import the XML file into your DB2 database.
See the IBM documentation.
The alias-SQL file that is generated by the DB2 Cube Views Generator must be executed by a SQL client on the database where the data warehouse is located. When executed, it creates aliases (synonyms) for tables in the database.
After you execute the alias-SQL file, you can import the XML file into the database.
Note:
You should understand IBM Cube Views and tools before attempting to import the XML file.
You can import this file using the following IBM tools:
IBM OLAP Center (recommended), seeGuidelines for Importing the XML File Using the IBM OLAP Center and the IBM documentation.
IBM command-line client utility (db2mdapiclient.exe). IBM ships this utility with DB2.
IBM DB2 Stored Procedure. IBM Cube Views provides a SQL-based and XML-based application programming interface (API) that you can use to run a single stored procedure to create, modify, and retrieve metadata objects.
Using the IBM OLAP Center, you can import cube metadata into DB2. The IBM OLAP Center provides wizards to help you import the file.
See the IBM documentation.
To import the XML file, use the following guidelines:
Using the IBM OLAP Center tool, connect to the DB2 database.
In the Import Wizard, choose the XML file that you want to import.
If metadata exists that refers to database constructs that are not in the database, then an error message is displayed.
When the wizard asks for an import option, choose to replace existing objects.
When you are returned to the IBM OLAP Center, a diagram of the cube model is shown.
Learn about possible actions to take after importing an XML file.
You might need to perform the following actions:
Because Oracle OLAP does not store foreign keys as metadata, the foreign keys do not exist in the converted metadata in the DB2 database. You must use the IBM Referential Integrity Utility for IBM Cube Views to generate foreign key informational constraints. You can obtain this utility on the IBM Web site.
You might encounter other issues such as nullable foreign key join columns. You can use the following methods to solve this problem:
If data in these columns are not nullable, then you should convert these columns to not-null columns.
If data in these columns are nullable, or you do not want to convert the column data type even if the column data is not null, then you should modify the cube model using the following guidelines:
In a fact-to-dimension join, you must manually eliminate the dimension object from the converted cube model and create a degenerated dimension object consisting of the foreign key of this join.
In a dimension-to-dimension join, you must manually eliminate the dimension object that represents the primary-key side of the join from the converted cube model and create a degenerated dimension object consisting of the foreign key of this join.
In a fact-to-fact join, you must manually eliminate the fact object that represents the primary-key side of the join from the converted cube model and create a degenerated dimension object consisting of the foreign key of this join.
Incremental metadata changes are not allowed by the Cube Generator. Schema changes require that you manually delete cube model metadata in the DB2 database and convert the Oracle Business Intelligence metadata again. For example, if you must change a dimension in a cube in the Oracle Business Intelligence metadata repository, then delete the cube model in the DB2 database, regenerate the XML file from the Oracle Business Intelligence repository, and import it into the DB2 database.
You cannot delete metadata using the DB2 Cube Views Generator. Manually delete the cube model using the IBM OLAP Center.
You must run the IBM Statistics tool and IBM Optimization Advisor periodically.
After you import the cube metadata into the database, you must run the IBM Optimization Advisor to generate SQL scripts and then execute those scripts to create the MQTs.
See the IBM documentation.
You must provide certain parameters to the IBM Optimization Advisor to get optimal results from the implementation. The IBM Optimization Advisor wizard analyzes your metadata and recommends how to build summary tables that store and index aggregated data for SQL queries. Running the IBM Optimization Advisor can help you keep the MQTs current. Additionally, you must refresh your database after each ETL.
To create MQTs, use the following guidelines:
In the IBM OLAP Center, choose the cube model that you want to optimize and open the IBM Optimization Advisor wizard.
Follow the instructions in the wizard, using the following table as a guide.
| When asked for: | Choose: |
|---|---|
|
Summary Tables |
Choose Deferred (or Immediate) and provide a tablespace for the tables |
|
Limitations |
Choose an appropriate value for the optimization parameters. You should turn on the Data-sampling option. |
|
SQL Scripts |
Creation of the scripts needed to run to create the Summary tables. Choose the filename and locations |
When the IBM Optimization Advisor closes, you must execute the SQL scripts to create the MQTs.