SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


ETL Auditing


ETL Auditing

Author
Message
tobe_ha
tobe_ha
SSChasing Mays
SSChasing Mays (645 reputation)SSChasing Mays (645 reputation)SSChasing Mays (645 reputation)SSChasing Mays (645 reputation)SSChasing Mays (645 reputation)SSChasing Mays (645 reputation)SSChasing Mays (645 reputation)SSChasing Mays (645 reputation)

Group: General Forum Members
Points: 645 Visits: 384
Comments posted to this topic are about the item ETL Auditing
robertm
robertm
Mr or Mrs. 500
Mr or Mrs. 500 (538 reputation)Mr or Mrs. 500 (538 reputation)Mr or Mrs. 500 (538 reputation)Mr or Mrs. 500 (538 reputation)Mr or Mrs. 500 (538 reputation)Mr or Mrs. 500 (538 reputation)Mr or Mrs. 500 (538 reputation)Mr or Mrs. 500 (538 reputation)

Group: General Forum Members
Points: 538 Visits: 118
Great article. I use almost the exact same architecture and it works a treat!

The main benefit that I have found it being able to identify exactly what data in the data warehouse has been affected by a particular process and when.

The main difference in my implementation is that the Audit entity is more generic and leveraged by related audit subtypes. For example, I have an SSIS audit subtype as well as a TSQL subtype as these each have a separate set of attributes associated to them that I want to store.

This is invaluable when loading and consolidating data into a third normal form data warehouse from numerous sources. Several data marts can then be subsequently populated from this consolidated warehouse. The ability to quickly identify new and/or changed data in the warehouse when deciding what should move into the data mart(s) becomes extremely easy.

Lastly, even when Kimball Type-1 changes are implemented in the warehouse we move the original row off into an audit database which mean that if you need to roll back a load, even partially, it's possible to return data to it's original state prior to that process even if that included Type-1 updates!



Anipaul
Anipaul
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13393 Visits: 1407
Nice article. well written...



R M Buda
R M Buda
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1027 Visits: 306
This is a great article and a much needed idea.Smile

In my opinion something like this should have been built into SSIS as part of standard logging.
arup chakraborty
arup chakraborty
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1764 Visits: 365
Very useful article. Easy 2 understand.
vishal.gamji
vishal.gamji
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3160 Visits: 532
Nice article. I would go for extending an existing solution than re-inventing the wheel, which is - project real. ETL auditing is provided as code samples and well documented. Not exactly the perfect solution, but one can always extend on these solutions and make an SSIS template package and embed the custom auditing in that, so that any new package created will benefit from it.
ericb1
ericb1
SSC Eights!
SSC Eights! (998 reputation)SSC Eights! (998 reputation)SSC Eights! (998 reputation)SSC Eights! (998 reputation)SSC Eights! (998 reputation)SSC Eights! (998 reputation)SSC Eights! (998 reputation)SSC Eights! (998 reputation)

Group: General Forum Members
Points: 998 Visits: 337
great article, thanks. This is exactly what I am looking to do. I have several jobs here, and would like to be able to audit them. Is there some sample sql that can show me how to query this out of the audit table?
Pedro Perfeito
Pedro Perfeito
SSC-Enthusiastic
SSC-Enthusiastic (131 reputation)SSC-Enthusiastic (131 reputation)SSC-Enthusiastic (131 reputation)SSC-Enthusiastic (131 reputation)SSC-Enthusiastic (131 reputation)SSC-Enthusiastic (131 reputation)SSC-Enthusiastic (131 reputation)SSC-Enthusiastic (131 reputation)

Group: General Forum Members
Points: 131 Visits: 138
In my opinion you should replace auditing by logging...
Logging is what you wrote about, and auditing is usuall refered to each row in the pipeline and is incorporated in the datamart... audit a row represent information from each row imported from source and not about package. (Check Kimball sub-systems)

Regards,
Pedro
www.pedrocgd.blogspot.com
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search