Relic downloads data from the CWSerenade DW Staging tables to the tables in the cwi_staging database on the CWData Server.
In this topic:
• CWData Staging (cwi_staging) Tables
For more information: See CWData Overview for an overview and Working with the Data Warehouse Integration in CWSerenade for more information on populating the DW Staging tables in the CWSerenade database.
Relic performs the following steps to move data from the DW Staging tables in the CWSerenade database to the tables in the cwi_staging database.
1. Establishes a connection to the CWSerenade database.
2. Establishes a connection to the cwi_staging database.
3. Looks at the Relic Job List in the Relic.ini file to extract data from the DW Staging tables in the CWSerenade database to the tables in the cwi_staging database.
Each job in the Relic Job List indicates:
• Source table: the name of the DW Staging table in the CWSerenade database where the data to extract is located.
• Destination table: the name of the table in the cwi_staging database where the extracted data is placed.
4. Relic processes the data table by table, keeping a record count for each of the tables defined in the Relic.ini file. Relic extracts the records from the DW Staging table and inserts the data into the corresponding table in the cwi_staging database. As the data in each table is transferred, Relic clears any data that might exist in the table in the cwi_staging database. Note: Relic clears the existing records in the table in the cwi_staging database only if there are records to transfer from the corresponding DW Staging table in the CWSerenade database.
• If the number of rows created in the cwi_staging table matches the number of records extracted from the DW Staging table, Relic considers the download successful and deletes the data from the DW Staging table in the CWSerenade database, with the exception of the DW Transaction table.
• If the number of rows created in the cwi_staging table does not match the number of records extracted from the DW Staging table, Relic considers the download a failure and does not delete the data from the DW Staging table in the CWSerenade database. A discrepancy between the number of records in DW Staging and the number of new rows created in the cwi_staging table indicates that one or more records contain errors; for example, a bogus record existed in tje DW Staging table in the CWSerenade database. Relic tries to process the table one more time. If the table fails a second time, Relic creates an error message in the Relic Log.
5. Once Relic completes the download of data to the tables in the cwi_staging database, Relic starts the cwi_load stored procedure. This program starts the warehouse build to extract, translate, and load the data from the tables in the cwi_staging database to the tables in the cwi_warehouse database.
For more information: See:
• CWData Staging (cwi_staging) Tables for more information on the contents of each table in the cwi_staging database.
• SQL Server Stored Procedures Processing in the CWData Reference Guide for more information on the process used to extract data from the tables in the cwi_staging database to the tables in the cwi_warehouse database.
Purpose: Relic updates the relic.log file with the events that occurred during the extraction process. The system creates a relic.log file for each transfer of data from the DW Staging tables in the CWSerenade database to the tables in the cwi_staging database. Each log file is prefixed with the date Relic was started, for example, 2011-12-14relic.log.
You can use the relic.log file to determine if any errors occurred during the data extract process.
Location of log file: The relic log is located at: C:\Serenade\CWSerenade\CWData\relic\logs, where C: is the root drive where CWSerenade is installed.
Relic processing messages: The relic.log file contains messages such as the following for each extraction process:
• the date and time when the Relic job started: [Thu Nov 14 8:14:49 PST 2011] RDC Extraction and Loading Integration Component started
• the date and time when each job in the relic.ini file is initialized: [Thu Nov 14 08:14:49 PST 2011] Job Number 1 (com.mr.interfaces.relic.AROpenItem) initialized
• the date and time when Relic connected with the CWSerenade database where the DW Staging tables are located: [Thu Nov 14 08:41:34: PST 2002] Opening connection to: jdbc:sqlserver://QASQL08:1433;DatabaseName=CWDirectQA35;selectMethod=direct
• the date and time when Relic counted the number of records in each DW Staging table: [Thu Nov 14 08:41:41 PST 2002] (Job 3) Row count on source database, table CWI.DWARTY is: 24
• the date and time when Relic connected with the CWData server where the cwi_staging database is located: [Thu Nov 14 08:41:45 PST 2002] Opening connection to: jdbc:sqlserver://PRODSQL08:1433;DatabaseName=cwi_staging;selectMethod=direct
• the date and time when Relic extracted data from the DW Staging tables and created rows in the cwi_staging tables: [Thu Nov 14 08:42:45 PST 2002] Starting jobs...(Job 3) Started. (Job 3) Finished, processing 24 of 24 rows for table: AR_TYPE.
• the date and time when the Relic extraction process completed: [Thu Nov 14 08:43:02 PST 2002] Completed jobs. Elasped time: 48:172 seconds.
• the date and time when the SQL Server Stored Procedures Process starts and completes extracting the data from the tables in the cwi_staging database to the cwi_warehouse database: [Wed Dec 14 03:03:24 EST 2011] Opening connection to: jdbc:sqlserver://PRODSQL08:1433;DatabaseName=cwi_warehouse;selectMethod=direct
[Wed Dec 14 03:03:24 EST 2011] Start command (cwi_build_warehouse)
[Wed Dec 14 03:04:29 EST 2011] End command (cwi_build_warehouse)
CWData Staging (cwi_staging) Tables
This table describes the data contained in each table in the cwi_staging database.
cwi_staging table: |
contains: |
The following tables are populated with data from CWDirect or CWSerenade: |
|
accounting_period |
Data from DW Accounting Period (DWACTP). |
ar_open_item |
Data from DW A/R Open Item (DWOITM). |
ar_payment_detail |
Data from DW A/R Payment Detail (DWPAY). |
ar_type |
Data from DW A/R Type (DWARTY). |
common_reference |
Data from DW Common Reference (DWCREF). |
company |
Data from DW Company (DWCOMP). |
correspondence_history |
Data from DW Correspondence History (DWCSEM). |
customer |
Data from DW Customer Sold To (DWCSSL). |
customer_affinity |
Data from DW Customer Affinity (DWAFFY). |
customer_billto |
Data from DW Customer Bill To (DWCBIL). |
customer_catalog_request |
Data from DW Catalog Request (DWCREQ). |
customer_email |
Data from DW Customer Sold To Email (DWCOHS). |
customer_entity |
Data from DW Customer Sold To Ent (DWCSEP). |
customer_individual |
Data from DW Customer Individual (DWCIFP). |
customer_membership |
Data from DW Customer Membership (DWCSMP). |
customer_ownership |
Data from DW Customer Ownership (DWCSOW). |
customer_profile |
Data from DW Customer Profile (DWCPRO). |
customer_shipto |
Data from DW Customer Ship To (DWSHIP). |
customer_shipto_entity |
Data from DW Customer Ship To Ent (DWCHEP). |
customer_warranty |
Data from DW Customer Warranty (DWWTRP). |
division |
Data from DW Division (DWDIV). |
entity |
Data from DW Entity (DWENTY). |
geography |
Data from DW Geography (DWGEOG). |
item |
Data from DW Item (DWITEM). |
item_attribute_assignment |
Data from DW Item Attribute Assign (DWATRA). |
item_ecommerce_category |
Data from DW E-Comm Item Category (DWICAT). |
item_offer |
Data from DW SKU Offer (DWSKOF). |
item_transaction_activity |
Data from DW Item Trans Activity (DWITHS). |
manifest_audit |
Data from DW Manifest Audit (DWMUAU). |
offer |
Data from DW Offer (DWOFFR). |
order_additional_charge |
Data from DW Order Additional Chg (DWOADC). |
order_detail |
Data from DW Order Detail (DWORDT). |
order_detail_activity |
Data from DW Order Detail Activity (DWOLHS). |
order_header |
Data from DW Order Header (DWORDR). |
order_payment |
Data from DW Order Payment (DWPAYM). |
order_payment_activity |
Data from DW Order Payment Activity (DWPAYA). |
order_shipto |
Data from DW Order Ship To (DWORST). |
sku |
Data from DW SKU (DWSKU). |
sku_cross_reference |
Data from DW SKU Cross Reference (DWSXR). |
sku_offer |
Data from DW SKU Offer (DWSKOF). |
sku_upc |
Data from DW SKU UPC (DWSUPC). |
source |
Data from DW Source (DWSRC). |
terms |
Data from DW Terms (DWTERM). |
vendor |
Data from DW Vendor (DWVEND). |
vendor_activity |
Data from DW Vendor History Detail (DWVHDT). |
warehouse |
Data from DW Warehouse (DWWRHS). |
The following tables are populated with data from other tables in the cwi_staging database: Note: To improve performance, by default, CWData does not populate the summary tables automatically. In order to populate the summary tables, you must update the active_flag in the CWI_BUILD table for each row whose build_type is S (summary) to Y. Typically, instead of using the summary tables to analyze data, you would use the CWAnalytics FACTS table and associated dimensions in conjunction with an analysis tool, such as Microsoft Data Analyzer, to query the data. See the CWAnalytics User Reference for more information on installing and using CWAnalytics. |
|
add_reason_fact |
Summary table created from data in the order_header, company, order_shipto, order_detail, and order_detail_activity tables in the cwi_staging database. |
backorder_tracking_fact |
Summary table created from data in the order_header, company, order_shipto, order_detail, order_detail_activity, and customer tables in the cwi_staging database. |
business_line_activity |
Activity table created from data in the company, order_header, order_shipto, order_detail, and order_detail_activity tables in the cwi_staging database. |
c_entity_activity |
Activity table created from data in the company, order_header, order_shipto, order_detail, and order_detail_activity tables in the cwi_staging database. |
c_entity_itemclass_activity |
Activity table created from data in the company, order_header, order_shipto, order_detail, and order_detail_activity tables in the cwi_staging database. |
c_entity_longsku_activity |
Activity table created from data in the company, order_header, order_shipto, order_detail, and order_detail_activity tables in the cwi_staging database. |
cancel_reason_fact |
Summary table created from data in the order_header, company, order_shipto, order_detail, and order_detail_activity tables in the cwi_staging database. |
cs_entity_activity |
Activity table created from data in the company, order_header, order_shipto, order_detail, and order_detail_activity tables in the cwi_staging database. |
cs_entity_itemclass_activity |
Activity table created from data in the company, order_header, order_shipto, order_detail, and order_detail_activity tables in the cwi_staging database. |
cs_entity_longsku_activity |
Activity table created from data in the company, order_header, order_shipto, order_detail, and order_detail_activity tables in the cwi_staging database. |
customer_class_activity |
Activity table created from data in the company, customer, order_header, order_shipto, order_detail, and order_detail_activity tables in the cwi_staging database. |
customer_purchasing_fact |
Summary table created from data in the order_header, company, order_shipto, order_detail, order_detail_activity, and customer tables in the cwi_staging database. |
entity_performance_fact |
Summary table created from data in the order_header, company, order_shipto, order_detail, order_detail_activity, and customer tables in the cwi_staging database. |
exchange_reason_fact |
Summary table created from data in the order_header, company, order_shipto, order_detail, and order_detail_activity tables in the cwi_staging database. |
inventory_tracking_fact |
Summary table created from data in the purchase_history and vendor tables in the cwi_staging database. |
offer_performance_fact |
Summary table created from data in the order_header, company, order_shipto, order_detail, order_detail_activity, and customer tables in the cwi_staging database. |
order_type_activity |
Activity table created from data in the company, order_header, order_shipto, order_detail, and order_detail_activity tables in the cwi_staging database. |
payment_type_activity |
Activity table created from data in order_header, order_payment, order_payment_activity, and company tables in the cwi_staging database. |
purchase_history |
History table created from data in the vendor and vendor_activity tables in the cwi_staging database. |
return_reason_fact |
Summary table created from data in the order_header, company, order_shipto, order_detail, and order_detail_activity tables in the cwi_staging database. |
vendor_performance |
Performance table created from data in the vendor_activity table in the cwi_staging database. |
vendor_performance_fact |
Summary table created from data in the vendor_performance and vendor tables in the cwi_staging database. |
