Home » How is the Audit table helpful in DataWarehouse Development Cycle

How is the Audit table helpful in DataWarehouse Development Cycle

How is the Audit table helpful in DataWarehouse Development Cycle?

The answer looks obvious which is debugging the package but what kind of information is normally loaded into a Audit Table and how it is helpful is a important topic.
Let us see some of the Audit Table columns helpful in our Audit table created here.

1. Using variable to populate the Audit key from master to child:
• When individual packages are executed from master package, pass the Auditkey to individual packages from master copy to populate the parentAuditkey column in Audit table designed.
• This way, we will have reference of parentAuditkey under which child id running.
• For doing this on Child packages, right click on package configurations and Enable package configuration for the variable by clicking add button. The value property needs to be selected for variable.
2. Auditkey and parentAuditkey provides information about master and child packages run together and if on the whole the execution of all packages is successful or not in the column SuccessfulProcessingInd (=’Y’) which needs to be populate in master package after all the child package executions for all packages from master and child using Auditkey in master package.
For Example:
End of Master Package:
UPDATE [DimAudit]
SET ExecStopDT = SYSDATETIME()
,SuccessfulProcessingInd=’Y’
WHERE AuditKey = ?
;

End of Child Package:

UPDATE [DimAudit]
SET ExecStopDT = SYSDATETIME()
, ExtractRowCnt = @RC_Xtrct
,TableFinalRowCnt = @RowCount
,ErrorRowCnt = @RC_Error
,SuccessfulProcessingInd=’Y’
WHERE AuditKey = @AuditKey
;

3. [ExecStartDT],[ExecStopDT]will be helpful in finding time taken to execute individual packages as well as master package.
1. [ErrorRowCnt],[TableInitialRowCnt],[TableFinalRowCnt]columns are for checking for any data errors in execution.
2. [InsertRowCnt],[UpdateRowCnt] are helpful in finding number of new rows inserted versus updated rows(already existing data).

1 thought on “How is the Audit table helpful in DataWarehouse Development Cycle”

  1. Ahaa, its nice conversation on the topic of this post at this place at this weblog, I have read all that, so at this time me also commenting at this place.| Eleanor Archie Hoye

Leave a Reply

Your email address will not be published. Required fields are marked *