Import Transactions Data
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 2009 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 2009 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.
Related Sections
How to Run the Transaction Data Import DTS Task