ETL Process for the Profile Data Import DTS Task

Article
11/12/2009

The Profile Data Import DTS task updates the Data Warehouse with user profile information from the Profiles data store. The task also keeps the schema of a user profile in the Profiles data store and any other exported profile synchronized with the Data Warehouse.

The Profile Data Import task also populates the Data Warehouse with Site term information from the Site_Commerce database. This information can be found in the SiteTerm and SiteTermDescription tables of the Data Warehouse.

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 Addresses table of the Site_Commerce database	Transformation	Target columns from the Address table in the Data Warehouse
`g_address_id`	Copy Column	`Id`
`u_address_name`	Copy Column	`AddressName`
`i_address_type`	Copy Column	`AddressType`
`u_city`	Copy Column	`City`
`u_country_code`	Copy Column	`CountryCode`
`u_country_name`	Copy Column	`CountryName`
`u_first_name`	Copy Column	`FirstName`
`u_last_name`	Copy Column	`LastName`
`u_postal_code`	Copy Column	`PostalCode`
`u_region_code`	Copy Column	`RegionCode`
`u_region_name`	Copy Column	`RegionName`
`u_tel_extension`	Copy Column	`TelephoneExtension`
`u_tel_number`	Copy Column	`TelephoneNumber`
`d_date_created`	Copy Column	`DateCreated`

Source columns from the UserObject table of the Site_Commerce database	Transformation	Target columns from the RegisteredUser table in the Data Warehouse
`i_account_status`	Copy Column	`AccountStatus`
`d_date_registered`	Copy Column	`DateRegistered`
`u_email_address`	Copy Column	`Email`
`u_first_name`	Copy Column	`FirstName`
`u_last_name`	Copy Column	`LastName`
`u_logon_name`	Copy Column	`logon_name`
`u_tel_number`	Copy Column	`TelephoneNumber`
`g_user_id`	Copy Column	`UserId`
`i_user_type`	Copy Column	`UserType`
`d_date_created`	Copy Column	`DateCreated`
`d_date_last_changed`	Copy Column	`DateLastChanged`

ETL Process for the Profile Data Import DTS Task

See Also

Additional resources