SCSM - Relationships and Classes archived into Facts and Dims

  • Article

While there is a fair amount of documentation out there regarding the structures of the Live SCSM Database ServiceManager and Archived SCSM Databases (DWStagingAndConfig, DWDataMart), there is not much documentation regarding what Tables are there to get the data archived!

So let's get into it, the DWStagingAndConfig is where the Mapping of Relationships and Classes becomes Facts and Dimensions.

The following Tables are the ones you should focus on:

[DWStagingAndConfig].[dbo].[ManagedType]
V
[DWStagingAndConfig].[dbo].[Dimension]
V
[DWStagingAndConfig].[dbo].[DimensionProperty]
[DWStagingAndConfig].[dbo].[Fact]
V
[DWStagingAndConfig].[etl].[WarehouseEntity]
[DWStagingAndConfig].[etl].[WarehouseEntityType]

So there ya go, when it comes down to it, the Dimension Table is the most important Table as it stores the TargetId which is the ManagedTypeId of the class the dimension represents! 

From there, you can get the dimension table of the archived class by using [DWStagingAndConfig].[etl].[WarehouseEntity] WHERE EntityGuid = '<DimensionId>'

Unfortunately the etl tables and dimension table and fact table aren't present in the DWDataMart so you must use DWStagingAndConfig database to query these tables.

To quickly see all the properties archived of a particular class (like Incident) and which view.

SELECT MT.[TypeName]
      ,DIM.[DimensionName]
      ,WE.[ViewName]
      ,WC.[ColumnName]
  FROM [DWStagingAndConfig].[dbo].[Dimension] DIM (nolock)
 
  JOIN [DWStagingAndConfig].[dbo].[ManagedType] MT (nolock)
  ON DIM.[TargetId] = MT.[ManagedTypeId]
 
  JOIN [DWStagingAndConfig].[etl].[WarehouseEntity] WE (nolock)
  ON WE.[EntityGuid] = DIM.[DimensionId]
 
  JOIN [DWStagingAndConfig].[etl].[WarehouseColumn] WC (nolock)
  ON WC.[EntityId] = WE.[WarehouseEntityId]
 
  WHERE MT.[TypeName] = 'System.WorkItem.Incident'

To quickly see all the relationships archived that involve a particular class (like Incident) and which view they become.

SELECT MT.[TypeName]
      ,FACT.[FactName]
      ,WE.[ViewName]
  FROM [DWStagingAndConfig].[dbo].[Fact] FACT (nolock)
   
  JOIN [DWStagingAndConfig].[dbo].[ManagedType] MT (nolock)
  ON MT.[ManagedTypeId] = FACT.[SourceType]
 
  JOIN [DWStagingAndConfig].[etl].[WarehouseEntity] WE (nolock)
  ON WE.[EntityGuid] = FACT.[FactId]
 
  WHERE TypeName = 'System.WorkItem.Incident'

Onwards SCSM Admins, the Data Warehouse isn't getting any better, but with this knowledge, you can better understand it!