Oracle Analytics Best Practices Series: Optimal Performance and Usage

June 19, 2021 | 5 minute read
Ahmed Awan
Director, Customer Excellence, Analytics
Text Size 100%:

The best practice articles in this series are aimed at developers, designers, and administrators using Oracle Analytics. These guidelines describe how to design and configure various aspects of Oracle Analytics, with optimal performance and usage in mind.

Explore best practices for Oracle Analytics

  • Business Model Design
    There are many ways to configure the business model (the logical layer) in Oracle Analytics. By following the best practices described in this blog, you can avoid several runtime errors and significantly decrease your maintenance workload.
  • Setting Implicit Facts in Subject Areas
    In the Oracle Analytics repository (RPD) you can set an Implicit Fact in the Subject Area. This blog describes a use case.
  • Time Dimension Design
    Fact tables often include dates and potentially, many time dimensions. This blog describes the most efficient way to handle and configure your time dimensions in in Oracle Analytics.
  • Time Series
    This post describes how to improve the performance of Oracle Analytics reports using time series or filter functions.
  • Performance Tuning Multi-Dimensional Database Queries
    When Oracle Analytics uses a multi-dimensional database as a data source, there are additional design considerations that may have a big impact on performance.
  • Physical Table Aliases
    In Oracle Analytics you can create aliases of tables in the physical layer. This blog describes when to create tables aliases and why, with some common examples.
  • Dimensional Hierarchies, Level Keys and Content Levels
    In Oracle Analytics, dimensional hierarchies, level keys, and content levels together form the basis of navigation. This blog describes dimensional hierarchies and how to set them up to enhance the capabilities of Oracle Analytics.
  • IndexCol Function
    You use the IndexCol function when the columns or values in a calculation vary depending on the value of a session, repository, or presentation variable.
  • Finding the Oracle SQL_ID
    This blog shows you how to find the SQL_ID by mining the Oracle Analytics Usage Tracking tables and the Oracle database system table V$SQL (or GV$SQL for a RAC database).
  • Performance Tuning Relational Database Queries
    The purpose of this blog is to provide some high-level guidelines on how you can analyze and improve the performance of a report in Oracle Analytics.
  • Initialization Block Maintenance
    You might experience performance issues if you run lots of (the number depends on your performance requirements) initialization blocks whenever someone signs-in because initialization block queries are executed serially.
  • Best Practices for Initialization Blocks and Usage Tracking in Oracle Analytics
    This blog describes how to enable and monitor usage tracking for initialization blocks to better understand their performance.
  • Reporting on Catalog Object ACLs
    It’s often useful for administrators to see the permissions assigned to objects in the Oracle Analytics catalog. Catalog permissions are kept in access control lists or ACLs. Administrators can generate ACL reports using Catalog Manager (UI or command line utility).
  • Use Prompts before Opening for Faster Dashboards
    A dashboard is a collection of reports, frequently displayed as data visualizations, that are presented together on a single screen or in a series of screens.  A dashboard prompt is an optional component which allows you to specify values to be applied as filters or as part of column formulas, which then selects only the data you want to see.
  • Gathering and Analyzing Query Logs
    Query Logging is powerful diagnostic information available to the Admin users to analyze and troubleshoot issues related to query performance, error scenario and wrong results.
  • Timestamps in Oracle Analytics Cloud
    In Oracle Analytics, administrators can set the default time zone used for date and time calculations in System Settings, and this default setting applies to all calculations.  If you want to use a different timestamp in your calculations, you can use the method described in this blog.
  • Report Totals in Oracle Analytics
    This article describes ways to explicitly assign the aggregation rule for report totals, which helps improve performance, and is intended specifically for technical developers writing reports.
  • Case Statements in Oracle Analytics
    This article describes ways to improve performance through the use of enhanced formulations of case statements. It's intended only for technical developers writing formulas for reporting metrics.
  • Understanding Application Roles in Oracle Analytics
    This article provides an understanding of the application roles found in Oracle Analytics, especially targeted to administrators.
  • Performance Tuning with Excluded Column
    Many customers have unused columns in their analyses. Columns that are no longer required but still selected significantly impact performance and the way to improve performance is very simple.
  • Best Practices for Performance of Oracle Analytics Cloud with Enterprise Performance Management Cloud
    This article describes guidelines to follow when creating reports in Oracle Analytics Cloud using Enterprise Performance Management Cloud as the data source. Not following these recommendations can lead to performance issues.
  • Select the best option to render dashboards in Oracle Analytics Cloud
    This post describes how dashboard content is rendered and the advantages of each rendering option.
  • Google Big Query as a data source for the repository
    Beginning with the September 2022 Update for Oracle Analytics Cloud (OAC), you can import data from Google Big Query sources and model the data in the repository file (.rpd).  This article provides best practices for using this data source.
  • Minimizing Impact on Query Performance Due to Network Latency
    Network latency increases when Oracle Analytics Cloud connects to a database on a different data center. You can minimize the negative performance impact from this higher network latency by reducing the number of network roundtrips between Oracle Analytics Cloud and your data source.
  • Troubleshooting Data Sources Connectivity for Oracle Analytics Cloud
    This blog describes troubleshooting Private Access Channel connectivity tips & tricks for Oracle Analytics Cloud.
  • Speed up Network Troubleshooting with Oracle Cloud Network Path Analyzer
    This blog describes how Network Path Analyzer can quickly diagnose the OAC routing and security policy configuration and tell you if a broken reachability is caused by misconfiguration.
  • Real-World Performance Testing for Oracle Analytics Cloud
    Performance testing is an essential step to ensure that OAC can handle the expected workload without compromising its performance.
  • Choosing the proper Oracle Autonomous Data Warehouse database service name
    This article describes each prebuilt database service name in Oracle Autonomous Data Warehouse (ADW) and offers suggestions for choosing the best one for Oracle Analytics Cloud.
  • Oracle EPM Cloud as a Data Source for a Repository Subject Area
    This article describes how to import Oracle EPM Cloud metadata into the repository (.rpd) requires additional configuration on the Windows desktop/laptop running Oracle Analytics Client Tools.
  • Best Practices For Implementing Row-Level Security In Oracle Analytics
    It’s intended for Model Administrators who are using row-level security filters and covers row-level security which has “or logic” and uses empty values when a part of the filter is not applicable.  Many businesses have security constraints involving different dimensions for different groups of users and for combinations of them.  The simplest implementation will apply filters for all possibilities and use an empty value for dimensions that do not apply to the specific user.  The correct results are obtained, but the SQL contains extra tables that are not used, which can negatively impact performance.  This post explains how to get Oracle Analytics to dynamically exclude these tables.

Ahmed Awan

Director, Customer Excellence, Analytics

Previous Post

Why is Your Number Different from My Number? Ensuring Data Validation and Testing in Oracle Fusion Analytics Warehouse

Ashlesh Bajpai | 4 min read

Next Post

Analytics in Finance – Where do YOU stand?

Mitch Campbell | 4 min read