2 Data Flow from Merchandising to Xstore
This chapter covers the data flow from Merchandising and Pricing to Xstore, where data can be consumed by Xstore Office for loading into the Xcenter database, and files distributed to Xstore for loading into the Xstore database.
The Xstore Suite can consume Merchandising and Pricing data in the following categories:
-
Merchandise hierarchy
-
Organizational hierarchy
-
Store (including addresses)
-
Dimension type (derived from item differentiator, or diff usage)
-
Dimension value (derived from item diff usage)
-
Items and Item Location
-
Item UPC
-
Value Added Tax (VAT) rules and item associations
-
Related items
-
Initial prices
-
Price changes
-
Promotions
-
Clearance prices
Conceptual Data Flow
This section describes the conceptual data flow for each of the inbound integration interfaces supported by the Xstore Suite.
-
RMFCS/RPCS Direct Services (MFCS version 21.x and above)
-
OCDS (MFCS v19)
-
Flat Files (deprecated)
RMFCS/RPCS Direct Services and OCDS integration styles utilize webservices to obtain merchandising and pricing data that is written into files using the dataloader's native .mnt file format.
Integrations with either provider can be established by using Xadmin’s Integration Manager UI for an Organization. See the Oracle Retail Xstore Office User Guide or the Oracle Retail Xstore Office Cloud Service User Guide for details about configuring these integrations using Xadmin’s Integration Manager.
Flat File integration style utilized files exported from Merchandising Foundation Cloud Service and Pricing Cloud Service.
Using RMFCS/RPCS Direct Services (MFCS version 21.x and above)
The Merchandising Foundation Cloud Service and Pricing Cloud Service expose a set of REST web services that allow Xstore Office to communicate directly with MFCS and RPCS.
Xstore Office leverages these interfaces to poll, at a regularly scheduled intervals, for updates (for example, additions, deletions, and modifications) of Merchandising and Pricing data used by the Xstore Suite. Xstore Office communicates requests for changes in data by directly calling the Merchandising and Pricing REST web services.
When changes to Merchandising and Pricing data are detected, Xstore Office generates .mnt files containing the commands to update Xstore Suite databases. When .mnt files are generated, they are automatically deposited into the Xstore Office auto-drop folder for data-loading and distribution.
-
If any detected updates necessitate updating the Xcenter database then the appropriate .mnt file will be automatically dataloaded.
-
If any detected updates necessitate updating the Xstore database then the appropriate .mnt files will be deployed to the store where they can be dataloaded either immediately or at store close.
Note:
The Direct Web Services were introduced in RMFCS and RPCS in version 21 to avoid the overhead of replicating the data in another data hub (OCDS) and then having web services build on top of OCDS. OCDS was decommissioned in version 21 and RMFCS and RPCS started exposing the same webservices. Refer to the following documents for additional details.-
Oracle® Retail Merchandising Cloud Services Inbound and Outbound Integration Guide on the Oracle Help Center
-
Reference paper Publish APIs - Customer Enablement Overview in the Oracle Retail Merchandising Documentation Library (Doc ID 1585843.1) on My Oracle Support
Using OCDS (MFCS v19)
Oracle Omnichannel Cloud Data Service (OCDS) is a data hub, enabling the merchandising applications to share information with the Oracle Retail Omnichannel applications.
OCDS is composed of three major components:
-
BDI Batch Job Admin - Enables the flow of data into OCDS using the Oracle Bulk Data Integration (BDI) technology. Job Admin has a User Interface (UI) to support the management of BDI batch jobs.
-
RIB Injector - Enables the flow of data into OCDS from the Oracle Retail Integration Bus (RIB).
-
ORDS Web Services - Enables the data contained in OCDS to be accessed by Omnichannel applications, such as the Xstore Suite, through the use of RESTful web services.
Figure 2-1 illustrates the major system components that make up OCDS, and the interactions of the applications major actors.
Figure 2-1 OCDS Component Diagram
![This image shows the OCDS component diagram. This image shows the OCDS component diagram.](img/ocds_comp_diagram.png)
Figure 2-2 Conceptual Data Flow from Merchandising and Pricing to Xstore POS
![This image shows the Data Flow from Merchandising and Pricing to Xstore POS This image shows the Data Flow from Merchandising and Pricing to Xstore POS](img/concept_data_flow.png)
The following steps describe the flow in Figure 2-2.
-
OCDS receives an initial load of Merchandising foundation data using BDI as the data transport. For foundation data, this is generally a one-time push of data over BDI into OCDS. Incremental foundation data flows into OCDS from the RIB.
Note:
It is important to understand that the receipt of Merchandising Foundation data using BDI will trigger Xstore Office to request a full refresh of all data from OCDS because Xstore Suite will detect that the system of record, OCDS, has been refreshed. Ideally, OCDS receives its initial load before the integration is established in Xadmin. If Xadmin has an active OCDS integration and OCDS needs to be refreshed, then the OCDS integration should be either paused or deleted until all BDI jobs have been completed successfully to refresh OCDS. Once all BDI jobs have completed to refresh OCDS, then the integration can be recreated or unpaused, allowing Xstore Office to request initial load to replace foundation data in Xcenter and Xstore databases.
-
OCDS starts to receive, on-going, regularly scheduled pricing and promotion data using BDI as the data transport.
-
OCDS starts to receive, on-going, near-real-time updates of Merchandising data using the RIB as the data transport.
-
Xstore Office starts polling OCDS, at a regularly scheduled interval, to check for updates (for example, additions, deletions, and modifications) of Merchandising and Pricing data used by the Xstore Suite. Xstore Office communicates requests for changes to OCDS data by calling the OCDS REST web services.
-
When changes to OCDS data are detected, Xstore Office generates .mnt files containing the commands to update Xstore Suite databases. When .mnt files are generated, they are automatically deposited into the Xstore Office auto-drop folder for data-loading and distribution.
-
If any detected OCDS updates necessitate updating the Xcenter database then the appropriate .mnt file will be automatically dataloaded.
-
If any detected OCDS updates necessitate updating the Xstore database then the appropriate .mnt files will be deployed to the store where they can be dataloaded either immediately or at store close.
Note:
OCDS leverages Basic Auth to authenticate web service requests made by Xstore Office. Retailers should develop procedures to proactively change the OCDS password on both sides of the integration before the OCDS Basic Auth password expires.
Using Flat Files
Note:
The flat file integration will be deprecated. For more information about this feature, see the Oracle Retail Merchandising Cloud Services Deprecation Advisory (Doc ID 2760721.1) on My Oracle Support. For new integrations, use the direct services.
The Merchandising Foundation Cloud Service and Pricing Cloud Service support the export of data into files that can be packaged for consumption by the Xstore Suite. Figure 2-3 illustrates the data flow from the Merchandising applications to Xstore Office and Xstore.
Figure 2-3 Conceptual Data Flow from Merchandising to Xstore Suite
![Data Flow Merchandising to Xstore Data Flow Merchandising to Xstore](img/dataflow_merch_xstore.png)
The following steps describe the flow in Figure 2-3:
-
RMFCS/RPCS writes data to .dat files using a series of .ksh extract scripts. These scripts support both kill/fill (full) and delta processing. Many of these scripts also support creating files that apply either to all stores or store-specific files.
Use the kill/fill export option in the merchandising and pricing system's extract scripts to generate full-load files along with running the staging batch for every store and extracting the data using the standard batch processes to convert items, price, and other key data elements from the Merchandising solutions into Xstore.
The following merchandising batch jobs are used for the integration:
-
nexport_merchhier.ksh
-
nexport_orghier.ksh
-
nexport_stores.ksh
-
nexport_diffs.ksh
-
nexport_diffgrp.ksh
-
nexport_itemmaster.ksh
-
nexport_itemloc.ksh
-
nexport_itemvat.ksh
-
nexport_relitem.ksh
-
nexport_vat.ksh
-
nexport_stg_purge.ksh
For more information, see the Oracle Retail Merchandising System Operations Guide, Volume 1 - Batch Overviews and Designs.
The following pricing batch jobs are used for the integration:
-
RegularPriceChangePublishBatch
-
ClearancePricePublishBatch
For more information, see the Oracle Retail Price Management Operations Guide.
-
-
The retailer's system integrator is responsible for packaging the extract .dat files in a zip-format archive file named using the file extension '.momzip'. The integration uses logic based on filenames so it is important to maintain the use of the file's name as generated.
Note:
The momzip file must be scanned for viruses before delivery to Xstore Office for processing
-
Momzip files are delivered to Xadmin's auto-deploy file folder either by use of webservice API or direct file system access, as available per hosting type.
-
Xadmin periodically checks the auto-deploy directory for new Momzip files. The system automatically processes the dat-file contents of the momzip. For details on the set of Merchandising files targeted to corporate or stores, see "Merchandising File Consumption by Location".
-
If any RMFCS/RPCS extract files necessitate updating the Xcenter database then the appropriate .dat files will be automatically dataloaded.
If any RMFCS/RPCS extract files necessitate updating the Xstore database then the appropriate .dat files will be deployed to the store where they can be dataloaded either immediately or at store close.
Xstore Office Deployments
Each of the types of integration discussed in this guide involve the delivery of merchandising and pricing data, in file format, to Xstore Office's auto-deploy folder. Xadmin polls the auto-deploy folder at a configured interval and automatically processes new files. The polling-interval is configurable, and is 15 minutes by default. For information on how to configure these settings, see the Oracle Retail Xstore Office User Guide.
Deployments of .mnt or .dat files, to be loaded at the store, are automatically created for either immediate or scheduled distribution. Each deployment status of the files is displayed in the Xstore Office Deployments screen (Figure 2-1).
Once a store is closed or when the retail period changes in a 24x7 configuration, Xenvironment of the lead register pulls down files scheduled for end-of-day and runs the Xstore DataLoader to import all the files deployed into the store primary database. Files scheduled for immediate dataloading are pulled down and dataloaded automatically.
Figure 2-4 Xstore Office Deployments
![Xstore Office Deployments Xstore Office Deployments](img/xstore_office_deploym.png)
Using RMFCS/RPCS Direct Services or OCDS
The .mnt files created by Xstore Office leverage an xml header to target when and where files are dataloaded.
For example, this header would appear in a store-specific file that is immediately downloaded and dataloaded.
<Header line_count="104" target_org_node="STORE:121"
destination="XSTORE_ONLY" apply_immediately="true" download_time="IMMEDIATE"
/>
Table 2-1 MNT Files
Filename Prefix | Store Specific | Destination | Downlaod Time |
---|---|---|---|
1_orgHier |
No |
ALL |
STORE_CLOSE |
2_dimensionType |
No |
ALL |
STORE_CLOSE |
2_dimensionValue |
No |
ALL |
STORE_CLOSE |
2_merchHier |
No |
ALL |
STORE_CLOSE |
2_retailLoc |
No |
ALL |
STORE_CLOSE |
2_vat |
No |
ALL |
STORE_CLOSE |
3_item |
Yes |
XSTORE_ONLY |
STORE_CLOSE |
3_itemCorp |
No |
XCENTER_ONLY |
|
3_itemUPC |
Yes |
XSTORE_ONLY |
STORE_CLOSE |
4_itemLoc |
Yes |
ALL |
STORE_CLOSE |
4_itemLocCorp |
Yes |
XCENTER_ONLY |
|
4_relatedItem |
Yes |
ALL |
STORE_CLOSE |
5_clrpc |
Yes |
ALL |
IMMEDIATE |
5_price |
Yes |
ALL |
IMMEDIATE |
5_promo |
Yes |
XSTORE_ONLY |
STORE_CLOSE |
5_regp |
Yes |
ALL |
IMMEDIATE |
Using Flat Files
The files produced by the extract programs containing data loaded into the Xcenter and Xstore databases comprise four data sets. A data file's targeted location is specified in its file name:
-
Data loaded into Xcenter
-
Data loaded into Xcenter and all stores
-
Data loaded into one store
-
Data Loaded into all stores
The figure below illustrates where files are dataloaded by type, using a two store chain example.
Figure 2-5 File Distribution/Dataloading by Type
![File Distribution Dataloading by Type File Distribution Dataloading by Type](img/file_distribution_example.jpg)
Xstore Dataloader
DataLoader is the Xstore component responsible for translating delimited files into instructions to modify the contents of an Xstore Suite database. It can consume .mnt files generated by an Xstore Office integrated with OCDS and MFCS, or the .dat files extracted from Oracle Retails Merchandising and Pricing Cloud Systems.
The DataLoader interacts with Xstore Office, Xcenter, Xenvironment, and Xstore Point of Service to provide a complete automated solution for the propagation of foundation data changes to the centralized and store-level databases used in an enterprise Xstore deployment. Xstore data not supplied by an Oracle Merchandising/Pricing system can also be loaded by the DataLoader using its native .mnt format.
The DataLoader is designed to adapt flat files of data into relational data that Xstore can use. These flat files are referred to generically as data files within the DataLoader. Each field in a data file is delimited by a vertical bar (|) character. The DataLoader is configured to detect file types so it can process a data file's lines in distinct units of work appropriate for the type of file.
If a failure occurs during DataLoader processing of a data file, all SQL statements associated with the unit of work are rolled back and the error is logged. Processing continues with the next unit of work in the data file.
For more detailed information, see the following documents:
-
Retail Reference Architecture
-
Oracle Retail Xstore Point of Service Host Interface Guide
Both documents are available on My Oracle Support.
File Type Detection
DataLoader is configured with a list of detectors to identify known file types that can be processed. Unknown file types are skipped and not processed. A Merchandising file detector is configured to identify all types of Merchandising data files and their meta data.
The detection is based solely on file names. Regular expressions are configured to perform pattern matching in file name to identify Merchandising file types and its meta-data including target store ID, fill type, timestamp, and line count:
-
Merchandising file type detection
A file name is matched against regular expressions configured to detect its Merchandising type. If no match is found, the file is not a Merchandising file type. The keys in the bean configuration are the Merchandising file types that Xstore/Xcenter care about.
-
Target store detection
Target store ID is used by DataLoader as well as Xstore Office to determine the deployment target of a Merchandising file.
A file name is matched against regular expressions configured to detect its target store ID. Not all Merchandising file types have a target store ID configured:
-
If a store ID is not detected, the file is deployed to all stores and imported into Xcenter.
-
If a store ID is detected and is corp, the file is imported into Xcenter only.
-
If a store ID is detected and is not corp, it is deployed to the store. With the exception of Item Header batch, it is also imported into Xcenter.
-
-
Timestamp detection
Timestamp is used by DataLoader to sort the files. For more details, see "File Sorting". A file name is matched against regular expressions configured to detect its timestamp.
-
Line count detection
Line count is used by DataLoader to validate a file. If the number of lines in the file does not match the line count, a warning is logged.
File Sorting
There are some data dependencies when importing .dat files into Xstore, such as related item detail that needs to be imported after the related item header. When DataLoader is called to import multiple Merchandising files in the same deployment, it applies sorting to the files before importing them.
A detector is configured to have a sorting strategy, which is used to sort all the files the detector detects. A Merchandising file sorting strategy bean is configured for the Merchandising file detector to perform sorting for all Merchandising files based on their file types. Files of the same Merchandising file type are sorted based on their timestamps. Out of the box the following sorting order is specified:
-
Org Hierarchy
-
Store
-
Store Address
-
Merchandise Hierarchy
-
VAT
-
Diff Group Head
-
Diff Group Detail
-
Diff
-
Item Head
-
Item Loc
-
VAT Item
-
Related Item Head
-
Related Item Detail
-
Regular Price Change
-
Clearance Price Change
-
Promotion Price Change
File Loading Dependency
Although the sorting strategy configuration lists all Merchandising file types, not all file types have file loading dependencies. The actual dependencies are shown in Table 2-2.
Table 2-2 File Loading Dependency
File Type | Depends on |
---|---|
VAT Item |
Item Loc |
Store Address |
Store |
Related Item Detail |
Related Item Head, Item Loc |
Item Loc |
Item Head |
Item Head |
Diff, Diff Group Detail, Diff Group Head, Merchandise Hierarchy |
Diff Group Detail |
Diff Group Head |
Diff |
Diff Group Detail, Diff Group Head |
File Iteration and Transformation
DataLoader processes each file in the sorted order. It invokes a file iterator to process each file. A file iterator implements Java Iterator interface. During each iteration, it transforms flat file records into a list of IPersistable (DAO or SQLQuery) objects, and returns them.
A Merchandising file iterator is configured for each Merchandising file type. It processes lines between unit dividers as a data unit that should be transformed together:
-
A single line iterator that expects each line in the file, other than FHEAD or FTAIL, is a data unit that gets transformed during each iteration. One and only one line in a unit is expected. An exception is raised if that is not the case.
-
A multi-line iterator expects multiple lines to form a data unit that gets transformed together during each iteration. A unit may contain one or more lines. Out of the box, only promotion price change is configured to have a multi-line iterator.
-
Unit dividers are lines that end a unit. They are configured as unit definitions for each Merchandising file type.
A Merchandising transformer is called to convert a unit of data from a flat file to a list of IPersistable (DAO or SQLQuery) objects. A transformer is configured for each Merchandising file type.
All Merchandising transformers implement the IMOMDataTransformer interface, which defines two APIs:
-
The transform API is invoked by the iterator in each iteration. It does all the transformation to turn a unit of flat file data to a list of IPersistable objects to create, update, or delete foundation data records in database.
-
The purgeData API is invoked once for a file by the iterator. It is only called if the file is for a full reload. It returns a list of IPersistable objects to remove all existing records sourced from Merchandising.
Persisting into the Database
DataLoader saves IPersistable objects to database in batches. A batch contains a list of AtomicPersistables objects. The maximum number of AtomicPersistables objects in a batch is configurable. An AtomicPersistables is a container of a group of IPersistable objects that must all be persisted or rolled back together as a unit. All IPersistable objects returned in one iteration are grouped into one AtomicPersistable object.
DataLoader first attempts to persist and commit all IPersistable objects from all AtomicPersistables objects in a batch together. If it fails, it tries to persist and commit IPersistable objects from one AutomiPersistables at a time. The number of succeeded and failed records are written to summary.ini files.
Full Refresh
The integration styles discussed in this document support use-cases for full refresh and incremental updates.
Using RMFCS/RPCS direct services or OCDS
Xstore Office uses the date January 1, 1970 to request a full refresh from the source system. All “on-demand” integration jobs initiated through the use of Xadmin’s Data Manager > Data Publisher feature will request all data changes since January 1, 1970. The initial ”scheduled job” for an integration can (optionally) be configured to request all data changes since January 1, 1970 – as an initial load. After the initial job completes, subsequent scheduled jobs will request only incremental changes.
New Stores
Using RMFCS/RPCS direct services or OCDS
When a new store is added in the source system to a chain that is included in an active integration, the new store will automatically flow, as an incremental change, to the Organization Hierarchy and Store tables in Xstore Suite databases. However, mnt files are not automatically generated and deployed to the new store. The decision of when seed the new store with foundation data is left up to the retailer, and is accomplished using Xadmin’s Data Manager>Data Publisher feature to request the full set of data for the new store from the source system.
Phased Rollout of Stores
Retailers who are migrating an existing Xstore Suite implementation from a different source for merchandising foundation and pricing data may desire to exclude stores defined in their organization hierarchy during a phased rollout of the integration.
Using RMFCS/RPCS direct services or OCDS
Xstore Office Integration Manager supports the use of Store Collections when configuring either integration method. By associating a Store Collection with an integration method a retailer can identify which stores require data from the integration system. This feature can facilitate a phased-store rollout of data to a subset of existing stores in Merchandising. An integration using a Store Collection will limit the creation and deployment of mnt files to only those stores defined by the Store Collection.
Adding another store to an existing integration will enable incremental changes to start flowing to the store. A manual refresh from Merchandising and Pricing through either integration method should be requested for the added store to seed it with a full set of foundation and pricing data. Use Xadmin’s Data Manager > Data Publisher feature to request the full set of data for the added store from the source system.
Using Flat Files
A momzip.properties file can be included in the root folder of the momzip file to prevent a .dat files from being dataloaded in Xcenter and to limit the creation of store deployments to only a subset of stores. By associating a Store Collection with the momzip's contents a retailer can identify which stores are eligible for targeting when creating deployments. This feature can facilitate a phased-store rollout of data to a subset of existing stores.
For example, the properties below are used to dataload Xcenter, but only result in deployment to stores that are members of the store collection XOstores.
dataload.xcenterEligibleFiles=true
deploy.storeCollection=XOstores
Table 2-3 Flat File Properties
Property | Description | Values | |
---|---|---|---|
Possible | Default | ||
dataload.xcenterEligibleFiles |
Indicates if Xcenter is to dataloaded |
true/false |
true |
deploy.storeCollection |
If a value is present for this property, then deployments will only be created for stores specified by the store collection. If no value is present then no deployments will be created |
The ID of an existing Xadmin store collection, or no value |
null |
Note:
When the momzip.properties file does not contain a supported property key, or the key exists but has no value, then the key's default value is used.
Manual Refresh of an Xstore Database
The typical integration's operational flow consists of a one-time initial load to seed Xstore Suite databases with data from the Oracle merchandising and pricing systems, followed by incremental updates to update Xstore Suite databases with changes made in the upstream systems. However, exceptions to this flow are expected and the integrations provide mechanisms to refresh databases on an as-need bases.
Using RMFCS/RPCS direct services or OCDS
Both integration methods are designed to be fully automated; under normal conditions no manual steps are required to have Merchandising and Pricing data flow into a store database. However, Xadmin's Data Publisher can be used to regenerate and redeploy .mnt files with Merchandising and Pricing data to a store if exceptional circumstances necessitate the refreshing of an Xstore database.
Use of the Data Publisher to replenish one or more types of Merchandising and Pricing data at a store will result in the purging of all existing sourced data, followed by the loading of a full set of the most recent data.
When the integration is enabled in Xstore Office, the DataManager screen's "Data Publisher" option will include a "Data Source" drop down list, which includes the list options: "Omni Channel Data Service" and "Merchandise Foundation Cloud Service".
Note:
When you select the "Merchandise Foundation Cloud Service" option, Pricing is included.
To publish data to one or more stores:
-
Choose the desired Organization Node for the target stores, select the integration, and click Next.
Figure 2-6 Xstore Office - Data Publisher Option
-
Choose the type of data you wish to publish, then click Next.
Figure 2-7 Xstore Office - Data to Publish
-
Select a Download Priority, Immediate or Store Close, and click Deploy.
Figure 2-8 Xstore Office - Target Organization Node Pop Up