2

Logging on data cleansing procedure

 

Already asked by Peter Jensen with 9 upvotes in 2017.

No comments from any TX Employees so far.
So even today I am forced to modify the cleansing procedure and add my own logging...
It would nice to be able to have an option per table by which you can enable extended data cleansing logging.
This would log the start and end date of each step in this procedure to a dedicated log table. (Have to do this when a proc starts throwing timeouts or takes a long time all of sudden)

I think this takes about 15 minutes to implement into your code base.
At the start of the proc you check if the logging table exists else you create one.
Before each step in the proc you store the datetime in a variable.
At the end of each step you write a record to the log table and reset the start time again as such:


IF OBJECT_ID(N'etl.HAWEmployeesPlanningMT_DataCleansingLog', N'U') IS NULL AND @enableExtendedLogging = 1
BEGIN
CREATE TABLE etl.HAWEmployeesPlanningMT_DataCleansingLog (
Id BIGINT IDENTITY(1,1) NOT NULL,
Version BIGINT NOT NULL,
Step NVARCHAR(1000) NOT NULL,
DateTimeStart DateTime,
DateTimeStop DateTime,

CONSTRAINT [PK_bb573581_2114_4b3e_b1bd_042488deac10] PRIMARY KEY CLUSTERED
(
Id ASC
)
)
END


SET @DateTimeStart = GETDATE()
SET @step = 'Keep field values up-to-date: detect lookup value changes'
-- Keep field values up-to-date: detect lookup value changes
/* TX generated SQL */

IF @enableExtendedLogging = 1
INSERT INTO etl.HAWEmployeesPlanningMT_DataCleansingLog(version, Step, DateTimeStart, DateTimeStop) VALUES(@version, @step, @DateTimeStart , GETDATE())


SET @DateTimeStart = GETDATE()
SET @step = 'Update conditional lookup fields (Many lookups, Take the first value):''CustomerHQId'',''CustomerCode1'',''IsTardyCancellation'',''IsExaminationsFinished'',''DateExamination'',''ExaminationPart1ResearchCode'',''MedicalExaminationCode'',''ExaminationTypeCode'',''MedicalExamination_IsPeriodical'',''CategoryCode'',''SubmissionCategory'',''DateInFunction'',''DateOutService'',''PlanningTimeCell_Time'',''PlanningEntityDate'''
-- Update conditional lookup fields (Many lookups, Take the first value):
/* TX generated SQL */

IF @enableExtendedLogging = 1
INSERT INTO etl.HAWEmployeesPlanningMT_DataCleansingLog(version, Step, DateTimeStart, DateTimeStop) VALUES(@version, @step, @DateTimeStart , GETDATE())


https://support.timextender.com/hc/en-us/community/posts/115011859306-Logging-during-Cleansing-Stored-Procedure 

 

3 comments

Please sign in to leave a comment.