1. Implementation Guide
  2. Planning and Flex Fact Configuration

3 Planning and Flex Fact Configuration

Retail Insights supports Merchandise Financial Planning (MFP) data on four separate interfaces, referred to as PLAN1 through PLAN4 tables, Assortment Planning (AP) data on a fifth interface called PLAN5, 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 Facts Flexible Facts Planning Forecast Facts

    RI_PLAN1_CAL_LEVEL

    RI_FLEXFACT1_CAL_LEVEL

    RI_PLANFC1_CAL_LEVEL

    RI_PLAN1_ORG_LEVEL

    RI_FLEXFACT1_ORG_LEVEL

    RI_PLANFC1_ORG_LEVEL

    RI_PLAN1_PROD_LEVEL

    RI_FLEXFACT1_PROD_LEVEL

    RI_PLANFC1_PROD_LEVEL

    RI_PLAN1_SUPP_LEVEL

    RI_FLEXFACT1_SUPP_LEVEL

    RI_PLANFC1_SUPP_LEVEL

    RI_PLAN1_ATTR_LEVEL

    RI_FLEXFACT1_ATTR_LEVEL

    RI_PLANFC1_ATTR_LEVEL

    RI_PLAN2_CAL_LEVEL

    RI_FLEXFACT2_CAL_LEVEL

    RI_PLANFC2_CAL_LEVEL

    RI_PLAN2_ORG_LEVEL

    RI_FLEXFACT2_ORG_LEVEL

    RI_PLANFC2_ORG_LEVEL

    RI_PLAN2_PROD_LEVEL

    RI_FLEXFACT2_PROD_LEVEL

    RI_PLANFC2_PROD_LEVEL

    RI_PLAN2_SUPP_LEVEL

    RI_FLEXFACT2_SUPP_LEVEL

    RI_PLANFC2_SUPP_LEVEL

    RI_PLAN2_ATTR_LEVEL

    RI_FLEXFACT2_ATTR_LEVEL

    RI_PLANFC2_ATTR_LEVEL

    RI_PLAN3_CAL_LEVEL

    RI_FLEXFACT3_CAL_LEVEL

    RI_PLAN3_ORG_LEVEL

    RI_FLEXFACT3_ORG_LEVEL

    RI_PLAN3_PROD_LEVEL

    RI_FLEXFACT3_PROD_LEVEL

    RI_PLAN3_SUPP_LEVEL

    RI_FLEXFACT3_SUPP_LEVEL

    RI_PLAN3_ATTR_LEVEL

    RI_FLEXFACT3_ATTR_LEVEL

    RI_PLAN4_CAL_LEVEL

    RI_FLEXFACT4_CAL_LEVEL

    RI_PLAN4_ORG_LEVEL

    RI_FLEXFACT4_ORG_LEVEL

    RI_PLAN4_PROD_LEVEL

    RI_FLEXFACT4_PROD_LEVEL

    RI_PLAN4_SUPP_LEVEL

    RI_FLEXFACT4_SUPP_LEVEL

    RI_PLAN4_ATTR_LEVEL

    RI_FLEXFACT4_ATTR_LEVEL

    RI_PLAN5_CAL_LEVEL

    RI_PLAN5_ORG_LEVEL

    RI_PLAN5_PROD_LEVEL

    RI_PLAN5_SUPP_LEVEL

    RI_PLAN5_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)

    CMP

    COMPANY

    YEAR

    SUPPLIER

    C (for Color)

    DIV

    CHAIN

    HALFYEAR

    PARENT_SUPPLIER

    B (for Brand)

    GRP

    AREA

    QUARTER

    ALL

    FLAVOR

    DEPT

    REGION

    PERIOD

    		  

    FABRIC

    CLS

    DISTRICT

    WEEK

    		  

    SCENT

    SBC

    LOCATION

    DAY

    		  

    SIZE

    ITEM

    CHANNEL

    GREGORIANYEAR

    		  

    STYLE

    ALL

    PLANNING_CHANNEL

    GREGORIANQUARTER

    		  

    ALL
    FLEX1 -FLEX20

    PRICE_ZONE

    GREGORIANMONTH

    		    
     

    ALL

    GREGORIANDAY

    		    
      FLEX1 – FLEX20      

  4. 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.

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_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 plan table has flexible partitions which can be either Day or Week level, depending on the data level you plan to use. You must perform this partition setup process before using the PLAN1 table, or it will not function properly during data loads. The other plan tables do not have partitioning at this time as they are only used by AIF forecasting processes which don’t need partitioned data.

