Xstore Sales Integration

4 Xstore Sales Integration

Retail Insights supports loading intraday sales transactions from an Xstore string-based XML receiver API. The data loaded in this method is for the specific purpose of reporting on today’s sales before the end-of-day batch processes the full snapshot of audited sales transactions. The sales data from Xstore is not used as a primary source of sales history in Retail Insights, as the system was designed around the concept of a Sales Audit system being used prior to data coming into the data warehouse.

Sales from XStore are currently displayed in reporting for the current Business Date+1. For example, if your audited sales have been loaded up to the business date of 09/21/2020, then the Xstore sales will be displayed in reporting only for 09/22/2020. Prior days of Xstore sales will not be shown, because it is assumed your audited sales will include all prior transactions. Prior days of Xstore sales will be kept in the database for a limited amount of time before being purged. It is also important to note that this integration is specifically for sales only, RI does not process other transaction types such as timeclock, pre-order, work order, till, open/close, and other non-sales types (except when one of those transactions results in a completed sale where customer payment is collected).

Data Load Process

The data first comes from Xstore to the Retail AI Foundation data model using a web service API. The API is configured as part of AI Foundation but is leveraged by Retail Insights to get the raw XML POSLOGs into the database for transformation to our data model. The steps below highlight the flow of data into RI.

The XML is first extracted from the AI Foundation’s staging area for the API into the RI database table W_RTL_POSLOG_XML_G.
Next, the data for the current load process is moved to temporary tables W_RTL_SLS_POS_IT_LC_DY_TMP and W_RTL_SLS_POS_TAX_TMP. The current set of records is defined as all unprocessed XMLs that have been received since the last time the intraday batch process was run. For example, if the process runs hourly then for each iteration of this cycle, one hour of transactions will be processed in a single set.
The XML data is transformed from raw XML into the RI relational data model, filtered to the subset of sales transaction types we support, and then inserted to W_RTL_SLS_POS_IT_LC_DY_FS.
Lastly the data is loaded from W_RTL_SLS_POS_IT_LC_DY_FS to W_RTL_SLS_POS_IT_LC_DY_F. This load process maps all of the primary key values in the transaction record to the RI dimensions and creates the links across all RI tables that are necessary to report on the data in OAS.
Transactions which don’t have matching keys on one or more of the RI dimensional tables (for example, a product that doesn’t exist) will be rejected by the final load step and will not appear in the reporting layer. This will be the case for the core dimensions of product, location, and calendar. Employees are also matched against RI data but they have the option to be auto-seeded into our data model so that we do not reject records for new/unknown employee IDs.

The program used to perform this intraday processing is LOAD_POSLOG_DATA_ADHOC, which can be found in the Ad Hoc area of the POM batch schedule for Retail Insights. Using the ad hoc scheduling capabilities of POM, you would configure this job to run on a set frequency throughout the day. The actual frequency will largely depend on the trade-off of performance and data volumes. For example, if it takes X minutes to process a block of transactions which come in over Y minutes, X cannot be greater than Y or RI would not have enough time to complete a load before starting the next one. Identifying the optimal run frequency will need to be worked out during the implementation using realistic daily volumes.

Supported Data Types

Only certain types of Xstore transactions are processed into RI as sales. All other transaction types are ignored by the data load process in order to provide equivalent comparisons of audited and unaudited sales data between our two subject areas. The general logic for this comparison is that a sale is recorded to the data warehouse when customer payment is collected and the item ownership has been transferred. A sale should show an equivalent reduction in inventory and both the sale and inventory changes would be recorded on the stock ledger at the end of the day. The only exception to this would be sales of services, which should be tracked as non-inventoried or non-merchandise items so that RI is still aware of the dimensions present on the transaction before it occurs. Review the transaction usage details below.

Table 4-1 Transaction Usage Details

Transaction Type	Usage Details
Sale	Processed as normal sale into RI SLS_* columns in the database.
Return	Processed as normal sale into RI RET_* columns in the database. Net sales in RI is calculated by subtracting return values from sales values, so both sales and returns are held in the database as positive values.
Exchange	An equal exchange transaction will have it’s lines condensed to show values both on SLS_* and RET_* columns in the database for that item.
Special Order (Complete)	A completed special order will be captured as a sale based on the PreviousCustomerOrder status.
Work Order (Pickup)	A completed work order will be captured as a sale based on the PreviousCustomerOrder status.
Hold Account (Pickup)	A pickup on a hold account will be captured as a sale based on the PreviousCustomerOrder status.
Pre-Sale (Pickup)	A pickup on a pre-sale will be captured as a sale based on the PreviousCustomerOrder status.
Send Sale (Complete)	A completed send sale will be captured as a sale based on the SaleForDelivery status.
Layaway (Complete)	A completed layaway will be captured as a sale based on the PreviousLayaway status.

Supported Dimensions

Only certain dimensions in RI are supported when reporting on Xstore sales data, based on what we expect to have in our daily interfaces with other applications such as RMFCS. Review the list of supported dimensions below.

Table 4-2 Supported Dimensions

Dimension	Usage Details
Item	The item ID listed on a Line Item of the transaction must be a valid item in the RI Item hierarchy in order for us to load the record.
Organization	The location ID where the transaction was captured must be a valid location in the RI Organization hierarchy in order for us to load the record.
Business Calendar Gregorian Calendar	The date when the transaction occurred will be captured and reported using the Fiscal Calendar or Gregorian Calendar. POS sales are only shown for the current business date + 1.
Time of Day	The hour and minute when the transaction occurred will be captured and reported using the Time of Day dimension. The POS Sales fact itself also as a supplemental Timestamp attribute for more granular analysis if needed.
Supplier	The primary supplier associated with an item can be used to report on and aggregate the POS sales data. The supplier-item relationship is a separate feed into RI and not part of the POS data.
Retail Type	The retail type of a POS transaction will depend on the contents of the transaction as well as the other data in RI. By default, a transaction will be categorized as a Regular (R) sale. If the transaction has a Price Modifier of type ‘Promotion’ then it will become a Promotional (P) transaction. If you are providing inventory data with clearance flags to RI, then we can leverage that additional detail to flag sales as Clearance (C) sales.
Employee	RI reports on employees using either the Cashier or Salesperson attributes, and both may be used with POS sales data assuming the cashier/salesperson is identified on the transaction. Because employee data is not maintained by a merchandising system, RI allows these records to be created automatically if we encounter an unknown employee ID on a transaction.

Intraday Scheduling

Due to the real-time nature of POSLOG data from Xstore, an intraday process must be used to move the data from it’s raw XLML format into the Retail Insights relational data model throughout the day. This intraday process can be maintained in the POM application alongside the RI nightly batch. Follow the steps below to enable this process.

Login to the POM application UI as a user with batch scheduling permissions.
Click on the Scheduler Administration link under the Tasks menu.
Click on the RI schedule. Make sure the LOAD_POSLOG_DATA_ADHOC process is present in the Standalone tab and enabled.
Highlight that row and click the Edit action icon.
Adjust the frequency to the desired interval, such as 30 minutes or 1 hour. Click OK to confirm the change.
Navigate to the Batch Monitoring screen and Restart Schedule for the RI Standalone schedule.
Once restarted, the adhoc process will begin to run automatically on the specified interval.