Visualize data lineage

[Anonymous]

Hi there!

I am trying to visualize the data lineage of certain attributes. The goal of the assignment is to determine what part of the data lineage still needs to be done. This is the case:

I have four different tables all related to each other that represent the entire data lineage of the attributes in table 1. For example:

Table 1 gives an overview of all attributes that I need the data lineage from. In this example I picked one of the attributes (Value of Product).

Table 2 gives me all attributes from which the value in table 1 retrieves its data. For example: (table1[Value of Product]) gets the data from the combination of (table2[attribute100]), (table2[attribute101]), (table2[attribute102]), etc.

Table 3 does the same as table 2 but for the attributes of table 2. For example: (table2[attribute100]) gets the data from (table3[attribute_x]), (table3[attribute_y]). However, it is also possible that there are blanks. This means that there is no known data lineage for the attribute in table 2 (! this means there is no data, there are no blanks in the original table).

Table 4 (source system) does the same as table 2 and 3 but it refers to the system level. For example: (table3[attribute_x]) retrieves its data from both (table4[System_1]) and (table4[System_2]).

All with all, the data looks like this (below):

Now I want to know how to visualize certain things:

1. How to automatically visualize this data into the tree I now made myself.
2. The count of the attributes in table2 that do not have a value in table3 (in this example the count should be 6)
3. The count of the attributes in table2 that do have a value in table 3 (in this example the count should be 6)
4. The count of the distinct systems in table4 (in this example the count should be 2), note: when I use the distinctcount option it gives me the count of all the systems in table4 without taking the filter (table1=value of product) into consideration.

I hope someone can help 🙂

Table 1 (attribute level)	Table 2 (attribute level)	Table 3 (attribute level)	Table 4 (source system level)
Value of product	Attribute100	Attribute_x	System_1
Value of product	Attribute100	Attribute_x	System_2
Value of product	Attribute100	Attribute_y	System_2
Value of product	Attribute101
Value of product	Attribute102	Attribute_z	System_1
Value of product	Attribute103
Value of product	Attribute104
Value of product	Attribute105	Attribute_x	System_1
Value of product	Attribute105	Attribute_x	System_2
Value of product	Attribute105	Attribute_y	System_2
Value of product	Attribute106
Value of product	Attribute107
Value of product	Attribute108	Attribute_x	System_1
Value of product	Attribute108	Attribute_x	System_2
Value of product	Attribute108	Attribute_y	System_2
Value of product	Attribute109	Attribute_z	System_1
Value of product	Attribute110	Attribute_x	System_1
Value of product	Attribute110	Attribute_x	System_2
Value of product	Attribute111

[Anonymous]

You can try Sankey diagram to show visual data linage, I think it is the closest match for your requirement.

To use sankey you have to transform your data in source and destination columns(or nodes) like shown below, and add a dummy Weigh column.

To transform your data simply follow this logic : All the nodes which are acting as a source for other nodes will be listed in source column and all the nodes which are acting as a destination for other nodes will be listed in Destination column.

(e.g. in your data Colum Table 1 (attribute level) is source for column Table 2(attribute level) and column Table 2 is source for Table 3 so on and so forth. So Table1 (attribute level)  and Table2 (attribute level) will appear in source column list and Table2 (attribute level)  and Table3 (attribute level) will appear in destination column list)

Source	Destination	Weigh
Value of product	Attribute100	1
Value of product	Attribute100	1
Value of product	Attribute100	1
Value of product	Attribute101	1
Value of product	Attribute102	1
Value of product	Attribute103	1
Value of product	Attribute104	1
Value of product	Attribute105	1
Value of product	Attribute105	1
Value of product	Attribute105	1
Value of product	Attribute106	1
Value of product	Attribute107	1
Value of product	Attribute108	1
Value of product	Attribute108	1
Value of product	Attribute108	1
Value of product	Attribute109	1
Value of product	Attribute110	1
Value of product	Attribute110	1
Value of product	Attribute111	1
Attribute100	Attribute_x	1
Attribute100	Attribute_x	1
Attribute100	Attribute_y	1
Attribute102	Attribute_z	1
Attribute105	Attribute_x	1
Attribute105	Attribute_x	1
Attribute105	Attribute_y	1
Attribute108	Attribute_x	1
Attribute108	Attribute_x	1
Attribute108	Attribute_y	1
Attribute109	Attribute_z	1
Attribute110	Attribute_x	1
Attribute110	Attribute_x	1
Attribute_x	System_1	1
Attribute_x	System_2	1
Attribute_y	System_2	1
Attribute_z	System_1	1
Attribute_x	System_1	1
Attribute_x	System_2	1
Attribute_y	System_2	1
Attribute_x	System_1	1
Attribute_x	System_2	1
Attribute_y	System_2	1
Attribute_z	System_1	1
Attribute_x	System_1	1
Attribute_x	System_2	1

The output of Sankey diagram is shown below:

[v-frfei-msft · Accepted solution]

Hi @Anonymous ,

Based on my research, There is no such kind of visual in power bi currently. I suggest you to use decomposition tree to work around.

The decomposition tree visual in Power BI lets you visualize data across multiple dimensions. It automatically aggregates data and enables drilling down into your dimensions in any order.