topic Re: Data Lineage in Qlik Sense needs to improve. Current feature has some problem in App Development

Data Lineage in Qlik Sense needs to improve. Current feature has some problem

i_shamaev — Thu, 01 Jul 2021 12:36:10 GMT

We have some problem with data lineage functional. Below I try to explain you what I mean.

Intro: we have Qlik Sense Enterprise Server and November 2020 SR3 version of software. We work with different api services for getting data from there. And often we use cycles for data extract processes. Sometimes we see that size of our qvd files only increases but its files can have very small counts of data rows.

I researched this problem and came to the following conclusion: Qlik writes all the previous information from the moment of launch to the headers of the QVD files.

Let's take a look at the following example:

IterationTBL: LOAD * Inline [ iteration 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 ]; For iRowIterationTBL = 0 to NoOfRows('IterationTBL') - 1 LET vIterationNum = Peek('iteration',$(iRowIterationTBL),'IterationTBL'); Trace vIterationNum = $(vIterationNum); [LOG $(vIterationNum)]: LOAD '$(vIterationNum)' as iteration_no AutoGenerate 1; STORE [LOG $(vIterationNum)] into 'C:\Data_Temp\LOG $(vIterationNum).qvd' (qvd); DROP Table [LOG $(vIterationNum)]; NEXT iRowIterationTBL

As a result, we got 20 QVD files with different sizes.

And if we open header Lineage section we find that in LOG 1.qvd has following info:

<LineageInfo> <Discriminator>{STORE - 'C:\Data_Temp\LOG 1.qvd' (qvd)};</Discriminator> <Statement></Statement> </LineageInfo>

If you look at the information in the log 20.qvd, you can find all the preceding lines.

<LineageInfo> <Discriminator>AUTOGENERATE;</Discriminator> <Statement></Statement> </LineageInfo> <LineageInfo> <Discriminator>INLINE;</Discriminator> <Statement></Statement> </LineageInfo> <LineageInfo> <Discriminator>{STORE - 'C:\Data_Temp\LOG 1.qvd' (qvd)};</Discriminator> <Statement></Statement> </LineageInfo> <LineageInfo> <Discriminator>{STORE - 'C:\Data_Temp\LOG 10.qvd' (qvd)};</Discriminator> <Statement></Statement> </LineageInfo> <LineageInfo> <Discriminator>{STORE - 'C:\Data_Temp\LOG 11.qvd' (qvd)};</Discriminator> <Statement></Statement> </LineageInfo> <LineageInfo> <Discriminator>{STORE - 'C:\Data_Temp\LOG 12.qvd' (qvd)};</Discriminator> <Statement></Statement> </LineageInfo> <LineageInfo> <Discriminator>{STORE - 'C:\Data_Temp\LOG 13.qvd' (qvd)};</Discriminator> <Statement></Statement> </LineageInfo> <LineageInfo> <Discriminator>{STORE - 'C:\Data_Temp\LOG 14.qvd' (qvd)};</Discriminator> <Statement></Statement> </LineageInfo> <LineageInfo> <Discriminator>{STORE - 'C:\Data_Temp\LOG 15.qvd' (qvd)};</Discriminator> <Statement></Statement> </LineageInfo> <LineageInfo> <Discriminator>{STORE - 'C:\Data_Temp\LOG 16.qvd' (qvd)};</Discriminator> <Statement></Statement> </LineageInfo> <LineageInfo> <Discriminator>{STORE - 'C:\Data_Temp\LOG 17.qvd' (qvd)};</Discriminator> <Statement></Statement> </LineageInfo> <LineageInfo> <Discriminator>{STORE - 'C:\Data_Temp\LOG 18.qvd' (qvd)};</Discriminator> <Statement></Statement> </LineageInfo> <LineageInfo> <Discriminator>{STORE - 'C:\Data_Temp\LOG 19.qvd' (qvd)};</Discriminator> <Statement></Statement> </LineageInfo> <LineageInfo> <Discriminator>{STORE - 'C:\Data_Temp\LOG 2.qvd' (qvd)};</Discriminator> <Statement></Statement> </LineageInfo> <LineageInfo> <Discriminator>{STORE - 'C:\Data_Temp\LOG 20.qvd' (qvd)};</Discriminator> <Statement></Statement> </LineageInfo> <LineageInfo> <Discriminator>{STORE - 'C:\Data_Temp\LOG 3.qvd' (qvd)};</Discriminator> <Statement></Statement> </LineageInfo> <LineageInfo> <Discriminator>{STORE - 'C:\Data_Temp\LOG 4.qvd' (qvd)};</Discriminator> <Statement></Statement> </LineageInfo> <LineageInfo> <Discriminator>{STORE - 'C:\Data_Temp\LOG 5.qvd' (qvd)};</Discriminator> <Statement></Statement> </LineageInfo> <LineageInfo> <Discriminator>{STORE - 'C:\Data_Temp\LOG 6.qvd' (qvd)};</Discriminator> <Statement></Statement> </LineageInfo> <LineageInfo> <Discriminator>{STORE - 'C:\Data_Temp\LOG 7.qvd' (qvd)};</Discriminator> <Statement></Statement> </LineageInfo> <LineageInfo> <Discriminator>{STORE - 'C:\Data_Temp\LOG 8.qvd' (qvd)};</Discriminator> <Statement></Statement> </LineageInfo> <LineageInfo> <Discriminator>{STORE - 'C:\Data_Temp\LOG 9.qvd' (qvd)};</Discriminator> <Statement></Statement> </LineageInfo>

