SCRM 2006 Databases

  • Article

The SCRM data warehouse contains the following SQL Server databases:

  • SystemCenterStaging

  • SystemCenterPresentation

  • SystemCenterConfiguration

  • TempDB

SystemCenterStaging Database

The SystemCenterStaging database holds SMS data in its original format. This data is validated and transformed before it is published to the SystemCenterPresentation database. The data or "fact" tables in the SystemCenterStaging database have the following functionality:

  • Store a full or delta copy of the source SMS table with any transformations applied.

  • May contain data from several source tables.

  • Are used for delta detection and for updating the SystemCenterPresentation database.

  • Store data that was rejected during the extract, transform, and load (ETL) process due to data integrity requirements.

Operational tables in the SystemCenterStaging database contain information describing the state of synchronized data and have the following functionality:

  • Contain a record of the most recent value obtained from each table that supports watermarks during the last SMS synchronization. Watermark information is used to determine what event data is new.

  • Contain an ordered list of tasks and the tables to which they apply.

  • Are used to mark the progress of the synchronization. If the synchronization is stopped, it is restarted at the last uncompleted task.

  • Are cleared at the end of synchronizations.

  • Hold statistics about a synchronization.

SystemCenterPresentation Database

The SystemCenterPresentation database is where all SCRM information is published for query, reporting, and analysis. The SystemCenterPresentation database contains fact tables, dimension tables, outrigger tables, and views. Views are used by the SQL Server Reporting Service and ad-hoc query tools. The SystemCenterPresentation database stores watermark information used for MOM data change detection, data lineage, and the Service Monitoring and Control meta model used by MOM 2005 to dynamically create DTS packages.

SystemCenterConfiguration Database

The SystemCenterConfiguration database stores back-end information that is displayed in the SCRM Administrator console, such as job history and data source information.

TempDB Database

The TempDB database is found on the fastest drive of the SCRM 2006 server, and it is used during the data transformation process when the SCRM data source is established.