The Oracle Demand Signal Repository application uses the following business process task flow:
An Administrator loads cleansed data from various sources, including Retailer Point of Sale, Retailer Inventory, and syndicated into the Demand Signal Repository.
Mapping tables are updated with newly loaded data and synchronized with existing data.
Oracle Warehouse Builder performs initial mapping of Organization- and Geography-level members for inclusion and synchronization with EBS items and locations for EBS implementations.
The Oracle Data Integrator is used for mapping Organization, Geography, and Product members for non-EBS implementations.
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.
Analytical applications upload the data from Demand Signal Repository for analysis within the application.
DSR Web Services enable sharing DSR data with complementary applications such as:
Demantra Demand Management
Manufacturer applications
Retailer applications
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 |
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
Copy the DDR_TDLINX_STR_DATA.csv file (data in strict TDLinx format) to the location specified by the DDR_TDLINX_FILE_LOCATION parameter.
Load Org data into the DDR_R_ORG table using the TDLINX_ORG_HCHY process flow.
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.
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
Use the DDR_TDLINX_STR_FI_MAP process flow to move the data to the DDR_I_ORG_BSNS_UNIT Interface table.
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.
Use the ORG_HCHCY_ERR_TO_INF process flow to move data from DDR_E_ORG_BSNS_UNIT error table to the Interface table.
Demand Signal Repository integrates with Enterprise Resource Planning (ERP) applications, so that these applications can share data. This integration facilitates:
Data transfer from a variety of sources. For example, point-of-sale and syndicated data is synchronized and available for feeding EBS or Manufacturer point applications.
Internal identifiers of EBS reference data, for example, items, customers, and so on, are synchronized with Demand Signal Repository data and used during export.
Internal identifiers of Manufacturer reference data, for example, items, customers, and so on, are synchronized with Demand Signal Repository data and used during export.
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
EBS ItemX = DSR ItemY
EBS Loc11 = DSR Loc1
Demantra export integration profile for ASCP:
Item | Location | Week1 | Week2 | |
DSR Retailer DC Shipment | ItemX | Loc11 | 10 | 20 |
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
Manufacturer ItemX = DSR ItemY
Manufacturer Loc11 = DSR Loc1
By integrating Demantra Demand Management with Demand Signal Repository, users are able to:
Load data from Demand Signal Repository for further analysis using the statistical capabilities of Demantra Demand Management.
Forecast point-of-sale data received at the lowest levels of Demand Management.
Start the Demantra forecast engine with store-level data, aggregated to site and weekly point-of-sale levels.
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:
Item and location reference data
Retailer POS Sales data at day or week, store aggregated to Site (Shipto), and Item levels using the Demantra identifying codes
Retailer POS Forecast at day or week, store aggregated to Site (Shipto), and Item levels using the Demantra identifying codes
Retailer DC/WHSE Forecast at day or week, Site (Shipto), and Item levels using the Demantra identifying codes
Retailer Store OnHand Total data at day or week, store aggregated to Site (Shipto), and Item levels using the Demantra identifying codes
Retailer Store Orders Total at day or week, store aggregated to Site (Shipto), and Item levels using the Demantra identifying codes
Retailer DC/Whse OnHand data at day or week, Site (Shipto), and Item levels using the Demantra identifying codes
Retailer DC/Whse Shipments (also known as Store Shipments) at day or week, Site (Shipto), and Item using the Demantra identifying codes
Retailer DC/Whse Intransit at day or week, Site (Shipto), and Item using the Demantra identifying codes
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:
Scheduled to run automatically
Executed by the DSR Administrator
Run as part of a custom PL/SQL job
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:
Retail Sale Return - Only Point of Sale (POS) data.
Sale Forecast – Two year span of all data.
Inventory – Two year span of all data for the last day of the week only.
Retailer Ship – Two year span of DC data
Retailer Order – Two year span of Point of Sale data
Demantra requires the following fields for each record in the item data:
A unique code to be used as the identifier for any lowest-level item. This code is provided by mapping in Demand Signal Repository.
A unique description, which is a short string that serves as a user-friendly name of the item. If no description is available, use the code.
Additional codes that indicate the membership of this item within all levels of the item hierarchy
Additional fields that describe this item, as needed.
Additional fields that specify unit conversion factors for this item, if needed.
Demantra requires the following fields for each record in the location data:
A unique code to be used as the identifier for any lowest-level location.
A unique description, which is a short string that serves as a user-friendly name of the location. If no description is available, use the code.
Additional codes that indicate the membership of this location within all levels of the location hierarchy.
Additional fields that describe this location, as needed.
Demantra requires the following fields for each record in the sales data:
The unique code of the item being sold.
The unique code of the location of the sale.
The date of the sale.
The number of units that were sold for this item, location, and date. This field must be numeric.
Price per unit for this item, at this location and date. This field must be numeric.
Additional fields, as needed.
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:
Item Levels
Product Category: Item > Category
Product Family: Item > Product Family
Demand Class: Demand Class
Location Levels
Zone: Site > Trading Partner Zone > Zone
Customer Class: Site > Account > Customer > Customer Class
Business Group: Organization > Oper Unit > Bus Grp
Legal Entity: Organization > Legal Entity
Sales Channel: Sales Channel
Use the following time considerations when implementing the Demand Signal Repository to Demantra Demand Management Integration:
If base time unit = day, then:
Gregorian Calendar: Day > Month > Quarter > Year
Quarter one starts January 1st
Default 4-4-5 Calendar: Day > Week (Default 4-4-5) > Period (Default 4-4-5) > Quarter (Default 4-4-5) > Year (Default 4-4-5)
Week 1 starts on the first day of the calendar year that matches the Demantra 'week starts on' setting. For example, if 'week starts on' = Sunday, then Week 1 of 2007 would start on Sunday January 7, 2007.
Periods are four, four, then five weeks long, with the pattern starting on Week 1 of the year.
Quarters are three periods long, starting on Week one.
Years last from Day one of Week one to the day before the first day of the next calendar year that is a 'week starts on' day. For example, Year 2007, assuming 'week starts on' = Sunday, Year 2007 will last from January 7, 2007 through January 5, 2008.
If base time unit = week, then:
Default 4-4-5 Calendar: Week (Default 4-4-5) > Period (Default 4-4-5) > Quarter (Default 4-4-5) > Year (Default 4-4-5)
If base time unit = month, then:
Gregorian Calendar: Month > Quarter > Year > All
The following DSR data can be loaded as series in Demantra:
Retailer POS Sales: Retailer POS data aggregated to Site
Retailer POS Forecast: Retailer POS Forecast aggregated to Site
Retailer DC/WHSE Forecast: Retailer Distribution Center/ Warehouse Forecast
Retailer Store OnHand Total by Site: Retailer OnHand Store inventory aggregated to Site
Retailer Store Orders Total by Site: Retailer Store Orders aggregated to Site
Retailer DC/Whse OnHand: Retailer OnHand inventory at DC/WHSE
Retailer DC/Whse Withdrawals by Site: Retailer Withdrawals (Store Shipments) from DC/Whse
Retailer DC/Whse Intransit from DC/WHSE: Retailer Intransit Shipments from DC/WHSE
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.
The following diagram illustrates the RMS to DSR integration flow:
In order for the RMS integration to function you must have the following prerequisite applications installed and configured:
Oracle Demand Signal Repository release 12.1
Oracle Retail Merchandising System version 10.0 (or higher)
Oracle Data Integrator version 10.1.3.4
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.
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:
Configure the JAVA_HOME variable.
Configure the DDR_R_LKUP_MST DSR lookup table.
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.
In the ODI Topology Manager, configure the ODI physical architecture for the DSR and XML schemas.
Copy the file RETLDLPRD.dtd to the location specified in the RETL_DSR_SRC_DIR parameter.
Copy the file DLPRDFileToXMLConverter.class to the location specified in the RETL_DSR_CLASS_DIR parameter.
Move the EDI file to the location specified in the RETL_DSR_SRC_DIR parameter.
Execute the Integration scenario in ODI.
In the Oracle Warehouse Builder, run the Sales and Return Fact Data and Inventory Fact Data process flows.
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 |
Ensure that the directory structures as specified in RETL_DSR_SRC_DIR, RETL_DSR_SUCCESS_DIR and RETL_DSR_FAILED_DIR exist.
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
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>
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.
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:
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') |
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') |