Demand Signal Repository Integration

Business Process Task Flow

The Oracle Demand Signal Repository application uses the following business process task flow:

  1. An Administrator loads cleansed data from various sources, including Retailer Point of Sale, Retailer Inventory, and syndicated into the Demand Signal Repository.

  2. Mapping tables are updated with newly loaded data and synchronized with existing data.

  3. Oracle Warehouse Builder performs initial mapping of Organization- and Geography-level members for inclusion and synchronization with EBS items and locations for EBS implementations.

  4. The Oracle Data Integrator is used for mapping Organization, Geography, and Product members for non-EBS implementations.

  5. The Demand Signal Repository exports data required for analytical applications at the required levels. For Demantra, item, organization, and site (Shipto) at the day or week level are required using Demantra’s internal identifiers for EBS implementations.

  6. Analytical applications upload the data from Demand Signal Repository for analysis within the application.

the picture is described in the document text

Web Services

DSR Web Services enable sharing DSR data with complementary applications such as:

Demand Signal Repository shares demand data with manufacturer applications at both the lowest level of detail and the aggregate level. A web service for each group of facts in the data model enables an application to select and retrieve data according to the dimensions of time, manufacturer item hierarchy, retailer organizational hierarchy (selected levels), and location. These web services provide cleansed retail point of sale, on-hand inventory and other data at daily store level, or aggregated to a higher level as needed. The following table describes each of the supported Web Services:

Web Service Description
getSalesReturnItem Returns detailed data from the Retailer Sales Return Item Day fact table.
getMrktItemSales Returns detailed data from the Market Item Sales Fact table.
getPrmtinPlan Returns detailed data from the Promotion Plan fact table.
getRtlInvItmDat Returns detailed data from the Retail Inventory Item Day fact table.
getRetailerOrderItem Returns detailed data from the Retailer Order Item Day fact table.
getSaleFrecastItem Returns the detailed data from the Sales Forecast Item fact table.
getRetailerShipItem Returns the detailed data from the Retailer Ship Item fact table.
getAgrSalesReturnItem Returns aggregate data from the Retailer Sales Return Item Day fact table.
getAgrMrktItemSales Returns aggregate data from the Market Item Sales fact table.
getAgrPrmtinPlan Returns aggregate data from the Promotion Plan fact table.
getAgrRtlInvItmDat Returns aggregate data from the Retail Inventory Item Day fact table.
getAgrRetailerOrderItem Returns aggregate data from the Retailer Order Item Day fact table.
getAgrSaleFrecastItem Returns aggregate data from the Retailer Order Item Day fact table.
getAgrSaleFrecastItem Returns aggregate data from the Sales Forecast Item fact table.
getAgrRetailerShipItem Returns aggregate data from the Retailer Shipment Item fact table
getFileName Returns the name of the files for the specified job.
getDDRXmlFile Returns the streamed xml file data for the specified file name.

The Demand Signal Repository provides a web services interface for external applications to retrieve data at detailed and aggregate levels. These services correspond to the major fact tables in the system. They support the most common repetitive data access scenarios:

DSR Data Types Detailed Data Access Aggregate Data Access
Sales (POS) Returns getSalesReturnItem getAgrSalesReturnItem
Sales (actual, average, normal, price cut, ad) ACV getMrktItemSalesWeek getAgrMrktItemSalesWeek
Retail Promotions getPrmtinPlan getAgrPrmtinPlan
On-Hand Inventory, In-Transit Inventory, Backorders, Quality Hold Merchandise, Receipts getRtlInvItmDat getAgrRtlInvItmDat
Store Orders getRetailerOrderItmDay getRetailerAgrOrderItmDay
Forecasts getSaleFrecastItmByDay getAgrSaleFrecastItmByDay
Shipments getRetailerShipItemDay getAgrRetailerShipItemDay

Web Services returns aggregated (lowest levels are added into any higher levels in dimension hierarchies) and detailed (lowest dimension hierarchy level maintained in Fact table) fact data. Output data is in XML format stored in an XML file. For detailed level Web Services, output data is generated based on the specified hierarchy level input given by the calling application. Generated Output data is at lowest level of the dimension hierarchy stored in Fact tables. For the aggregate level Web Services, aggregated output will be generated based on the web service call for the specified hierarchy level and expected aggregation hierarchy level.

