ETL Process for the Transaction Data Import DTS Task

Article
11/12/2009

The Transaction Data Import DTS task imports site transaction data, such as purchase orders and requisition numbers, into the Data Warehouse. The Transaction Data Import DTS task should be run before creating reports that synchronize transaction data.

Caution

When running the Transaction Import DTS task on a Data Warehouse that contains data for multiple Web sites, ensure 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 Site_commerce 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 Site_commerce 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 Site_commerce 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 placed in the OrderFormHeader table. If it does not exist in the Site_commerce 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 placed 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 Site_commerce 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 Site_commerce 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`

ETL Process for the Transaction Data Import DTS Task

See Also

Additional resources