Strategies/Resources for implementing data lineage in an ETL process?

Question

I'm struggling to articulate my need here.

I've been tasked with developing a significant ETL process dealing with warehouse item costs in a major grocery chain using the full Microsoft Stack.

I'd like to be able to provide our finance team and auditors with the ability to trace the final results back through to the source systems, ideally with metadata like which cost calculations were used.

The Kimball Group and the Data Warehouse Toolkit book all hint at lineage (Subsystem 29!), but I am finding very few resources that help guide an actual implementation. There are a plethora of sites indicating that it is important and should be considered with data governance, and a few sites that are purely academic and go into the science of it but don't really provide practical guidance.

I'm likely not following best practices with this question, but I don't know how else to phrase it. As I said earlier, we are full Microsoft Stack, but I'm open to any resources even if for other tools.

Any and all help is appreciated. Thank you.

Answer 1

Lineage is sometimes implemented in the ETL tool, but the Microsoft stack doesn't really. Since you want to offer the insight to your end users it's probably not even a lot of help if your development environment offers it.

One way you could go about it, and something that I've used successfully in the past (even if it violates a few design principles) is to add a field to my fact tables indicating the source of the fact, which can then be exposed via drilldown or possibly even a fact dimension.

For example if you have a fact table which combines forecasts from erp as well as a CRM system you could have a column "factsource" or whatever which contains a concatenated field with sourcesystem|table|primarykey like this:

 sourceid        | customer    | amount
 ----------------------------------------------------
 CRM|forecast|41 | C001        | 1000
 ERP|quotation|6 | C002        | 5000

Then when a user uses the drilldown functionality in the cube client or some other mechanism to see the facts behind the transaction they could immediately see that the 5K comes from quotation number 6 in the ERP system.

Off course you would need to construct that value in your ETL system while loading the fact table.

Answer 2

If by "The full Microsoft stack" you are referring to the on-prem:

• SQL Server
• SSIS
• SSAS
• SSRS / PowerBI

Then you pretty much have to track lineage manually. None of these tools will help you.

It's possible they have something better in their cloud offerings. If you have access to their Hadoop distribution on Azure (HDInsight) then you can use tools like Apache Atlas to track data lineage.