The file's text, extracted by GovTribe without its formatting.
ATTACHMENT 3 – LEGACY DATA MODEL
Legacy Data Model This information is provided for reference purposes.
The main system of record for all USM data is CW Direct. To begin the life cycle of a purchase we begin with a customer. The main customer table in CW Direct is the ‘Customer Sold To’ table. This table contains basic customer information such as name, account number, address, etc. Once the customer places an order the order information is stored in the ‘Order Header’ table. The Order Header table contains all the general information per order such as order number, order date, order status, etc. Each order placed has detailed information stored in the ‘Order Detail’ table such as item quantity and item amount per item ordered. All general item information is stored in the ‘Item’ table.
Once an order has been placed, it can either be cancelled or fulfilled. Before an order can be fulfilled, however, it must exist in stock. Once an order is shipped to the customer it is considered fulfilled or sold. All product inventory information is stored in the ‘Item Warehouse’ table. Assuming that inventory is sufficient the item can then be shipped. Customer shipping information is stored in ‘Customer Ship To’ table containing the customers shipping address. Because the shipping address and the billing address can be different the billing address is stored in another table, the ‘Customer Bill To’.
1. Customer The customer subcategory groups tables that contain customer information. This information is stored in three separate tables; Customer, Customer Billing, and Customer Shipping. The Customer table contains the individuals who bought a product from the USM. The Customer Billing table contains the customer information of those individuals who were billed. The Customer Shipping table contains information for those customers who were shipped a product from the USM. The customer information is either entered manually into CWDirect or comes from eCommerce. This information is protected at the USM and requires encrypting when sending externally.
The two main uses of the customer data are for Service Source and list pulls. Service Source is a once a week process where any customer who requests a brochure is pulled from the customer table. These lists of customers are sent to a third party vendor to mail. Customer list pulls are done for any USM mass mailing. This includes the catalogs and brochures. The customer table is the only table imported into the data warehouse during the Extract, Transform, and Load (ETL) process.
A snap shot of the relevant fields from the customer table is displayed below.
Customer
Company[footnoteRef:1] [1: CWDirect Order Management System is designed to support multiple retail companies which are part of a business group. Company field in CWDirect tables keeps all transactions tied to the specific retail company. USM has only one company defined in the CWDirect system.]
Warehouse ID
| Customer # |
| CW Direct Customer ID |
| Account # |
| Billing Account Number |
| Email Address |
| Customer Email ID |
| Prefix |
| Customer Name Prefix |
| First Name |
| Customer First Name |
| Middle Name |
| Customer Middle Name |
| Last Name |
| Customer Last Name |
| Street Address |
| Customer Address Line 1 |
| Apartment |
| Customer Apartment # |
| Address Line 2 |
| Customer Address Line 2 |
| Zip Code |
| Customer Zip Code |
| Suffix |
| Customer Name Suffix |
2. Order The order subcategory groups all of the tables that deal with the purchasing of products. This subcategory contains the order information, the order status, where the order was shipped and the order billing history. The primary table in this group is the Order Header. This contains the basic information of the order including the order number, order date, customer and order status. The detail of the order is contained in the Order Detail Table. This includes the order number, status, quantities, price and customer. This subcategory also contains the Ship To table which is used to track the shipping information of the items including the Customer Shipping.
There are also two sets of historical data that are tracked. The first is the Order Billing History which tracks the order detail from a billing perspective. The second data set is the Order History which tracks whether the item was shipped, returned, exchanged, sold out or placed on hold. Finally, there is the Source table which contains which source the order was purchased. This is used to mainly track sales from catalogs and postcards. The Order Header, Order Detail, and Source are imported to the data warehouse during the ETL process. Information from the Order History table is brought into the data warehouse for the daily sales report. The order subcategory is used to generate most of the reports for IRIS.
A snap shot of the relevant fields from the Order Header, Order Detail, Order Billing History, and Order Line History tables are displayed below.
Order Header
| Order # |
| CW Direct Order ID |
| Order Status |
| Status of Order |
| Order Date |
| Date Order was placed |
| Customer # |
| Customer who purchased Order |
| Order Type |
| Medium through which Order was placed |
| Source Code |
| Unique code if purchased from mailing |
Order Detail
| Order # |
| CW Direct Order ID |
| Ship To |
| References the Shipping Location |
| Sequence # |
| order in which Items were added to order |
| Line Status |
| Status of each line item entry in the order |
| Original Price |
| Original Price of the item |
| Price |
| Current Price of the item |
| Quantity Cancelled |
| Quantity of cancelled items |
| Quantity Ordered |
| Quantity of ordered items |
| Quantity Reserved |
| Quantity of reserved items |
| Quantity Shipped |
| Quantity of shipped items |
| Quantity Sold Out |
| Quantity of sold out items |
| Quantity Returned |
| Quantity of returned items |
| Quantity Shipped |
| Quantity of shipped items |
| Quantity Credited |
| Quantity of credited items |
| Item # |
| Product Code of the Item |
| Source Code |
| Unique code if item was ordered from mailing |
Order Billing History
| Transaction Date |
| date of the transaction |
| Customer # |
| CW Direct Customer ID |
| Customer Ship To # |
| CW Direct Customer ID of the receiver of the order |
| Item # |
| Product Code of the item that was shipped |
| Order # |
| CW Direct Order ID |
| $ Order Total |
| Total Amount Ordered |
| Quantity Ordered |
| Total Quantity Ordered |
| $ Cancelled Total |
| Total Amount Cancelled |
| Quantity Cancelled |
| Total Quantity Cancelled |
| $ Exchanged Total |
| Total Amount Exchanged |
| Quantity Exchanged |
| Total Quantity Exchanged |
| $ Sold Out Total |
| Total Amount of Sold Out items |
| Quantity Sold Out |
| Total Quantity of Sold Out items |
| $ Returned Total |
| Total Amount Returned |
| Quantity Returned |
| Total Quantity Returned |
| $ Sold Total |
| Total Amount Sold |
| Quantity Sold Out |
| Total Quantity Sold |
Order Line History
| Order # |
| CW Direct Order ID |
| Ship To # |
| Order Shipping ID |
| Sequence # |
| Order in which items were added to order |
| Quantity |
| Quantity of items involved in transaction |
3. Inventory The inventory subcategory groups all related tables that deal with the inventory of the items. The data from this table in CWDirect is imported from the fulfillment system. This group only contains the current inventory levels and has no historical information. The main use of this group in reporting is to show the current inventory and forecast inventory levels. This information is imported into the data warehouse as part of the ETL process.
A snap shot of the relevant fields from the Item Warehouse, and Item Warehouse History tables are displayed below.
Item Warehouse
| Item # |
| Product Code for item |
| Max Quantity |
| Maximum Quantity |
| Min Quantity |
| Minimum Quantity |
| On Hand Qty |
| On Hand Quantity |
| B/O Qty |
| Back Ordered Quantity |
| Protected Qty |
| Protected Quantity |
| Reserve Qty |
| Reserved Quantity |
Item Warehouse History
| Item # |
| Product Code for Item |
| Dollars Returned |
| Amount Returned |
| Units Returned |
| Quantity Returned |
4. Billing The billing subcategory groups all related tables that deal with billing of the products. This could either be billing a customer or a vendor. This subcategory is a new addition to the reporting team.
5. Product The product subcategory groups all of the tables that deal with an item. The product table contains the basic item information including the code and name. The program is used to categorize the various products (e.g. American Eagle or American Buffalo). Individual items would be children under each program. This is utilized in the Daily Sales report to group the individual items and to provide cumulative totals for the programs.
These subcategories hold true whether the data is from CWDirect or another data source. There are customers who place an order and are billed for the purchased products that must be fulfilled from the current inventory.
A snap shot of the relevant fields from the Item, and Item Class tables are displayed below.
Item
| Description |
| Product Description |
Item Class
| Description |
| Class Description |
6. Data Volume
