Most of the data in the Reporting Server data warehouse is imported from the SMS and MOM operational databases by using SQL Data Transformation Services (DTS) jobs.

Business Context Data

Some of the information, such as user location, cost center, and manager, is not available in the SMS and MOM databases. It is assumed that your company has this information available in a database, such as Active Directory, and that it is possible to export the User and Node Location information. The loading of external data directly into the SCRM 2006 data warehouse can be achieved using script. Alternatively, a Microsoft Excel template (SCRMUserDataTemplate.xls) is provided on the System Center Reporting Manager 2006 CD to import this information into the Reporting Server database. You can import the information in the populated Excel template by using the SQL Import/Export DTS Wizard. For more information about adding business context data to the SCRM 2006 data warehouse, see the Business Context Data white paper on the SCRM 2006 Web site (http://go.microsoft.com/fwlink/?LinkId=62109).

MOM Data Sources

When creating a new MOM data source, it is necessary to add the SCRM account that is used to run the MOM DTS job Windows Task Scheduler task to the SC DW DTS local user group.

SMS Data Sources

The policy level is the principal consideration for determining the appropriate level of the SMS site hierarchy to be configured as a data source. Because policy (client agent settings, collections, packages, advertisements, and status messages) is propagated down through the hierarchy, the most appropriate level to be configured as an SCRM data source would be the level at which policy is defined in the site hierarchy. Typically, this is the Central or Primary site level. Configuring SCRM data sources from two levels of a SMS site hierarchy that have a parent-child relationship is not a valid SCRM data source configuration because the data collected would be duplicated from each of the two levels.

The following install and configuration changes occur on the SMS Server when an SMS data source is added and configured:

  • The SCRM SMS site service (SCRMClientSvc.exe) is created.

  • A DTS job is created.

  • The SC DW SMS DTS local user group is created.

  • The SC DW SMS DTS SQL user account is created.

  • The SC DW SM DTS SQL role is created.

  • SCRM tables are added to the SMS site database.

  • SELECT, INSERT, and DELETE permissions are granted to these tables for the SC DW SMS DTS SQL role.

  • The SC DW DTS user account is made of a member of the SC DW SMS DTS SQL role.

  • A new SQL job is created.

  • The UNC path to the shared folder on the SCRM 2006 server is stored in the configuration table contained in the SMS site database.

SMS Data Source Directory Structure

For each new SMS data source that is created, a new directory and file structure is created in the SCRM_Staging shared folder on the System Center data warehouse computer. The share is called SCRMSMSShare. This folder is used to store SMS data before it is moved to the SCRM data warehouse. Ensure that this folder has sufficient free disk space available for Bulk Copy Program (BCP) files. The recommended free space is 70% of SMS database size.

The following schematic is a representation of the data source directory and the folders that are created under the directory:

SMS Data

Atlanta Site Server

Inbound

Outbound

Chicago Site Server

Inbound

Outbound

Domain Changes

If an SMS site server domain is changed, data synchronization with the SCRM 2006 server fails because the security context of the SMS site server computer account is changed. If this problem occurs, you must add the new domain computer account of the SMS site server to the SCRM_Staging folder, which is shared as SCRMSMSShare, on the SCRM 2006 server with full access to the share.

See Also