ETL Process for the Transaction Data Import DTS Task
For the latest version of Commerce Server 2007 Help, see the Microsoft Web site.
The Transaction Data Import DTS task imports site transaction data, such as purchase orders and requisition numbers, into the Data Warehouse. Transaction data is created on your Web site when a user adds and removes items from a shopping cart, or makes a purchase. You can run reports to analyze transaction data. The Transaction Data Import DTS task should be run before creating reports that synchronize transaction data.
When you run the Transaction Import DTS task on a Data Warehouse that contains data for multiple Web sites, make sure that classes extended by more than one Web site have uniquely named members if their data types are not the same. If a new member is added to a class in multiple Web sites and the data types of the new members are not the same, a failure might occur or data might be incorrectly converted when the Transaction Import DTS task is run.
The following tables list the columns extracted from the source tables in the <SiteName>_Transactions database, the transformation made, and the tables to which the data is saved in the Data Warehouse.
Source columns from the OrderGroupAddresses table of the <SiteName>_Transactions database |
Transformation |
Target columns from the OrderGroupAddresses table in the Data Warehouse |
|---|---|---|
address_id |
Copy Column |
Address_id |
address_line1 |
Copy Column |
Address_Line1 |
address_line2 |
Copy Column |
Address_Line2 |
address_name |
Copy Column |
Address_Name |
address_type |
Copy Column |
Address_type |
City |
Copy Column |
City |
country_code |
Copy Column |
country_code |
d_DateCreated |
Copy Column |
d_DateCreated |
d_DateLastChanged |
Copy Column |
d_DateLastChanged |
first_name |
Copy Column |
First_Name |
g_UserIDChangedBy |
Copy Column |
g_UserIdChangedBy |
Last_name |
Copy Column |
Last_Name |
OrderGroup_id |
Copy Column |
OrderGroup_id |
postal_code |
Copy Column |
Postal_Code |
region_code |
Copy Column |
region_code |
region_name |
Copy Column |
region_name |
tel_number |
Copy Column |
tel_number |
country_name |
Copy Column |
country_name |
Source columns from the OrderFormHeader table of the <SiteName>_Transactions database |
Transformation |
Target columns from the OrderFormHeader table in the Data Warehouse |
|---|---|---|
billing_currency |
Copy Column |
billing_currency |
buying_org_name |
Copy Column |
buying_org_name |
cc_auth_number |
Copy Column |
cc_auth_number |
CurrencyCode |
Generated If the column exists in the OrderFormHeader table of the Site_commerce database, the value is copied to the CurrencyCode table in the Data Warehouse, and then the ID for the code is included in the OrderFormHeader table.If it does not exist in the <SiteName>_Transactions database, it is retrieved from the MSCS_Admin database, the value is copied to the CurrencyCode table in the Data Warehouse, and then the ID for the code is included in the OrderFormHeader table. |
Order_CurrencyCodeID |
d_DateCreated |
Copy Column |
d_DateCreated |
d_DateLastChanged |
Copy Column |
d_DateLastChanged |
g_UserIdChangedBy |
Copy Column |
g_UserIDChangedBy |
orderform_id |
Copy Column |
OrderForm_Id |
orderform_number |
Copy Column |
OrderForm_number |
ordergroup_id |
Copy Column |
ordergroup_id |
receiver_email_address |
Copy Column |
receiver_email_address |
receiver_fax_number |
Copy Column |
receiver_fax_number |
receiver_first_name |
Copy Column |
receiver_first_name |
receiver_last_name |
Copy Column |
receiver_last_name |
receiver_tel_number |
Copy Column |
receiver_tel_number |
saved_cc_expiration |
Copy Column |
saved_cc_expiration |
saved_cc_number |
Copy Column |
saved_cc_number |
saved_cy_tax_total |
Copy Column |
saved_cy_tax_total |
saved_cy_total_total |
Copy Column |
saved_cy_total_total |
selling_org_name |
Copy Column |
selling_org_name |
tax_exempt_code |
Copy Column |
tax_exempt_code |
tax_exempt_id |
Copy Column |
tax_exempt_id |
total_lineitems |
Copy Column |
total_lineitmes |
user_email_address |
Copy Column |
user_email_addresses |
user_fax_number |
Copy Column |
user_fax_number |
user_first_name |
Copy Column |
user_first_name |
user_id |
Copy Column |
user_id |
user_last_name |
Copy Column |
user_last_name |
user_org_id |
Copy Column |
user_org_id |
user_org_name |
Copy Column |
user_org_name |
user_tel_number |
Copy Column |
user_tel_number |
orderform_date |
Generated A normalized date value including the hour is written into the date table and an ID is loaded here. |
DateID |
OrderGroupID |
Copy Column |
OrderGroupID |
orderform_date |
Copy Column |
Order_formdate_unnorm |
Source columns from the OrderFormLineItems table of the <SiteName>_Transactions database |
Transformation |
Target columns from the OrderFormLineItems table in the Data Warehouse |
|---|---|---|
cy_lineitem_total |
Copy Column |
cy_lineitem_total |
cy_unit_price |
Copy Column |
cy_unit_price |
d_DateCreated |
Copy Column |
d_DateCreated |
d_DateLastChanged |
Copy Column |
d_DateLastChanged |
description |
Copy Column |
description |
g_UserIDChangedBy |
Copy Column |
g_UserIDChangedBy |
lineitem_id |
Copy Column |
lineitem_id |
OrderForm_id |
Copy Column |
OrderForm_id |
OrderGroup_id |
Copy Column |
OrderGroup_id |
product_catalog_base |
Copy Column |
product_catalog_base |
product_id |
Copy Column |
product_id |
product_variant_identifying_property |
Copy Column |
product_identifying_property |
quantity |
Copy Column |
quantity |
tax_exempt |
Copy Column |
tax_exempt_code |
tax_id |
Copy Column |
tax_id |
unit_code |
Copy Column |
unit_code |
N/A |
Generated Provider-generated six-byte ID that links this record with the OrderformHeader table. |
OrderFormHeaderID |
N/A |
Generated Provider-generated 6-byte ID that links this record with the OrderGroupAddresses table. |
OrderGroupAddressesID |
N/A |
Generated Provider-generated 6-byte ID. |
OrderGroupID |
shipping_address_id |
Copy Column |
shipping_address_id |
shipping_method_name |
Copy Column |
shipping_method_name |
Source columns from the OrderGroup table of the <SiteName>_Transactions database |
Transformation |
Target columns from the OrderGroup table in the Data Warehouse |
|---|---|---|
alt_currency |
Copy Column |
alt_currency |
alt_Icid |
Copy Column |
alt_Icid |
billing_currency |
Copy Column |
billing_currency |
d_DateCreated |
Copy Column |
d_DateCreated |
d_DateLastChanged |
Copy Column |
d_DateLastChanged |
default_currency |
Copy Column |
default_currency |
default_Icid |
Copy Column |
default_Icid |
g_UserIDChangedBy |
Copy Column |
g_UserIDChgnedBy |
order_approval_decision |
Copy Column |
order_approval_decision |
order_create_date |
Copy Column |
order_create_date |
order_number |
Copy Column |
order_number |
order_status_code |
Copy Column |
order_status_code |
ordergroup_id |
Copy Column |
ordergroup_id |
previous_order_id |
Copy Column |
previous_order_id |
saved_cy_oadjust_subtotal |
Copy Column |
saved_cy_oadjust_subtotal |
saved_cy_total_total |
Copy Column |
saved_cy_total |
saved_order_name |
Copy Column |
saved_order_name |
total_lineitems |
Copy Column |
total_lineitmes |
user_first_name |
Copy Column |
user_first_name |
user_last_name |
Copy Column |
user_last_name |
user_first_name and user_last_name |
Concatenation |
user_name |
user_org_name |
Copy Column |
user_org_name |
user_id |
Copy Column |
RegisteredUserID |