Oracle Analytics (OAC & OAS)
Item 1 – Using Data Flows for Repeatable Data Prep
TL;DR: Data Flows in Oracle Analytics Cloud and Oracle Analytics Server let you turn ad‑hoc data wrangling into a repeatable, schedulable pipeline. They’re ideal for lightweight transformations without involving DBAs or ETL tooling.
Many Oracle Analytics environments still rely on analysts manually exporting data to Excel or running one‑off SQL scripts for data preparation. Data Flows provide a middle ground: a governed, UI‑driven way to clean, join, and enrich data, and then persist the results back into a database or as a dataset.
Key practical uses:
- Joining subject areas and files: Combine a curated subject area (e.g. Sales) with an uploaded CSV (e.g. quarterly targets) without changing the underlying RPD or semantic model.
- Light transformations: Derive new columns, filter rows, and standardise codes (e.g. mapping multiple status values into a single reporting category).
- Persisting results: Write the output to an Autonomous Data Warehouse table or an internal dataset so dashboards can query the prepared data directly.
- Scheduling: Run the flow nightly/weekly to keep your curated dataset in sync with source changes.
For teams coming from classic OBIEE, Data Flows are often the easiest way to move “spreadsheet logic” into a governed, auditable pipeline without waiting for a full data‑warehouse change request.
Source: https://docs.oracle.com/en/cloud/paas/analytics-cloud/acsds/create-and-manage-data-flows.html
Item 2 – Custom Calculations in Workbooks
TL;DR: Custom calculations in OAC/OAS workbooks let you keep business logic close to the visualisation layer, useful for agile analysis and prototyping before pushing logic down into the semantic model or database.
While the ideal place for complex logic is usually the database or semantic model, there are many scenarios where you need quick, local calculations:
- What‑if metrics: e.g. “Revenue with 5% discount” or “Cost + 10% uplift” for scenario analysis.
- Ratio and share metrics: Contribution to total, year‑on‑year percentage change, or conversion rates.
- Conditional flags: Simple case expressions such as “High / Medium / Low” based on numeric thresholds.
Because these calculations live in the workbook, they’re easy to iterate with business users. Once a formula stabilises and becomes widely used, you can then promote it into the semantic model or database view for better governance and reuse.
Source: https://docs.oracle.com/en/cloud/paas/analytics-cloud/acsve/create-custom-calculations.html
Fusion Data Intelligence (FDI)
Item 3 – Treating FDI as a Productised Data Foundation
TL;DR: Fusion Data Intelligence provides a pre‑built, Oracle‑maintained data foundation for Fusion SaaS, reducing the need to reverse‑engineer ERP/HCM schemas and build everything from scratch.
For customers running Oracle Fusion Cloud Applications, a recurring challenge is getting consistent, analytics‑ready data out of complex operational schemas. FDI addresses this by delivering:
- Curated data model: Predefined entities and relationships that align to business concepts (e.g. invoice, supplier, worker), rather than raw transactional tables.
- Pre‑built metrics: Standard KPIs and measures that match common reporting needs, which you can extend rather than reinvent.
- Integration with Oracle Analytics: FDI content can be surfaced in OAC/OAS, allowing you to build dashboards on top of a consistent semantic layer.
From a project‑delivery perspective, FDI is best viewed as a starting point: use the delivered model and metrics where they fit, and layer custom logic and additional sources on top. This can materially shorten time‑to‑value compared to hand‑crafted data models built solely from raw Fusion tables.
Source: https://www.oracle.com/analytics/fusion-data-intelligence/
Autonomous Data Warehouse / 23ai
Item 4 – Why ADW Is Still a Strong Default for Analytics
TL;DR: Autonomous Data Warehouse remains a solid default for Oracle‑centric analytics workloads: it handles patching, tuning, and backups automatically while giving you a full Oracle Database feature set.
Many analytics teams want the robustness of Oracle Database without the operational overhead. ADW provides:
- Automated operations: Patching, backups, and basic tuning are handled by the service, reducing DBA workload.
- Elastic scaling: You can scale compute up/down to match workload peaks, which is particularly useful for month‑end reporting or ad‑hoc data science runs.
- Compatibility: It’s still an Oracle Database under the hood, so existing SQL skills, PL/SQL code, and BI tools (including OAC/OAS) continue to work.
With the 23ai generation, you also get the ongoing evolution of the Oracle Database platform itself, but for many analytics teams the main win is still operational simplicity plus familiar capabilities.
Source: https://docs.oracle.com/en/cloud/paas/autonomous-database/index.html
SQL Tip of the Week
Item 5 – Generating a Calendar Table on the Fly
TL;DR: Use a recursive subquery factoring clause (WITH clause) to generate a calendar date series directly in SQL, ideal for filling gaps in time‑series reports without a permanent calendar table.
Time‑series reports often need to show dates with zero activity (e.g. days with no sales). If you don’t have a dedicated calendar dimension, you can generate one on the fly and left‑join your facts to it.
Example: generate a daily calendar for a given month and join to a sales table.
-- Parameters: change these for your reporting window
VAR start_date VARCHAR2(10);
VAR end_date VARCHAR2(10);
EXEC :start_date := '2024-02-01';
EXEC :end_date := '2024-02-29';
WITH calendar (dt) AS (
SELECT TO_DATE(:start_date, 'YYYY-MM-DD') AS dt
FROM dual
UNION ALL
SELECT dt + 1
FROM calendar
WHERE dt + 1 <= TO_DATE(:end_date, 'YYYY-MM-DD')
)
SELECT
c.dt,
NVL(SUM(s.amount), 0) AS total_sales
FROM
calendar c
LEFT JOIN sales s
ON s.tran_date = c.dt
GROUP BY
c.dt
ORDER BY
c.dt
;
Notes:
- The recursive CTE (
calendar) generates one row per day between:start_dateand:end_date. - The
LEFT JOINensures every date appears, even when there are no matching rows insales. - This pattern is handy in OAC/OAS data sets or data flows when you need a complete date series without modifying the underlying schema.