Monitor / Audit your Data Warehouse load

One of the most important means of monitoring the run of your Data Warehouse load, is getting insight in the status of running jobs, the duration of the jobs (in history) and which jobs doesn't seem to end. An execution of a simple update script of an admin table before the start of a job and after the end of a job can result in a table like:

RunID	Process	Action	Object	New	Updated	Deleted	Load_start	Load_end
1	EDW - HRM	Publish HRM_cube	HRM_Montly.mdc				4/15/09 8:09:38 AM	4/15/09 8:10:02 AM
1	EDW - HRM	FTP HRM cube	HRM_Montly.mdc				4/15/09 8:09:22 AM	4/15/09 8:09:38 AM
1	EDW - HRM	Create HRM cube	HRM_Montly.mdc				4/15/09 8:09:21 AM	4/15/09 8:09:22 AM
1	EDW - HRM	Load new facts into aggregate table in Business Model	BSM.FCT_Hour_Monthly	11408	0	0	4/15/09 8:09:21 AM	4/15/09 8:09:21 AM
1	EDW - HRM	Delete existing facts from aggregate table in Business Model	BSM.FCT_Hour_Monthly	0	0	344	4/15/09 8:09:03 AM	4/15/09 8:09:21 AM
1	EDW - HRM	Load Dimension Supplier into BSM (Refresh)	BSM.DIM_Supplier	3	0	0	4/15/09 8:09:02 AM	4/15/09 8:09:03 AM
1	EDW - HRM	Load Dimension Organisational Unit into BSM (Refresh)	BSM.DIM_OrganisationalUnit	23	0	0	4/15/09 8:09:00 AM	4/15/09 8:09:02 AM
1	EDW - HRM	Load Dimension Employee into BSM (Refresh)	BSM.DIM_Employee	45632	0	0	4/15/09 7:59:20 AM	4/15/09 8:09:00 AM
1	EDW - HRM	Load Date Dimension into Business Model (Refresh)	BSM.DIM_Date	365	0	0	4/15/09 7:59:18 AM	4/15/09 7:59:20 AM
1	EDW - HRM	Load new and changed hour facts into ODS	ODS.FCT_Hour	45632	0	0	4/15/09 7:59:16 AM	4/15/09 7:59:18 AM
1	EDW - HRM	Load new and changed targets into ODS	ODS.FCT_Target	12	0	0	4/15/09 7:59:15 AM	4/15/09 7:59:16 AM
1	EDW - HRM	Load new and changed suppliers into ODS	ODS.DIM_Supplier	0	0	0	4/15/09 7:59:08 AM	4/15/09 7:59:15 AM
1	EDW - HRM	Load new and changed organisational units into ODS	ODS.DIM_OrganisationalUnit	0	1	0	4/15/09 7:58:58 AM	4/15/09 7:59:08 AM
1	EDW - HRM	Load new and changed employees into ODS	ODS.DIM_Employee	1	2	0	4/15/09 7:58:55 AM	4/15/09 7:58:58 AM
1	EDW - HRM	Transformation step: create hierarchy for organisational unit	STG.OrganisationalUnit	0	23	0	4/15/09 7:58:52 AM	4/15/09 7:58:53 AM
1	EDW - HRM	Transformation step: calculate productivity	STG.Hour	0	45632	0	4/15/09 7:58:49 AM	4/15/09 7:58:52 AM
1	EDW - HRM	Lookup surrogate key supplier	STG.Supplier	0	3	0	4/15/09 7:58:23 AM	4/15/09 7:58:49 AM
1	EDW - HRM	Lookup surrogate key organisational unit	STG.OrganisationalUnit	0	23	0	4/15/09 7:51:56 AM	4/15/09 7:58:04 AM
1	EDW - HRM	Lookup surrogate key employee	STG.employee	0	2456	0	4/15/09 7:51:28 AM	4/15/09 7:51:56 AM
1	EDW - HRM	Detect corrected hours	STG.Hour	0	35	0	4/15/09 7:51:24 AM	4/15/09 7:51:28 AM
1	EDW - HRM	Detect changed supplier attributes	STG.Hour	0	0	0	4/15/09 7:51:22 AM	4/15/09 7:51:24 AM
1	EDW - HRM	Detect changed organisational unit attributes	STG.OrganisationalUnit	0	1	0	4/15/09 7:51:18 AM	4/15/09 7:51:22 AM
1	EDW - HRM	Detect changed employee attributes	STG.Employee	2	0	0	4/15/09 7:51:15 AM	4/15/09 7:51:18 AM
1	EDW - HRM	Extract Monthly Targets	STG.Target	12	0	0	4/15/09 7:50:54 AM	4/15/09 7:51:15 AM
1	EDW - HRM	Extract Fact Hours	STG.Hour	45632	0	0	4/15/09 7:50:49 AM	4/15/09 7:50:52 AM
1	EDW - HRM	Extract Dimension Supplier	STG.Supplier	3	0	0	4/15/09 7:50:17 AM	4/15/09 7:50:49 AM
1	EDW - HRM	Extract Dimension Organisational Unit	STG.OrganisationalUnit	23	0	0	4/15/09 7:49:21 AM	4/15/09 7:50:17 AM
1	EDW - HRM	Extract Dimension Employee	STG.Employee	2456	0	0	4/15/09 7:49:20 AM	4/15/09 7:49:21 AM

Firstly, create an admin table:

-- Create MetaData layer

create schema mda

-- Create Admin table

CREATE TABLE [mda].[admin_run](

[Runid] [int] NULL,

[Process] [varchar](50) ,

[Action] [varchar](50) ,

[Object] [varchar](50) ,

[Inserted] [int] NULL,

[Updated] [int] NULL,

[Deleted] [int] NULL,

[LoadStart] [datetime] NULL,

[LoadEnd] [datetime] NULL

)

Secondly, create stored procedures to update the table:

CREATE PROCEDURE [mda].[PUpdate_Admin_Run_Start]

(

@Runid int

, @Process varchar(255)

, @Action varchar(255)

, @Object varchar(255)

)

AS

BEGIN

insert into mda.admin_run

(

RunID

, Process

, Action

, Object

, Inserted

, Updated

, Deleted

, LoadStart

)

values

(

@RunID

, @Process

, @Action

, @Object

, ''

, ''

, ''

, getdate()

)

END

CREATE PROCEDURE [mda].[PUpdate_Admin_Run_End]

( @RunId int

, @Action varchar(50)

, @Records_New int = 0

, @Records_Updated int = 0

, @Records_Deleted int = 0 )
AS
BEGIN
update mda.admin_run

set LoadEnd = getdate()

,Inserted = @Records_new

,Updated = @Records_updated

,Deleted = @Records_deleted

where RunId = @RunId

and LoadEnd is null

and Action = @Action
END

Below you find an example script which updates the admin table:

begin

declare @Records_inserted int

declare @Records_updated int

declare @Records_deleted int

declare @Runid int

declare @Process varchar(50)

declare @Action varchar(50)

declare @Object varchar(50)

declare @rows int

select @rows = count(*) from mda.admin_run

if @rows = 0 set @Runid = 1 else select @runid = max(runid) from mda.admi_run

set @Process = 'EDW - HRM'

set @Action = 'Extract Dimension Employee'

set @Object = 'STG.Employee'

exec mda.PUpdate_Admin_Run_Start @RunID,@Process,@Action,@Object

--load script

select getdate()

set @Records_Inserted = @@rowcount

exec mda.PUpdate_Admin_Run_End @RunID,@Action,@Records_Inserted, @Records_updated, @Records_deleted

end