Oracle Analytics Weekly Roundup — 23-Feb

Posted on By Adrian Ward

There were no new items provided to summarise this week, so this roundup focuses on a set of “evergreen” topics and practical tips across Oracle Analytics, Fusion Data Intelligence, Autonomous Data Warehouse / 23ai, and SQL. Where relevant, links point to core product documentation and landing pages rather than news that doesn’t exist.

Oracle Analytics (OAC & OAS)

Item 1: Revisiting Data Modeling Best Practices in OAC/OAS

TL;DR: A clean semantic model in OAC/OAS still delivers the biggest performance and usability gains. Focus on conformed dimensions, clear subject areas, and minimal data duplication.

Even without a major new release this week, it’s worth revisiting the fundamentals of data modeling in Oracle Analytics Cloud (OAC) and Oracle Analytics Server (OAS). Many performance and usability issues we see in the field are still rooted in ad‑hoc data modeling choices that grow organically over time.

Key reminders:

  • Conformed dimensions: Ensure dimensions like DATE, CUSTOMER, and PRODUCT are shared across subject areas where possible. This simplifies cross‑analysis and reduces the need for complex joins in analyses.
  • Logical vs. physical separation: Keep your physical model (tables, joins, keys) clean and close to the source design, and use the logical layer to present business‑friendly structures. Avoid pushing business semantics down into physical joins unless necessary.
  • Measure consistency: Reuse logical columns for common measures (e.g. Revenue, Margin %) instead of redefining them per subject area. This helps with consistent KPIs across dashboards.

For teams planning a move from OAS to OAC, or consolidating legacy RPDs, now is a good time to refactor models before migration. Oracle’s documentation is still the best canonical reference for the semantic model concepts:

Item 2: Monitoring OAC Usage with Built‑In Usage Tracking

TL;DR: Usage tracking in OAC/OAS remains underused; enabling it gives you concrete data on which dashboards, users, and subject areas drive load and value.

Usage tracking is still one of the fastest ways to understand how your OAC/OAS environment is actually used. By enabling it and pointing it to a small reporting schema, you can answer questions like:

  • Which analyses and dashboards are most frequently executed?
  • Who are the top power users and which groups generate the most queries?
  • Which subject areas are effectively “dead” and could be candidates for deprecation?

Once enabled, you can import the usage tracking tables into your RPD and build an internal “Admin Analytics” subject area for performance tuning and governance. For configuration details, see:

Fusion Data Intelligence (FDI)

Item 3: Positioning FDI Alongside OAC

TL;DR: FDI complements, rather than replaces, OAC by providing curated, cross‑Fusion data products; OAC still plays a key role for custom models, external data, and advanced visualisation.

Fusion Data Intelligence (FDI) continues to evolve as the curated analytics and data platform for Fusion SaaS applications (ERP, HCM, SCM, CX). While there are no specific new public features to call out this week, it’s useful to clarify positioning that still causes confusion:

  • FDI: Focused on pre‑built data products, KPIs, and semantic models for Fusion data, with governed pipelines and a strong emphasis on out‑of‑the‑box value.
  • OAC: Remains the general‑purpose analytics front‑end for custom data models, non‑Fusion sources, and bespoke dashboards, both in the cloud and on‑prem (OAS).

In practice, many customers will run both: FDI as the Fusion‑centric data foundation, and OAC as the broader analytics and visualisation layer that can also consume FDI outputs where appropriate.

For the latest official positioning and documentation, refer to:

Autonomous Data Warehouse / 23ai

Item 4: Housekeeping and Cost Control in Autonomous Data Warehouse

TL;DR: Even with auto‑scaling and automation, ADW benefits from explicit housekeeping: archive cold data, review unused objects, and right‑size compute to avoid silent cost creep.

Autonomous Data Warehouse (ADW) and the broader Oracle Database 23ai stack continue to provide a lot of automation, but cost and performance still depend on basic hygiene:

  • Storage management: Identify large, rarely queried tables or partitions and consider moving them to cheaper storage tiers or summarising them into aggregates.
  • Compute profiles: Periodically review auto‑scaling behaviour and peak OCPU usage. If peaks are driven by a handful of heavy jobs, consider scheduling or optimising those rather than permanently over‑provisioning.
  • Object cleanup: Remove unused staging tables, temporary schemas, and abandoned development artifacts that accumulate over time.

Oracle’s ADW documentation remains the best reference for current capabilities and recommended practices:

Item 5: Planning for 23ai Features Without Over‑Promising

TL;DR: 23ai introduces a broad set of enhancements, but production teams should adopt them selectively, focusing on features that directly solve current pain points.

Oracle Database 23ai is positioned as a major release, but from a practical standpoint, most analytics teams will adopt it incrementally. Without diving into unannounced or speculative capabilities, the general guidance remains:

  • Start by aligning 23ai adoption with existing upgrade cycles rather than forcing a big‑bang move.
  • Prioritise features that reduce operational overhead (e.g. improved automation, manageability) over “nice‑to‑have” capabilities.
  • Use lower environments to validate behaviour with your existing OAC/OAS workloads before promoting to production.

For an overview of the 23ai platform and officially documented features, see:

SQL Tip of the Week

Item 6: Using QUALIFY to Simplify Top‑N Per Group Queries (Where Available)

TL;DR: On platforms that support it (including Autonomous Database with the appropriate compatibility), QUALIFY can make “top‑N per group” queries more readable by filtering directly on analytic functions.

A common pattern in analytics is “top N per group”, such as “latest order per customer” or “top 3 products per category”. Traditionally, this is done with a subquery or inline view. Where supported, the QUALIFY clause lets you filter on analytic functions in a single query block, improving readability.

Example: return the latest order per customer based on order_date:

SELECT
    customer_id,
    order_id,
    order_date,
    order_total
FROM   orders
QUALIFY
    ROW_NUMBER() OVER (
        PARTITION BY customer_id
        ORDER BY     order_date DESC
    ) = 1;

Without QUALIFY, you’d typically write:

SELECT *
FROM (
    SELECT
        customer_id,
        order_id,
        order_date,
        order_total,
        ROW_NUMBER() OVER (
            PARTITION BY customer_id
            ORDER BY     order_date DESC
        ) AS rn
    FROM   orders
)
WHERE rn = 1;

The logic is identical: assign a row number within each customer partition ordered by date descending, then keep only the first row. If your Oracle Database or Autonomous Database version supports QUALIFY, it’s a neat way to keep analytic queries concise and easier to maintain.

For official SQL language reference, see:

Weekly News