Oracle Analytics Weekly Roundup — March 8th

Posted on By Adrian Ward

There were no specific news items or links provided this week, so this roundup focuses on current themes, recent trends, and practical guidance across Oracle Analytics Cloud (OAC), Oracle Analytics Server (OAS), Fusion Data Intelligence (FDI), Autonomous Data Warehouse / 23ai, and SQL. All content is intentionally conservative and based on generally available capabilities rather than speculative or unannounced features.

Oracle Analytics (OAC & OAS)

1. Hardening Data Models for Self-Service Analytics

TL;DR: Spend time tightening up subject areas and semantic models so business users can safely self-serve without repeatedly calling on the BI team. Focus on clear naming, row-level security, and consistent metrics.

Many OAC and OAS environments are now dominated by self-service dashboards and workbooks. The bottleneck is no longer the visualisation layer, but the semantic layer that feeds it. A well-governed RPD (for OAS) or data model (for OAC) dramatically reduces support tickets and “shadow” spreadsheets.

Key practices you can reinforce this week:

  • Business-friendly naming: Rename columns and tables in the model to reflect business terms, not source system jargon. For example, use Customer Status instead of CUST_STAT_CD.
  • Centralised metrics: Define KPIs (e.g. Revenue, Margin %, Active Customers) once in the model and reuse them in all workbooks and analyses, rather than letting each analyst reinvent the logic.
  • Row-level security: Use application roles and data filters to ensure users only see the data they are entitled to. For OAS, this typically means RPD security filters; for OAC, consider using data security policies and filters at the dataset or model level.
  • Documented joins: Make joins explicit and documented so users understand grain and cardinality. This reduces “double counting” and performance issues when users drag multiple subject areas into a single workbook.

For a solid reference on modelling and security concepts in Oracle Analytics, see the official documentation:

2. Performance Tuning: Start with the Query, Not the Chart

TL;DR: When dashboards are slow, profile the generated SQL and data model first. Visual changes alone rarely fix systemic performance issues.

Performance complaints in OAC/OAS often surface as “the dashboard is slow”, but the root cause is usually in query design, data volume, or model complexity. Before you start removing charts or limiting filters, capture and review the SQL generated by the analysis or workbook.

  • Check query grain: Ensure you are not querying at transaction-level detail when the visualisation only needs aggregated data (e.g. monthly or weekly). Consider creating aggregate tables or using summary views in the model.
  • Filter early: Encourage designs that apply filters at the highest possible level (e.g. date range, region) before drilling into detail. This reduces the rows scanned and returned to OAC/OAS.
  • Use database features: Push as much work as possible down to the database (e.g. window functions, aggregations) rather than relying on post-processing in the BI server.

Useful references on performance and tuning:

Fusion Data Intelligence (FDI)

3. Treat FDI as a Productised Data Foundation, Not Just Another Source

TL;DR: FDI gives you a curated, governed data foundation for Fusion applications. Use it as the primary source of truth for cross-functional analytics rather than stitching together raw extracts.

Fusion Data Intelligence is increasingly central for customers running Oracle Fusion Cloud applications (ERP, HCM, SCM, CX). Instead of building bespoke pipelines from each Fusion module, FDI provides a standardised, modelled data layer and subject areas designed for analytics.

Some practical ways to get more value from FDI:

  • Align KPIs with FDI definitions: Where possible, adopt the KPI and metric definitions provided by FDI to avoid local variations of “Revenue” or “Headcount”.
  • Use FDI as the hub: When combining Fusion data with external sources (e.g. legacy systems, spreadsheets), treat FDI as the hub and integrate other data into that model, rather than bypassing it.
  • Govern access centrally: Leverage the security and role model that comes with FDI so that analytics entitlements align with application entitlements.

To understand the current scope and supported domains, refer to the official FDI documentation and release notes:

Autonomous Data Warehouse / 23ai

4. Using ADW as the Core Analytics Engine Behind OAC/OAS

TL;DR: Autonomous Data Warehouse (ADW) remains a strong default choice for powering OAC and FDI workloads. Focus on partitioning, statistics, and resource management to keep it responsive under mixed workloads.

Many Oracle Analytics deployments now standardise on ADW (and more broadly Oracle Database 23ai) as the central analytics engine. Even without using advanced or experimental features, you can gain significant performance and manageability benefits by following a few basics:

  • Partition large fact tables: Partition by date or another natural key to improve pruning and manageability. This is especially important for time-series dashboards and rolling-window analyses.
  • Keep statistics fresh: Use automatic statistics gathering, but also schedule targeted stats refreshes after large data loads to avoid suboptimal execution plans.
  • Separate workloads: Consider using separate services (e.g. LOW, MEDIUM, HIGH) or consumer groups for ETL, ad hoc queries, and scheduled reporting to reduce resource contention.

For current ADW and 23ai capabilities, see:

SQL Tip of the Week

5. Using Window Functions for “Top N per Group” in Analytics

TL;DR: Instead of complex correlated subqueries, use window functions like ROW_NUMBER() to get the top N rows per group. This pattern is efficient and plays nicely with OAC/OAS and ADW.

“Top N per group” is a common requirement in analytics: for example, “top 3 products per region by revenue”. Window functions provide a clear and performant way to do this in Oracle Database, ADW, and 23ai.

Example: Top 3 products by revenue per region


SELECT region,
       product_name,
       revenue
FROM (
    SELECT region,
           product_name,
           revenue,
           ROW_NUMBER() OVER (
               PARTITION BY region
               ORDER BY revenue DESC
           ) AS rn
    FROM sales_summary
) t
WHERE rn <= 3
ORDER BY region, revenue DESC;

How it works:

  • ROW_NUMBER() OVER (PARTITION BY region ORDER BY revenue DESC) assigns a rank starting at 1 for the highest revenue product in each region.
  • The outer query filters to the top 3 (rn <= 3) per region.
  • This pattern can be used directly in views or materialized views that feed OAC/OAS subject areas.

For more on analytic functions in Oracle SQL, see:

Weekly News