External application requests data by specifying the specific dimensional level and its values from the supported hierarchies:

Hierarchy Supported Dimensions
Time Day
Gregorian Month
Gregorian Year
Time Day
Business Week
Business Month
Business Quarter
Business Year
Item SKU
Item
Sub Class
Class
Group
Division
Organization BU
Retail Chain
Retail Org
Retail Cluster
Location Address
Location
City
State
Country

Loading TDLinx Store/Outlet Data

The TDLinx Store/Outlet adapter enables the import of syndicated Consumer Goods data from Nielsen. Nielsen syndicated store data is available in a format called TDLinx. Manufacturers use this data to analyze Retailer store level and market level data.

The adapter loads the supplied .csv file, which must follow Nielsen's TDLinx formats strictly: column order, length, position as specified in the TDLinx Data Dictionary. The input file must be a csv file with placeholders for all values. The adapter performs the necessary translations prior to populating the associated DSR interface table. The Import Data procedure maps fields using the column names defined within the TDLinx source file to the matching columns in the interface table.

Loading TDLinx Data using the TDLinx Store/Outlet Adapter

  1. Copy the DDR_TDLINX_STR_DATA.csv file (data in strict TDLinx format) to the location specified by the DDR_TDLINX_FILE_LOCATION parameter.

  2. Load Org data into the DDR_R_ORG table using the TDLINX_ORG_HCHY process flow.

  3. Load the Org Hierarchy data tables (DDR_R_ORG_CHAIN, DDR_R_ORG_AREA, DDR_R_ORG_REGION, DDR_R_ORG_DISTRICT and DDR_R_BSNS_UNIT) using the TDLINX_ORG_HCHY process flow.

  4. Load Store data (DDR_R_BSNS_UNIT), from TDLinx data using the TDLINX_ORG_HCHY process flow.

To move the data from the TDLinx file in separate steps

  1. Use the DDR_TDLINX_STR_FI_MAP process flow to move the data to the DDR_I_ORG_BSNS_UNIT Interface table.

  2. Use the DDR_ORG_HCHY_IF_MAP process flow to move the data from Interface table to the DDR_R_ORG_BSNS_UNIT target table.

  3. Use the ORG_HCHCY_ERR_TO_INF process flow to move data from DDR_E_ORG_BSNS_UNIT error table to the Interface table.

Enterprise Resource Planning to Demand Signal Repository Integration

Demand Signal Repository integrates with Enterprise Resource Planning (ERP) applications, so that these applications can share data. This integration facilitates:

Inputs to Demantra from EBS and Demand Signal Repository

EBS Data

Location Hierarchy Loc11 Loc22 Loc33  
Item Hierarchy ItemX Cat11 Cat22 Cat44

DSR Data

Location Hierarchy Loc11 Loc2 Loc3 Loc4
Item Hierarchy ItemY Cat1 Cat2  

DSR Retailer DC Shipment data

  Item Location Week1 Week2
DSR Retailer DC Shipment ItemY Loc1 10 20

Mapping

Outputs from Demantra to EBS Applications

Demantra export integration profile for ASCP:

  Item Location Week1 Week2
DSR Retailer DC Shipment ItemX Loc11 10 20

Demand Signal Repository Manufacturer Data Synchronization

Use the Demand Signal Repository integrations to synchronize the Manufacturer reference data to the corresponding data from point-of-sale, syndicated sources, and retailer inventory.

Inputs to manufacturer’s point application from Demand Signal Repository and manufacturer systems:

Manufacturer Reference Data

Location Hierarchy Loc11 Loc22 Loc33  
Item Hierarchy ItemX Cat11 Cat22 Cat33

DSR Data

Location Hierarchy Loc1 Loc2 Loc3 Loc4
Item Hierarchy ItemY Cat1 Cat2  

DSR Retailer DC Shipment Data

  Item Location Week1 Week2
DSR Retailer DC Shipment ItemY Loc1 10 20

