Chapter 96: CWData Overview | Contents | SCVs | Search | Glossary | Reports | XML | Index | Data Warehouse Staging Errors Report |
Chapter 97: Working with the Data Warehouse Integration in CWDirect
Purpose: CWDirect allows you to capture data to download to the CWData data warehouse. You can use the CWData data warehouse to analyze your data; see CWData Overview for a better understanding of how you can use CWData.
If you capture data warehouse transactions, CWDirect:
• creates records in the DW Transaction file to track each transaction that will be downloaded to the data warehouse. The DW Transaction file acts as a “to do” list for the transactions that require download to the data warehouse. This process saves system resources that would be required to write each data warehouse transaction record at the same time as processing the transactions in CWDirect. See DW Capture Transaction File.
• creates records in DW Staging files to capture non-transactional activity. For example, updates to your company, such as a change in address, are stored in the DW Company file for download. See DW Common Reference File.
In this chapter:
• What DW Staging Files are Updated by Each Initial Load Stage?
• Submitting an Incremental Load
• What data is captured in the DW Common Reference file?
• CWDirect activities captured in the DW Capture Transaction File
• What key data is captured for each DW capture transaction record?
• Downloading Data to the Tables in the cwi_staging Database on the CWData Server
• Working with Data Warehouse Transactions (WDWT)
• Work with Data Warehouse Transactions Screen
• Process Data Warehouse Changes Window
• Reprocess Data Warehouse Changes Window
Before you can download CWDirect data to the data warehouse staging files, you must perform the necessary CWDirect setup and processing.
Information requiring setup includes:
Important: CWData does not support special characters (for example: # , % * &). Before you transfer information from CWDirect to CWData, you should remove any special characters from your data.
System Control Value |
Description |
Select this field to capture information on activity in CWDirect for transfer to the CWData data warehouse. CWDirect uses the DW Transaction file to track each transaction that should be downloaded to the data warehouse. See DW Capture Transaction File. In addition, if this system control value is selected, the system requires you to define a vendor number when you create an item/SKU. |
|
Enter the number of days to retain records in the DW Transaction file before purging them. The Process DW Changes job performs the purge, deleting any records if: Original process date is less than or equal to the current system date - the number of purge days. Example: Today is 8/14, and you have set this value to 5. Any record whose Original process date is 8/7 or earlier is purged. You can also set this job up as part of your periodic processing, or run it on demand. |
|
Suppress Customer Credit Card Information in Data Warehouse (I49) |
Select this field to prevent the system from downloading and storing customer credit card information in the CWData data warehouse. When the system processes a data warehouse load and you suppress customer credit card information, the system does not populate the Last credit card and Last credit card expiration date fields in the DW Customer Sold To file. Because these fields are not populated, the last_credit_card and last_credit_card_expire fields in the Customer table in the data warehouse are not populated. Encryption: If you use credit card encryption and you do not prevent the system from downloading and storing customer credit card information in the CWData data warehouse, the credit card number will not be encrypted, since the information is being sent to an external system. Tokenization: If you use credit card tokenization and you do not prevent the system from downloading and storing customer credit card information in the CWData data warehouse, the number downloaded may be a token rather than the actual credit card number. |
Select this field if you want the system to pass the hidden tax amount included in the order line price for orders subject to Value Added Tax (VAT) as a separate amount to CWData. • The hidden tax amount defined for the order line is passed to CWData in the Tax field in the DW Order Detail file. • The merchandise amount defined for the order line is passed to CWData in the Price field in the DW Order Detail file and DW Order Detail Activity file. Leave this field blank if you want the system to pass the hidden tax amount included in the order line price for orders subject to Value Added Tax (VAT) as part of the order line price to CWData. • The hidden tax amount, along with the merchandise amount defined for the order line, is passed to CWData in the Price field in the DW Order Detail file and DW Order Detail Activity file. • The Tax field in the DW Order Detail file remains blank. |
Secured Feature |
Description |
If you allow access to this feature, the user can process the initial load of the data warehouse for CWData. If you prohibit access to this feature, the user cannot advance to the Process Initial Load Screen in the Work with Data Warehouse Transactions menu option. |
Menu Option |
Description |
Allows you to review and download CWDirect data to the CWData data warehouse. |
Periodic Function |
Description |
Process Data Warehouse Changes (program name DWR0068) |
Builds records in each related DW Staging file based on the key in the DW Transaction file. You can also submit this process on demand at the Process Data Warehouse Changes Window in the Work with Data Warehouse Transactions menu option. |
If the Use Data Warehouse (G94) system control value is selected, you can download CWDirect data to the DW Staging files.
1. Submit an initial load to extract and load CWDirect data to the CWData data warehouse. You can submit an initial load at the Process Initial Load Screen.
2. On a periodic basis, submit an incremental load to extract and load changes. You can submit an incremental load:
• at the Process Data Warehouse Changes Window
• by submitting the Process Data Warehouse Changes periodic function (program name DWR0068)
Note: You can start capturing changes at the same time you perform the initial extract and load. If there is a period when transactions are captured by the initial load and the incremental load; the system captures the latest image during the incremental load and passes the most recent data to the data warehouse.
Important: Before loading data to the CWData data warehouse, it is imperative that you verify the integrity of your CWDirect data. Make sure you do not have any invalid records or the data in the CWData database will be inaccurate.
Flowchart: This flowchart explains how CWDirect data is downloaded to the CWDirect DW Staging files.
You can submit an initial load at the Process Initial Load Screen. MICROS recommends you submit the initial load in stages, since the load can be time-consuming.
Loading the data in stages: There are 3 categories of records to include in the initial extract and load. You should load each category separately, in the order presented below.
1. supporting data (reference information such as vendors, codes, financial, and business structures and the company itself)
2. customers Note: You must load all customers before loading orders.
3. orders
Journaling: You can turn off journaling for the DW Staging files to improve performance. To turn off journaling for these files, enter CALL DWENDJRN PARM(’CWMPDTA’) at a command line, where CWMPDTA is the library specified in the Data Base Library (A37) system control value. This command permanently turns off journaling in all DW Staging files.
Note: Make sure no users are logged into CWDirect before using the command to turn off journaling. If you enter this command and journaling is already turned off for the DW Staging files, the system displays an error message.
When you perform an initial load, the system:
1. creates records in the DW Staging files, based on the category of records you selected to extract and load; see What DW Staging Files are Updated by Each Initial Load Stage?.
2. generates the Data Warehouse Staging Errors report. This report displays order transactions that contain item or SKU errors. For example, you perform a merge/purge which updates an old order that contains an item or SKU that has since been deleted. Transactions that contain item/SKU errors remain in the DW Capture Transaction file until the errors are corrected. The system does not create any records in these DW Staging files that are related to the order transaction in error:
• DW Order Header
• DW Order Detail
• DW Order Ship To
• DW Order Payment
• DW Order Payment Activity
• DW Order Additional Charges
For more information: See Data Warehouse Staging Errors Report.
3. creates an end-of-file record in the DW Common Reference file. RELIC looks for the end-of-file record to trigger the transfer of the records in the DW Staging files to the tables in the cwi_staging database on the CWData server; see Downloading Data to the Tables in the cwi_staging Database on the CWData Server.
What DW Staging Files are Updated by Each Initial Load Stage?
This table indicates the DW Staging files the system updates, based on the category of records you selected to extract and load.
Note: The system does not update the DW Price Change Hist Detl file or DW Price Change Hist Summ file when you perform an initial load, regardless of the selected data to extract.
DW staging file: |
refers to CWDirect file(s): |
DW Staging files updated during each selected extract and load: |
|
DW A/R Type |
A/R Type |
DW Accounting Period |
Accounting Period |
DW Common Reference |
The DW Common Reference file is updated by many CWDirect files; see What data is captured in the DW Common Reference file? for a list of CWDirect files that update the DW Common Reference file. |
DW Company |
Company Company Address |
DW Division |
Division |
DW Entity |
Entity |
DW Geography |
pulls address information from: Customer Sold To Customer Bill To Customer Ship To Company Address Entity Vendor Warehouse |
DW Terms |
Terms |
DW Warehouse |
Warehouse |
DW Staging files updated when you extract supporting data: |
|
DW A/R Open Item |
A/R Open Item |
DW A/R Payment Detail |
A/R Payment Detail |
DW Correspondence History |
Correspondence History |
DW Customer Bill To |
Customer Bill To Customer Bill To Extended Customer Bill To Phone Number |
DW E-Comm Item Category |
Ecommerce Item Categories |
DW Item |
Item |
DW Item Attribute Assign |
Item Attribute Assignment |
DW Item Trans Activity |
Item Transaction History |
DW Manifest Audit |
Manifest Upload Audit |
DW Offer |
Offer |
DW SKU |
SKU |
DW SKU Cross Reference |
SKU Cross Reference |
DW SKU Offer |
SKU Offer |
DW SKU UPC |
Item UPC |
DW Source |
Source |
DW Vendor |
Vendor Vendor Extended |
DW Vendor History Detail |
A/P Invoice Detail History PO Detail PO Detail Estimated Charge PO Header PO Receipt Vendor Charge Back Detail |
DW Staging files updated when you extract customer files: |
|
DW Catalog Request |
Catalog Request |
DW Customer Affinity |
Customer Affinity |
DW Customer Individual |
Customer Individual |
DW Customer Membership |
Customer Membership |
DW Customer Profile |
Customer Profile |
DW Customer Ship To |
Customer Ship To Customer Ship To Extended Customer Ship To Order History Customer Ship To Phone Number |
DW Customer Ship To Ent |
Customer Ship To Entity |
DW Customer Sold To |
Customer Sold To Customer Sold To Extended Customer Sold To Order History Customer Sold To Phone Number |
DW Customer Sold To Email |
Customer Sold To Email |
DW Customer Sold To Ent |
Customer Sold To Entity |
DW Customer Warranty |
Customer Warranty Track |
DW Customer Ownership |
Customer Ownership |
DW Staging files updated when you extract order files: |
|
DW Order Additional Chg |
Order Additional Charge |
DW Order Detail |
Order Detail |
DW Order Detail Activity |
Order Line History |
DW Order Header |
Order Header Order Header Extended |
DW Order Payment |
Order Payment Method |
DW Order Payment Activity |
Invoice Payment Method |
DW Order Ship To |
Order Ship To |
Submitting an Incremental Load
If the Use Data Warehouse (G94) system control value is selected, the system creates records in the DW Capture Transaction file, based on CWDirect activity. The DW Transaction file acts as a “to do” list for the transactions that require download to the data warehouse.
You can submit an incremental load:
• at the Process Data Warehouse Changes Window.
• by submitting the Process Data Warehouse Changes periodic function (program name DWR0068).
When you perform an incremental load, the system:
1. builds the supporting DW Staging files for non-transactional activity. The supporting files include information such as codes and categories used to group and classify data. See DW Common Reference File.
2. creates records in the DW Staging files, based on the records in the DW Capture Transaction file. Using the File code and the Key information for each DW Capture Transaction record, the job builds records in the related DW Staging file for each transaction. See DW Capture Transaction File.
3. purges DW transaction records, based on the Data Warehouse Transaction Purge Days (H10) system control value. The system checks each record in the DW Transaction file and deletes any record whose Original process date is equal to the current system date minus the number of purge days specified. For example, if you processed a record a week ago, and the number of purge days is 7, the record is now eligible to be purged.
4. generates the Data Warehouse Staging Errors Report. This report displays order transactions that contain item or SKU errors. For example, you perform a merge/purge which updates an old order that contains an item or SKU that has since been deleted. Transactions that contain item/SKU errors remain in the DW Capture Transaction file until the errors are corrected. The system does not create any records in these DW Staging files that are related to the order transaction in error:
• DW Order Header
• DW Order Detail
• DW Order Ship To
• DW Order Payment
• DW Order Payment Activity
• DW Order Additional Charges
5. creates an end-of-file record in the DW Common Reference file. RELIC looks for the end-of-file record to trigger the transfer of the records in the DW Staging files to the tables in the cwi_staging database on the CWData server. See Downloading Data to the Tables in the cwi_staging Database on the CWData Server.
The system repopulates the DW Common Reference file each time you process changes to the data warehouse. See What data is captured in the DW Common Reference file? to view the non-transactional data captured in the DW Common Reference file.
Field |
Description |
Company |
The company where you submitted the data warehouse load. |
File |
A code used to identify the CWDirect file containing data to download to the data warehouse. See What data is captured in the DW Common Reference file? for an understanding of the data downloaded to the data warehouse from the DW Common Reference file. |
Key |
A code used to identify the CWDirect company and data to download to the data warehouse. The File field identifies the CWDirect file where the data is located. The Key field is then used to identify the record in the file to download. Example: If the File is EXR (exchange reason), the File key identifies the CWDirect company and exchange reason record to download. See What data is captured in the DW Common Reference file? for an understanding of the file code assigned to each DW capture transaction record, based on the CWDirect activity. |
Description |
A description of the data captured, based on the File and Key. Example: If the File is EXR (exchange reason) and the File key is 001 (exchange reason code), the Description is the description of the exchange reason code. |
What data is captured in the DW Common Reference file?
This table indicates:
• which CWDirect file is referenced, based on the File code assigned to the DW common reference record.
• what File key is defined, based on the File code assigned to the DW common reference record.
• what Description is defined, based on the File code assigned to the DW common reference record.
File code: |
Refers to CWDirect file: |
Menu Option: |
File key: |
Description: |
ADD |
Additional Charge Code |
WADC |
additional charge code |
additional charge description |
ADR |
Add Reason |
WADR |
add reason code |
add reason description |
BNK |
Bank |
WBNK |
bank number |
bank description |
BUY |
Buyer |
WBUY |
buyer code |
buyer name |
CCL |
Customer Class |
WCCL |
customer class code |
customer class description |
CNR |
Cancel Reason |
WCNR |
cancel reason code |
cancel reason description |
CNT |
Country |
WCTY |
country code |
country code description |
DRN |
Dispute Reason |
WDSR |
dispute reason code |
dispute reason description |
ECA |
Ecommerce Category |
WECC |
ecommerce category code |
ecommerce category code |
EXR |
Exchange Reason |
WEXR |
exchange reason code |
exchange reason description |
GZF |
Geographic Zone |
WGZN |
geographic zone code |
geographic zone description |
HAZ |
Hazard Code |
WHAZ |
hazard code |
hazard description |
IAT |
Item Attribute |
WIAT |
item attribute code |
item attribute description |
IAV |
Item Attribute Value |
WIAT |
item attribute value type code + item attribute value code |
item attribute value description |
ICC |
Item Cycle |
WICL |
item cycle code |
item cycle description |
ICL |
Item Class |
WICL |
item class code |
item class description |
IST |
Item Status |
WIST |
item status code |
item status description |
ITC |
Item Category |
WITC |
item category code |
item category description |
LCL |
Location Class |
WLCL |
location class code |
location class description |
LDV |
Long SKU Division |
WLDV |
long SKU division code |
long SKU division description |
LNG |
Language |
WLAN |
language code |
language description |
LOB |
Line of Business |
WLOB |
line of business code |
line of business description |
LSC |
Long SKU Class |
WLSC |
long SKU class code |
long SKU class description |
LSD |
Long SKU Department |
WLSD |
long SKU department code |
long SKU department description |
LST |
List Source |
WLSR |
list source code |
list source description |
MCC |
Mail/Call Code |
WMCC |
mail/call code |
mail/call code description |
OLA |
Order Line Activity Code |
WOLA |
order line activity code |
order line activity description |
OTY |
Order Type |
WOTY |
order type code |
order type description |
PAY |
Pay Type |
WPAY |
pay type code |
pay type description |
PDA |
Profile Data |
WPFL |
profile data code |
profile data description |
POR |
Price Override Reason Code |
WPOR |
price override reason code |
price override reason description |
PRM |
Promotion |
WPRO |
promotion code |
promotion description |
PRP |
Prep Code |
WPRC |
prep code |
prep code description |
RCY |
Recency |
WRCC |
recency code |
recency description |
RTR |
Return Reason |
WRTR |
return reason code |
return reason description |
SCG |
Source Category |
WSCT |
source category code |
source category description |
SEA |
Season |
WSEA |
season code |
season description |
SEO |
SKU Element 1 |
WSK1 |
SKU element 1 code |
SKU element 1 description |
SET |
SKU Element 3 |
WSK3 |
SKU element 3 code |
SKU element 3 description |
SEW |
SKU Element 2 |
WSK2 |
SKU element 2 code |
SKU element 2 description |
SLC |
Soldout Control |
WSLD |
soldout control code |
soldout control description |
SLS |
Salesman |
WSLS |
salesman code |
salesman description |
TRC |
Item Transaction Code |
WITC |
item transaction code |
item transaction description |
TRR |
Item Transaction Reason Code |
WIT1 |
item transaction reason code |
item transaction reason description |
VIA |
Ship Via |
WVIA |
ship via code |
ship via description |
When you perform certain CWDirect activities, the system creates a record in the DW Capture Transaction file to download to the data warehouse. Each record in the DW Capture Transaction file contains a File code, indicating the CWDirect file(s) affected by the transaction, the key fields necessary to retrieve transaction details, and which DW Staging file to update. See CWDirect activities to view the types of activities that create a DW capture transaction record.
Note: Changes from upddta and other direct file updates are not captured in the DW Capture Transaction file.
You can view the records in the DW Capture Transaction file at the Work with Data Warehouse Transactions Screen. The system downloads the records in the DW Capture Transaction file to the DW Staging files when you submit an initial or incremental load.
Field |
Description |
Company |
The company where the CWDirect activity occurred. |
Sequence # |
A unique number assigned to each DW capture transaction record. |
File name |
A code identifying the CWDirect file containing records you wish to download to the data warehouse. This code also indicates which DW Staging file to update when you process an initial or incremental load. Example: If you create a catalog request, the system creates a DW capture transaction record with a File name of CRQ, indicating a change has been made to the Catalog Request file and this record should update the DW Catalog Request file. The File key field identifies the particular CWDirect company and data, in this example, catalog request record, that has been updated. See What key data is captured for each DW capture transaction record? for an understanding of the file code assigned to each DW capture transaction record, based on the CWDirect activity. |
File key |
A code used to identify the CWDirect company and data that has been updated. The File name field identifies the CWDirect file where the updated data is located and the DW Staging file to update when you process an initial or incremental load. The File key field is then used to identify the record in the file that has been updated. Example: If the File name is CRQ (catalog request), the File key indicates the CWDirect company and catalog request record that has been updated. See What key data is captured for each DW capture transaction record? for an understanding of the file code assigned to each DW capture transaction record, based on the CWDirect activity. |
Capture date |
The date the CWDirect activity occurred which created the DW capture transaction record. |
Capture time |
The time the CWDirect activity occurred which created the DW capture transaction record. |
Capture type |
A code that indicates whether the CWDirect activity represents creation of a new record, a change to an existing record, or a delete of an existing record. Valid values are: • A: a new record was added. • C: an existing record was updated. • D: an existing record was deleted. The system creates a delete DW capture transaction record only when you delete a sold to customer or a bill to customer. When you send a delete transaction to the data warehouse, the system deletes the customer record and also deletes associated records that apply to the customer. If the deleted customer was merged into a target customer, the system updates the associated records that apply to the target customer with the information that was defined for the deleted customer. |
Original process date |
The date the DW capture transaction record was originally processed, creating the record in the related DW Staging file. |
Original process time |
The time the DW capture transaction record was originally processed, creating the record in the related DW Staging file. |
Last process date |
The date the DW capture transaction record was last downloaded to the DW Staging file. |
Last process time |
The time the DW capture transaction record was last downloaded to the DW Staging file. |
Captured by |
The CWDirect program that created the DW capture transaction record. |
CWDirect activities captured in the DW Capture Transaction File
This table indicates:
• which CWDirect activities create a DW capture transaction record.
• which CWDirect files the DW capture transaction record references.
• what file code is assigned to the DW capture transaction record, based on the CWDirect file(s) referenced.
See What key data is captured for each DW capture transaction record? to review a table displaying the key data captured for each DW capture transaction record and the DW Staging file updated, based on the file code assigned to the DW capture transaction record.
RDC activity: |
Menu option: |
creates a DW transaction record which refers to RDC file: |
File code: |
create an A/R payment post an A/R refund |
WCRT |
A/R Payment Detail |
APD |
create an A/R open item |
WCRT |
A/R Open Item |
ARI |
enter orders perform batch affinity update perform merge/purge |
OEOM MBAU MMCS |
Customer Affinity |
CAF |
update a sold to or bill to customer delete a sold to or bill to customer |
WCST WCBT OEOM |
Customer Bill To Customer Bill To Extended Customer Bill To Phone Number |
CBT |
create or update a customer sold to email address |
WCST OEOM WCAT |
Customer Sold To Email |
CEM |
enter or bill orders if you track customer history by entity |
OEOM MCON |
Customer Ship To Entity |
CHE |
enter orders if you specify individuals update individuals associated with sold to customers |
WCST OEOM |
Customer Individual |
CIF |
update customer profile data in Work with Customers or while entering orders |
WCST OEOM |
Customer Profile |
CPL |
enter or change catalog requests |
WCAT OEOM |
Catalog Request |
CRQ |
enter or bill orders if you track customer history by entity |
OEOM MCON |
Customer Sold To Entity |
CSE |
create or update ship to customers enter or bill orders |
WCST OEOM MCON |
Customer Ship To Customer Ship To Extended Customer Ship To Order History Customer Ship To Phone Number |
CSH |
create or update a customer membership |
WWCM OEOM |
Customer Membership |
CSM |
create, update, or delete sold to customers enter or bill orders perform merge/purge |
WCST OEOM MCON MMCS |
Customer Sold To Customer Sold To Extended Customer Sold To Order History Customer Sold To Phone Number |
CST |
create or update customer warranty information enter orders |
WCST OEOM |
Customer Warranty |
CWT |
create or update customer ownership information |
WCST |
Customer Ownership |
CSO |
assign an item to an ecommerce category |
MITM |
Ecommerce Item Categories |
EIC |
assign attributes and values to items |
MITM |
Item Attribute Assignment |
IAA |
perform any inventory transaction |
PORC WITI WITB WVCB MPIR MCON |
Item Transaction History |
ITH |
create or update an item |
MITM MUSP |
Item |
ITM |
confirm shipments using PC manifest or manually confirm by pick control number |
MCON |
Manifest Upload Audit |
MUA |
create or update offers enter orders (for date of first offer) |
WOFR |
Offer |
OFR |
enter or bill orders add or update the order email address |
OEOM MCON |
Order Additional Charge |
OHD |
Order Payment Method |
|||
Order Detail Order Line History |
|||
Order Header Order Header Extended |
|||
Invoice Payment Method |
|||
Order Ship To |
|||
create or update item/offers, SKU/offers, item prices, or SKU prices |
MITM MISO MUSO MUSP |
SKU Offer |
SKO |
create or update SKU level information |
MITM MUSP |
SKU |
SKU |
create or update source codes enter orders |
WSRC OEOM |
Source |
SRC |
create a SKU cross reference |
MITM |
SKU Cross Reference |
SXR |
assign UPC codes to items |
MITM |
SKU UPC |
UPC |
create or update vendors |
WVEN |
Vendor Vendor Extended |
VND |
enter, update, or receive purchase orders enter or process a vendor charge back |
MPOE PORC WVCB |
A/P Invoice Detail History PO Detail PO Detail Estimated Charge PO Header PO Receipt Vendor Charge Back Detail |
What key data is captured for each DW capture transaction record?
This table indicates:
• the key data that is captured in the File key field, based on the CWDirect file(s) referenced in the File code field for the DW capture transaction record.
• the DW Staging file updated, based on the File code defined for the DW capture transaction record.
File code: |
Refers to RDC file(s): |
File key: |
DW Staging file updated: |
APD |
A/R Payment Detail |
111222222233333, where: 111 is the company code 2222222 is the A/R open item number 33333 is the A/R payment detail sequence number |
DW A/R Payment Detail |
ARI |
A/R Open Item |
1112222222, where: 111 is the company code 2222222 is the A/R open item number |
DW A/R Open Item |
CAF |
Customer Affinity |
1112222222223, where: 111 is the company code 222222222 is the sold to customer number 3 is the affinity type |
DW Customer Affinity |
CBT |
Customer Bill To Customer Bill To Extended Customer Bill To Phone Number |
1112222222, where: 111 is the company code 2222222 is the customer bill to number |
DW Customer Bill To |
CEM |
Customer Sold To Email |
111222222222333, where: 111 is the company code 222222222 is the sold to customer number 333 is the customer email sequence number |
DW Customer Sold To Email DW Correspondence History |
CHE |
Customer Ship To Entity |
111222222222333444, where 111 is the company code 222222222 is the sold to customer number 333 is the ship to number 444 is the entity number |
DW Customer Ship To Ent |
CIF |
Customer Individual |
111222222222333, where: 111 is the company code 222222222 is the sold to customer number 333 is the customer individual number |
DW Customer Individual |
CPL |
Customer Profile |
111222222222333, where: 111 is the company code 222222222 is the sold to customer number 333 is the profile code |
DW Customer Profile |
CRQ |
Catalog Request |
1112222222223333333444444, where: 111 is the company code 222222222 is the customer number 3333333 is the request date 444444 is the request time |
DW Catalog Request |
CSE |
Customer Sold To Entity |
111222222222333, where: 111 is the company code 222222222 is the sold to customer number 333 is the entity number |
DW Customer Sold To Ent |
CSH |
Customer Ship To Customer Ship To Extended Customer Ship To Order History Customer Ship To Phone Number Customer Shipment History |
111222222222333, where: 111 is the company code 222222222 is the sold to customer number 333 is the ship to number |
DW Customer Ship To |
CSM |
Customer Membership |
1112222222223333333333444, where: 111 is the company code 222222222 is the customer number 333333333333 is the membership ID 444 is the membership sequence number |
DW Customer Membership |
CSO |
Customer Ownership |
1112222222223333333333, where: 111 is the company code 222222222 is the customer number 3333333333 is the ownership ID |
DW Customer Ownership |
CST |
Customer Sold To Customer Sold To Extended Customer Sold To Order History Customer Sold To Phone Number Customer Shipment History |
111222222222, where: 111 is the company code 222222222 is the sold to customer number |
DW Customer Sold To |
CWT |
Customer Warranty |
111222222222333, where: 111 is the company code 222222222 is the sold to customer number 333 is the customer warranty sequence number |
DW Customer Warranty |
EIC |
Ecommerce Item Categories |
1112222222222223333, where: 111 is the company code 222222222222 is the item number 3333 is the ecommerce item category |
DW Ecomm Item Category |
IAA |
Item Attribute Assignment |
111222222333333444444444444, where: 111 is the company code 222222 is the attribute type 333333 is the attribute value 444444444444 is the item number |
DW Item Attribute Assign |
ITH |
Item Transaction History |
111222222222222333333333333334445555555666666777, where: 111 is the company code 222222222222 is the item number 33333333333333 is the SKU code 444 is the warehouse code 5555555 is the transaction date 666666 is the transaction time 777 is the transaction sequence number |
DW Item Trans Activity |
ITM |
Item |
111222222222222, where: 111 is the company code 222222222222 is the item number |
DW Item |
MUA |
Manifest Upload Audit |
111222222233333334444445566666, where: 111 is the company code 2222222 is the pick control number 3333333 is the batch date 444444 is the batch time 55 is the label number 66666 is the manifest upload audit sequence number |
DW Manifest Audit |
OFR |
Offer |
111222, where: 111 is the company code 222 is the offer code |
DW Offer |
OHD |
Order Additional Charge |
11122222222, where: 111 is the company code 22222222 is the order number |
DW Order Additional Chg |
Order Payment Method |
DW Order Payment DW Order Payment Activity |
||
Order Detail Order Line History |
DW Order Detail DW Order Detail Activity |
||
Order Header Order Header Extended |
DW Order Header |
||
Invoice Payment Method |
DW Order Payment Activity |
||
Order Ship To |
DW Order Ship To |
||
SKO |
SKU Offer |
11122222222222233333333333333444, where: 111 is the company code 222222222222 is the item number 33333333333333 is the SKU code 444 is the offer code |
DW SKU Offer |
SKU |
SKU |
111222222222222, where: 111 is the company code 222222222222 is the item number |
DW SKU |
SRC |
Source |
1112222, where: 111 is the company code 2222 is the source code |
DW Source |
SXR |
SKU Cross Reference |
1112222333333333333333333333333333333, where: 111 is the company code 2222 is the SKU cross reference type 333333333333333333333333333333 is the SKU cross reference number. |
DW SKU Cross Reference |
UPC |
Item UPC |
1112222222222223333333333333344455555555555555, where: 111 is the company code 222222222222 is the item number 33333333333333 is the SKU code 444 is the UPC type 55555555555555 is the UPC number |
DW SKU UPC |
VND |
Vendor Vendor Extended |
1112222222, where: 111 is the company code 2222222 is the vendor number |
DW Vendor |
A/P Invoice Detail History PO Detail PO Detail Estimated Charge PO Header PO Receipt Vendor Charge Back Detail |
DW Vendor History Detail |
Downloading Data to the Tables in the cwi_staging Database on the CWData Server
The CWDirect data remains in the DW Staging files until an end-of-file record exists in the DW Common Reference file.
The end-of-file record in the DW Common Reference file indicates that the DW Staging files are updated and ready for transferal to tables in the cwi_staging database on the CWData server.
DW Common Reference File: end-of-file record |
|
Field |
Value |
Company# |
0 |
File |
XXX |
Key |
END OF FILE |
Description |
STAGING PROCESS COMPLETE |
The system creates an end-of-file record at the end of an initial load or incremental load, once all of the records in the DW Staging files have been processed.
The RELIC job on the CWData server looks for the end-of-file record in the DW Common Reference file. Once the RELIC job finds the end-of-file record, the job downloads the records in the DW Staging files to the tables in the cwi_staging database on the CWData server.
After RELIC has transferred the staging data to the CWData server, it clears each of the DW Staging files, with the exception of the DW Transaction file.
For more information: See the CWData user reference for more information on downloading and viewing data in the cwi_warehouse database.
Working with Data Warehouse Transactions (WDWT)
Purpose: Use this menu option to review and download data to the CWData data warehouse staging files. This option allows you to:
• submit the initial data warehouse extract and load
• submit an update of new information for extract and download
• resubmit data warehouse transactions for a specified date range and optionally, file
• review and work with the transactions in the DW Transaction file that are ready for processing
• optionally, clear or copy the data warehouse (DW) staging files
The DW_ASYNC builds records in each related DW Staging file based on the key in the DW Capture Transaction File. You can start and stop this async at the Work with Data Warehouse Transactions Screen.
When active, the DW_ASYNC looks for transactions in the DW Capture Transaction file to download to the DW Staging files in 60 second intervals. The async processes all records in the DW Capture Transaction file with a blank Original process date and Original process time that have a capture date and time that is less than the date and time when the async woke up. Any new transactions that are written to the DW Capture Transaction file are processed the next time the async wakes up.
When to use: If you run the Process DW Changes periodic function or submit an incremental load once a day or once a week, you may wish to use the DW_ASYNC to download transactions to the DW Staging tables on a more regular basis, though using the DW_ASYNC is not required for data warehouse processing. If you run the Process DW changes periodic function more then once a day, you do not need to run the DW_ASYNC.
To transfer the data to the CWData server: To transfer the data in the DW Staging files to the CWData server, a trigger record must exist in the DW Common Reference file. The trigger record signals that the completed staging data is ready to be transferred to the CWData server. To create the trigger record, you must run the Process DW Changes periodic function or press F7 to submit an incremental load at the Work with Data Warehouse Transactions Screen.
Note: When you run the Process DW Changes periodic function or submit an incremental load, the system ends the DW_ASYNC if it is running.
Work with Data Warehouse Transactions Screen
Use this screen to review and work with transactions in the DW Transaction file that are ready for processing.
How to display this screen: Enter WDWT in the Fast path field or select Work with Data Warehouse Transactions from a menu.
DWR0002 DISPLAY Work with Data Warehouse Transactions 10/17/02 11:55:06 KAB Co. Async Staging Inactive
---- Capture ---- -- Orig Process - -- Last Process - Opt File Date Time Date Time Date Time From A/C
Type options, press Enter. 4=Delete
CST 10/16/02 10:12:10 0:00:00 0:00:00 OER0553 Key: 555000000006 C OHD 10/16/02 10:12:10 0:00:00 0:00:00 OER0553 Key: 5550000627600000000 A CAF 10/16/02 10:12:10 0:00:00 0:00:00 CSR1135 Key: 555000000006A A1 C SRC 10/16/02 10:12:10 0:00:00 0:00:00 OER0553 Key: 5552002 C +
F3=Exit F7=Proc changes F8=Reproc changes F9=Sbm initial load F12=Cancel F14=Clear staging files F15=Copy staging F16=Start async F17=End async |
Field |
Description |
File |
This code indicates both the file where the change takes place and the data warehouse staging file to be updated when you process changes. For example, a code of CST indicates a new or changed record in the Customer Sold To file; when you process changes, CWDirect creates a record in the DW Customer Sold To file (or changes an existing record, if an additional transaction takes place before you have process changes). Not all changes and activity are tracked through the DW Transaction file. For example, a change to your company address is tracked in the DW Company file. See CWDirect activities captured in the DW Capture Transaction File for more information on the CWDirect activities that create a DW capture transaction record and the file code assigned to the DW capture transaction record, based on the CWDirect file(s) referenced. Alphanumeric, 3 positions; optional. |
Capture date |
The date that the activity took place, creating the record in the DW Transaction file. Numeric, 6 positions (MMDDYY); optional. |
Capture time |
The time that the activity took place, creating the record in the DW Transaction file. Numeric, 6 positions (HHMMSS); display-only. |
Original process date |
The date when the DW Transaction record was processed, creating the record in the related DW Staging file; for example, this is the date that a CST record was processed, creating a record in the DW Customer Sold To file. Numeric, 6 positions (MMDDYY); display-only. |
Original process time |
The time that the DW Transaction record was processed, creating the record in the related DW Staging file; for example, this is the date that a CST record was processed, creating a record in the DW Customer Sold To file. Numeric, 6 positions (HHMMSS); display-only. |
Last process date |
This date is the same as the original process date. Numeric, 6 positions (MMDDYY); display-only. |
Last process time |
This time is the same as the original process time. Numeric, 6 positions (HHMMSS); display-only. |
From |
The program that processed the transaction or activity creating the DW Transaction record; for example, if you change a customer at the Change Customer Sold To screen, the program ID identifying that screen (CSR0078) appears here. Alphanumeric, 10 positions; display-only. |
A/C (Add/Change) |
This flag indicates whether the activity represented creation of a new record, a change to an existing record, or a delete to an existing record. Valid values are: A (Add) = A new record was added. C (Change) = An existing record was modified. D (Delete) = An existing record was deleted. Note: Deletions are only captured for customer sold to records and customer bill to records. Alphanumeric, 1 position; display-only. |
Key |
The key field information to uniquely identify the updated record. For example, if you change a Customer Sold To, the key might be 027000000317, where 027 indicates the company, and 000000317 indicates the customer number. Numeric fields are right-justified and zero-filled. See What key data is captured for each DW capture transaction record? for more information on the key data captured in the File key field, based on the CWDirect file(s) referenced in the File code field for the DW capture transaction record and the DW Staging file updated. Alphanumeric, 80 positions; display-only. |
Screen Option |
Procedure |
Delete a transaction |
Enter 4 next to a transaction to delete it from the DW Transaction file. |
Process changes |
Press F7 to display the Process Data Warehouse Changes Window. |
Reprocess changes for a specified date range |
Press F8 to display the Reprocess Data Warehouse Changes Window. |
Submit the initial load of base information to the data warehouse |
Press F9 to advance to the Process Initial Load Screen. Note: The Access Initial Data Warehouse Load (B04) secured feature controls access to this screen. |
Clear the Data Warehouse staging files |
Press F14 to display the Clear Staging Files Window. |
Copy the Data Warehouse staging files |
Press F15 to display the Copy Staging Files Window. |
Start the DW_ASYNC job |
Press F16. A message displays at the top of the screen indicating the Async is active: Async Staging Active. See Data Warehouse Async. |
End the DW_ASYNC job |
Press F17. A message displays at the top of the screen indicating the Async is inactive: Async Staging Inactive. See Data Warehouse Async. |
Purpose: Use this screen to process the initial load of key CWDirect information to the data warehouse. Normally, you would process the initial load in stages; see Submitting an Initial Load.
Note: The Access Initial Data Warehouse Load (B04) secured feature controls access to this screen.
How to display this screen: Press F9 at the Work with Data Warehouse Transactions Screen.
DWR0035 ENTER Process Initial Load 7/26/00 17:14:00 EZK Mail Order
Clear previously staged data . . N (Y/N)
Process all files . . . . . . . . Y (Y/N) {other than customers and orders}
Starting with customer # <-- leave blank if not loading customers
Ending with customer # . <-- leave blank if not loading customers
Starting with order date . . <-- leave blank if not loading orders
Ending with order date . . . <-- leave blank if not loading orders
F3=Exit F9=Submit F12=Cancel |
Field |
Description |
Clear previously staged data |
This field indicates whether to clear the DW Staging files as part of the initial load. Normally, you would not need to clear this information, as the RELIC program script clears these files after transferring the staging data to the CWData server; however, you might choose to clear data created through testing. Valid values are: Y = Clear the data warehouse staging files. N (default) = Do not clear the staging files. Alphanumeric, 1 position; required. |
Process all files |
This field indicates whether to download all supporting files which are not related to customers or orders. These files include reference information such as vendors, codes, financial and business structures, and the company itself. Normally, you would download these files before downloading customer and order information. Valid values are: Y (default)= Download all supporting files not related to customers or orders. N = Do not download the supporting files. Alphanumeric, 1 position; required. |
Starting with customer # |
Use this field to indicate the first customer sold to number to include in the download. By specifying the first and last customer numbers to download, you can control the total number of records downloaded at one time and better allocate system resources. For example, if you have 5 million customer records, you can download these records in stages by first specifying the range of 1 to 1,000,000, then the range of 1,000,000 to 2,000,000, and so on. Normally, you would begin downloading customers after you have downloaded the supporting files, but before you begin the order download. Note: The system does not validate that the starting and ending numbers are valid (that is, currently assigned to a customer sold to). Numeric, 9 positions; optional. |
Ending with customer # |
Use this field together with the Starting with customer # field to specify the range of customers to download at this time. Numeric, 9 positions; required if you specify a starting #. |
Starting with order date |
Use this field to download order-related files by indicating the first order date to include in the download. By specifying the first and last order dates to download, you can control the total number of records downloaded at one time and better allocate system resources. For example, if you have 10 million orders, you can download these records in stages by first specifying a range of dates to include 2 million orders, then the next range of dates to include the next 2 million, and so on. Normally, you would begin downloading order-related information after the supporting and customer file downloads are complete. Note: The system does not validate that you have orders taken on the starting and ending dates. Numeric, 6 positions (MMDDYY format); optional. |
Ending with order date |
Use this field together with the Starting with order date field to specific the range of order-related information to download at this time. Numeric, 6 positions (MMDDYY format); required if you enter a starting order date. |
Screen Option |
Procedure |
Submit the initial load |
Press F9 to submit the PRC_DWLOAD job, which populates the related DW Staging files. See Submitting an Initial Load. |
Process Data Warehouse Changes Window
Purpose: Use this window to:
• build the supporting DW Staging file records.
• populate the appropriate DW Staging files using the key information in the DW Transaction file.
• purge DW Transaction records based on the Data Warehouse Transaction Purge Days (H10) system control value.
• trigger the transfer of the DW staging files to the CWData server.
You can also use the Process Data Warehouse Changes periodic function (program name DWR0068) to process data warehouse changes.
How to display this window: Press F7 at the Work with Data Warehouse Transactions Screen.
Process Data Warehouse Changes
Scheduled submit . . . N (Y/N) Process Time . . 0:00:00 (HH:MM:SS) Process Date . . . 72600
F12=Cancel |
Field |
Description |
Scheduled submit |
This field indicates whether to schedule the change processing for a later time, or submit the job immediately. Valid values are: Y = Schedule the job for a later time; you will need to complete the Process time and Process date fields N (default) = Submit the job immediately Alphanumeric, 1 position; required. |
Process time |
The time to submit the job, if you select to schedule the job for a later time. Numeric, 6 positions (HH:MM:SS format); required if you select to schedule the job. |
Process date |
The date to submit the job, if you select to schedule the job for a later time. The current date defaults. Numeric, 6 positions (MMDDYY format); required if you select to schedule the job. |
Completing this window: Press Enter to submit the Process DW Changes (PRC_DWCHGS) job. See Submitting an Incremental Load.
Reprocess Data Warehouse Changes Window
Purpose: Use this window to reprocess all data warehouse transactions for a specified date range and optionally, file.
How to display this screen: Press F8 at the Work with Data Warehouse Transactions Screen.
Reprocess Data Warehouse Changes
This function will reprocess all data warehouse transactions for a given date and file (optional). The date range date entered below will be used to clear the original process date so records will be resubmitted on the next scheduled staging process.
From date To date
File name . . . . . . . . . .
(leave blank for all files)
F9=Submit F12=Cancel |
To reprocess: Press F9 to reprocess all data warehouse transactions that meet the criteria you defined.
Field |
Description |
From date |
The starting date CWDirect uses to clear the Original processed date field so that the records can be resubmitted on the next scheduled staging process. All records whose Original processed date was equal to or greater than the From date and less than or equal to the To date are reprocessed. Numeric, 6 positions; required. |
To date |
The ending date CWDirect uses to clear the Original processed date field so that the records can be resubmitted on the next scheduled staging process. All records whose Original processed date was equal to or greater than the From date and less than or equal to the To date are reprocessed. Numeric, 6 positions; required. |
File name |
The name of the file containing records you wish to reprocess. Leave this field blank if you want to reprocess records based on date range alone. Valid values are: ACP = Accounting Period ADD = Additional Charge ADR = Add Reason Code APD = A/R Payment Detail APT = Terms ARI = A/R Open Item ART = A/R Type BNK = Bank BUY = Buyer CAF = Customer Affinity CBT = Customer Bill To CCL = Customer Class CCM = Customer Company CEM = Customer Sold To Email CHE = Customer Ship To Ent CHH = Correspondence History CIF = Customer Individual |
|
CMP = Company CNR = Cancel Reason CNT = Country CPL = Customer Profile CRQ = Catalog Request CSE = Customer Sold To CSH = Customer Ship To CSM = Customer Membership CSO = Customer Ownership CST = Customer Sold To CWT = Customer Warranty DIV = Division DRN = Dispute Reason ECA = E-Commerce Catalog EIC = E-Commerce It ENT = Entity EXR = Exchange Reason GZF = Geographic Zone HAZ = Hazard IAA = Item Attribute |
|
IAT = Item Attribute IAV = Item Attribute ICC = Item Cycle ICL = Item Class IOF = Item Offer IST = Item Status ITC = Item Category ITH = Item Transaction History ITM = Item ITW = Item Warehouse LCL = Location Class LDV = Long SKU Division LNG = Language LOB = Line of Business LSC = Long SKU Class LSD = Long SKU Description LST = List Source MCC = Mail/Call Code MUA = Manifest Upload ODT = Order Detail |
|
OFR = Offer OHD = Order Header OPM = Order Payment Method OST = Order Ship To OTY = Order Type PAY = Pay Type PDA = Profile Data PDT = PO Detail POH = PO Header POR = Price Override PRD = RI Price Change Detail PRH = RI Price Change Header PRM = Promotion PRP = Prep Code RCY = Recency RTC = Retail Class RTR = Return Reason SCG = Source Category SEA = Season SEO = SKU Element 1 |
|
SET = SKU Element 3 SEW = SKU Element 2 SKO = SKU Offer SKU = SKU SLC = Soldout Control SLS = Salesman SRC = Source SXR = SKU Cross Reference TRC = Item Transaction TRR = Item Transaction UPC = Item (SKU) UPC USR = User VIA = Ship Via VIT = Vendor Item VND = Vendor WHS = Warehouse Alphanumeric, 3 positions; optional. |
Purpose: Use this window to clear the DW Staging files for your company. Normally, these files are cleared automatically during processing; however, you might use this option to clear test data.
How to display this window: Press F14 at the Work with Data Warehouse Transactions Screen.
Clear Staging Files
Enter library . . . . . . . . CWM42QDTA
F9=Submit F12=Cancel |
Completing this window: Indicate the library where the staging files are located and press F9 to submit the job CLR_DWTBLS. This job clears each of the Data Warehouse staging files with the exception of the DW Transaction file. Only the records for your company are cleared; as a result, any language records (with a file code of LNG) in the DW Common Reference file are not cleared, because the Language file does not include a company code.
Purpose: Use this window to copy the DW Staging files to a different library. You might use this option to copy data during testing.
How to display this window: Press F15 at the Work with Data Warehouse Transactions Screen.
Copy Staging Files
From library . . . . CWM42QDTA
To library . . . . .
F9=Submit F12=Cancel |
Step-by-step instructions:
1. Optionally, enter the name of the source library; the library specified in the Data Base Library (A37) system control value, which is where the system writes the DW Staging files, defaults.
2. Enter the name of the destination library.
3. Press F9. The system submits the job CPY_DWTBLS, which copies the Data Warehouse staging files to the destination library.
Chapter 96: CWData Overview | Contents | SCVs | Search | Glossary | Reports | XML | Index | Data Warehouse Staging Errors Report |
SO11_02 CWDirect 18.0 August 2015 OTN