Due to the fact that a qlik accumulates metadata in itself and cannot reset lineage information at subsequent stages, a situation may arise when qvd with 1 data line has 31 MB size.

Is there some way to reset the lineage metadata during script execution?
@Sonja_Bauernfeind @Yaaser_Ahmed_Khan @Daniel_Seo @Damien_V

Re: Data Lineage in Qlik Sense needs to improve. Current feature has some problem

AlbinS — Thu, 20 May 2021 06:49:49 GMT

I had the same problem and currently solve it with an extra store/load step

store each file as a csv in a separate folder
In a new app scheduled for reload after the original app, load each csv and store it as a qvd in the desired folder.

Lineage is not stored in the csv-files and your qvd's will be small!

Note that you cannot store/load from the csv's in the same app as the lineage is inherited from the app reload, a separate app is needed.

Also, there seems to be an option in the QMS under engine settings where you can choose not to allow data lineage. I don't know what impact it has on other parts so before i know i don't want to try.

Re: Data Lineage in Qlik Sense needs to improve. Current feature has some problem

pabloviera — Tue, 01 Jun 2021 10:12:19 GMT

Just to add a +1 here, a script sentence to reset the lineage when looping is much needed for big extract+transform+save apps.

Regards

Re: Data Lineage in Qlik Sense needs to improve. Current feature has some problem

MaWi — Mon, 14 Jun 2021 13:40:46 GMT

+1 ... script command to reset data lineage while looping is urgently required

Re: Data Lineage in Qlik Sense needs to improve. Current feature has some problem

i_shamaev — Tue, 29 Jun 2021 07:03:06 GMT

Up 😐
We turned off data lineage in setting. I don't know why qlik sense tech support don't read messages in community.

Re: Data Lineage in Qlik Sense needs to improve. Current feature has some problem

Peter-Lawlor — Thu, 01 Jul 2021 06:28:17 GMT

Vote up here from me!

This would be a great adage to the tool as a whole.

Re: Data Lineage in Qlik Sense needs to improve. Current feature has some problem

i_shamaev — Thu, 01 Jul 2021 12:37:12 GMT

FYI @Sonja_Bauernfeind @Yaaser_Ahmed_Khan @Daniel_Seo @Damien_Villaret

Re: Data Lineage in Qlik Sense needs to improve. Current feature has some problem

i_shamaev — Fri, 02 Jul 2021 07:17:11 GMT

If you disable data lineage in Qlik Sense you will lose all comments for table.qvd and its fields 😢

Re: Data Lineage in Qlik Sense needs to improve. Current feature has some problem

i_shamaev — Mon, 09 Aug 2021 10:50:09 GMT

Re: Data Lineage in Qlik Sense needs to improve. Current feature has some problem

marc_behrens — Mon, 16 Aug 2021 07:53:15 GMT

Re: Data Lineage in Qlik Sense needs to improve. Current feature has some problem

i_shamaev — Mon, 16 Aug 2021 20:10:31 GMT

I've created https://community.qlik.com/t5/Ideation-Discussions/Create-a-mechanism-for-resetting-the-cache-for-the-data-lineage/m-p/1828911 Idea. Please like it! 😉