Oracle Retail Knowledge Base

3 Planning and Flex Fact Configuration

13 min read

Mirror of Oracle documentation

Converted for search and offline reading. Authoritative source: Oracle. Diagrams and some complex tables are simplified — check the PDF when in doubt.

3 Planning and Flex Fact Configuration

Retail Insights supports Merchandise Financial Planning (MFP) data on four main interfaces, referred to as PLAN1 through PLAN4 tables, Assortment Planning (AP) data on a fifth interface called PLAN5, MFP budgets for LPO on a sixth interface called PLAN6, as well as two IPO Cloud Service-Demand Forecasting forecast interfaces (PLANFC1 and PLANFC2) and four flexible fact interfaces (FLEXFACT1 to FLEXFACT4) for any other external data. All of these tables have configurable data levels to align with the hierarchy levels used in MFP, IPO Cloud Service-Demand Forecasting, or other source systems, but they must be configured prior to using them. The following section describes how to configure RI flexible interfaces for first-time use.

Setting Data Levels

Perform the following procedure to update the expected data levels for the planning interfaces. These levels determine which values the primary key columns are joined with inside Retail Insights (for example, is your plan at Department level or Subclass level). Each interface can be configured with a different set of levels.

1. Access the Control & Tactical Center to make updates to the C_ODI_PARAM_VW table.

2. Search for parameter names in the list below, depending on which interfaces you wish to use:

Table 3-1 C_ODI_PARAM Planning and Flex Fact Parameters

Planning FactsFlexible FactsPlanning Forecast Facts
RI_PLAN1_CAL_LEVELRI_FLEXFACT1_CAL_LEVE
L		RI_PLANFC1_CAL_LEVEL
RI_PLAN1_ORG_LEVELRI_FLEXFACT1_ORG_LEV
EL		RI_PLANFC1_ORG_LEVEL
RI_PLAN1_PROD_LEVELRI_FLEXFACT1_PROD_LE
VEL		RI_PLANFC1_PROD_LEVEL
RI_PLAN1_SUPP_LEVELRI_FLEXFACT1_SUPP_LEV
EL		RI_PLANFC1_SUPP_LEVEL
RI_PLAN1_ATTR_LEVELRI_FLEXFACT1_ATTR_LEV
EL		RI_PLANFC1_ATTR_LEVEL
RI_PLAN2_CAL_LEVELRI_FLEXFACT2_CAL_LEVE
L		RI_PLANFC2_CAL_LEVEL
RI_PLAN2_ORG_LEVELRI_FLEXFACT2_ORG_LEV
EL		RI_PLANFC2_ORG_LEVEL
RI_PLAN2_PROD_LEVELRI_FLEXFACT2_PROD_LE
VEL		RI_PLANFC2_PROD_LEVEL
RI_PLAN2_SUPP_LEVELRI_FLEXFACT2_SUPP_LEV
EL		RI_PLANFC2_SUPP_LEVEL
RI_PLAN2_ATTR_LEVELRI_FLEXFACT2_ATTR_LEV
EL		RI_PLANFC2_ATTR_LEVEL

Table 3-1 (Cont.) C_ODI_PARAM Planning and Flex Fact Parameters

Planning FactsFlexible FactsPlanning Forecast Facts
RI_PLAN3_CAL_LEVELRI_FLEXFACT3_CAL_LEVE
L
RI_PLAN3_ORG_LEVELRI_FLEXFACT3_ORG_LEV
EL
RI_PLAN3_PROD_LEVELRI_FLEXFACT3_PROD_LE
VEL
RI_PLAN3_SUPP_LEVELRI_FLEXFACT3_SUPP_LEV
EL
RI_PLAN3_ATTR_LEVELRI_FLEXFACT3_ATTR_LEV
EL
RI_PLAN4_CAL_LEVELRI_FLEXFACT4_CAL_LEVE
L
RI_PLAN4_ORG_LEVELRI_FLEXFACT4_ORG_LEV
EL
RI_PLAN4_PROD_LEVELRI_FLEXFACT4_PROD_LE
VEL
RI_PLAN4_SUPP_LEVELRI_FLEXFACT4_SUPP_LEV
EL
RI_PLAN4_ATTR_LEVELRI_FLEXFACT4_ATTR_LEV
EL
RI_PLAN5_CAL_LEVEL
RI_PLAN5_ORG_LEVEL
RI_PLAN5_PROD_LEVEL
RI_PLAN5_SUPP_LEVEL
RI_PLAN5_ATTR_LEVEL
RI_PLAN5_SOURCE
RI_PLAN6_CAL_LEVEL
RI_PLAN6_ORG_LEVEL
RI_PLAN6_PROD_LEVEL
RI_PLAN6_SUPP_LEVEL
RI_PLAN6_ATTR_LEVEL

