4Maintenance and Migration

This chapter contains the following:

How to Tune Performance for Reports

SQL query tuning can improve the performance of reports. Diagnose issues and an explain plan. Here are some factors that can slow down query performance, and some suggestions for improvement.

Factor Description Suggestions


Reports with no filters or filters that allow a large volume of data may hurt performance.

Use filter conditions to limit data.


Reports that join a lot of tables can run slowly.

Remove any unnecessary joins.

Data volumes

Reports with no filters or filters that allow a large volume of data may hurt performance.

Add filter conditions to limit data, preferably using columns with database indexes. Use caching for small tables.


Filters that use database indexes can improve performance.

Use SQL hints to manage which indexes are used.


Sub-queries can impact performance.

  • Avoid complex sub-queries and use Global Temporary Tables where necessary.

  • Avoid too many sub-queries in where clauses if possible. Instead, rewrite queries with outer joins.


It helps performance to prioritize aggregation in the database.

  • Use Oracle SQL Analytical functions for multiple aggregation.

  • Use CASE statements and DECODE functions for complex aggregate functions.

Review SQL Statements Used in Analyses

You can review logical and physical SQL statements using either of the following procedures.

Logical and Physical SQL

Logical SQL is non-source specific SQL that's issued to the Oracle BI Server for an analysis. Logical queries use column names from the subject areas in the Presentation Layer in the repository (RPD) metadata. Based on the logical request, the BI Server issues optimized source-specific SQL to the actual data sources in the Physical Layer of the metadata. If you have administrative privileges, you can review both logical and physical SQL for analyses.

Edit Mode

  1. Open the analysis in Edit mode and click the Advanced tab.

  2. In the SQL Issued section, review the logical SQL statement.

Administration Page

  1. On the Administration page, in the Session Management section, click the Manage Sessions link.

    Note: You must be a Business Intelligence Administrator to access the Administration and Manage Sessions page.
  2. On the Manage Sessions page, in the Action column, click the View Log link to review the SQL statement.

Archive and Move Analytics

You can archive your analytic objects and move them to a new location. You can bundle the whole catalog, specific folders, or multi-component objects as a .catalog file and upload the file to unarchive its data in another location.

Note: It's important to note that folders that contain seeded content should not be changed. Moving or adding a child folder to a seeded folder results in analytics not being visible in dashboards or infolets.

Create an Archive

  1. Locate the object in the catalog.

  2. Select More and then select Archive.

  3. In the Archive dialog box, select one or more of the following options:

    • Keep Permissions: Maintain the object or folder's existing permissions. If you don't select this, the archiving process doesn't include any permissions. Once unarchiving, the parent folder's permissions are assigned to all of the objects and folders.

    • Keep Time stamps: Maintain the Creation Time, Last Modified, and Last Accessed times assigned to the object or folder. Upon unarchiving, the Last Modified time is updated to indicate the time when the object or folder is unarchived. If you select this option, the Old option in the Paste Overview area of the Preferences dialog box is available when unarchiving. Use the Old option to overwrite existing catalog items older than the items in the archive.

      If you don't select this option, then the archiving process doesn't include time information and the Old option in the Paste Overview area of the Preferences dialog box isn't available.

  4. Click OK to download the archive file.

Move an Archived Object to a New Location

  1. Select the folder in the catalog where you want to upload the archived file.

  2. In the Tasks pane click Unarchive.

  3. In the Unarchive dialog box, browse for and select the archive file.

  4. Use the Replace option to specify whether to replace an existing folder or object with the same name.

    • All: Replace any existing folders or objects with the same names as folders or objects included in the archive file that you're uploading.

    • Old: Replace folders or objects except those folders or objects that exist, unless they're older than the source.

    • None: Add any new folders or objects, but preserve any existing folders or objects.

    • Force: Add and replace all folders or objects.

  5. Use the Access Control Lists option to specify how the folders or objects are assigned permissions using Access Control Lists Access when unarchived.

    • Inherit: Inherits the folder or object's permissions from its new parent folder.

    • Preserve: Preserves the folder or object's permissions as it was in the original, mapping accounts as necessary.

    • Create: Preserves the folder or object's permissions as it was in the original, creating and mapping accounts as necessary.

  6. Click OK.


You can export your financial reports from a source environment and import them to a target environment using implementation projects.

Only the financial reports in the /shared/Custom/Financials folder are exported, so make sure to copy all the financial reports, or the folders containing them, to this folder. In the Setup and Maintenance work area, create an implementation project that includes only the Create Financial Statements task. Then use the Manage Configuration Packages task to export and import the reports.

Note: For the Financial Reporting report definition migration service, from a source to a target environment, references to version IDs of dimension members hierarchies are synchronized to their version IDs in the target environment.