Hi Daniel,

The problem here is how to look up the Audit_Id in your Audit table using one or more columns from your dimension or fact tables. You have the following information: Execution start time, Machine name, User name, Package name and Task name.

How you can relate this two table, good question. If you store the creation time of the columns (I always create a CretatedOn and UpdatedOn column for Dimension tables and CreatedOn column for Fact tables) you can make the following:

Get the Audit_Id where the CreateDT >Execution start time

But you must do it after an execution and before the next execution in order to obtain one and only one Audit_Id.

This approach does not work if you have different user, machines or packages running at the same time, it is just an idea and it seems to be not precise.

Other approach that definitely works is to drop your separated Audit table and add audit columns to each of your dimension tables and fact tables. It is redundant and space consuming but I think it not so bad at all.