Mapping

Demand Signal Repository to Demantra Demand Management Integration

By integrating Demantra Demand Management with Demand Signal Repository, users are able to:

This integration extracts specified data from Demand Signal Repository for downloading into Demantra staging tables at the lowest level of the Demantra hierarchies. By default, data is exported at a weekly level but can be modified by the Administrator to export at other aggregation levels.

Data that is exported from Demand Signal Repository includes:

Demand Signal Repository Data Extract

The Oracle Warehouse Builder map DDR_DMNT_INTG_MAP populates the Demand Management interface staging table BIIO_DSR_SALES_DATA using information from the mapping tables. The process flow pulls 2 years of data and can be:

Up to two years of data relative to the current System date (one year's data before the system date, and one year's data after the system date) is collected from DSR and loaded. This data includes:

Lowest-Level Item Data

Demantra requires the following fields for each record in the item data:

Lowest-Level Location Data

Demantra requires the following fields for each record in the location data:

Sales Data

Demantra requires the following fields for each record in the sales data:

Aggregation in Time

You must select the smallest time unit that you will use within Demantra. Correspondingly, you must specify the start of that time unit (such as the starting day of the week) and an aggregation method for Demantra to use when importing data (backwards or forwards). However, it is not necessary to pre-aggregate the raw data in time. The Demantra loading and integration tools can perform that aggregation, if needed. That is, if you import multiple sales records for different dates for the same item-location combination, then Demantra automatically sums them into the time unit to which those dates belong.

Note: Together, the item, location, and date will form the primary key for the sales record. That is, Demantra stores no more than one record for each combination of item, location, and date.

The preseeded hierarchies in Demantra are:

Time Considerations

Use the following time considerations when implementing the Demand Signal Repository to Demantra Demand Management Integration:

Loading DSR Data as Demantra Series

The following DSR data can be loaded as series in Demantra:

Demand Signal Repository to Retail Merchandising System Integration

The Retail Merchandising System (RMS) Integration provides an out-of the box integration of EDI 852 data coming from Oracle’s RMS system into Oracle’s Demand Signal Repository (DSR) module. This integration allows Consumer Goods Manufacturers to import data from their retail customers using Oracle’s Retail Merchandising System (RMS) to Demand Signal Repository.

Oracle Retail produces a report in a flat file pre-EDI format that contains information for each supplier. To secure the data for each supplier Oracle Retail should produce one file per supplier. This report contains item quantities grouped by Stores and Warehouse Locations.

Integration Flow

The following diagram illustrates the RMS to DSR integration flow:

the picture is described in the document text

Integration Prerequisites

In order for the RMS integration to function you must have the following prerequisite applications installed and configured:

RMS Integration Setup and Process

To use the RMS integration, you must first install and configure Demand Signal Repository as well Oracle Data Integrator (ODI). Once these have been installed and configured, you must RMS Integration system parameters in DSR are set appropriately.

Once the set up steps are complete, you will need to load the RMS EDI 852 file into the proper directory and run the DSR integration scenario. Upon successful completion of the scenario, the DSR sales and return interface table and the DSR inventory interface table will be populated. The standard Oracle Warehouse Builder (OWB) process flows are used to process the data in the interface tables and populate the target DSR tables.

RMS Configuration Steps

The productivity data report is a sales audit summary that is sent to specify EDI vendors, giving sales details, current stock on hand for all location, and current in transit quantities for each of the items primarily supplied by that vendor.

This program polls all suppliers that require activity reporting. Those that require daily reports have daily sales information selected from the EDI_DAILY_SALES table, while those requiring weekly reports pull information from the ITEM_LOC_HIST and ITEM_MASTER tables to get the weekly sales data. The store level table ITEM_LOC is queried for current stock levels, and the quantities that are in-transit are derived from the transfer tables.