3. Modify the parameters as needed using the following list of supported values. A value of ALL means the column should be set to a default value of -1 on the data file and it will not be joined with any other dimension in RI during ETL. A value of FLEX# indicates an alternate hierarchy level will be used, such as FLEX1 matching data on the W_PRODUCT_FLEX_D.FLEX1_CHAR_VALUE field. Usage of flex fields assumes that this is the same level also used in Planning applications for the plan data.

Table 3-2 C_ODI_PARAM Planning and Flex Fact Values

Product
(PROD)		Organization
(ORG)		Calendar
(CAL)		Supplier
(SUPP)		Attribute
(ATTR)		Plan Source
(SOURCE)
CMPCOMPANYYEARSUPPLIERC (for Color)AP_PLAN1_E
XP

Table 3-2 (Cont.) C_ODI_PARAM Planning and Flex Fact Values

Product
(PROD)		Organization
(ORG)		Calendar
(CAL)		Supplier
(SUPP)		Attribute
(ATTR)		Plan Source
(SOURCE)
DIVCHAINHALFYEARPARENT_SUP
PLIER		B (for Brand)AP_PLAN2_E
XP
GRPAREAQUARTERALLFLAVORAP_PLAN3_E
XP
DEPTREGIONPERIODFABRIC
CLSDISTRICTWEEKSCENT
SBCLOCATIONDAYSIZE
ITEMCHANNELGREGORIANY
EAR		STYLE
ITEM_LEVEL1CHANNEL_CO
UNTRY		GREGORIANQ
UARTER		ALL
ALLPRICE_ZONEGREGORIAN
MONTH
FLEX1 -
FLEX20		ALLGREGORIAND
AY
FLEX1 –
FLEX20

4. Specific to the calendar (CAL) settings, if you are using DAY or WEEK level calendar and are changing the parameter for any PLAN, FLEX, or FACT table, then you must run the following utility in APEX to make that change. Only updating the parameter in C_ODI_PARAM does not perform any table partitioning or cleanup of related configurations, which this process will do for you. Partitioning is only supported on DAY and WEEK level data, so if you only plan to use higher levels above WEEK, then just update the C_ODI_PARAM setting directly from Control & Tactical Center.

For the function call, specify your target table name along with a value of DAY or WEEK in the process call below. If you already ran this once, but are changing the level to a different value, then run it again for the new value (for example, changing from DAY to WEEK, or WEEK to DAY). When changing the value, existing partitions will be dropped and all data purged in order to reconfigure the interface, so backup any data that needs to be preserved.

BEGIN
RI_SUPPORT_UTIL.CONFIGURE_CAL_LEVEL_FOR_TABLE(p_table_name =>
'W_RTL_FLEXFACT1_F', p_cal_level => 'DAY');
END;

5. For RI customers, raise a Service Request with Oracle Support when all configurations are complete, stating that the Planning Configuration processes must be executed on your environment (provide the environment name and summarize the interfaces you plan to use). Part of the configuration process directly alters the application metadata to use your specified levels, so it currently must be managed by Oracle. This step only applies if you need to use the Reporting functionality of RI. It does not apply if you are only loading data for AI Foundation applications.

The plan source parameter for the PLAN5 interface allows you to select which AP plan intersection you wish to load from. By default, the AP_PLAN1_EXP table will hold SKU/ store level plan data and the AP_PLAN2_EXP table will hold style-color/store level plan data. If you will only be using the data for Retail Insights reporting, then it’s best to load the

data at SKU/store to enable the maximum amount of reporting across fact areas. The style-color/store data is typically used for fashion customers and may be loaded into RI if you need it for any downstream purposes, such as feeding AI Foundation applications that will run at the style-color level. Style-color plan data will only work in RI reports which are above sku level.

Preparing Data Files

After the interfaces are configured, you must prepare the data files for upload to Retail Insights. If you are using MFP, AP, or IPO Cloud Service-Demand Forecasting Cloud Services, the data can be automatically integrated between applications. This involves a one-time setup process during your Planning implementation to enable the batch processes that move data from PDS to RI, and then enable the RI jobs to consume that data. In that case the rest of this section does not apply. Proceed to the next section on partitioning the tables instead.

