Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

ETL Auditing Expand / Collapse
Author
Message
Posted Friday, December 12, 2008 12:22 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, January 30, 2012 6:49 AM
Points: 94, Visits: 359
Comments posted to this topic are about the item ETL Auditing
Post #618414
Posted Friday, December 12, 2008 2:53 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, December 11, 2013 3:04 AM
Points: 210, Visits: 115
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!





Post #618454
Posted Friday, December 12, 2008 4:06 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, July 17, 2014 10:36 PM
Points: 5,308, Visits: 1,378
Nice article. well written...


Post #618478
Posted Friday, December 12, 2008 4:46 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 8, 2014 6:02 AM
Points: 146, Visits: 238
This is a great article and a much needed idea.:)

In my opinion something like this should have been built into SSIS as part of standard logging.
Post #618504
Posted Friday, December 12, 2008 5:35 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, July 3, 2012 5:49 AM
Points: 418, Visits: 365
Very useful article. Easy 2 understand.
Post #618524
Posted Saturday, December 13, 2008 6:39 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Sunday, March 16, 2014 10:26 AM
Points: 536, Visits: 516
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.
Post #619203
Posted Wednesday, December 17, 2008 2:01 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, May 16, 2014 12:39 PM
Points: 93, Visits: 300
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?
Post #621609
Posted Tuesday, April 7, 2009 5:42 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, January 2, 2014 1:24 PM
Points: 5, Visits: 94
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
Post #691866
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse