ETL Process for the Profile Data Import DTS Task
The Profile Data Import DTS task updates the Data Warehouse with the following user profile information from the Profiles data store:
User profile data
Organization profile data
Generic profile data
Site terms
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 <SiteName>_Profiles 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 <SiteName>_Profiles 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 <SiteName>_Profiles 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 <SiteName>_Profiles 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 |
|
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 |
u_user_type |
Copy Column |
UserType |
d_date_created |
Copy Column |
DateCreated |
d_date_last_changed |
Copy Column |
DateLastChanged |