none
User fact table in DW

    Question

  • I've created a few customized reports one of which pulls incident data by departments.  We've been running into the problem where people have changed departments over time and my reports show the transfer of incidents for that user from one department to another.

    ie. User X has 130 incidents over the past 3 months.  X transfers to department XYZ and now department XYZ has 130 more incidents than before.  This data is not correct in that user X created those incidents as a part of department ABC not XYZ.

    We've been discussing changing our data retention polices for SM from the default 90 days to something less, but my question is will the user data in DW change as the user information changes or once its committed to DW is it then static?


    Allen Anderson Systems Analyst Arizona State University - OKED Knowledge Informatics

    Monday, February 20, 2012 3:25 PM

Answers

  • This is a typical problem with slowly changing dimensions in data warehouses. Changing the data retention settings in SCSM doesn't resolve this problem: updates to the users will still be transfered to the DW.

    In order to work around this issue, you have basically two options:

    1) Store (i.e. copy) the value of the department field of the affected user to a custom property of the incident class using a workflow which is triggered when incidents are created or closed.

    2) Copy and store the data of the table which holds historical changes to users and use it in your report. Note that this table is only available in the production database ("ServiceManager") and that changes older than 365 days will be deleted by default. You can change this setting in the SCSM settings area. The table name which holds the said information starts with MT_ and has _Logs at the end. In between should be something like Domain$User (I don't have a access to SCSM right now ;)) In this table you will see when each property changed from which old value to which new value.

    HTH.

    Monday, February 20, 2012 10:43 PM
  • Okay, looking a little deeper in the blog post, I don't think you can transfer the _Log tables using custom dimensions. Custom dimensions rely on the class model of SCSM, and I was not able to find classes for the log, hence I assume this information is kept in SQL only.

    I thus recommend you look into the second option of "manually" pulling the information into the DW using T-SQL and SQL Agent.

    Wednesday, February 22, 2012 3:10 PM

All replies

  • This is a typical problem with slowly changing dimensions in data warehouses. Changing the data retention settings in SCSM doesn't resolve this problem: updates to the users will still be transfered to the DW.

    In order to work around this issue, you have basically two options:

    1) Store (i.e. copy) the value of the department field of the affected user to a custom property of the incident class using a workflow which is triggered when incidents are created or closed.

    2) Copy and store the data of the table which holds historical changes to users and use it in your report. Note that this table is only available in the production database ("ServiceManager") and that changes older than 365 days will be deleted by default. You can change this setting in the SCSM settings area. The table name which holds the said information starts with MT_ and has _Logs at the end. In between should be something like Domain$User (I don't have a access to SCSM right now ;)) In this table you will see when each property changed from which old value to which new value.

    HTH.

    Monday, February 20, 2012 10:43 PM
  • For option 2, will I have to put this table through ETL to make it available to DW?  I also assume option 2 will be of use to existing incidents (My SM site is only a couple months old) once I code my reports appropriately?

    Allen Anderson Systems Analyst Arizona State University - OKED Knowledge Informatics

    Monday, February 20, 2012 11:13 PM
  • You probably could transfer the table using SCSM ETL by creating a custom dimension in a Management Pack. There is some blogging available at http://blogs.technet.com/b/servicemanager/archive/2010/03/30/deep-dive-into-the-data-warehouse-custom-fact-tables-dimensions-and-outriggers.aspx. Another option would be to write a simple SQL script which transfers the data to the DataMart, and schedule it using SQL Agent. The you can join this table in your reports.
    Tuesday, February 21, 2012 2:46 PM
  • Does it look like I am on the right track?  I am not sure how exactly to reference MT_System$User_Log in the MP.

    <ManagementPack ContentReadable="true" SchemaVersion="1.1" OriginalSchemaVersion="1.1" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
     <Manifest>
      <Identity>
       <ID>DataWarehouse.User.History</ID>
       <Version>0.0.0.1</Version>
      </Identity>
      <Name>Data Warehouse User Information Tracking</Name>
      <References>
       <Reference Alias="System">
        <ID>System.Library</ID>
        <Version>7.0.6555.0</Version>
        <PublicKeyToken>31bf3856ad364e35</PublicKeyToken>
       </Reference>
       <Reference Alias="DWBase">
        <ID>Microsoft.SystemCenter.Datawarehouse.Base</ID>
        <Version>7.0.6555.0</Version>
        <PublicKeyToken>31bf3856ad364e35</PublicKeyToken>
       </Reference>
      </References>
     </Manifest>
     <Warehouse>
      <Dimensions>
       <Dimension ID="UserInformationLogDim" Accessibility="Public" InferredDimension="true" Target="System!System.User" HierarchySupport="IncludeExtendedClassProperties" Reconcile="true" />
      </Dimensions>
      <Facts>
       <RelationshipFact ID="UserInformationLogFact" Accessibility="Public" Domain="DWBase!Domain.ConfigurationManagement" TimeGrain="Daily" SourceType="System!System.User" >
       <Relationships RelationshipType="System!System.User" TargetDimension="UserInformationLogDim" />
       </RelationshipFact>
      </Facts>
     </Warehouse>
     <LanguagePacks>
      <LanguagePack ID="ENU" IsDefault="true">
       <DisplayStrings>
        <DisplayString ElementID="DataWarehouse.User.History">
         <Name>Data Warehouse User Information Tracking</Name>
         <Description>This management pack adds a user information logging table to Data Warehouse.</Description>
        </DisplayString>
       </DisplayStrings>
      </LanguagePack>
     </LanguagePacks>
    </ManagementPack>



    Tuesday, February 21, 2012 9:49 PM
  • Okay, looking a little deeper in the blog post, I don't think you can transfer the _Log tables using custom dimensions. Custom dimensions rely on the class model of SCSM, and I was not able to find classes for the log, hence I assume this information is kept in SQL only.

    I thus recommend you look into the second option of "manually" pulling the information into the DW using T-SQL and SQL Agent.

    Wednesday, February 22, 2012 3:10 PM
  • I figured that might be the case.  It looks like I'll have to copy the EntityChangeLog table to data warehouse too so I can leverage the LastModified column to build a complete report.

    Its too bad that you can't leverage historical data on configuration items in DW.  It would be a nice thing to see in future versions.


    Allen Anderson Systems Analyst Arizona State University - OKED Knowledge Informatics

    Wednesday, February 22, 2012 6:09 PM
  • Here is the code I wrote to copy the needed tables:

    USE DWDataMart
    GO
      
    
    IF EXISTS (
      SELECT *
        FROM INFORMATION_SCHEMA.TABLES
         WHERE TABLE_NAME = 'MT_System$Domain$User_Log'
    
    )
       DROP TABLE [dbo].[MT_System$Domain$User_Log]
    
    IF EXISTS (
      SELECT *
        FROM INFORMATION_SCHEMA.TABLES
         WHERE TABLE_NAME = 'EntityChangeLog'
    )
       DROP TABLE [dbo].[EntityChangeLog]
       
    
        SELECT * INTO DWDataMart.dbo.EntityChangeLog FROM ServiceManager.dbo.EntityChangeLog
    	ALTER TABLE DWDataMart.dbo.EntityChangeLog ADD
    		CONSTRAINT PK_EntityChangeLog PRIMARY KEY(EntityChangeLogId)
    
    
    	SELECT * INTO DWDataMart.dbo.MT_System$Domain$User_Log FROM ServiceManager.dbo.MT_System$Domain$User_Log
    	CREATE NONCLUSTERED INDEX idx_MT_System$Domain$User_Log_BaseManagedEntity 
    		ON DWDataMart.dbo.MT_System$Domain$User_Log(BaseManagedEntityId ASC) WITH FILLFACTOR = 80
    
    	ALTER TABLE DWDataMart.dbo.MT_System$Domain$User_Log ADD
    		CONSTRAINT PK_MT_System$Domain$User_Log PRIMARY KEY(EntityChangeLogId), FOREIGN KEY(EntityChangeLogId)
    		REFERENCES DWDataMart.dbo.EntityChangeLog(EntityChangeLogId)
    GO
    
    IF OBJECT_ID ('MT_DomainUserLogvw', 'V') IS NOT NULL
    	DROP VIEW MT_DomainUserLogvw
    GO
    
    CREATE VIEW MT_DomainUserLogvw AS
    	SELECT 
    		UserLog.*,
    		ChangeLog.LastModified,
    		UserView.UserDimKey
    	FROM DWDataMart.dbo.MT_System$Domain$User_Log UserLog
    	JOIN EntityChangeLog ChangeLog ON UserLog.EntityChangeLogId = ChangeLog.EntityChangeLogId
    	JOIN UserDimvw UserView ON UserLog.BaseManagedEntityId = UserView.BaseManagedEntityId
    GO
      
    
    

    Creates the needed tables with their keys and indices.  It also creates a view.  I'm looking to running this every night with the agent.  I'll probably make some mods to this so that things are not running unnecessarily with the agent.  No need to re-create the view every time...


    Allen Anderson Systems Analyst Arizona State University - OKED Knowledge Informatics


    Monday, February 27, 2012 11:39 PM
  • Then here is the JOIN I use in my report that uses this LOG table to determine the correct department for the time in which the incident was created:

    SELECT 
    				UserName,
    				DisplayName,
    				Department = 
    				CASE 
    					WHEN (AffectedUserInfo.DeptRTS = '' OR AffectedUserInfo.DeptRTS IS NULL)
    				THEN
    					CASE 
    						WHEN (UserLog.LastModified <= MAX(WIAUFact.CreatedDate))
    					THEN NewDepartment
    					ELSE OldDepartment
    					END
    				ELSE AffectedUserInfo.DeptRTS
    				END,
    				WIAUFact.WorkItemDimKey
    
    			FROM UserDimvw AffectedUserInfo
    			
    			JOIN dbo.WorkItemAffectedUserFactvw WIAUFact ON AffectedUserInfo.UserDimKey = WIAUFact.WorkItemAffectedUser_UserDimKey
    			OUTER APPLY 
    			(
    				SELECT TOP 1
    					OldDepartment = Pre_Department_312201FE_C1B3_E95A_01DF_E132E9BD3EC9,		
    					NewDepartment = Post_Department_312201FE_C1B3_E95A_01DF_E132E9BD3EC9,
    					LastModified
    				FROM MT_DomainUserLogvw UserLog
    				WHERE
    					UserDimKey = AffectedUserInfo.UserDimKey
    					AND WIAUFact.CreatedDate >= UserLog.LastModified
    				GROUP BY LastModified, Post_Department_312201FE_C1B3_E95A_01DF_E132E9BD3EC9, Pre_Department_312201FE_C1B3_E95A_01DF_E132E9BD3EC9, UserDimKey
    				ORDER BY UserLog.LastModified DESC
    			) UserLog
    			GROUP BY WorkItemDimKey, CreatedDate, UserName, NewDepartment, OldDepartment, DisplayName, DeptRTS, LastModified

    The key here is the OUTER APPLY.  This is where the correct department is determined from the MT_UserLog table.  Seems to work.  Any coding recommendations here to make it more efficient? 


    Allen Anderson Systems Analyst Arizona State University - OKED Knowledge Informatics



    Monday, February 27, 2012 11:42 PM
  • Nice job! Thanks for sharing this.
    Tuesday, February 28, 2012 12:14 PM