Share via


ETL Process for the Transaction Data Import DTS Task

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

See Also

Other Resources

ETL Processing for DTS Tasks