If you are uploading data files directly to RI, then there are some rules and guidelines to follow when creating the files:

  • All key columns on the interface must be populated, even if you have specified “ALL” as the data level. You should use a default value of -1 to populate these fields. This includes the fields PROD_DH_NUM, PROD_DH_ATTR, ORG_DH_NUM, SUPPLIER_NUM, as well as all other columns before the CAL_DATE on the flex interfaces.

  • The calendar (CAL_DATE) field must always be a date. If loading the data above day level, use the end-of-period date. The format must match the date mask specified on the context (CTX) file.

  • The PLANNING_TYPE_CODE field was originally used to specify if the plan type was COST or RETAIL, but this makes no functional difference in reporting at this time and can be set to any value.

  • The VERSION_NUM field specifies the plan version in numerical order, starting with 0. When integrating with MFP, the Original Plan is always version 0, and the Current Plan is version 1. These two versions correlate to OP and CP metrics in Retail Insights metadata. Versions greater than 1 may be used to capture version history in the RI database, and the highest available version will be shown in the CP set of metrics.

  • The DATASOURCE_NUM_ID field must be provided with a hard-coded value of 1, similar to all other RI interface specifications.

  • The INTEGRATION_ID field must be provided with a unique value that identifies the record, such as a concatenation of all primary key values.

  • The data file may use standard RI formatting, which uses pipes (|) as delimiters for the columns, or you may pick other delimiters as needed. You can change the file format options in the CTX file.

The accepted list of data files would align to the interfaces:

  • W_RTL_PLAN1_PROD1_LC1_T1_FS.dat

  • W_RTL_PLAN2_PROD2_LC2_T2_FS.dat

  • W_RTL_PLAN3_PROD3_LC3_T3_FS.dat

  • W_RTL_PLAN4_PROD4_LC4_T4_FS.dat

  • W_RTL_PLAN5_PROD5_LC5_T5_FS.dat

  • W_RTL_PLAN6_PROD6_LC6_T6_FS.dat

  • W_RTL_PLANFC_PROD1_LC1_T1_FS.dat

  • W_RTL_PLANFC_PROD2_LC2_T2_FS.dat

  • W_RTL_FLEXFACT1_FS.dat

  • W_RTL_FLEXFACT2_FS.dat

  • W_RTL_FLEXFACT3_FS.dat

  • W_RTL_FLEXFACT4_FS.dat

All of the files must be packaged into a ZIP file for upload, such as RI_MFP_DATA.zip or RI_EXT_DATA.zip. The zip file for these can be marked as optional (which means the nightly batch will not wait for them to arrive before starting) or required (meaning the batch must receive them every night to start processing). It is standard to mark these files as required, since you do not want the batch to run without your planning data, or the next day’s reporting could be inaccurate.

Lastly, you must also provide context (CTX) files for each interface, which specifies the exact columns you are populating with data. This allows you to provide only a subset of the many columns on the interface, as long as the key columns and at least one data column are populated. Each CTX file is named similarly to the interface (e.g.

W_RTL_PLAN1_PROD1_LC1_T1_FS.dat.ctx) and contains the file format parameters such as column delimiter and header row counts, followed by a single column of values matching the interface field names. Refer to the Retail Insights Interfaces Guide for complete details on the available fields. Refer to the Retail Analytics and Planning Implementation Guide for complete details on CTX file contents.

Partition Tables

The first planning table (W_RTL_PLAN1_PROD1_LC1_T1_F) requires special partition structures due to its use in integrating data to the AI Foundation applications like Promotion and Markdown Optimization. The other plan tables (PLAN2-6) may also be partitioned but it’s not required to use them with AI Foundation at this time. The flex fact and forecasting tables also require similar partitioning before they can be used. The plan and flex tables have flexible partitions which can be either Day or Week level, depending on the data level you plan to use.

The plan and flex fact partitioning is configured in table C_MODULE_EXACT_TABLE where MODULE_CODE in (PLAN1, PLANFC, FLEXFACT1, FLEXFACT2, FLEXFACT3, FLEXFACT4). Modify the columns PARTITION_COLUMN_TYPE and PARTITION_INTERVAL to be one of the following values:

  • If your input data will be at Day level, set both columns to ‘DY’

  • If your input data will be at Week level, set both columns to ‘WK’

You must also enable the partitioning process in C_MODULE_ARTIFACT by locating the rows for MODULE_CODE in (PLAN1, PLANFC, FLEXFACT1, FLEXFACT2, FLEXFACT3, FLEXFACT4) and setting ACTIVE_FLG=Y and PARTITION_FLG=Y. If your plan data will extend into the future, you must also change PARTITION_FUTURE_PERIOD to the number of future months that need partitions built beyond the specified business date (e.g. use a value of 6M to partition 6 months into the future).

