Calendar and Partition Setup

This is the first step that must be performed in all new environments, including projects that will not be implementing RI, but only AI Foundation or Planning solutions. Before beginning this step, ensure your configurations are complete per the initial configuration sections in the prior chapter. Your START_DT and END_DT variables must be set correctly for your calendar range (START_DT at least 12 months before start of history data) and the C_MODULE_ARTIFACT table must have all of the required tables enabled for partitioning. C_MODULE_EXACT_TABLE must be configured if you need PLAN partitions for planning data loads.

Upload the calendar file CALENDAR.csv (and associated context file) through Object Storage (packaged using the RAP_DATA_HIST.zip file).

Execute the HIST_ZIP_FILE_LOAD_ADHOC process. Example Postman message body:

{
"cycleName":"Adhoc", 
"flowName":"Adhoc",
"processName":"HIST_ZIP_FILE_LOAD_ADHOC"
}

Verify that the jobs in the ZIP file load process completed successfully using the POM Monitoring screen. Download logs for the tasks as needed for review.
Execute the CALENDAR_LOAD_ADHOC process. This transforms the data and moves it into all internal data warehouse tables. It also performs table partitioning based on your input date range.

Sample Postman message body:
```
{
  "cycleName":"Adhoc", 
  "flowName":"Adhoc", 
  "processName":"CALENDAR_LOAD_ADHOC",
  "requestParameters":"jobParams.CREATE_PARTITION_PRESETUP_JOB=2018-12-30,jobParams.ETL_BUSINESS_DATE_JOB=2021-02-06"
}
```
There are two date parameters provided for this command:
1. The first date value specifies the first day of partitioning. It must be some time before the first actual day of data being loaded. The recommendation is 1-6 months prior to the planned start of the history so that you have room for back-posted data and changes to start dates. You should not create excessive partitions for years of data you won’t be loading however, as it can impact system performance. The date should also be >= START_DT value set in C_ODI_PARAM_VW, because RAP cannot partition dates that don’t exist in the system; but you don’t need to partition your entire calendar range.
2. The second date (ETL business date) specifies the target business date, which is typically the day the system should be at after loading all history data and starting daily batches. It is okay to guess some date in the future for this value, but note that the partition process automatically extends 4 months past the date you specified. Your fiscal calendar must have enough periods in it to cover the 4 months after this date or this job will fail. This date can be changed later if needed, and partitioning can be re-executed multiple times for different timeframes.
If this is your first time loading a calendar file, check the RI_DIM_VALIDATION_V view to confirm no warnings or errors are detected. Refer to the AI Foundation Operations Guide for more details on the validations performed. The validation job will fail if it doesn’t detect data moved to the final table (W_MCAL_PERIOD_D). Refer to Sample Validation SQLs for sample queries you can use to check the data.
If you need to reload the same file multiple times due to errors, you must Restart the Schedule in POM and then run the ad hoc process C_LOAD_DATES_CLEANUP_ADHOC before repeating these steps. This will remove any load statuses from the prior run and give you a clean start on the next execution.

Note:
If any job having STG in the name fails during the run, then review the POM logs and it should provide the name of an external LOG or BAD table with more information. These error tables can be accessed from APEX using a support utility. Refer to the AI Foundation Operations Guide section on “External Table Load Logs” for the utility syntax and examples.

You can monitor the partitioning process while it’s running by querying the RI_LOG_MSG table from APEX. This table captures the detailed partitioning steps being performed by the script in real time (whereas POM logs are only refreshed at the end of execution). If the process fails in POM after exactly 4 hours, this is just a POM process timeout and it may still be running in the background so you can check for new inserts to the RI_LOG_MSG table.

The partitioning process will take some time (~5 hours per 100k partitions) to complete if you are loading multiple years of history, as this may require 100,000+ partitions to be created across the data model. This process must be completed successfully before continuing with the data load process. Contact Oracle Support if there are any questions or concerns. Partitioning can be performed after some data has been loaded; however, it will take significantly longer to execute, as it has to move all of the loaded data into the proper partitions.

You can also estimate the number of partitions needed based on the details below:

RAP needs to partition around 120 week-level tables if all functional areas are enabled, so take the number of weeks in your history time window multiplied by this number of tables.
RAP needs to partition around 160 day-level tables if all functional areas are enabled, so take the number of days in your history time window multiplied by this number of tables.

For a 3-year history window, this results in: 120*52*3 + 160*365*3 = 193,920 partitions. If you wish to confirm your final counts before proceeding to the next dataload steps, you can execute these queries from APEX:

select count(*) cnt from dba_tab_partitions where table_owner = 'RADM01' and table_name like 'W_RTL_%'
select table_name, count (*) cnt from dba_tab_partitions where table_owner = 'RADM01' and table_name like 'W_RTL_%' group by table_name

The queries should return a count roughly equal to your expected totals (it will not be exact, as the data model will add/remove tables over time and some tables come with pre-built partitions or default MAXVALUE partitions).