Qlik Community

QlikView Documents

Documents for QlikView related information.

Lineage / Dependency Analyzer for QlikView

v_petrenko
New Contributor III

Lineage / Dependency Analyzer for QlikView

A free QV App that allows you to visualize your solution as a system of related elements.

2016-07-04_205123.png


The application scans your QV environment and automatically discovers dependencies between QVW Apps, QVD Tables, Flat files etc.

Then it shows you what the properties of the system are: it's goals (i.e. final user analytical apps), inputs and outputs of the elements, the complexity of direct and indirect relations. All in form of a single image - graph. And in lists of course.

Search and navigate the dependencies in an associative manner

Just filter any Entity of interest and instantly find out which other Entities depend on this one, even if they lay far behind in a data flow. So you know where a collision would rise if you made any harmful changes (say removing a field) to this particular Element.

Analyze the lineage of your data

When facing a question like "what exact data sources are you using for this specific QVW App?", you'll be ready to answer it in seconds. Just make a search in Entities listbox and filter out the one, then filter Ancestor relations.
Now you have all the necessary information - a list of all sources and transformations involved in data flows as well as their abstract scheme on the graph.

Get a visibility into data structures

Simply said, data in a QlikView solution is organised into Data Models (QVW) plus Data Files (QVD/Flat files) and Tables plus Fields inside them.
The application helps you find out similarities and differences and relations between those mentioned. For example: which fields are most commonly used and which - least? Which App has the widest Fact table? If you are to make changes to a field - what Apps need to be considered? And so on.


Known limitations and workarounds

1. If you facing the absence of lineage metadata in particular QVWs and workaround suggested on FAQ sheet doesn't suit you please have a look at Rob's alternative approach: https://community.qlik.com/docs/DOC-16993#comment-47699

2. If you need to add more than 5 folders for scanning, place 5th 6th and consequent folders all together into Folder 5 inputbox with the following separator:

','05|

Example:

\\MyShare\MyFolder5','05|\\MyShare\MyFolder6

(I know it's awkward and counter-intuitive but it works)


The Script code

The script is available at GitHub on the following repository: GitHub - vasiliy-petrenko/QlikView-Lineage-Dependency-Analyzer‌. Please feel free to learn and contribute!


Change Log

2018-08-13 an issue with qvw's lineage metadata misbehaviour was fixed. Only environments with QlikView 12.20 SR4+ were affected.

2018-07-30 BNF tag was added to the script for the app being compatible with QV 12.20 (November 2017 and newer).

2017-07-13 an issue with Date Masks misbehaviour was fixed. Only environments with QlikView 12.10 SR3 (and possibly following releases) were affected.

2016-11-18 the script is now open and available at GitHub (see the link above).

2016-09-02 a simple workaround is found for situations when there is a need to add more than 5 folders for scanning (thank you tony.hollywood-qi‌)

2016-07-29 minor fixes in interface (thank you pcammaert)

2016-07-28 minor fixes based on user feedback (thank you thabang_petje)


Tags (2)
Attachments
Comments
thornofcrowns
Valued Contributor II

Great piece of work, many thanks!

srchilukoori
Valued Contributor

This is a very useful application. Thank you.

santiago_respane
Valued Contributor

Excellent application!

Thanks a lot for sharing!

In my case it reads wrong the sources when using SQL Server, unfortunately the script is hidden and password protected so it wont be very useful to me if can't fix it.


If would be great for developers to be able to view and modify the script (share the password) in order to fix the errors and make it work for each case.

Thanks anyway!

Great contribution!

Really nice and useful app. A couple of comments:

1) Note that the lineage data is currently missing in QV12 generated QVWs and QVDs. This is a QV bug.

2) I'm curious about your suggestion in the FAQ about losing metadate for DROPed tables. Is is the field data you are trying to preserve? If so, isn't that available in the generated QVD?

Excellent work!

-Rob

v_petrenko
New Contributor III

Santiago Respane wrote:

In my case it reads wrong the sources when using SQL Server

Santiago, it would be better if you share with me what kind of problems you've faced and help me make necessary improvements to the app than if you try to parse lot of russian-language commented code by yourself.

Please don't hesitate contacting me via email. Thanks for sharing your thoughts!

v_petrenko
New Contributor III

Rob Wunderlich wrote:

1) Note that the lineage data is currently missing in QV12 generated QVWs and QVDs. This is a QV bug.

2) I'm curious about your suggestion in the FAQ about losing metadate for DROPed tables. Is is the field data you are trying to preserve? If so, isn't that available in the generated QVD?

Thanks Rob, I've tested the app with QV 12 SR2, everything worked fine, may be in following SRs it doesn't. Also there were rare occasions when QV didn't store meta-data in QVW. I haven't been able to locate the exact reason, but moving QVW to any different location and then reloading from there is the trick that helps.

The thing about dropping tables is this: if a table is loaded and then dropped, no lineage metadata is stored in QVW about this table (and that is reasonable to my mind). But it's not a rare operation for QVD Generators and other QV apps in a lot of QV solutions I've seen, so I suggested a simple workaround using pseudo-Truncate operation.

isorinrusu
Contributor III

Hi Vasily, following on Rob's second point:

Wouldn't it be better if you got the qvw -> qvd relationship from qvds instad of qvw? Instead of asking a qvw what qvds it creates, you could ask qvds what qvws created them.

v_petrenko
New Contributor III

Sorin Rusu wrote:

Wouldn't it be better if you got the qvw -> qvd relationship from qvds instad of qvw? Instead of asking a qvw what qvds it creates, you could ask qvds what qvws created them.

Thanks for reply Sorin. Certainly we can "ask" qvds of their "qvw->qvd" relations, but no qvd can "tell" which qvws use it, so we need to "ask" qvws directly. And if a qvw (not necessarily QVD Generator!) doesn't keep some parts of it's lineage metadata because of dropping tables than it's not possible to determine the complete picture of overall dependencies on qvds.

Vasily,

"some parts of it's lineage metadata because of dropping tables than it's not possible to determine the complete picture of overall dependencies on qvds"

Not to be argumentative, but I produce a similar scanner app for my customers and I don't encounter this issue. Every file loaded, even if subsequently dropped, produces a <LineageInfo> element in the QVW. Is that what you are using for lineage or some other path?

I only press this because I think your suggestion to maintain empty tables for the purpose of metadata brings several bad side effects.

I ran your scanner against a QVW that loaded and dropped a QVD, and as you warned, it did not report the QVD as input. However, my scanner does report it as input, so it's possible. Perhaps you are driving your list of tables from the data model instead of the <LineageInfo> elements? I can't see your script.

maxim_senin
Contributor III

Hi Vasily,

Thanks for the app. As you wrote in the app description, could you please provide its sources?

I would try grouping timed files by constant part of their names since we have a lot of a huge numer of them.

Thanking you in advance.

Best regards,

Maxim

Version history
Revision #:
1 of 1
Last update:
‎07-04-2016 01:40 PM
Updated by: