Modify content in DWDataMart Possible?

Brian_Wiest

Management has asked if it is possible to update content in the data warehouse. 
Use case = Some incidents the analyst left the classification as Null
Management would like the value updated. 
Is this possible? Will is affect any other SQL processes?
Thanks

Conner_Wood

You can indeed update the DWDataMart, personally I recommend to update both DWRepository and then DWDataMart.  Cause DWDataMart pulls from DWRepository.  "But this doesn't follow ITIL standards" .... and Microsoft does?!?    [I can see it now: "No comment" "Yeah, you don't archive comments Microsoft!" "That's not what we meant" "Yeah well, you don't archive comments and you change data over time by deleting data in the archive so ITIL means nothing to you."]

In regards to classification on Incidents, you will need to run the query on each base table that makes up the [dbo].[IncidentDimvw] view ... Luckily in this case it's just a single table "IncidentDim" which is much easier to work with than relationship tables.

So find the EnumTypeId from [dbo].[IncidentClassificationvw] that matches your Enumeration you want to set on those tickets that have classification as "NULL".  This query should help you achieve that.

USE [DWRepository];
--USE [DWDataMart];

SELECT IRClassification.[IncidentClassificationId] AS 'EnumDimKey'
      ,IRClassification.[ID] AS 'EnumInternalID'
      ,DS_IRClassification.[DisplayName] AS 'EnumDisplayName'
  FROM [dbo].[IncidentClassificationvw] IRClassification (nolock)

  JOIN [dbo].[DisplayStringDimvw] DS_IRClassification (nolock)
  ON IRClassification.[EnumTypeId] = DS_IRClassification.[BaseManagedEntityId]
  AND DS_IRClassification.[LanguageCode] = 'ENU'

You should find and mark down the data for both DWRepository and DWDataMart, in case DWRepository uses a different EnumDimKey, that'd be embarrassing and quite the mess.

The final part is of course updating the data in the [dbo].[IncidentDim] Table, mainly the [Classification] and [Classification_IncidentClassificationId] parts WHERE [Classification_IncidentClassificationId] IS NULL


Let's say you want EnumDimKey 225 as [Classification_IncidentClassificationId] and therefore it's corresponding EnumInternalID Enum.a5779267230a4fabb92cc3e808c07038 as [Classification].  Let's assume it's the same on both DWRepository and DWDataMart, but you need to check this, OK? OK, cause it's up to you to check it.

Obviously you should make a backup of the databases or have a way to roll-back before proceeding with the following SQL UPDATE query which you should use accordingly on each database with correct values:

USE [DWRepository];
--USE [DWDataMart];


UPDATE [dbo].[IncidentDim]
SET [Classification_IncidentClassificationId] = 225
   ,[Classification] = 'Enum.a18852e70f2b4aa4b69b495616ca0b6d'
WHERE [Classification] IS NULL

Now, you may want to take into consideration what this means with you being able to alter archived data, you don't want to become the fall guy that someone else can say "Well, he got rid of how many tickets I was doing, I was doing more, it's his fault, bllllleeeeeerrrrghhhhhhh!!!!" or some other similar issue.  I suggest you say you won't alter anything unless you get it in writing, you can't disprove a negative, but you can show that you are only altering things when approved.  You can see the trust issue here, "how do I know you secretly didn't behind my back, blllleeeeeerrrrrghghhhhhhh" but hey, let me know if you solve that one!

Billy_Wilson

If those incidents have already been groomed out of the Active database, it shouldn't be an issue to set those values. If they are still in the Active db, the etl process will most likely reset the fields to null.

That being said, I'd highly recommend testing this first   Data Warehouse issues are no fun.

Conner_Wood

You can indeed update the DWDataMart, personally I recommend to update both DWRepository and then DWDataMart.  Cause DWDataMart pulls from DWRepository.  "But this doesn't follow ITIL standards" .... and Microsoft does?!?    [I can see it now: "No comment" "Yeah, you don't archive comments Microsoft!" "That's not what we meant" "Yeah well, you don't archive comments and you change data over time by deleting data in the archive so ITIL means nothing to you."]

In regards to classification on Incidents, you will need to run the query on each base table that makes up the [dbo].[IncidentDimvw] view ... Luckily in this case it's just a single table "IncidentDim" which is much easier to work with than relationship tables.

So find the EnumTypeId from [dbo].[IncidentClassificationvw] that matches your Enumeration you want to set on those tickets that have classification as "NULL".  This query should help you achieve that.

USE [DWRepository];
--USE [DWDataMart];

SELECT IRClassification.[IncidentClassificationId] AS 'EnumDimKey'
      ,IRClassification.[ID] AS 'EnumInternalID'
      ,DS_IRClassification.[DisplayName] AS 'EnumDisplayName'
  FROM [dbo].[IncidentClassificationvw] IRClassification (nolock)

  JOIN [dbo].[DisplayStringDimvw] DS_IRClassification (nolock)
  ON IRClassification.[EnumTypeId] = DS_IRClassification.[BaseManagedEntityId]
  AND DS_IRClassification.[LanguageCode] = 'ENU'

You should find and mark down the data for both DWRepository and DWDataMart, in case DWRepository uses a different EnumDimKey, that'd be embarrassing and quite the mess.

The final part is of course updating the data in the [dbo].[IncidentDim] Table, mainly the [Classification] and [Classification_IncidentClassificationId] parts WHERE [Classification_IncidentClassificationId] IS NULL


Let's say you want EnumDimKey 225 as [Classification_IncidentClassificationId] and therefore it's corresponding EnumInternalID Enum.a5779267230a4fabb92cc3e808c07038 as [Classification].  Let's assume it's the same on both DWRepository and DWDataMart, but you need to check this, OK? OK, cause it's up to you to check it.

Obviously you should make a backup of the databases or have a way to roll-back before proceeding with the following SQL UPDATE query which you should use accordingly on each database with correct values:

USE [DWRepository];
--USE [DWDataMart];


UPDATE [dbo].[IncidentDim]
SET [Classification_IncidentClassificationId] = 225
   ,[Classification] = 'Enum.a18852e70f2b4aa4b69b495616ca0b6d'
WHERE [Classification] IS NULL

Now, you may want to take into consideration what this means with you being able to alter archived data, you don't want to become the fall guy that someone else can say "Well, he got rid of how many tickets I was doing, I was doing more, it's his fault, bllllleeeeeerrrrghhhhhhh!!!!" or some other similar issue.  I suggest you say you won't alter anything unless you get it in writing, you can't disprove a negative, but you can show that you are only altering things when approved.  You can see the trust issue here, "how do I know you secretly didn't behind my back, blllleeeeeerrrrrghghhhhhhh" but hey, let me know if you solve that one!

Conner_Wood

I should mention that it is rather lucky you don't have to deal with the "DurationFact" crap.  Such as if you were going to update a status on a ticket that to ensure status history remained accurate you would need to also set the "FinishDateTime" and calculate duration and any other important fields on the latest status for that ticket and then insert a new row with the StartDateTime.... I dunno about InsertedBatchId and UpdatedBatchId, but I think you can see it's quite the nightmare!