Use the following steps to configure the RMS to DSR integration:

  1. Configure the JAVA_HOME variable.

  2. Configure the DDR_R_LKUP_MST DSR lookup table.

  3. Create the directory structures as specified in the RETL_DSR_SRC_DIR, RETL_DSR_SUCCESS_DIR, RETL_DSR_FAILED and RETL__DSR_CLASS_DIR parameters.

  4. In the ODI Topology Manager, configure the ODI physical architecture for the DSR and XML schemas.

  5. Copy the file RETLDLPRD.dtd to the location specified in the RETL_DSR_SRC_DIR parameter.

  6. Copy the file DLPRDFileToXMLConverter.class to the location specified in the RETL_DSR_CLASS_DIR parameter.

  7. Move the EDI file to the location specified in the RETL_DSR_SRC_DIR parameter.

  8. Execute the Integration scenario in ODI.

  9. In the Oracle Warehouse Builder, run the Sales and Return Fact Data and Inventory Fact Data process flows.

Configuring the DDR_R_LKUP_MST Lookup Table

The following DSR system parameters are used by the RMS integration. These values can be changed using the Look Up Master ETL process flow.

The Retail Source Directory (RETL_DSR_SRC_DIR) parameter specifies the directory where the RMS EDI (EDIDLPRD) file is stored for upload to DSR. When running the RMS integration scenario, the scenario will look for a file in this directory. If a file is found, the scenario continues; otherwise, the scenario waits indefinitely for an EDI file to show up in this directory.

Parameter Name Description Example
RETL_DSR_SRC_DIR The directory where the RMS EDI File is stored for upload to DSR. ODI_HOME/retldsr/source
RETL_DSR_CLASS_DIR The directory where the DLPRDFileToXMLConverter.class File should be kept. RETL_DSR_HOME\RetailDSRHome
RETL_DSR_SRC_FILENAME The RMS EDI file name that is loaded into DSR. EDIDLPRD.edi
RETL_DSR_SUCCESS_DIR The directory where the RMS EDIDLPRD file is moved after a successful upload. ODI_HOME/retldsr/sucess
RETL_DSR_FAILED_DIR The directory where the RMS EDIDLPRD file is moved if the upload fails. ODI_HOME/retldsr/failed
RETL_DSR_TO_ADDR The admin email address where notifications are sent. firstname.lastname@oracle.com
RETL_DSR_FM_ADDR The Integrator admin email from which notifications are sent. retl-dsr@integrator.com
RETL_DSR_MAIL_SERVER Mail Server that sends the ODI email notifications. mail.oracle.com

Creating Directory Structures

Ensure that the directory structures as specified in RETL_DSR_SRC_DIR, RETL_DSR_SUCCESS_DIR and RETL_DSR_FAILED_DIR exist.

Setting the Location for the RETLDLPRD.dtd Schema in ODI

You must set the location of the RETLDLPRD.dtd schema file in ODI. From the Topology Manager, navigate to the Physical Architecture tab and enter the JDBC location. For example:

jdbc:snps:xml?d=RETL_DSR_HOME\RETLDLPRD.dtd &S=RETLDLPRD

Configuring the ODI Physical Architecture for the DSR and XML Schemas

You must configure the EBS DSR target database schema so that the integration can connect to connect the DSR installation to ODI. The schema name is typically “DDR”. For example:

jdbc:oracle:thin:@<server_name>:<port>:<db_name>

Running the Integration Scenario

Once the system parameters have been set and an EDI file has been loaded into the directory specified in the system parameters, start Oracle Data Integrator (ODI) and run the Load Retail Items to DSR scenario.

After running the integration scenario, the History tab displays all the steps within the scenario that executed and whether or not they were successful. In the case of an error, the EDI file is moved to the failed directory and an e-mail notification is sent that the scenario failed.

Attribute Mappings

The integration transforms the Oracle Retail Sales and Stock On Hand pre-EDI file to XML format and then maps it to Oracle-DSR Sales and Inventory Facts interface tables. The mapping between the XML elements to table columns is shown below:

Sales Item Mappings

Oracle Retail Source EDIDLPRD File Oracle Retail Source [XML Element] Oracle DSR Target DDR_I_RTL_INV_ITEM Columns Mapping Details
    REC_ID DDR_I_RTL_INV_ITEM_SEQ.NEXTVAL
