Oracle^® Business Intelligence Server Administration Guide > Connectivity and Third-Party Tools in Oracle BI Server > Using Materialized Views in the Oracle Database with Oracle BI >

Process of Deploying Metadata for Oracle

NOTE:  Become familiar with the Oracle Database and its tools before attempting to deploy metadata in the Oracle Database. For more information, refer to Oracle Database documentation.

Before deploying metadata, complete the steps in Generating the Import File. To deploy cube metadata, perform the following tasks in the order shown:

1. Executing the SQL File for Oracle
2. Defining Constraints for the Existence of Joins
3. Creating the Query Workload
4. Creating Materialized Views

Executing the SQL File for Oracle

This step is part of the Process of Deploying Metadata for Oracle.

Before executing the SQL file for importing into the Oracle Database Summary Advisor, ensure that you are familiar with Oracle Database import tools. Refer to Oracle Database documentation for information.

Use a tool such as SQL*Plus to execute the SQL file that Oracle Database Metadata Generator generated. You might see error messages if the dimensions already exist or if the database schema differs from that in the RPD file. When the script executes successfully, you can see the dimensions that were created by using the database web console or the Oracle Enterprise Manager Console. In the Oracle Enterprise Manager Console, expand the following nodes: Network, Databases, database-name, Warehouse, Summary Management, Dimensions, System.

After you execute the SQL file, you might need to perform the following actions:

• No incremental metadata changes are allowed. Schema changes require that you manually delete cube model metadata in the Oracle Database and convert the Oracle BI metadata again. For example, if you need to make a change to a dimension in a cube in the Oracle BI metadata repository, you need to delete the cube model in the Oracle Database, regenerate the SQL file from the Oracle BI repository, and import it into the Oracle Database Summary Advisor.
• You cannot delete metadata using the Oracle Database Metadata Generator. The Oracle BI Administrator must manually delete the cube model using Oracle Enterprise Manager Console.

Defining Constraints for the Existence of Joins

This step is part of the Process of Deploying Metadata for Oracle. For more information, refer to Oracle Database documentation.

You must ensure that the Oracle Database knows about the joins between the dimension tables and the fact tables. To do so, you create constraints in SQL*Plus or Oracle Enterprise Manager Console. In Oracle Enterprise Manager Console, you select the table on which you must create a constraint then select the Constraint tab.

You create a different type of constraint for each kind of table, as follows:

• For dimension tables, create a UNIQUE key constraint.
• For fact tables, create a FOREIGN key constraint and specify the referenced schema and referenced table. In the Constraint Definition area, include the foreign key columns in the fact table and the corresponding unique keys in the dimension table. An attempt to create a foreign key on a dimension table can fail if the foreign key column data does not match the unique key column data on the dimension table.

Creating the Query Workload

This step is part of the Process of Deploying Metadata for Oracle. For more information, refer to Oracle Database documentation.

A query workload is a sample set of physical queries that you want to optimize. Before you create the workload, you generate a Trace file with information on the slowest-running queries.

To generate the Trace file

You can generate the Trace file of the slowest-running queries using a tool that is appropriate to your database version, as described in the following list:

• Usage Tracking: Use this capability in Oracle BI to log queries and how long they take to run. Long running Oracle BI queries can then be executed as a script and used in conjunction with the Trace feature in the Oracle Database to capture the Oracle Database SQL code for these queries.
• Oracle Database Trace: Use this tool to identify the slowest physical query. You can enable the Trace feature either within Oracle Enterprise Manager Database Control or by entering SQL commands with the DBMS_MONITOR package. Once you enable the Trace feature, you use a script to create a Trace file to capture the SQL code for queries in a query workload table.
• Oracle Enterprise Manager: Use this tool to track slow-running queries.

TIP:   The capabilities that are described in the following sections are available in the Oracle Database, rather than as part of Oracle BI.

To analyze the information in the Trace file

1. Use the following guidelines when reviewing the Trace file:
   • When you have traced many statements at once, such as in batch processes, quickly discard any statements that have acceptable query execution times. Focus on those statements that take the longest times to execute.
   • Check the Query column for block visits for read consistency, including all query and subquery processing. Inefficient statements are often associated with a large number of block visits. The Current column indicates visits not related to read consistency, including segment headers and blocks that will be updated.
   • Check the Disk column for the number of blocks that were read from disk. Because disk reads are slower than memory reads, the value will likely be significantly lower than the sum of the Query and Current columns. If it is not, check for issues with the buffer cache.
   • Note that locking problems and inefficient PL/SQL loops can lead to high CPU time values even when the number of block visits is low.
   • Watch for multiple parse calls for a single statement, because this indicates a library cache issue.
2. After identifying the problem statements in the file, check the execution plan to learn why each problem statement occurred.

To load queries into the workload

• After you use the Trace utility to learn the names of the slowest physical queries, insert them into the USER_WORKLOAD table.

  Table 33 describes the columns of the USER_WORKLOAD table.

• Use INSERT statements to populate the QUERY column with the SQL statements for the slowest physical queries and the OWNER column with the appropriate owner names.

Creating Materialized Views

This step is part of the Process of Deploying Metadata for Oracle.

After you create the query workload table, use the appropriate tool for the Oracle Database version to create materialized views. In Oracle Database 10g, use the Summary Advisor on the Oracle Enterprise Manager Console and specify the query workload table that you created.

The Summary Advisor generates recommendations on improving the performance of the fact tables that you specify. The Summary Advisor displays the SQL code with which it will create the appropriate materialized views. Before indicating that the Summary Advisor should create the materialized views, review the following tips:

• The creation of a materialized view can fail if the SQL code includes a CAST statement.
• Ensure that the CREATE MATERIALIZED VIEW statement does not specify the same query that you provided as a workload table. If the statement does specify the same query, then the materialized views will likely not reflect the true performance gain. However, if the query is executed frequently, then the creation of the materialized view might still be worthwhile.
• Add a forward slash (/) to the end of the CREATE MATERIALIZED VIEW statement after the SQL statement. Otherwise, the SQL*Plus worksheet will not recognize it as a valid statement.

TIP:   SQLAccess Advisor can also help determine appropriate indexing schemes.