Oracle Analytics Weekly Roundup — 20-Apr

Posted on By Adrian Ward

Note: No specific news items or links were provided this week, so this roundup focuses on current themes, recent patterns in the Oracle ecosystem, and evergreen techniques that remain highly relevant for OAC, OAS, FDI, ADW/23ai, and SQL practitioners.

Oracle Analytics (OAC & OAS)

1. Hardening Data Access with Row-Level Security in OAC/OAS

TL;DR: Use row-level security (RLS) via session variables and security filters to ensure users only see data they are entitled to, without duplicating datasets or dashboards.

Row-level security remains one of the most impactful – and often underused – capabilities in both Oracle Analytics Cloud and Oracle Analytics Server. Instead of building separate datasets or workbooks per business unit, you can centralise logic in the semantic model and drive access rules from user attributes.

In practice, a common pattern is:

  • Define session variables (e.g. USER_REGION) populated from an identity store or a lookup table.
  • Apply data filters (security filters) on logical tables in the semantic model, referencing those variables.
  • Test with different user accounts to ensure the filters behave as expected across dashboards and DV projects.

This approach scales well when you have multiple dimensions of security (region, line of business, legal entity). It also keeps your visualisations simple: the same workbook can be reused by many audiences, with the data filtered transparently in the background.

For official background on security in Oracle Analytics, see:
https://docs.oracle.com/en/cloud/paas/analytics-cloud/index.html

2. Performance Tuning with Aggregate Tables and Query Caching

TL;DR: For high-volume dashboards, combine aggregate tables in the semantic model with query caching to reduce response times and load on the underlying database.

When usage grows, even well-designed datasets can start to struggle. Two long-standing techniques still pay big dividends:

  1. Aggregate tables: Pre-aggregate fact data at common grains (e.g. month, region, product group) and map these tables into the semantic model using logical table sources with appropriate content levels.
  2. Query cache: Enable and tune the BI Server cache (OAS) or equivalent caching mechanisms in OAC to reuse results for frequently executed queries.

Key considerations:

  • Ensure aggregate tables are refreshed on a predictable schedule aligned with business SLAs.
  • Use usage tracking to identify the most common query patterns and target those for aggregation.
  • Test failover: if aggregates are stale or unavailable, queries should gracefully fall back to base-level tables.

More details on modelling aggregates are in the Oracle Analytics documentation:
https://docs.oracle.com/en/middleware/bi/analytics-server/index.html

Fusion Data Intelligence (FDI)

3. Treating FDI as a Productised Data Warehouse Layer

TL;DR: FDI works best when treated as a curated semantic and data foundation for Fusion Apps, not just “another reporting schema” to query ad hoc.

Fusion Data Intelligence continues to evolve as the strategic analytics layer for Fusion Cloud Applications (ERP, HCM, SCM, CX). A practical way to get value quickly is to treat FDI as a productised data warehouse rather than a raw operational store.

Pragmatic patterns we see working well:

  • Start with delivered subject areas and KPIs, then extend only where necessary.
  • Use FDI as the authoritative source for cross-pillar reporting (e.g. HCM + ERP), rather than stitching together multiple exports downstream.
  • Align your semantic model in OAC/OAS with FDI’s conformed dimensions to avoid re-inventing integration logic.

Because FDI is tightly coupled with Fusion Apps’ data model and security, it can significantly reduce the effort required to build trustworthy analytics – provided you lean into the delivered content and extend it carefully.

For the latest official information on FDI, see:
https://docs.oracle.com/en/cloud/saas/index.html

4. Governance and Change Management Around FDI Extensions

TL;DR: Treat FDI extensions (custom facts, attributes, and KPIs) as governed assets, with clear ownership, versioning, and regression testing.

As teams build more on top of FDI, the risk is that “just one more custom metric” creeps into dozens of dashboards. To keep things manageable:

  • Establish a catalogue of custom FDI metrics and attributes, with business definitions and owners.
  • Use lower environments to regression test FDI changes against key dashboards before promoting to production.
  • Document dependencies between FDI extensions and downstream OAC/OAS workbooks or external consumers.

Even lightweight governance avoids surprises when Fusion quarterly updates roll out and underlying objects shift.

Autonomous Data Warehouse / 23ai

5. Using ADW as the Analytics Hub for Hybrid Oracle Landscapes

TL;DR: ADW remains a strong central hub for combining Fusion, on-prem E-Business Suite, and third-party data, especially when paired with OAC.

Many organisations now run a mix of Fusion SaaS, legacy on-prem systems, and external SaaS platforms. ADW (and 23ai as it rolls out) provides:

  • Elastic compute for ETL/ELT and analytics workloads.
  • Built-in security and separation of duties for data engineering vs analytics teams.
  • Tight integration with OAC data models and datasets.

A typical pattern is to land Fusion/FDI extracts, E-Business Suite data, and third-party feeds into ADW, then build a conformed warehouse layer that OAC/OAS can consume. This keeps heavy lifting off transactional systems and gives you more control over performance and governance.

Official ADW and 23ai information:
https://docs.oracle.com/en/cloud/paas/autonomous-database/index.html

6. Cost Control with Auto-Scaling and Workload Isolation

TL;DR: Use separate ADW instances or services for dev/test vs prod, and leverage auto-scaling to handle peak loads without over-provisioning.

With ADW, it’s easy to spin up environments – and just as easy to let costs creep. Practical controls include:

  • Separate prod vs non-prod ADW instances with different resource caps.
  • Enable auto-scaling where unpredictable peaks occur (e.g. month-end reporting).
  • Use resource management (consumer groups) to protect critical workloads from ad hoc queries.

These patterns help keep performance predictable while avoiding permanent over-allocation of compute.

SQL Tip of the Week

7. Using MATCH_RECOGNIZE for Simple Time-Series Pattern Detection

TL;DR: Oracle’s MATCH_RECOGNIZE clause is not just for complex event processing; it can simplify common “find this pattern in time-series data” problems.

Suppose you want to find customers whose daily balance has fallen for three consecutive days. Instead of complex self-joins, you can use MATCH_RECOGNIZE:

SELECT *
FROM   account_balances
MATCH_RECOGNIZE (
  PARTITION BY customer_id
  ORDER BY balance_date
  MEASURES
    FIRST(balance_date) AS start_date,
    LAST(balance_date)  AS end_date,
    FIRST(balance)      AS start_balance,
    LAST(balance)       AS end_balance
  PATTERN (down down down)
  DEFINE
    down AS balance < PREV(balance)
);

This query:

  • Partitions data by customer_id and orders by balance_date.
  • Looks for three consecutive rows where each day’s balance is lower than the previous day.
  • Returns the start/end dates and balances for each detected pattern.

MATCH_RECOGNIZE is available in Oracle Database (including ADW and 23ai) and is well worth adding to your toolkit for analytics-style SQL.

For full syntax and examples, see:
https://docs.oracle.com/en/database/oracle/oracle-database/index.html

Weekly News