| Oracle® Retail Demand Forecasting Implementation Guide Release 16.0 |
|
 Previous |
 Next |
This appendix addresses RMS integration between RPAS and RDF.
The strategy for the extraction of foundation data from Retail Merchandising System (RMS) is for the extract programs (RMSE) to provide flat files in a generic format. For each solution that uses this data, transformation scripts are used to reformat the data as needed to produce a file suitable for loading into the application. For the instances of data coming from RPAS to non-RPAS applications, extract programs are specific to the application in need of the data. Other scripting languages are then used (Perl or AWK) to perform additional data formatting.
This appendix summarizes the following:
Specifics on the usage of RMS extract programs (RMSE's) within the RDF transformation programs are beyond the scope of this document. See the Oracle Retail Merchandising System Operations Guide for more information on the RMS extract programs.
|
Note: For integration compatibility information, see the Oracle Retail Predictive Application Server Installation Guide. |
In addition to any variables identified in the RMS integration documentation, the transformation or extract programs or both require the environment variables listed in Table A-1.
Table A-1 Environment Variables
| Environment Variable | Description |
|---|---|
|
$FROM_RPAS |
The staging area for the data extract out of RPAS. This directory should be located at the same level as the root of the RPAS domain. For example, if the domain RDF is located in Domains directory. (example: |
|
$RDF_HOME |
Identifies the location of the root of the RFX directory. The RFX directory packaged with the ARPOPlatform should be added to the location RFX directory packaged with the RMS RETL programs. |
|
$RI_RMSVERSION |
Identifies the version of RMS. If this variable is not set, the integration scripts assume an RMS version of 13. Set the value of this environment variable to 13. |
|
$RPAS_INTEGRATION_HOME |
Identifies the location of the integration scripts when |
|
$TO_RPAS |
The staging area for the data to be loaded into RPAS. This directory should be located at the same level as the root of the RPAS domain. For example, if the domain RDF is located in Domains directory. (example: |
|
LIBPATH |
To run RETL, include these items in the LIBPATH variable: $RETL_JAVA_HOME/jre/bin:$RETL_JAVA_HOME/jre/bin/classic:$RFX_HOME/bin:$LIBPATH |
|
PATH |
To run RETL, include these items in the PATH variable: $RETL_JAVA_HOME/bin:$RETL_JAVA_HOME/jre/bin:$RFX_HOME/lib:$RFX_HOME/bin:$PATH |
|
PREFERRED_LANG |
This value indicated the language that the calendar labels are shown The default is english (_en), but any value from |
|
RDF_SCHEMA_DIR |
This directory contains the RFX or RETL schemas. This variable is used when converting the The default (and recommended) value is |
|
RESOURCE_DIR |
This directory contains resource information to support multi-language capability for calendar-related fields as well as the The default (and recommended) value is |
|
RETL_JAVA_HOME |
The directory that contains Java. See the latest version of the Oracle Retail Extract, Transform, and Load Programmer's Guide for the version of Java that corresponds to the version of RETL used. |
|
RFX_HOME |
The directory that holds the RETL executable files. |
|
RMS_RPAS_HOME |
This variable needs to be set in order to maintain compatibility with RMS. The required value is |
|
RPAS_ALERTNAVIGATIONTHRESHOLD |
This variable controls alerts and specifies the override for the default alert navigation threshold. The RPAS alertmgr api takes navigation threshold as an argument. If this navigation threshold is not passed into the api as an argument, then the value of the environment variable RPAS_ALERTNAVIGATIONTHRESHOLD is used. If RPAS_ALERTNAVIGATIONTHRESHOLD is not set then a default value of 5000 is used. |
|
UPGRADE_BACKUP_DIR |
This directory is used to backup existing schema information prior to converting the The default (and recommended) value is |
This section describes the RDF transformation scripts.
The rdft.ksh script runs all of the necessary data extraction and transformation scripts (rmse_*.ksh and rdft_*.ksh, respectively) that are needed to produce the files to be loaded into RPAS/RDF/Planning. Most of these scripts are run in parallel (as background jobs).
rdft.ksh [-x] [-c] [-sd startDate] [-ed endDate] [-d dir]
Arguments
The following table lists and describes the arguments for these scripts.
| Argument | Description |
|---|---|
| -x | This option causes the running of the RMS data extraction wrapper (rmse.ksh) to be skipped. |
-c |
This option causes FILE_DATE in rmse_config.env to be set to the current date instead of using VDATE. |
-sd |
This option sets the start date for optionally filtering out records based on date. Records with dates prior to this date are excluded from loading into RDF. The date needs to be in the format YYYYMMDD. |
| ed | This option sets the end date for optionally filtering out records based on date. Records with dates after this date are excluded from loading into RDF. The date needs to be in the format YYYYMMDD. |
-d |
This option causes all programs run by rdft.ksh to be obtained from the dir directory. |
The rdft_merchhier.ksh script is the primary script used to build the data for RPAS from the RMS Merchandise Hierarchy tables. The schema used to produce the output file depends on the attributes and differentiator settings in RMS:
| Case | Settings | Outcome |
|---|---|---|
| 1 | If PROD_ATTRIBUTES_ACTIVE = false and DIFFS_ACTIVE = false | Then rdft_merchhier.base.schema is used to produce the file. In this case, attributes and diff fields are not included in the merchandise hierarchy file. |
| 2 | If PROD_ATTRIBUTES_ACTIVE = true and DIFFS_ACTIVE = false | Then rdft_merchhier.attributes.schema is used to produce the file. This schema must be manually edited to support a specific attribute model and must be kept in sync with rmse_attributes.schema and rmse_attributes.ksh. |
| 3 | If PROD_ATTRIBUTES_ACTIVE = false and DIFFS_ACTIVE = true | Then rdft_merchhier.schema is used to produce the file. In this case, diff fields are included in the merchandise hierarchy file. |
| 4 | If PROD_ATTRIBUTES_ACTIVE = true and DIFFS_ACTIVE = true | Then an error results. In this release, the combination of diffs and attributes is not supported. |
Intermediate schema and scripts which may be used (depending on configuration options) to produce the merchandise hierarchy file:
rdft_diff.domain.schema
rdft_merchdiff.domain.schema
rdft_merchhier_diff_trans.ksh
rdft_merchhier_split_by_domain.ksh
rdft_clean_partition.ksh
Additional merchandise hierarchy support for issue domains is provided in rdft_item_loc.ksh. This script is designed to produce a full item list for issues domains, only containing items that exist in the warehouses.
|
Note: Issues-specific data transformation functionality is triggered based on the issues setting in RMS (ISSUES_ACTIVE must be set to True). |
The script rdft_clean_partition.ksh was designed to mimic some of the functionality of local domains prior to local domains being supported by RPAS. This script is not relevant to versions of RDF 12.0 or later. It is retained for use by customers on earlier versions of RDF.
RPAS position names must consist only of alphanumeric characters.
Invalid position names are created when using any other characters including:
Spaces
Punctuation
Non-alphanumeric characters
Invalid position names are not allowed in RPAS and are rejected by loadHier. RMS does allow non-alphanumeric characters, though, in the fields that are transformed into RPAS position names, including DIFF fields. These non-alphanumeric characters must be screened out or transformed by the customer before they can be loaded into RPAS. The GA scripts do not perform this function.
The rdft_orghier.ksh script is the primary script used to build the location data file needed for RPAS from the RMS Organizational Hierarchy Table.
The following constants may be modified in the script based on location hierarchy data requirements:
| Modifiable Constant | Description |
|---|---|
| COMPANY_NAME | The label for the company position to be populated in the file. |
| COMPANY_ID | The name for the company position to be populated in the file. |
| STORE_CLASS_CONCAT | When set to True, causes the STORE_CLASS to be concatenated on the left of the STORE_CLASS_DESCRIPTION field in the final Store data output file. |
| ADD_AT_SIGN_TO_WH_DESC | When set to True, causes the WHSE_NAME field in the Warehouse output file to have an at sign ”@” prefix. |
| LONG_WAREHOUSE_RECORDS | When set to True, the Warehouse output records consists of 16 fields. If it is False, the records contain only four fields, WH, WHSE_NAME, COMPANY, and CO_NAME. |
Intermediate schemas which may be used (depending on configuration options) to produce the location hierarchy file:
rdft_issues.schema
rdft_issues_long.schema
rdft_orghier_store.schema
|
Note: Issues-specific data transformation functionality is triggered based on the issues setting in RMS (ISSUES_ACTIVE must be set to True). |
The rdft_calhier.ksh script transforms the Calendar Hierarchy data extracted from RMS for loading into RPAS.
Configuration inputs to the rdft_calhier.ksh script include:
DATE_PREF - The path to the file that contains text indicating whether the format of the Date Description field are mm/dd/yyyy or dd/mm/yyyy. See the Oracle Retail Merchandising System Operations Guide for date format options.
LAST_DOW - The path to the file that contains a day of week name or abbreviation indicating which day of the week is considered to be the end of the week for the fiscal calendar being used at this installation.
The RMS to RDF calendar integration has been enhanced to allow calculation of several new quantities. These new quantities are present in rdft_calhier.dat, and include:
dos (Day of Season)
woy (Week of Year)
wos (Week of Season)
In order for these values to calculate properly, the rdft_clndhier.ksh script looks to two files, rfx/etc/first_day_of_season.txt and rfx/etc/first_week_of_season.txt. Each file contains a single number representing the day or the week number from where the season starts.
For example, suppose the data coming from RMS is starting from the 2nd day of the 3rd week of season, then the first_day_of_season.txt has two (2) and first_week_of_season.txt has three (3) as starting day and starting week of the season.
If the files are missing then the season is assumed to start with first day of the first week of the season.
The rdft_daily_sales.ksh script produces the daily sales and issues data files based on regular, promotion, clearance, and issues.
The following constant may be modified in the script based on data requirements:
DOM_START_COL
Defines the starting column position of the Domain ID in the RETL output schema. This is needed by rdft_merchhier_split_by_domain.ksh to split the files by domain ID. If the OUTPUT_SCHEMA file is modified, the value of DOM_START_COL may also require modification from the default value.
Intermediate schemas which may be used (depending on configuration options) to produce either or both of the sales or issues data file:
rdft_daily_sales.schema
|
Note: Issues-specific data transformation functionality is triggered based on the issues setting in RMS (ISSUES_ACTIVE must be set to True). |
The rdft_weekly_sales.ksh script produces the weekly sales and issues data files based on regular, promotion, clearance and issues.
The following constant may be modified in the script based on data requirements
DOM_START_COL
Defines the starting column position of the Domain ID in the RETL output schema. This is needed by rdft_merchhier_split_by_domain.ksh to split the files by domain ID. If the OUTPUT_SCHEMA file is modified, the value of DOM_START_COL may also require modification from the default value.
Intermediate schemas which may be used (depending on configuration options) to produce either or both of the sales or issues data files:
rdft_weekly_sales.schema
|
Note: Issues-specific data transformation functionality is triggered based on the issues setting in RMS (ISSUES_ACTIVE must be set to True). |
The rdft_open_date.ksh script produces the Store/Warehouse Opening Date data file.
Intermediate schema used to produce the store open date data files:
rdft_open_date.schema
The rdft_close_date.ksh script produces the Store/Warehouse Closing Date data file.
Intermediate schema used to produce the store closing date data files:
rdft_close_date.schema
The rdft_outofstock.ksh script produces the Store and Warehouse (issues) out-of-stock indicator data extracted from RMS.
Intermediate schema and scripts which may be used (depending on configuration options) to produce the out-of-stock indicator data file:
rdft_outstock_split_by_domain.awk
rdft_outofstock.schema
rdft_outofstock_issues.schema
rdft_outofstock_sales.schema
The following matrix identifies the transformation scripts and schemas used for each the hierarchy and data files produced for RDF:
Table A-2 RDF Transformation Scripts and Schema
| Directory | Script or Schema Name | Merchandise Hierarchy | Location Hierarchy | Calendar | Daily Sales & Issues | Weekly Sales & Issues | Out-of-stock Indicator | Store Open Dates | Store Close Dates |
|---|---|---|---|---|---|---|---|---|---|
|
rfx/lib |
rdft_merchhier_diff_trans.ksh |
X |
|||||||
|
rdft_merchhier_split_by_domain.ksh |
X |
||||||||
|
rdft_outofstock_split_by_domain.ksh |
X |
||||||||
|
rfx/ schema |
rdft_close_date.schema |
X |
|||||||
|
rdft_daily_sales.schema |
X |
||||||||
|
rdft_diff.domain.schema |
X |
||||||||
|
rdft_merchierdiff.domain.schema |
X |
||||||||
|
rdft_merchier.attributes.schema |
X |
||||||||
|
rdft_merchhier.base.schema |
X |
||||||||
|
rdft_merchhier.domain.schema |
X |
||||||||
|
rdft_merchhier.schema |
X |
||||||||
|
rdft_open_date.schema |
X |
||||||||
|
rdft_orghier_issues.schema |
X |
||||||||
|
rdft_orghier_issues_long.schema |
X |
||||||||
|
rdft_orghier_strore.schema |
X |
||||||||
|
rdft_outofstock.schema |
X |
||||||||
|
rdft_outofstock_issues.schema |
X |
||||||||
|
rdft_outofstock_sales.schema |
X |
||||||||
|
rdft_weekly_sales.schema |
X |
||||||||
|
rfx/src |
rdft_ksh |
X |
X |
X |
X |
X |
X |
X |
X |
|
rdft_calhier.ksh |
X |
||||||||
|
rdft_clean_partition.ksh |
X |
||||||||
|
rdft_close_date.ksh |
X |
||||||||
|
rdft_daily_sales.ksh |
|||||||||
|
rdft_item_loc.ksh |
X |
||||||||
|
rdft_merchhier.ksh |
X |
||||||||
|
rdft_open_date.ksh |
X |
||||||||
|
rdft_orghier.ksh |
X |
||||||||
|
rdft_outofstock.ksh |
X |
||||||||
|
rdft_weekly_sales.ksh |
X |
This section describes the loading of transformed RMS data into RDF.
After the transformation of the RMS hierarchy and history data, the data files must be combined and renamed to match the appropriate measure names expected by RDF. This is accomplished by a script, renameRETLFiles.ksh. Usage of this script is optional, but recommended. The script reads the data files produced by the RETL transformation, combines and renames the data files, and writes the output to the domain's input folder. Existing data in the domain's input folder is backed up prior to the writing the new files.
Usage
renameRETLFiles.ksh -dataDir <data dir> -domInput <domain's input dir>
Arguments
The script has two required arguments:
| Argument | Description |
|---|---|
| -dataDir | Required. The path to the data directory. This directory contains the RMS data transformed by the RDF RETL scripts. This is typically $RDF_HOME/data. |
| domInput | Required. The input directory of the domain in which the transformed data is to be loaded. The directory is typically the input directory under the domain root. |
This section describes the common programs for extracts.
The config.ksh script is a configuration directory that requires both the RMS version being integrated and the backup action to be defined.
Arguments
The following optional arguments are available:
| Argument | Description |
|---|---|
| Name of the domain | Defaults to directory name |
| Number of the domain | Defaults to the 2 last digits of the directory name |
| Format of timestamp attached to logs and processed input files | Defaults to: (date +"%b%d%a%I%M%p") (example: Aug02Thu0111PM) |
| Data Drop | Defaults to ../../to_rpas |
| Data Export | Defaults to ../../from_rpas |
| Log Drop | Defaults to ./logs |
| Error Drop | Defaults to ./err |
| Reclass Data | Defaults to ../reclass_data |
This section describes the scripts that extract forecast data for RMS.
The rdf_e_rms.ksh script extracts forecast demand value and standard deviation (cumulative interval) from an RDF domain.For more information, refer to Chapter 7, "Batch Processing."
This section describes the load of extracted forecast data and standard deviations to RMS.
The rmsl_forecast.ksh script pulls the daily/weekly forecast items into RMS.
During the loading of each domain file the following steps are performed:
Truncate the partition in the RMS forecast table which corresponds to the domain ID.
|
Note: Partition names should always be in the format:[tablename]_[domainID] |
Append a domain field and insert the domain_id into each record.
Load the forecast data into the RMS forecast table.
Intermediate schemas which may be used (depending on configuration options) to produce the forecast data files:
rmsl_forecast_daily.schema
rmsl_forecast_weekly.schema
This section describes the extract of diff profile data for Allocation.
The profile_e_alloc.ksh script extracts Curve diff profiles for use by Allocation.
Arguments
The script accepts the following arguments:
| Argument | Description |
|---|---|
| -p | <Profile Number> |
| -m | <Mask Measure> (Optional mask; only positions for which the mask value is non-NA are exported.) |
| -w | <Data Width> ([7...18], defaults to 12) |
| -d | <Domain> (defaults to current directory) |
| -n | <Domain Number> (defaults to last two digits of domain) |
Output file
${RPAS_EXPORT}/d1<Product Level>.<Domain Number>
|
Note: Where Product Level is the Aggregation intersection's Prod dimension. |
The following table provides information about the output file data format.
| Field | Start | Width | Format |
|---|---|---|---|
| Product ID | 1 | 25 | Alpha |
| Location ID | 26 | 20 | Alpha |
| Diff ID (optional) | 46 | 36 | Alpha |
| Quantity | 82 | 12* | Numeric (floating point, 4 decimal digits, no decimal)* |
| Std. Dev. Demand | 68* | 12* | Numeric (floating point, 4 decimal digits with decimal) |
| * Quantity width may be overridden with the -w parameter. | |||
|
Note: The following must be defined in the shell environment prior to calling this script:
|
This section describes the extract of Store Grade data for RMS.
The grade_e_rms.ksh script extracts store grades for use by RMS.
Arguments
The script accepts the following arguments:
| Argument | Description |
|---|---|
| -t | <Timestamp> (YYMMDDTTTT). This value corresponds to the timestamp of the Cluster Membership measure (clpm+<Timestamp>) to be extracted |
| -d | <Domain> (defaults to current directory) |
| -n | <Domain Number> (defaults to last two digits of domain) |
Output File
${RPAS_EXPORT}/gr<Timestamp>.<Domain Number>
|
Note: The following must be defined in the shell environment prior to calling this script:
|
The following tables list the output file data formats:
| Header Records |
|---|
| FHEAD |
| Line ID Number |
| GRADU |
| Detail Records | Data Type | Width | Description |
|---|---|---|---|
| FDETL | String | 5 | Always 'FDETL' |
| Line ID | Number | 10 | Line Sequence Identifier (generated by the script) |
| Grade Group ID | Number | 8 | This value corresponds to the first 8 characters of the Cluster Run Name measure (clnam+<user-defined name>) set by the user in the Generate Cluster wizard in Grade. For integration with RMS, the Cluster Run Name must be populated with only numeric characters. |
| Grade Group | String | 120 | This value corresponds to the first 120 characters of the Cluster Run Name measure (clnam+<user-defined name>) set by the user in the Generate Cluster wizard in Grade. |
| Grade Store | Number | 10 | Valid Grade Store derived from the point mapping measure (clpm+<user-defined name>). For integration with RMS, the Cluster Run Name must be populated with only numeric characters. |
| Grade ID | Number | 10 | Valid Grade ID derived from the point mapping measure (clpm+<user-defined name>). For integration with RMS, the Cluster Run Name must be populated with only numeric characters. |
| Grade Name | String | 120 | Valid Grade Name, also derived from the point mapping measure (clpm+<user-defined name>). |
| Footer Records |
|---|
| Line ID Number |
| FDETL Line Total Number |
The following matrix identifies the extract scripts and schemas used for each the data files produced for RMS.
| Directory | Script or Schema Name | Forecasts and Standard Deviations | Diff Profiles |
|---|---|---|---|
| common | config.ksh | ||
| functions.ksh | X | ||
| header.ksh | X | X | |
| curve | profile_e_alloc.ksh | X | |
| grade | grade_e_rms.ksh | ||
| plan | Plan_e_alloc.ksh | ||
| Plan_e_price.ksh | |||
| Plan_e_plcblwdm.ksh | |||
| Plan_e_ploblwdm.ksh | |||
| rdf | rdf_e_rms.ksh | X | |
| rmsl_forecast.ksh | X | ||
| rmsl_forecast_daily.schema | X | ||
| rmsl_forecast_weekly.schema | X |
These sections describe internationalization considerations for RETL:
Calendar data (that is names of weeks and months) is translatable into Oracle supported languages. Enabling this feature requires setup of these two environment variables:
export RESOURCE_DIR=$RDF_HOME/resources
export PREFERRED_LANG=_en
Note that for PREFERRED_LANG, the default is english (_en), but any value from $RDF_HOME/resources/SupportedLanguages.txt where column 4 = Yes is supported, provided the corresponding retl_msgs.* file is present.
Once these variables are setup, then the translation takes place for RETL. No separate script is required.
The Unassigned value in schemas can be converted to a locale specific equivalent. To enable this feature, set these environment variables:
export RESOURCE_DIR=$RDF_HOME/resources
export PREFERRED_LANG=_en
export RDF_SCHEMA_DIR=$RDF_HOME/rfx/schema
export UPGRADE_BACKUP_DIR=$RDF_HOME/schema_backup
Note that for PREFERRED_LANG, the default is english (_en), but any value from $RDF_HOME/resources/SupportedLanguages.txt where column 4 = Yes is supported, provided the corresponding retl_msgs.* file is present.
After the environment variables are set, then run this script: $RDF_HOME/rfx/etc/update_schema_nullvalues.ksh.
This script backs up existing schemas into UPGRADE_BACKUP_DIR and then replaces the Unassigned value with a locale specific equivalent. This update preserves field widths.