In order to configure the plan partitions, you must update the table C_MODULE_EXACT_TABLE where MODULE_CODE = PLAN1. 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 row for MODULE_CODE=PLAN1 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 (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 (this is run once as part of any standard implementation, but if you have already run it once before updating the plan configurations, then you must run it again). 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"
}

Loading Plan Data

Table 3-3 Dataset Jobs

Job Ad Hoc Processes Usage

W_RTL_PLAN1_PROD1_LC1_T1_FS_JOB

W_RTL_PLAN2_PROD2_LC2_T2_FS_JOB

W_RTL_PLAN3_PROD3_LC3_T3_FS_JOB

W_RTL_PLAN4_PROD4_LC4_T4_FS_JOB

W_RTL_PLAN5_PROD5_LC5_T5_FS_JOB

N/A (Nightly Only)

Load the planning flat files from the server into the staging area in the database. Only used as part of nightly batch processing, flat files cannot be loaded in ad hoc processes. Tables are truncated at the beginning of each execution so new data can be inserted.

W_RTL_PLAN1_PROD1_LC1_T1_FS_SDE_JOB

W_RTL_PLAN2_PROD2_LC2_T2_FS_SDE_JOB

W_RTL_PLAN3_PROD3_LC3_T3_FS_SDE_JOB

W_RTL_PLAN4_PROD4_LC4_T4_FS_SDE_JOB

W_RTL_PLAN5_PROD5_LC5_T5_FS_SDE_JOB

LOAD_PLANNING1_DATA_ADHOC

LOAD_PLANNING2_DATA_ADHOC

LOAD_PLANNING3_DATA_ADHOC

LOAD_PLANNING4_DATA_ADHOC

LOAD_PLANNING5_DATA_ADHOC

Imports planning data directly from the MFP and AP exports from Planning Data Store (PDS) to Retail Insights. The data is inserted into the staging tables, and assumes you are not providing any data via flat file (each interface should only come from either MFP/AP or flat file but not both).

W_RTL_PLAN1_PROD1_LC1_T1_F_JOB

W_RTL_PLAN2_PROD2_LC2_T2_F_JOB

W_RTL_PLAN3_PROD3_LC3_T3_F_JOB

W_RTL_PLAN4_PROD4_LC4_T4_F_JOB

W_RTL_PLAN5_PROD5_LC5_T5_F_JOB

LOAD_PLANNING1_DATA_ADHOC

LOAD_PLANNING2_DATA_ADHOC

LOAD_PLANNING3_DATA_ADHOC

LOAD_PLANNING4_DATA_ADHOC

LOAD_PLANNING5_DATA_ADHOC

Transforms and loads planning data from staging areas into the final fact tables. Used both for the nightly processing and for ad hoc loads.

CLEANUP_C_LOAD_DATES_PLANNING_JOB

CLEANUP_C_LOAD_DATES_PLANNING_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.

Applying Configurations

The following information is provided as a reference for the process that Oracle follows to setup the planning and flex fact configurations. This process is man-aged by Oracle in cloud environments and would not be run by implementers directly, but it can be useful to know what changes are being made to the system when this is performed.

Create a Wallet

First create a wallet for executing the planning configuration script for WebLogic Username/Password and RPD Username/Password. RPD Username can be set to "RI_RPD" since RPD does not have a username specifically. Passing WebLogic alias and RPD alias as an argument while running script connects to WebLogic user and executes the configuration script to deploy the RPD with the RPD alias provided.

Note:

Reuse the wallet already created by the Installer if exists else create wallet using the steps below.

Perform the following steps to create a wallet:

  1. Set the JAVA_HOME environment variable.

  2. Navigate to the following directory:

    cd <STAGING_
