Posted on:
Categories: System Center
Description:

One of the latest experiences with an SCSM Datawarehouse involved a time where the SQL server hosting Service Manager went down for a few hours over night. For some reason the Datawarehouse server decided that it had had enough of not being able to preform the standard ETL jobs so it opted to remove itself entirely by disassociating each and every MP!

After the source SQL server came back, the ETL jobs resumed (after some tender love and care), and the entire  datawarehouse did a full rebuild over a 6 hour period.

When the dust settled many of the list items showing up in the cube contain nothing but GUID’s! Sometimes Support Groups, other times Classifications, all for a variety of Work Items to!

Image
GUID’s GUID’s EVERYWHERE!

Who knows why, all I know is that here’s a fix! Just run these scripts against the DWDatamart to update all the affected columns using a special display name cross reference table.


update dbo.IncidentClassification
set [IncidentClassificationValue]=
Case when (select [ENU] from dbo.DisplayStringDimCrosstabvw ds where EnumTypeId = ds.BaseManagedEntityId) IS NULL
then [IncidentClassificationValue]
else
(select [ENU] from dbo.DisplayStringDimCrosstabvw ds where EnumTypeId = ds.BaseManagedEntityId)
end

update dbo.IncidentTierQueues
set [IncidentTierQueuesValue]=
Case when (select [ENU] from dbo.DisplayStringDimCrosstabvw ds where EnumTypeId = ds.BaseManagedEntityId) IS NULL
then [IncidentTierQueuesValue]
else
(select [ENU] from dbo.DisplayStringDimCrosstabvw ds where EnumTypeId = ds.BaseManagedEntityId)
end

update dbo.ServiceRequestArea
set [ServiceRequestAreaValue]=
Case when (select [ENU] from dbo.DisplayStringDimCrosstabvw ds where EnumTypeId = ds.BaseManagedEntityId) IS NULL
then [ServiceRequestAreaValue]
else
(select [ENU] from dbo.DisplayStringDimCrosstabvw ds where EnumTypeId = ds.BaseManagedEntityId)
end

update dbo.ServiceRequestSupportGroup
set [ServiceRequestSupportGroupValue]=
Case when (select [ENU] from dbo.DisplayStringDimCrosstabvw ds where EnumTypeId = ds.BaseManagedEntityId) IS NULL
then [ServiceRequestSupportGroupValue]
else
(select [ENU] from dbo.DisplayStringDimCrosstabvw ds where EnumTypeId = ds.BaseManagedEntityId)
end

update dbo.ChangeArea
set [ChangeAreaValue]=
Case when (select [ENU] from dbo.DisplayStringDimCrosstabvw ds where EnumTypeId = ds.BaseManagedEntityId) IS NULL
then [ChangeAreaValue]
else
(select [ENU] from dbo.DisplayStringDimCrosstabvw ds where EnumTypeId = ds.BaseManagedEntityId)
end