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:

• CWDirect Setup

• System Control Values

• Secured Feature

• Menu Option

• Periodic Functions

• Extracting CWDirect Data

• Submitting an Initial Load

• What DW Staging Files are Updated by Each Initial Load Stage?

• Submitting an Incremental Load

• DW Common Reference File

• What data is captured in the DW Common Reference file?

• DW Capture Transaction 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)

• Data Warehouse Async

• Work with Data Warehouse Transactions Screen

• Process Initial Load Screen

• Process Data Warehouse Changes Window

• Reprocess Data Warehouse Changes Window

• Clear Staging Files Window

• Copy Staging Files Window

CWDirect Setup

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:

• System Control Values

• Secured Feature

• Menu Option

• Periodic Functions

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 Values

System Control Value	Description
Use Data Warehouse (G94)	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.
Data Warehouse Transaction Purge Days (H10)	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.
Subtract Hidden Tax from CWData Order Line Price (J15)	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

Secured Feature	Description
Access Initial Data Warehouse Load (B04)	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

Menu Option	Description
Working with Data Warehouse Transactions (WDWT)	Allows you to review and download CWDirect data to the CWData data warehouse.

Periodic Functions

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.

Extracting CWDirect Data

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.

 

Submitting an Initial Load

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.

DW Common Reference File

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

DW Capture Transaction File

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

Data Warehouse Async

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.

Process Initial Load Screen

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.

Clear Staging Files Window

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.

Copy Staging Files Window

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.