8. Visualizing dependencies (lineage)

Lineage is something that you almost get for free when you are using meta data based ETL generation. Lineage tells you what route your data has followed in order to get into your final report. This route can be visualized. We can do this in order to:

1. Help the betl developer in analysing issues. E.g. if you suspect a measure to be wrong, or you suspect that you are missing some data, you want follow the route back to the source to look for errors. Visualization can help in this task.

2. GDPR reporting. Every object and column can be given a privacy_level property. When combining this with the routing information you can construct reports that tell you which users are allowed to see which (privacy related) information. BETL also includes reading meta data from SSAS tabular cubes.

This tutorial will show you how to visualize the routing (nr 1 above) using force directed graphs in power BI.

Setting up

1. Install Power BI desktop. You can use this for free.

Refresh dependency meta data

Dependencies are stored in dbo.obj_dep. Currently dependencies are not refreshed automatically, so you need to refresh them from time to time.

-- let's start with a fresh empty table for this tutorial
truncate table betl.dbo.obj_dep

-- get all dependencies for [My_Staging].[AW].[Culture]
exec betl.dbo.get_dep '[My_Staging].[AW].[Culture]'

-- get all dependencies for [My_Staging].[AW]' 1 level down the object tree
exec betl.dbo.get_dep @full_obj_name = '[My_Staging].[AW]'
, @dependency_tree_depth =0
, @obj_tree_depth = 1

-- show dependencies
select * 
from betl.dbo.obj_dep_ext

-- get all dependencies for all databases 
exec betl.dbo.reset
--exec betl.dbo.debug

-- first make sure that object tree is up to date
exec betl.dbo.refresh @full_obj_name = '[LOCALHOST]'
, @obj_tree_depth = 3

-- next find dependencies for all tables and views under all databases and schemas in localhost. might take some time. 
exec betl.dbo.get_dep @full_obj_name = '[LOCALHOST]'
, @dependency_tree_depth =0
, @obj_tree_depth = 3

Next open betl_dep_graph.pbix using Power BI Desktop and select My_RDV.dbo.hub_Center and after this My_Staging.NF.stgh_Center

It should look like this: