User fact table in DW

lunedì 20 febbraio 2012 15:25

0

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
- Risposta
- Citazione

Tutte le risposte

lunedì 20 febbraio 2012 22:43

0
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.
- Contrassegnato come risposta hemirunner426 lunedì 27 febbraio 2012 23:56
- Risposta
- Citazione
lunedì 20 febbraio 2012 23:13

0

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
- Risposta
- Citazione
martedì 21 febbraio 2012 14:46

1
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.
- Proposto come risposta Andreas BaumgartenMVP, Moderator martedì 21 febbraio 2012 14:59
- Risposta
- Citazione

martedì 21 febbraio 2012 21:49

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>

Modificato hemirunner426 martedì 21 febbraio 2012 23:04 code edit

mercoledì 22 febbraio 2012 15:10

0
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.
- Contrassegnato come risposta hemirunner426 lunedì 27 febbraio 2012 23:55
- Risposta
- Citazione
mercoledì 22 febbraio 2012 18:09

0

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
- Risposta
- Citazione

lunedì 27 febbraio 2012 23:39

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

Modificato hemirunner426 lunedì 27 febbraio 2012 23:43

lunedì 27 febbraio 2012 23:42

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

Modificato hemirunner426 lunedì 27 febbraio 2012 23:45
Modificato hemirunner426 lunedì 27 febbraio 2012 23:55

martedì 28 febbraio 2012 12:14

0

Nice job! Thanks for sharing this.
- Risposta
- Citazione

User fact table in DW

Tutte le risposte

Prodotti

Risorse

Soluzioni

Aggiornamenti

Valutazioni

Siti correlati

Formazione

Certificazioni

Ulteriori risorse

Per prodotto

Altri collegamenti