TQUTY.Location <Location> LOC_IDNT_CD RTRIM (ITEMQUANTITY.LOCATION)
TITEM.Item <ItemNum> GLBL_ITEM_ID RTRIM (ITEM.ITEMNUM)
TITEM.Ref_Item
TITEM.Vendor catalog number		 <RefItemNum>
<VendorCatalogNumber>		 RTL_SKU_ITEM_NBR CASEWHEN (ITEM.REFITEM IS NOT NULL, ITEM.REFITEM, ITEM.VENDORCATALOGNUMBER)
TITEM.Item_Num_Type <ItemNumType> GLBL_ITEM_ID_TYP RTRIM (ITEM.ITEMNUMTYPE)
TQUTY.Quantity where QuantityDescriptor=’Sold’ <SoldQty> SLS_QTY RTRIM(QTYBYLOCATION.SOLDQUANTITY)
    PERIOD_TYP_FLAG Default ‘D’
    LOC_IDNT_FLAG Default ’B’
    ORG_LVL_CD Default ’O’
THEAD.Report date <Supplier ReportDate=”YYYYMMDD”> SRC_SYS_DT TO_DATE (DLPRD.CREATEDATE,'YYYYMMDD')
FHEAD.File Source <DLPRD Source=”DLPRD”> SRC_SYS_IDNT CONCAT('RETL:', DLPRD.SOURCE)
FHEAD.File Create Date <DLPRD CreateDate=”YYYYMMDD”> TRANS_DT TO_DATE (SUPPLIER.REPORTDATE,'YYYYMMDD')

Inventory Item Mappings

Oracle Retail Source EDIDLPRD File Oracle Retail Source [XML Element] Oracle DSR Target DDR_I_RTL_INV_ITEM Columns Mapping Details
    REC_ID DDR_I_RTL_INV_ITEM_SEQ.NEXTVAL
TQUTY.Location <Location> RTL_BSNS_UNIT_CD RTRIM (ITEMQUANTITY.LOCATION)
TITEM.Item <ItemNum> GLBL_ITEM_ID RTRIM (ITEM.ITEMNUM)
TITEM.Ref_Item
TITEM.Vendor catalog number		 <RefItemNumber>
<VendorCatalogNumber>		 RTL_SKU_ITEM_NBR CASEWHEN (ITEM.REFITEM IS NOT NULL, ITEM.REFITEM, ITEM.VENDORCATALOGNUMBER)
TITEM.Item_Num_Type <ItemNumType> GLBL_ITEM_ID_TYP RTRIM (ITEM.ITEMNUMTYPE)
TQUTY.Location <Location> INV_LOC_CD RTRIM (ITEMQUANTITY.LOCATION)
TQUTY.Quantity where QuantityDescriptor=’On hand’ <OnhandQty> ON_HAND_QTY RTRIM(QTYBYLOCATION.ONHANDQUANTITY)
TQUTY.Quantity where QuantityDescriptor=’In Transit’ <InTransitQty> IN_TRANSIT_QTY RTRIM(QTYBYLOCATION.INTRANSITQUANTITY)
TQUTY.UnitCost where QuantityDescriptor=’On hand’ <OnhandQtyUnitCost> ON_HAND_NET_COST_AMT RTRIM(QTYBYLOCATION.ONHANDQTYUNITCOST) * RTRIM(QTYBYLOCATION.ONHANDQUANTITY)
TQUTY.UnitCost where QuantityDescriptor=’In Transit’ <InTransitQtyUnitCost> IN_TRANSIT_NET_COST_AMT RTRIM(QTYBYLOCATION.INTRANSITQTYUNITCOST) * RTRIM(QTYBYLOCATION.INTRANSITQUANTITY)
THEAD.Report date <Supplier ReportDate=”YYYYMMDD”> TRANS_DT TO_DATE (SUPPLIER.REPORTDATE,'YYYYMMDD')
FHEAD.File Source <DLPRD Source=”DLPRD”> SRC_SYS_IDNT CONCAT('RETL:', DLPRD.SOURCE)
FHEAD.File Create Date <DLPRD CreateDate=”YYYYMMDD”> SRC_SYS_DT TO_DATE (DLPRD.CREATEDATE,'YYYYMMDD')