1. The Problem
Some Oracle Analysis reports do not expose a native date or last-updated timestamp column the way Fusion data models/ Raw tables do.
In a standard incremental-refresh setup, the pipeline needs a watermark column — typically a LAST_UPDATE_DATE or similar timestamp — to filter the source query so it only pulls rows that are new or changed since the last run. Fusion Raw tables provide this natively, so their pipelines can do fast incremental loads out of the box.
Analysis reports are different. They are often built on summarized or aggregated data with no direct date column to anchor incremental logic on. As a result, these reports have historically been handled as full loads on every run — re-pulling the entire dataset each time.
This becomes a serious problem at scale. A report can contain well over a million rows, and doing a complete full load on every scheduled run is slow, resource-intensive, and eventually impractical.
The solution: Oracle reports usually do contain a column that carries timeline information in an indirect form — most commonly a fiscal period column with values such as 19-12, 20-11, 21-01. Even though this isn't a date, it encodes enough information (year + period) to derive one. By creating a calculated date/timestamp column from the fiscal period, we give the pipeline a usable watermark column, which unlocks incremental refresh and eliminates the need for repeated full loads. The pipeline can then load only the last X days of data on each run instead of the full dataset, with X configurable per report.
2. Understanding the Fiscal Period Format
Oracle analysis reports commonly expose a fiscal period column with values in the format YY-PP:
Part | Meaning |
YY | Last 2 digits of the fiscal year label (e.g. 20 = FY2020) |
PP | Fiscal month within the fiscal year. |
Period-to-month mapping (Example Fiscal calendar: July – June)
Period | Month |
01 | Jul |
02 | Aug |
03 | Sep |
04 | Oct |
05 | Nov |
06 | Dec |
07 | Jan |
08 | Feb |
09 | Mar |
10 | Apr |
11 | May |
12 | Jun |
Important: the YY label refers to the start year of the fiscal cycle and stays fixed for all 12 periods of that cycle. Period 01 (July) falls in calendar year 20YY. Periods 07–12 (Jan–Jun) fall in calendar year 20YY + 1, even though the report's separate Fiscal Year field still shows 20YY for all of them.
Worked examples:
Fiscal Period | Fiscal Year (label) | Derived Calendar Date |
19-12 | 2019 | June 2020 |
20-07 | 2020 | January 2021 |
20-11 | 2020 | May 2021 |
21-01 | 2021 | July 2021 |
Note: Do not use the report's raw Fiscal Year column as the watermark — it stays flat (e.g. 2020) across all 12 periods of a cycle, so it carries no month-level granularity. Only the calculated column derived below is suitable for incremental refresh. |
3. Creating the Calculated Column in the Analysis Report
Follow these steps:
- Open the Analysis (report) in edit mode.
- Go to the Criteria tab, where the report's columns are listed.
- In the Subject Area panel on the left, expand the relevant folder and drag and drop a column (Fiscal Period) to add it to Selected Columns. Then click the gear icon (⚙) on that column and choose Edit formula.
- In the Column Formula dialog, tick Custom Headings and set a Column Heading (e.g.
Fiscal Calendar Timestamp), then clear the existing formula and enter the formula below.
CAST( TIMESTAMPADD(SQL_TSI_MONTH, CAST(SUBSTRING("Fiscal Period" FROM 4 FOR 2) AS INTEGER) - 1, CAST( CONCAT(CONCAT('20', SUBSTRING("Fiscal Period" FROM 1 FOR 2)), '-07-01 00:00:00') AS TIMESTAMP ) ) AS TIMESTAMP) |
- Replace "Fiscal Period" with the exact column name/case as it appears in your subject area if it differs.
- Click OK to confirm the formula parses without error.
- Click on the Results tab and spot-check a few values against the mapping table in Section 2 (e.g. 20-11 should show 2021-05-01 00:00:00).
- Save the report.
Formula breakdown
Step | Function | What it does |
1 | SUBSTRING("Fiscal Period" FROM 1 FOR 2) | Extracts YY (e.g. 20) |
2 | CONCAT('20', ...) | Builds full start year (2020) |
3 | CAST(... '-07-01 00:00:00' AS TIMESTAMP) | Anchors to July 1 of that year as a timestamp |
4 | SUBSTRING("Fiscal Period" FROM 4 FOR 2) | Extracts PP (e.g. 11) |
5 | TIMESTAMPADD(SQL_TSI_MONTH, PP - 1, anchor) | Adds the month offset |
6 | Outer CAST(... AS TIMESTAMP) | Ensures the output is a true timestamp type for the watermark |
Reuse for Other Reports
This same approach applies to any Oracle analysis report that lacks a native date column but has a fiscal-period-style column carrying timeline info. As long as the period follows the YY-PP format with July = period 01, the formula in Section 3 can be reused directly. If a report uses a different fiscal calendar start month, adjust the anchor month (-07-01) accordingly.