Once the configuration steps are completed, you must run the adhoc POM process CREATE_PARTITION_ADHOC to apply them. If you already have nightly batches running, then the ETL date is being managed there, and you must disable both the

ETL_BUSINESS_DATE_JOB and W_RTL_CURR_MCAL_G_JOB in this process and only run the two partition jobs by themselves.

Example Postman message body to send to POM via Rest API call:

{
"cycleName": "Adhoc",
"flowName":"Adhoc",
"processName":"CREATE_PARTITION_ADHOC",
"requestParameters":"jobParams.CREATE_PARTITION_PRESETUP_
JOB=2018-12-30,jobParams.ETL_BUSINESS_DATE_JOB=2021-02-06"
}

If you disabled the business date jobs, then the process call would be like this instead:

{
"cycleName": "Adhoc",
"flowName":"Adhoc",
"processName":"CREATE_PARTITION_ADHOC",
"requestParameters":"jobParams.CREATE_PARTITION_PRESETUP_JOB=2018-12-30"
}

If you are entering the parameters from the POM UI instead of Postman, then you must specify each date as the input parameter on the jobs listed above. The first date is added as the parameter on CREATE_PARTITION_PRESETUP_JOB and the second date is added on ETL_BUSINESS_DATE_JOB (if using).

Loading Plan and Forecast Data

The plan and forecast data will be loaded either from flat files or directly from the planning systems. If you are loading from flat file, then you will run the COPY/STG jobs to import the file ata followed by the fact loads (*_F_JOB) and disable any other jobs. If you are loading from Planning directly, then you would use the _SDE_JOB programs followed by the fact loads (_F_JOB) and disable any other jobs. The forecast direct-loads are run outside the nightly batch as a recurring flow you can schedule, so that the weekly forecast load does not impact your nightly batch runtimes.

Table 3-3 Dataset Jobs

Job Ad Hoc Processes Usage or Flows W_RTL_PLAN1_PROD1_LC1_T1_FS_C N/A (Nightly Only) Load the MFP and AP planning flat OPY_JOB files from the server into the staging W_RTL_PLAN2_PROD2_LC2_T2_FS_C area in the database. Only used as OPY_JOB part of nightly batch processing, flat files cannot be loaded in ad hoc W_RTL_PLAN3_PROD3_LC3_T3_FS_C processes. Tables are truncated at the OPY_JOB beginning of each execution so new W_RTL_PLAN4_PROD4_LC4_T4_FS_C data can be inserted. OPY_JOB W_RTL_PLAN5_PROD5_LC5_T5_FS_C OPY_JOB W_RTL_PLAN6_PROD6_LC6_T6_FS_C OPY_JOB W_RTL_PLAN1_PROD1_LC1_T1_FS_S TG_JOB W_RTL_PLAN2_PROD2_LC2_T2_FS_S TG_JOB W_RTL_PLAN3_PROD3_LC3_T3_FS_S TG_JOB W_RTL_PLAN4_PROD4_LC4_T4_FS_S TG_JOB W_RTL_PLAN5_PROD5_LC5_T5_FS_S TG_JOB W_RTL_PLAN6_PROD6_LC6_T6_FS_S TG_JOB W_RTL_PLAN1_PROD1_LC1_T1_FS_S LOAD_PLANNING1 Imports planning data directly from DE_JOB _DATA_ADHOC the MFP and AP exports from W_RTL_PLAN2_PROD2_LC2_T2_FS_S LOAD_PLANNING2 Planning Data Store (PDS) to Retail DE_JOB _DATA_ADHOC Insights. The data is inserted into the staging tables, and assumes you are W_RTL_PLAN3_PROD3_LC3_T3_FS_S LOAD_PLANNING3 not providing any data via flat file DE_JOB _DATA_ADHOC (each interface should only come W_RTL_PLAN4_PROD4_LC4_T4_FS_S LOAD_PLANNING4 from either MFP/AP or flat file but not DE_JOB _DATA_ADHOC both). W_RTL_PLAN5_PROD5_LC5_T5_FS_S LOAD_PLANNING5 DE_JOB _DATA_ADHOC W_RTL_PLAN6_PROD6_LC6_T6_FS_S LOAD_PLANNING6 DE_JOB _DATA_ADHOC W_RTL_PLAN1_PROD1_LC1_T1_F_JO LOAD_PLANNING1 Transforms and loads planning data B _DATA_ADHOC from staging areas into the final fact W_RTL_PLAN2_PROD2_LC2_T2_F_JO LOAD_PLANNING2 tables. Used both for the nightly B _DATA_ADHOC processing and for ad hoc loads. W_RTL_PLAN3_PROD3_LC3_T3_F_JO LOAD_PLANNING3 B _DATA_ADHOC W_RTL_PLAN4_PROD4_LC4_T4_F_JO LOAD_PLANNING4 B _DATA_ADHOC W_RTL_PLAN5_PROD5_LC5_T5_F_JO LOAD_PLANNING5 B _DATA_ADHOC W_RTL_PLAN6_PROD6_LC6_T6_F_JO LOAD_PLANNING6 B _DATA_ADHOC