DIR>/ori/installer/ori/Build/orpatch/deploy/retail-public-security-api/bin

  3. Add execute permission to the save_credential.sh scripts in above location.

    chmod +x save_credential.sh.

  4. Create an alias for the WebLogic and RPD user by running save_credential.sh with the following arguments:

    ./save_credential.sh  -a <alias_name> -u <username> -l </location/wallet/dir>-p <Partiton_Key>

    Where:

    • <alias_name>: Alias Name for the User

    • <username>: Username

    • </location/wallet/dir>: Location where wallet needs to be created

    • <Partiton_Key>: RI Partition Key

    For example:

    ./save_credential.sh  -a WEBLOGIC-ALIAS -u wlsadmin -l/u01/retail/ri/wallet -p
RI_KEY
./save_credential.sh  -a RPD-ALIAS -u RI_RPD -l /u01/retail/ri/wallet -p RI_KEY

RunPlanningConfigurationScript.sh

Perform the following steps to run PlanningConfigurationScript.sh:

  1. Navigate to the following directory.

    cd <RETAIL_HOME>/ra_obiee_source_code/OBIEE_BAR_Deployment

  2. Unzip the "OBIEE_BAR_FILE_DEP.zip" file in the above path.

  3. Run the PlanningConfigurationScript.sh with the following arguments:

    ./PlanningConfigurationScript.sh RETAIL_HOME OBIEE_HOME PLANNING_CONFIG_HOME MODE WEB-LOGIC_USER_ALIASNAME RPD_ALIAS_NAME WALLET_PATH PARTITON_KEY EXECUTION_ MODE

    Where:

    • RETAIL_HOME: Retail Home path

    • OBIEE_HOME: OBIEE Home Path

    • PLANNING_CONFIG_HOME: Path where all input xml's, properties.txt file, keyTableNames.txt file, All java files, jar files, PlanningConfigurationScript.sh are placed

    • MODE: File mode (f) or Database Mode (d)

    • WEBLOGIC_USER_ALIASNAME: Alias name of WebLogic user created in wallet

    • RPD_ALIAS_NAME: Alias name of RPD user created in wallet

    • WALLET_PATH: Path where wallet is created

    • PARTITON_KEY: RI Partition Key

    • EXECUTION_MODE: Parameter for Configuration, which we are running

Database Mode (d)

In database mode user has to first set Planning levels in C_ODI_PARAM table then run PlanningConfigurationScript.sh script with MODE argument d. Configuration levels need to be set for below list of PARAM_NAME in the C_ODI_PARAM table.

File Mode (f)

In File Mode user has to first set Planning levels in the file proper-ties.txt in the path where OBIEE_BAR_FILE_DEP.zip is un-zipped, then run PlanningConfigurationScript.sh script with MODE argument f.

Note:

FLEX_FACT Configuration requires setting of only FLEX_ FACT levels in properties.txt and for planning only planning levels should be set. Similarly Planning IPO Cloud Service-Demand Forecasting Forecast Integration requires setting only Planning IPO Cloud Service-Demand Forecasting Forecast Integration levels in properties.txt.

Figure 3-1 Properties.txt File


Properties.txt File

Note:

While running in filemode, planning levels to be set in properties.txt should match the C_ODI_PARAM table planning level entries.

The following values are valid for the properties.txt file:

Organization:

  • COMPANY

  • CHAIN

  • AREA

  • REGION

  • DISTRICT

  • LOCATION

  • CHANNEL

  • PRICE ZONE

  • ALL

Product:

  • CMP

  • DIV

  • GRP

  • DEPT

  • CLS

  • SBC

  • ITEM

  • ITEM LEVEL1

  • ALL

Calendar:

  • YEAR

  • HALFYEAR

  • QUARTER

  • PERIOD

  • WEEK

  • DAY

Supplier:

  • SUPPLIER

  • PARENT_SUPPLIER

  • ALL

Product Attributes:

  • STYLE

  • FLAVOR

  • SCENT

  • FABRIC

  • SIZE

  • C (C denotes Color)

  • B (B denotes Brand)

PlanningConfigurationScript.shScript

The PlanningConfigurationScript.sh script executes the following three operations:

  • Downloads the deployed RPD with name default.rpd

  • Executes jar file to modify RPD reading planning levels from properties.txt (file mode) or C_ODI_PARAM table (Database Mode).

  • Uploads modified RPD (default.rpd).