SCSM DW and extended workitems

JC9542

Hello, I recently took over as the admin for SCSM at my job.  I'm trying to add in a new field as a string to IRs and SRs.  We've had previous additions implemented in a sealed MP.  Luckily I have the source XML for that so I added in the requested property and customized the included form in the MP source.  Then I sealed it and imported in.  It looks correct if I create a new SR or IR, but how can I tell that these changes are being carried over to the Data Warehouse?

My understanding is so long as I extend the workitem with the authoring tool and then seal the MP, it should automatically be imported to the DW.  I can't seem to find any references to the new field in the Cube under IncidentDim.  I also tried using MS SQL Management Studio, but it's been a long time since I've done any DB work and I'm not familiar with how the SCSM DBs are configured.  So just seeing if anyone can tell me how to check if my changes have been implemented correctly. 

Thanks!

Leigh_Kilday

Go to the SCSM console > Data Warehouse tab > Data Warehouse Jobs view > click MPSyncJob.

Check that the job is enabled, then sort your Syncronization Job Details table by Batch Id (descending). You'll see the sealed MP appear in the MP column and it should be in a status of "Associated". If not, the ETL process failed and you'll need expert help to troubleshoot this because it's a "black box solution".

You won't find the extension in the cubes. This is a limitation we've avoided by creating our own cubes.

To find the extension in the DW, open SQL Server Management Studio navigate through the instance tree to DWDataMart > Views > dbo.ServiceRequestDimvw (or dbo.IncidentDimvw) > Columns. Verify that you see your new field there.

If you have already added data to the new field, run a query against the table, like SELECT * FROM DWDataMart.dbo.ServiceRequestDimvw and verify that there is data in the latest SR/IR columns.

Conner_Wood

Class Extensions and their Properties should automatically sync into the Data Warehouse if the main class in question has all it's properties set to being synced.  

• Service Manager Service Request Data Warehouse Library (ServiceManager.ServiceRequest.Library.Datawarehouse.xml) ==== Dimension of ServiceRequestDim has HierarchySupport="IncludeExtendedClassProperties"
• Service Manager Incident Management Data Warehouse Library (ServiceManager.IncidentManagement.Library.Datawarehouse.xml) ==== Dimension of IncidentDim has HierarchySupport="IncludeExtendedClassProperties"

However Relationships are not archived by default (and only some Out Of Box are archived), you need to define those just as you would define comments to keep in the Archive, which I gave an example of using My.WorkItem.CommentLog.DataWarehouse.xml

Now if you actually want to verify the data is in the SCSM Databases:

In Live SCSM DB "ServiceManager" --> there should be a table with your class extension internal name: Ex.  If your extension internal name is "MyIncidentExtension" then you'd be looking for the table "[dbo].[MT_MyIncidentExtension]"

In the DW Archive DB "DWDataMart" --> for class properties they become another column in the main parent table, so if "MyIncidentExtension" extends from "System.WorkItem.Incident" then the table you'll find your additional property columns in will be "[dbo].[IncidentDim]"

Leigh_Kilday

Go to the SCSM console > Data Warehouse tab > Data Warehouse Jobs view > click MPSyncJob.

Check that the job is enabled, then sort your Syncronization Job Details table by Batch Id (descending). You'll see the sealed MP appear in the MP column and it should be in a status of "Associated". If not, the ETL process failed and you'll need expert help to troubleshoot this because it's a "black box solution".

You won't find the extension in the cubes. This is a limitation we've avoided by creating our own cubes.

To find the extension in the DW, open SQL Server Management Studio navigate through the instance tree to DWDataMart > Views > dbo.ServiceRequestDimvw (or dbo.IncidentDimvw) > Columns. Verify that you see your new field there.

If you have already added data to the new field, run a query against the table, like SELECT * FROM DWDataMart.dbo.ServiceRequestDimvw and verify that there is data in the latest SR/IR columns.

Conner_Wood

Class Extensions and their Properties should automatically sync into the Data Warehouse if the main class in question has all it's properties set to being synced.  

• Service Manager Service Request Data Warehouse Library (ServiceManager.ServiceRequest.Library.Datawarehouse.xml) ==== Dimension of ServiceRequestDim has HierarchySupport="IncludeExtendedClassProperties"
• Service Manager Incident Management Data Warehouse Library (ServiceManager.IncidentManagement.Library.Datawarehouse.xml) ==== Dimension of IncidentDim has HierarchySupport="IncludeExtendedClassProperties"

However Relationships are not archived by default (and only some Out Of Box are archived), you need to define those just as you would define comments to keep in the Archive, which I gave an example of using My.WorkItem.CommentLog.DataWarehouse.xml

Now if you actually want to verify the data is in the SCSM Databases:

In Live SCSM DB "ServiceManager" --> there should be a table with your class extension internal name: Ex.  If your extension internal name is "MyIncidentExtension" then you'd be looking for the table "[dbo].[MT_MyIncidentExtension]"

In the DW Archive DB "DWDataMart" --> for class properties they become another column in the main parent table, so if "MyIncidentExtension" extends from "System.WorkItem.Incident" then the table you'll find your additional property columns in will be "[dbo].[IncidentDim]"

JC9542

Thanks guys appreciate the help greatly!