Data lineage

[Cyriackpazhe]

How does Data lineage work? How is it lost? Can someone explain or provide links to resources. How can we maintain data lineage with treatas

[bhanu_gautam · Accepted solution]

@Cyriackpazhe Go through this module 

https://learn.microsoft.com/en-us/power-bi/collaborate-share/service-data-lineage

And this video -https://www.youtube.com/watch?v=IfX3Npn6Nm4

https://www.youtube.com/watch?v=Hqs0lJJq1YA&t=350s

Data lineage is a process that tracks the journey of data from its origin through various transformations to its final destination. It captures all relevant metadata and events associated with the data throughout its lifecycle, including the source of the data set, transformations performed, and the final data set created

[OwenAuger · Accepted solution]

Hi @Cyriackpazhe 

This would be my go-to article on this subject.

https://www.sqlbi.com/articles/understanding-data-lineage-in-dax/

My personal interpretation:

• In any DAX expression returning a table, if a column of the resulting table (let's call it an "expression column") is created by referencing a single column of a physical table without modifying the column values (let's call the physical table column the "source column"), then the expression column is given a "tag" which identifies the source column. This tag is referred to as the column's "lineage".
• Lineage of an "expression column" survives operations such as filtering or renaming of columns, but does not survive any operation that modifies the values in the column.
• Lineage also does not survive when an expression column has multiple source columns. For example, if you UNION tables with different source columns, lineage is broken (i.e. lost) for any columns where there is a conflict in lineage.
• Lineage is useful because it allows expression columns to have the same filtering effect as their corresponding source columns. Columns with no lineage have no filtering effect.
• Lineage survives sequences of operations as long as nothing is done to break lineage at any stage and each expression column maintains a single unadulterated source column (as described above).
• Table expressions can happily exist with columns whose source columns are from different physical tables. This can be useful when you need to construct a complex filter of some sort.
• The TREATAS function allows lineage to be assigned to any table expression. This is useful in cases where lineage has been lost earlier for some reason, or when lineage did not exist in the first place, but the resulting table expression needs to be used for filtering. TREATAS effectively lets you specify the source column for each expression column within a table expression.

Well, that's a bit of a brain dump. The SQLBI article above has various DAX examples but I could come up with some examples to post here if that's useful.

I'm sure the community members have some useful insights/comments to add as well!

[OwenAuger · Accepted solution]

Hi @Cyriackpazhe 

This would be my go-to article on this subject.

https://www.sqlbi.com/articles/understanding-data-lineage-in-dax/

My personal interpretation:

• In any DAX expression returning a table, if a column of the resulting table (let's call it an "expression column") is created by referencing a single column of a physical table without modifying the column values (let's call the physical table column the "source column"), then the expression column is given a "tag" which identifies the source column. This tag is referred to as the column's "lineage".
• Lineage of an "expression column" survives operations such as filtering or renaming of columns, but does not survive any operation that modifies the values in the column.
• Lineage also does not survive when an expression column has multiple source columns. For example, if you UNION tables with different source columns, lineage is broken (i.e. lost) for any columns where there is a conflict in lineage.
• Lineage is useful because it allows expression columns to have the same filtering effect as their corresponding source columns. Columns with no lineage have no filtering effect.
• Lineage survives sequences of operations as long as nothing is done to break lineage at any stage and each expression column maintains a single unadulterated source column (as described above).
• Table expressions can happily exist with columns whose source columns are from different physical tables. This can be useful when you need to construct a complex filter of some sort.
• The TREATAS function allows lineage to be assigned to any table expression. This is useful in cases where lineage has been lost earlier for some reason, or when lineage did not exist in the first place, but the resulting table expression needs to be used for filtering. TREATAS effectively lets you specify the source column for each expression column within a table expression.

Well, that's a bit of a brain dump. The SQLBI article above has various DAX examples but I could come up with some examples to post here if that's useful.

I'm sure the community members have some useful insights/comments to add as well!

[Cyriackpazhe]

Hi owen, your explanation was helpful, but could you post the examples

[OwenAuger · Accepted solution]

@Cyriackpazhe 

Sure, I've put together some examples in a set of dax.do queries here:

https://dax.do/WsgXRvl1IeQr4f/

All query results include a "Sales Amount" column.  I've set these up so that it's obvious when lineage is lost because Sales Amount is identical on all rows (since lineage is lost, no filter applies).

e.g.

Hope this helps!

Regards,

Owen

[bhanu_gautam · Accepted solution]

@Cyriackpazhe Go through this module 

https://learn.microsoft.com/en-us/power-bi/collaborate-share/service-data-lineage

And this video -https://www.youtube.com/watch?v=IfX3Npn6Nm4

https://www.youtube.com/watch?v=Hqs0lJJq1YA&t=350s

Data lineage is a process that tracks the journey of data from its origin through various transformations to its final destination. It captures all relevant metadata and events associated with the data throughout its lifecycle, including the source of the data set, transformations performed, and the final data set created