Table 3-3 (Cont.) Dataset Jobs

JobAd Hoc Processes
or Flows		Usage
W_RTL_PLANFC_PROD1_LC1_T1_FS_
COPY_JOB		N/A (Nightly Only)Load the forecast fat fles from the
server into the staging area in the
W_RTL_PLANFC_PROD2_LC2_T2_FS_
COPY_JOB		database. Only used as part of nightly
batch processing, fat fles are not
W_RTL_PLANFC_PROD1_LC1_T1_FS_
STG_JOB
W_RTL_PLANFC_PROD2_LC2_T2_FS_
STG_JOB		loaded in the ad hoc processes. Tables
are truncated at the beginning of
each execution so new data can be
inserted.
W_RTL_PLANFC_PROD1_LC1_T1_FS_
SDE_JOB		RI_FORECAST_AD
HOC		Imports forecast data directly from
the AIF exports to Retail Insights. The
W_RTL_PLANFC_PROD2_LC2_T2_FS_
SDE_JOB		data is inserted into the staging
tables, and assumes you are not
providing any data via fat fle. Only
the sku/store/week forecasts are
pulled by default.
W_RTL_PLANFC_PROD1_LC1_T1_F_J
OB
W_RTL_PLANFC_PROD2_LC2_T2_F_J
OB		RI_FORECAST_AD
HOC		Transforms and loads forecast data
from staging areas into the fnal fact
tables. Used both for the nightly
processing and for ad hoc loads.
CLEANUP_C_LOAD_DATES_PLANNIN
G_JOB		CLEANUP_C_LOAD
_DATES_PLANNIN
G_ADHOC		In order to run a planning load more
than once in a single business date,
you must clear the execution status
from the prior run, otherwise the
batch process will skip any
subsequent loads (until the business
date changes).

Loading Aggregate History

Special flex fact tables have also been provided to support loading of pre-aggregated history fact data for AI Foundation to use to generate forecasts for Planning. These tables should not be used unless there is no other way to provide history data except at a level above item/ location. These tables will completely bypass the normal data flow for history in RI and AIF, directly populating history fact tables just for limited AIF use-cases.

These tables work identically to PLAN and FLEX tables described in the previous sections. They use FACT as the table name prefix, for example W_RTL_FACT1_PROD1_LC1_T1_F. Four such FACT tables are provided for the different data intersections that may exist for aggregate actuals. Just like PLAN tables, you must configure these in C_ODI_PARAM_VW in the Control Center using parameters like RI_FACT1_PROD_LEVEL and RI_FACT1_ORG_LEVEL.

Refer to the Retail Analytics and Planning Solutions Implementation Guide section on Loading Aggregate History Data for additional information on how to use these tables.

In this guide

  • 12 Pre-Pack Optimization — AI Foundation Implementation Guide · shares CAL_DATE, C_ODI_PARAM, C_ODI_PARAM_VW, DATASOURCE_NUM_ID
  • 6 Metrics — Retail Insights Cloud Service User Guide · shares C_ODI_PARAM, RI_MFP_DATA, W_RTL_PLAN1_PROD1_LC1_T1_FS
  • 7 Support Utilities — AI Foundation Operations Guide · shares CONFIGURE_CAL_LEVEL_FOR_TABLE, C_MODULE_ARTIFACT, C_ODI_PARAM, C_ODI_PARAM_VW
  • 2 AI Foundation Data Standalone Processes — AI Foundation Operations Guide · shares AP_PLAN1_EXP, CLEANUP_C_LOAD_DATES_PLANNING_ADHOC, CREATE_PARTITION_ADHOC, C_MODULE_ARTIFACT
  • 2 Setup and Configuration — Retail Insights Cloud Service Implementation Guide · shares C_ODI_PARAM, C_ODI_PARAM_VW, DATASOURCE_NUM_ID, INTEGRATION_ID
  • 5 AI Foundation Data Standalone Process Flows — AI Foundation Operations Guide · shares COPY_JOB, ETL_BUSINESS_DATE_JOB, OPY_JOB, STG_JOB

Graph View

Backlinks