SCCM database table relationship map
Note: SCCM utilizes a normalized Microsoft SQL Server (MSSQL) database (data is spread across many tables). above default Business Objects with appropriate Fields, Relationships, Forms, and Grids for mapping/importing inventory data;. No, we do not document the schema of our database - never have, never will:) We do have the views documented for reporting, but not the. The rows and columns of data come from tables or other SQL views referenced Knowing the Configuration Manager database view schema is an important first step in Much of the Configuration Manager SQL view schema maps to the SMS . v_UserMachineSourceRelation Lists all user device affinity relationships, by.
Do not attempt to create, delete, or modify any database objects, or to modify data stored in the database, unless asked to do so by Microsoft support personnel. Remember to test all modifications before applying them to your production environment.
This object provides access to the properties and methods of the ConfigMgr collections defined in the site database. Notice that the MemberClassName column provides the name of the view for the collection membership. The naming conventions for views generally map to the corresponding WMI classes, according to the following rules: View names more than 30 characters are truncated.
Site Properties Basic ConfigMgr site properties are stored in the Sites table and exposed though several views and stored procedures. The sysreslist table stores information about the site systems. The tables and views discussed so far relate to the ConfigMgr objects and infrastructure. The database also contains a wealth of data gathered by various discovery methods and client inventory.
Chapter 9 discusses discovery and inventory. Discovery and inventory data is stored in resource tables and presented in resource views. The naming conventions for resource views are as follows: Other Views Several views are included that present metadata on other views and serve as keys to understanding the view schema. Dimensions Dimensions represent the classes, where each row in the dimension is an instance of the class and each column is a property.
Fact tables Fact tables are the most notable difference between a data warehouse and a transaction processing system. Generally fact tables are used to track transactions, or things that happen, over time. These transactions are usually quantified and summarized, so they get represented as metrics called measures in data warehousing terms.
In Service Managerthere are two types of fact tables: Relationship fact tables Relationship fact tables are used to track the relationships between instances of classes over time.
SCCM’s Resource Explorer to SQL table mapping | Laurie Rhodes' Info
As the WorkItem is assigned or reassigned to a user, a new row is inserted into the relationship WorkItemAssignedToUser fact table which targets this relationship. Relationship fact tables also have CreatedDate and DeletedDate columns which enable determining when the relationship was in effect. If the DeletedDate column is null it is currently an active relationship. All the code required to populate and maintain these fact tables are automatically generated once the fact table is defined in a management pack.
SCCM’s Resource Explorer to SQL table mapping
More on creating relationship fact tables in this blog post. Custom fact tables Custom fact tables are fact tables which a developer can write a custom code for and populate based on their specific business requirements.
Out of the box we have a few custom fact tables which can be quite useful.
- SCCM data import process and source tables
- Service Manager Data Warehouse schema now available
- Microsoft SCCM Integration 2012