Oracle Retail Knowledge Base

Calendar initialization using MFCS integration

3 min read

Context

Runbook to load the retail calendar into AIF DATA through MFCS integration, instead of a file upload. The RDE extract reads the MFCS CALENDAR and SYSTEM_OPTIONS tables and populates the AIF staging table W_MCAL_PERIOD_DS, which the calendar load then builds into the dimension tables. Run it once the MFCS calendar is configured and the C_ODI_PARAM date range is set.

Quick access

Configuration

Review the following parameters in the C_ODI_PARAM table (scenario SIL_DAYDIMENSION) — see Initial setup - C_ODI_PARAM for the full setup:

Parameter nameValue
START_DT20070101
END_DT20351230

Configure the jobs

  1. Accessing POM click on the top left menu
  2. Select the sub menu “Tasks” and then “Batch Administration”
  3. Select the “AIF DATA” scheduler and then the “Standalone” chain
  4. Look for the RDE_DIM_FLOW_ADHOC process and enable only the following jobs:
    • DELETESTATS_JOB
    • RDE_EXTRACT_DIM_P12_MCALPERIODSDE_JOB
  5. Then look for the CALENDAR_LOAD_ADHOC process and disable the following jobs:
    • SI_W_MCAL_PERIOD_DS_JOB
    • COPY_SI_CALENDAR_JOB
    • STG_SI_CALENDAR_JOB

Run the RDE extract

  1. Click on the top left menu
  2. Select the sub menu “Tasks” and then “Batch monitoring”
  3. Select the AIF DATA scheduler, and restart the schedule to apply the changes
  4. After POM reload, select the AIF DATA scheduler on the Standalone chain
  5. Find the RDE_DIM_FLOW_ADHOC process, verify the active jobs are correct, and press the start button. The job RDE_EXTRACT_DIM_P12_MCALPERIODSDE_JOB will look on the MFCS CALENDAR and SYSTEM_OPTIONS tables to populate the AIF table W_MCAL_PERIOD_DS.
  6. After the execution finishes, perform the pre-load staging checks on AIF APEX.

Run the calendar load

  1. On the AIF DATA scheduler, Standalone chain, select the process CALENDAR_LOAD_ADHOC
  2. Using the filters, find the job CREATE_PARTITION_PRESETUP_JOB
  3. Click on the “Actions” button and then on “Edit Parameters”
  4. Specify a date in YYYY-MM-DD format that is before the start of your historical data but still within the range of fiscal calendar periods you plan to load. Use the same date you chose in Business date setup (“6–12 months before your historical data starts”).
  5. Execute the process.

After the load completes, run these checks in AIF APEX:

  • Confirm no warnings or errors are detected in RI_DIM_VALIDATION_V:
select * from RI_DIM_VALIDATION_V
  • Check the number of records on the tables in the Calendar subject area:
SELECT t.table_name,
       CASE
           WHEN o.object_name IS NOT NULL THEN
               TO_NUMBER(
                   EXTRACTVALUE(
                       XMLTYPE(
                           DBMS_XMLGEN.GETXML('SELECT COUNT(*) AS c FROM ' || t.table_name)
                       ),
                       '/ROWSET/ROW/C'
                   )
               )
           ELSE NULL
       END AS row_count
FROM c_ri_subjectarea t
LEFT JOIN all_objects o
       ON o.object_name = t.table_name
      AND o.object_type IN ('TABLE', 'VIEW', 'SYNONYM')
WHERE t.subjectarea = 'Calendar'
ORDER BY row_count DESC NULLS LAST;

Then perform the post-load checks.

Reference

Graph View

Backlinks