Import Transactions Data

For the latest version of Commerce Server 2007 Help, see the Microsoft Web site.

You use the Transaction Data Import DTS task to import site transaction data into the Data Warehouse. Transaction data is stored in the Commerce Server transaction database. 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.

The Commerce Server transaction database includes purchase orders and requisition numbers that are used in analysis reports to determine how well individual products are selling, how much is being requisitioned, or how much your customers are purchasing.

Imported Data

Use the Transaction Import Properties Dialog Box to specify the sites whose log file transaction data you want to import into the Data Warehouse by using the Transaction Data Import DTS task.

The following tables list the columns extracted from the source tables in the Site_Transactions database, the transformation made, and the tables to which the data is saved in the Data Warehouse.

Most Data Warehouse tables also contain a SiteName column. This column, like TableID and TableInternalFlag, is also not listed in the tables in the following sections because the SiteName is always extracted from the same location for all DTS tasks.

Source columns from the OrderAddresses table of the Site_Transactions database

Transformation

Target columns from the OrderGroupAddresses table in the Data Warehouse

OrderAddressId

Copy Column

Address_id

Line1

Copy Column

Address_Line1

Line2

Copy Column

Address_Line2

Name

Copy Column

Address_Name

City

Copy Column

City

CountryCode

Copy Column

country_code

d_DateCreated

Copy Column

d_DateCreated

d_DateLastChanged

Copy Column

d_DateLastChanged

FirstName

Copy Column

First_Name

g_UserIDChangedBy

Copy Column

g_UserIdChangedBy

LastName

Copy Column

Last_Name

OrderGroup_id

Copy Column

OrderGroup_id

PostalCode

Copy Column

Postal_Code

RegionCode

Copy Column

region_code

RegionName

Copy Column

region_name

DaytimePhoneNumber

Copy Column

tel_number

CountryName

Copy Column

country_name

Source columns from the LineItems table of the Site_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

LineItemId

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_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

Notes:

  • Strings that are stored as a global unique identifier (GUID) must begin and end with curly brackets “{” and “}”.This is the case with address_id, which is processed by the Transaction data import task. Make sure you insert the beginning and ending curly bracket before writing an event to the Web log file. For example, your site must generate the GUID in the Commerce event of the type CEVT={T=ORD,EVT=SUBOR,ORID="fd5823ed-cac9-4b29-aa82-a18fe43b78e8"} as {fd5823ed-cac9-4b29-aa82-a18fe43b78e8}. The CSOLEDB provider does not convert the GUID if the required beginning and ending curly brackets are missing.

  • You must use the full load option and perform a full import the first time you import transaction data into the Data Warehouse. The Transaction DTS task also supports an incremental import, which will just import new orders since the last time that the Transaction DTS task ran.  If you want the data warehouse to always reflect the latest status of transaction data, a full import is recommended.

  • When running the Transaction Data 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 can occur or data can be incorrectly converted when the Transaction Data Import DTS task is run.

How to Run the Transaction Data Import DTS Task

See Also

Other Resources

What Data Does DTS Import into the Data Warehouse?