ETL Process for the Configuration Synchronization DTS Task
The Configuration synchronization DTS task synchronizes information between the Administration database and the Data Warehouse. Specifically, the task synchronizes the Data Warehouse with your Commerce Server application after you change configuration settings, such as site topology, query strings, crawlers, and report definitions. The Configuration synchronization DTS task should be run before any of the other import or delete tasks are run, especially if you change configuration settings.
The following tables list the columns extracted from the source tables in the Administration database, the transformation performed, and the tables that the data is saved to in the Data Warehouse.
Synchronizing the Site Topology
Source property name from the ResourceProps table of the Administration database |
Transformation |
Target columns in the Site table of the Data Warehouse |
|---|---|---|
WhenImportIsComplete |
Generated Value of s_Value plus 1 is loaded. |
EndImportAction |
N/A |
Generated Set to zero (0). |
IsAdjustTimeStamps |
ExcludeCrawlers |
Copy Column |
IsExcludeSpiders |
i_QSSaveWithUri |
Generated Value of s_Value plus 1 is loaded. |
IsSeparateUriAndQueryString |
UseCookiesForInferences |
Copy Cell |
IsUseCookiesForInferences |
MultipleUsersUseTheSameName |
Copy Cell |
IsUseUsernameForInferences |
UseUPMCookiesForInferences |
Copy Cell |
IsUseUPMCookiesForInferences |
s_InfNames |
Copy Cell |
CookieIdentifiers |
IfOverlapDetected |
Generated Value of s_Value plus 1 is loaded. |
OverlapAction |
MinutesRecordsOverlap |
Copy Cell |
OverlapMins |
s__Name from Sites Table in MSCS_Admin database |
Copy Cell |
SiteName |
TimeZone |
Copy Cell |
TimeZone |
IdleTmeBeforeVisitEnds |
Copy Cell |
VisitTimeOut |
RetryCount |
Copy Cell |
RetryCount |
RetryExhaustAction |
Copy Cell |
RetryExhaustAction |
g_nStartDayOfWeek |
Copy Cell Default value is zero (0). |
WeekStartDay |
Source PropertyName from the ResourceProps table of the Administration database |
Transformation |
Target columns from the ServerGroup table in the Data Warehouse |
|---|---|---|
s_WebServerName |
Copy Cell |
varList |
s_LogFileDirectory |
Copy Cell |
LogFileDir |
s_LogType |
Copy Cell |
LogFormatID |
s_WebServerMachine and s_WebServerName |
Concatenation "LDS For" s_WebServerMachine/ s_WebServerName. Used only if varList is empty. |
ServerGroupName |
N/A |
Generated Value of 25 (GMT) is loaded. |
TimeZone |
s_LogFileCodePage |
Copy Cell |
LogFileCodePage |
s_URLEncodingCodePage |
Copy Cell |
URLEncodingCodePage |
Source PropertyName from the ResourceProps table of the Administration database |
Transformation |
Target columns from the VirtualServer table in the Data Warehouse |
|---|---|---|
s_DirectoryIndexFiles |
Copy Cell |
DirIndexes |
LocalDomainDnsServer |
Copy Cell If cell is empty load “Unknown” |
LocalDomain |
s_WebServerMachine and s_WebServerName |
Generated s_WebServerName on s_WebServerMachine |
VserverName |
N/A |
Generated Value of 1 is loaded. |
VserverType |
Source PropertyName from the ResourceProps table of the Administration database |
Transformation |
Target columns from the ServerBinding table in the Data Warehouse |
|---|---|---|
s_ServerBindings |
Middle of String |
HostHeaderName |
s_ServerBindings |
Middle of String |
IPAddress |
N/A |
Generated If the binding is secure, 443 is loaded. If it is not secure, 80 is loaded. |
IPPort |
Source PropertyName from the ResourceProps table of the Administration database |
Transformation |
Target columns from the SiteURL table in the Data Warehouse |
|---|---|---|
s_ExFileExpressions and s_ExFileTypes |
Concatenation Asterisk (*) separated list of expressions and types. |
ExcludeCriteria |
s_ExHosts |
Copy Cell |
ExcludeHosts |
TruncateTopDir |
Copy Cell |
IsChopTopLevelDir |
s_NonSecureHostname and s_VirtualRootName |
Concatenation http://" & s_NonSecureHostname & "/" & s_VirtualRootName |
URL |
s_NonSecureHostname and s_VirtualRootName |
Concatenation http://" & s_NonSecureHostname & "/" & s_VirtualRootName |
URLExt |
Query String Updates
Source Property Name from the ResourceProps table of the Administration database |
Transformation |
Target columns from the URIQuery table in the Data Warehouse |
|---|---|---|
s_QSName |
Copy Cell |
QueryString |
N/A |
Generated |
QueryStringKey |
Source Property Name from the ResourceProps Table of the Administration Database |
Transformation |
Target Columns from the SVQStringName Table in the Data Warehouse |
|---|---|---|
s_QSName |
Copy Cell |
QStringName |
Source Property Name from the ResourceProps table of the Administration database |
Transformation |
Target columns from the LinkSVQStringName table in the Data Warehouse |
|---|---|---|
s_QSName |
Copy Cell Load a LinkSVQStringName for every SiteURLID listed in s_OSApplicationList. |
LinkSVQStringName |
Source Property Name from the ResourceProps table of the Administration database |
Transformation |
Target columns from the MVQStringName table in the Data Warehouse |
|---|---|---|
s_QSName |
Copy Cell |
QStringName |
Crawler Updates
Source Property Name from the ResourceProps table of the Administration database |
Transformation |
Target columns from the Crawler table in the Data Warehouse |
|---|---|---|
Crawler.ini |
Read File |
CrawlerString |
i_SiteID from Sites Table in MSCS_Admin database |
Copy Cell |
SiteID |