8Maintenance and Migration

This chapter contains the following:

How to Tune Performance for Analytics and Reports

When you create analytics and reports, don't use blind queries and include sufficient filters when creating analytics or reports with hierarchies.

Blind Queries

Avoid blind queries because they are performed without filters and therefore fetch large data sets. Performance could be an issue with these queries and can easily overload the application. All Transactional Business Intelligence queries on large transaction tables must be time bound. For example, include a time dimension filter and additional filters to restrict by key dimensions such as worker. In addition, apply filters to columns that have database indexes in the transaction tables. This ensures a good execution plan is generated for the Business Intelligence query.

Hierarchies and Trees in Transactional Business Intelligence

Queries on trees and hierarchical dimensions such as manager can have an impact on performance. Transactional Business Intelligence uses a column-flattening approach to quickly fetch data for a specific node in the hierarchy. Still, because there is no pre-aggregation for the different levels of the hierarchy, carefully craft any query involving hierarchies to ensure that sufficient filters are applied to keep the result set small.

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 is issued to the Oracle BI Server for an analysis. Logical queries use column names from 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 repository metadata. If you have proper administrative privileges, you can review both logical and physical SQL for analyses.

Using Analysis in 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.

Using 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.

Localize Catalog Captions

As an administrator, you can localize the names of reporting objects that users create (known as captions) in the catalog. For example, you might localize the names of dashboards and analyses into Spanish and French.

To localize object names in the catalog, you export the captions from the catalog, localize them, and upload back to the catalog.

  1. Export the default captions to an XML file.

  2. Localize the downloaded XML file.

  3. Upload the localized XML file.

After translated caption XML is uploaded, translated strings take effect once the cache refreshes. Translated strings are protected during an upgrade.

Export Captions from the Catalog

To export captions:

  1. Click Administration.

  2. Click Export Captions.

  3. Click Browse and select the folder you want to localize in the catalog, then click OK.

  4. Save the XML file.

Localize Your Captions

Translate the XML file into one or more languages.

Upload Localized Captions to the Catalog

After you have localized captions, upload the translated XML files, one for each language.

To import a translated XMLfile:

  1. Click Administration.

  2. Click Import Captions.

  3. Click Browse and select the localized XML file, then click OK.

  4. Use the Select Language option to select the language to which you have localized, then click OK.

  5. Save the XML file.

To download, review, or delete imported captions files, use the Manage Captions option.

Archive and Move Analytics

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

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

Create an Archive

To create an archive file:

  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 do not select this option, then the archiving process does not include any permissions. Upon 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 LastModified time is updated to indicate the time at which 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. You use the Old option to overwrite existing catalog items that are older than the catalog items in the archive.

      If you do not select this option, then the archiving process does not include time information and the Old option in the Paste Overview area of the Preferences dialog box is not available.

  4. Click OK to download the archive file.

Move an Archived Object to a New Location

To unarchive a file:

  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: Select this option to replace any existing folders or objects with the same names as folders or objects included in the archive file that you are uploading.

    • Old: Select this option to replace folders or objects except those folders or objects that exist, unless they are older than the source.

    • None: Select this option to add any new folders or objects, but preserve any existing folders or objects.

    • Force: Select this option to 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.

Row Limits in Analyses

When you run an OTBI analysis that contains more than 65,000 records, the results are limited to 65,000 records due to a limit on the maximum number of records for analyses in cloud environments. Exports to Excel are limited to 25,000 rows and 50,000 cells.

It's recommended that you use BI Publisher reports to export large data sets.

About Warehouse Column Errors in Transactional Analyses

In some OTBI analyses and dashboards, users may encounter errors on some columns in their analyses in cases where non-transactional data sources are being queried.

Some subject areas and analyses in the OTBI catalog for offerings including Oracle Customer Experience include analyses which query subject area columns which map to data warehouse data, which is part of Oracle BI Applications and Oracle Transactional Business Intelligence Enterprise (OTBI-E) and is used for historical analysis. In cases where historical warehouse data is included in transactional analyses, users may encounter errors on these columns if the warehouse is not set up and configured.

In some cases, but not all, these conditions are managed by Business Intelligence repository (RPD) metadata which supports switching from warehouse to transactional sources using variables.

Apply Preferences in BI Publisher

If you're logged in to BI Publisher, and make changes to preferences, for example time zone and locale, to immediately apply the changes in your BI Publisher session, either:

  • Append the &relogon=1 parameter to the end of the page URL and re-load the page.

  • Log out of BI Publisher and log in again.