HOW TO - SCSM DataWarehouse - Report FULL NAME of IR CLASSIFICATION

Conner_Wood

So recently I've been tasked with creating another report in SQL.  One of the requirements was to display the FULL NAME of the IR Classification Enumeration.  This means having to do SQL recursion on the SCSM DataWarehouse.

Below is the SQL required to generate "IR_CLASSIFICATION_FULLENUM" which contains the FULL PATH/FULL NAME of the Incident Classification Enumeration.

USE [DWDataMart];

WITH IR_CLASSIFICATION_ENUM AS
(
     SELECT Enum.[IncidentClassificationId] AS 'EnumDimKey'
           ,DS.[DisplayName] AS 'EnumDisplayName'
           ,Enum.[ParentId] as 'EnumParentDimKey'
     FROM [dbo].[IncidentClassificationvw] Enum (nolock)

     JOIN [dbo].[DisplayStringDimvw] DS (nolock)
     ON DS.[BaseManagedEntityId] = Enum.[EnumTypeId]
     AND DS.[LanguageCode] = 'ENU'
)
,IR_CLASSIFICATION_FULLENUM AS
(
     SELECT Enum.[EnumDimKey]
           ,cast(Enum.[EnumDisplayName] as nvarchar(max)) AS 'EnumDisplayName'
           ,Enum.[EnumParentDimKey]
     FROM IR_CLASSIFICATION_ENUM Enum
     WHERE Enum.[EnumParentDimKey] IS NULL
     UNION ALL
     SELECT Enum.[EnumDimKey]
           ,cast(FullEnum.[EnumDisplayName] + '\' + Enum.[EnumDisplayName] as nvarchar(max)) AS 'EnumDisplayName'
           ,Enum.[EnumParentDimKey]
     FROM IR_CLASSIFICATION_ENUM Enum
     JOIN IR_CLASSIFICATION_FULLENUM FullEnum on Enum.[EnumParentDimKey] = FullEnum.[EnumDimKey]
)


SELECT TOP 1000 IR.[DisplayName]
      ,IR.[Classification_IncidentClassificationId]
      ,IR_CLASSIFICATION.[EnumDisplayName] AS 'Full Classification'
FROM [dbo].[IncidentDimvw] IR (nolock)

LEFT JOIN IR_CLASSIFICATION_FULLENUM IR_CLASSIFICATION 
ON IR_CLASSIFICATION.[EnumDimKey] = IR.[Classification_IncidentClassificationId]

Konstantin_Slavin-Bo

That is some nice querying @Conner_Wood, thanks a lot for this. Good work!

Karen_Bruster1

@Conner_Wood are you using this in the Analytics or in SCSM Reporting?

Conner_Wood

@Karen_Bruster1 We're at the point where we directly query the DWSQL Database, this was less about metrics and more about reporting and reading actual data.