article Qlik Sense App: QVD Meta Data Viewer in Qlik Sense Documents

Qlik Sense App: QVD Meta Data Viewer

stevedark — Mon, 23 Apr 2018 11:50:24 GMT

There is a large amount of useful information that is stored in the header of QVD files. This gives information about the data in the file, when it was created and which app created it. This data is in an XML format and can be viewed in a text editor.

Being XML it can also be loaded in to Sense and viewed there.

This blog post describes how this can be done and gives some background to the application uploaded here:

https://www.quickintelligence.co.uk/qlik-qvd-xml-header-viewer

The application enumerates around a number of libraries and reads the header information from each QVD in those libraries. It persists the information it finds to a QVD and also creates a timestamped archive - so you can see how your QVDs are changing over time.

You will need to configure the app to find your libraries by editing the load script, just list the libraries in this INLINE load:

If you have any questions regarding getting this app set up please post in the comments below.

There is a QlikView version of this application also, which you can find here:

QlikView App: QVD Meta Data Viewer

Both versions of the app also include our popular Data Profiler, which you can find more information about here:

Qlik Sense App: Generic Data Profiler

I hope that you find the application useful. You will find other applications that I have uploaded under my profile on QlikCommunity or on our Downloads page..

Steve

http://www.quickintelligence.co.uk/qlikview-blog/

Re: Qlik Sense App: QVD Meta Data Viewer

sergeyko — Mon, 08 Mar 2021 23:31:17 GMT

This app looks awesome and I think it embodies a few key concepts that will enable us to finally start reusing master measures and dimensions across apps. This link can go a long way, thank you @stevedark !

Quick question related to this QVD metadata app - is there a view you can suggest to start monitoring changes to QVDs over time. I would be interested in tracking certain abnormalities as relates to QVDs and their fields. Some examples include QVD size changing by x%, count of unique field values changing by x%, count of blank values in a field changing by x%, etc.

I feel like I have all the data with your awesome app but I could use some help building automation to monitor all the fields across my QVDs and allow for reviewing the cases when those thresholds were exceeded.

Re: Qlik Sense App: QVD Meta Data Viewer

stevedark — Tue, 09 Mar 2021 08:09:22 GMT

Hi @sergeyko

You can download the app referenced in that blog post and then use that as a base for other things. I may create a metadata file for it for the QVD structure viewer at some point.

To look at changes over time you will want to add a date field to the table and then save the QVD of data with a suffix.

To add the suffix do the following:

let vSuffix = Date(today(), 'YYYYMMDD');

STORE QVDFileStructure INTO [$(vDataFolder)QVDFileStructure_$(vSuffix).qvd] (qvd);

You can then load from these with a for each vFile in FileList... or with a wildcard.

Just looking into the code for the QVF above I notice that I have put code for an incremental load into an archive QVD. You can clone this app and then build in functionality for an 'over time' version of the app. The existing measures will work wherever you have Checked Date as a dimension. For where you are not using the dimension you will always need to use the avg function, or set analysis to limit to a day at a time.

To get some ideas on how you might use set analysis to compare two points in time, to pick up % growth etc., you could take a look at this app for ideas:

https://community.qlik.com/t5/Qlik-Sense-Documents/Qlik-Sense-App-Prior-Period-Comparison-with-Set-Analysis/ta-p/1495445

Hopefully that points you in the right direction.

Cheers,

Steve

Re: Qlik Sense App: QVD Meta Data Viewer

sergeyko — Wed, 10 Mar 2021 18:50:36 GMT

@stevedark- makes sense, thanks for the pointers!

I think I have everything to build another version of the app for monitoring changes in my QVDs. I will give it a try and share the result if I am able to develop something interesting.

Re: Qlik Sense App: QVD Meta Data Viewer

sergeyko — Thu, 01 Apr 2021 03:51:58 GMT

Hi @stevedark ,

I spent some more time with your app and was pleased to confirm that it is building its archive of changing QVDs and fields based on the last modification date of each QVD. These dates are embedded into QVDKey that is used for appending the data into the archive files.

Provided that most of my QVDs are update daily, it sure gives me a nice trend view over the key changes. Thanks again for sharing this immensely useful app!

The XML headers provide a host of useful information but one thing I couldn't find is the count of NULL values in fields. Have you stumbled across a solution for quickly counting NULLs in fields across multiple data sources? I am hoping to leverage some built-in functions and/or tricks related to QVDs so I don't have to cycle through each field in each file...

Re: Qlik Sense App: QVD Meta Data Viewer

stevedark — Thu, 01 Apr 2021 08:44:29 GMT

Hi @sergeyko

Afraid I don't know of any way of doing this without loading the whole files and then interrogating the field.

Looking at @rwunderlich 's Sense Document Analyser ( https://qlikviewcookbook.com/recipes/download-info/qs-document-analyzer/ ) may be a good idea - but you will still need to load the data first.

Another option is to write a profiling step into your QVD generators, so stats are generated and written to a QVD alongside the QVD with the data in.

Hope you get to a solution that works for you.

Steve

Re: Qlik Sense App: QVD Meta Data Viewer

rwunderlich — Thu, 01 Apr 2021 15:32:05 GMT

Hi @stevedark

I do count Nulls in my QViewer tool. Reading the values and counting nulls is how I get the count.

For QVFs, the engine API will report number of nulls as a TableField attribute, so you don't have to read the actual data. That's what I use in Document Analyzer. Again, this is app tables, not QVD.

-Rob

Re: Qlik Sense App: QVD Meta Data Viewer

sergeyko — Thu, 01 Apr 2021 19:56:41 GMT

Thanks @stevedark & @rwunderlich !

I was hopeful that would be a shortcut for counting NULLs in QVDs without diving into each file. I will keep digging and share here if I can find anything interesting.

Re: Qlik Sense App: QVD Meta Data Viewer

jpjust — Tue, 02 Aug 2022 20:00:13 GMT

Hi Steve,

I am kind of confused.

What kind of information goes here?

The path to the qvd's?

tmpLibs:
LOAD
Lib
INLINE [
Lib
QVDs
OtherQVDs
];

Re: Qlik Sense App: QVD Meta Data Viewer

stevedark — Tue, 02 Aug 2022 20:30:07 GMT

Hi @jpjust ,

That's exactly what's going in there. It is looking for QVDs in a library. So, if you are on Sense SaaS Business you would just have:

tmpLibs:
LOAD
Lib
INLINE [
Lib
DataFiles
];

If you have a space on SaaS Enterprise with data in a DataFiles connection it would be:

tmpLibs:
LOAD
Lib
INLINE [
Lib
DataSpace:DataFiles
];

Or, if you are on Sense Client Managed you can have a path in a data connection:

tmpLibs:
LOAD
Lib
INLINE [
Lib
DataConnection/QVDs
];

You can also loop around multiple connections or folders, if you have them, by adding them to the inline table.

In each case the libraries loaded from the inline table will be prefixed with lib:// and then each QVD file in that library will be looked at.

Hope that helps.

If you would like a different way of looking at these data then you may want to download my Instant Sense Application, which has this as an example script. You can find more information and a download link for this here:

https://www.quickintelligence.co.uk/instant-qlik-sense-app/

There's a direct link to the right of the sign-up form, should you wish to download it without giving any details.

Steve

Re: Qlik Sense App: QVD Meta Data Viewer

jpjust — Tue, 02 Aug 2022 21:13:17 GMT

Thank you Steve so much for the detailed explanation. Really Appreciate it.

So here is my code

let vDataFolder = 'lib://QVD_REPO/'; // Library for outputting QVDs to

// List the libraries you want to include QVDs from
tmpLibs:
LOAD
Lib
INLINE [
Lib
QVD_REPO/BIN.qvd
];

But I get the following error:

Table 'QVDFileStructure' not found

The error occurred here:

LEFT JOIN (QVDFileStructure) LOAD JoinID, QvBuildNo, [File Created By], [Table Name], [Number Of Records] RESIDENT Temp_QVDHeader

Can you please advice? Thanks Steve in advance for your help.

Another question is, this app don't show the list of app that generates the qvd / qvd's. Is that correct?

Thanks

Re: Qlik Sense App: QVD Meta Data Viewer

stevedark — Tue, 02 Aug 2022 22:54:29 GMT

Hi @jpjust

Rather than specifying a QVD file the inline table just needs to point to a library. The script will then load from all QVDs in that library. It should therefore be:

tmpLibs:
LOAD
Lib
INLINE [
Lib
QVD_REPO
];

The script will attempt to load the document that created the QVD into the File Created By field:

If the QVD has been created by Sense Enterprise (either SaaS or Client Managed) this will just give you a GUID rather than a sensible app name. If you have permissions to the app with that GUID though you should be able to find the app by altering the URL to include that GUID. Hope that makes sense?

If you are just looking to find out information on one QVD at a time then I highly recommend QViewer, which you can download from here:
https://easyqlik.com/qviewer/

You can use this for free, with a limit on the number of rows that can be viewed, but it is well worth the cost of a licence to unlock all of the features.

Within QViewer you can get to all of the metadata within a QVD, as well as exploring the data in it without having to build a Qlik application to view it.

Steve

Re: Qlik Sense App: QVD Meta Data Viewer

rwunderlich — Wed, 03 Aug 2022 13:58:32 GMT

Thanks for the mention @stevedark . Note that Qviewer will produce a list of QVDs if you open a folder with QViewer. You can open an individual QVD by clicking the name or perform other operations by right-click.

-Rob
http://www.easyqlik.com
http://masterssummit.com
http://qlikviewcookbook.com

Re: Qlik Sense App: QVD Meta Data Viewer

jpjust — Wed, 03 Aug 2022 14:29:24 GMT

Awesome Steve.

I am using On-prem qliksense enterprise.

So I added File Created By field to QVD details table. I see the ID of the App that created each qvd.

Is it possible to have App name?

I will also review other details of this app. This is going to be very useful for sure.

Is there a way to

Re: Qlik Sense App: QVD Meta Data Viewer

stevedark — Wed, 03 Aug 2022 15:38:52 GMT

Hi @rwunderlich

I've only recently spotted the ability to open a folder and have all QVDs in that folder listed with headline information. A really neat feature!

Hi @jpjust

That is the only information in the QVD regarding the app that created it. You can however use a different API to get a list of applications on the server and then do an ApplyMap to map the application name in place of the GUID. This article talks about accessing that API:

https://www.quickintelligence.co.uk/back-up-qlik-sense-applications/

You could simplify the call to the monitor_apps_REST_app API, as you only need the GUID and the name, but hopefully it points you in the right direction.

However, it means that if you delete the application that created the QVD that lookup will no longer work. As a result you may want to persist application details to a QVD and do an incremental load refreshing the data for applications that still exist, whilst loading deleted ones from the QVD.

Again the complete script for getting a list of applications is in the Instant Sense Application download.

Hope that all makes sense.

Cheers,
Steve

Re: Qlik Sense App: QVD Meta Data Viewer

jpjust — Wed, 03 Aug 2022 17:10:53 GMT

Definitely make sense. Thank you so much for putting this app.

Do you know for any app that can provide information on a user access level in Qliksense enterprise?

Such as user access to stream/ app etc.,

Thanks

Re: Qlik Sense App: QVD Meta Data Viewer

stevedark — Thu, 04 Aug 2022 08:50:26 GMT

Hi @jpjust

Take time to explore the other APIs that are available in QSE CM. You can certainly download a complete list of users (including those in the AD without a licence) and their licence types. I've not pulled Stream access from the APIs before, as far as I can remember, but that's not to say the information is not in there.

Another tool you may find useful is this one from @StefanStoichev which allows you to download tables of information from QMC:

https://sstoichev.eu/2021/01/08/export-qmc-tables/

It means a manual step, but it could be useful to you.

Cheers,
Steve

Re: Qlik Sense App: QVD Meta Data Viewer

jpjust — Thu, 04 Aug 2022 21:27:13 GMT

Thanks Steve for your advice. I will spend some time to explore the other API's.

In qliksense SaaS there is an access evaluator app under monitoring, pretty helpful to see who has access to what. Kind of similar thing I wanted to build in On-prem. Will go over the api's or qlik cli.

Thanks

Re: Qlik Sense App: QVD Meta Data Viewer

jpjust — Fri, 12 Aug 2022 18:47:21 GMT

Hi Steve,

I am trying to bring the app name by using monitor_apps_REST_app API but after adding the below code on the "Getstats" script tab, the script runs forever. Can you please check and let me know if there is anything wrong. id_u4 is the app ID.

Temp_QVDHeader:
LOAD
$(iID) as JoinID,
QvBuildNo as QvBuildNo,
CreatorDoc as [File Created By],
TableName as [Table Name],
NoOfRecords as [Number Of Records]
FROM [$(vFile)] (XmlSimple, Table is [QvdTableHeader]);

RestConnectorMasterTable:
SQL SELECT
"id" AS "id_u4",
"createdDate" AS "createdDate_u0",
"modifiedDate" AS "modifiedDate_u0",
"modifiedByUserName" AS "modifiedByUserName_u0",
"name" AS "name_u3",
"publishTime",
"published",
"description",
"fileSize",
"lastReloadTime",
"availabilityStatus",
"__KEY_root",
(SELECT
"userId",
"userDirectory",
"__FK_owner"
FROM "owner" FK "__FK_owner"),
(SELECT
"name" AS "name_u2",
"__FK_stream"
FROM "stream" FK "__FK_stream"),
(SELECT
"id" AS "id_u7",
"@Value" AS "@Value_u0",
"name" AS "name_tag",
"__FK_tags"
FROM "tags" FK "__FK_tags" ArrayValueAlias "@Value_u0")
FROM JSON (wrap on) "root" PK "__KEY_root";

App:
LOAD
[id_u4] AS [File Created By],
[name_u3] AS [App Name QRS]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__KEY_root]);

drop table RestConnectorMasterTable;

Re: Qlik Sense App: QVD Meta Data Viewer

stevedark — Sat, 13 Aug 2022 14:58:18 GMT

Not sure what is going on there @jpjust . First thought though is that you are doing a second REST call before dropping the temporary table from the first. The way the connector names the fields there could be a number that are identical between the temporary tables, and that would cause a massive synthetic key.

I would create a list of applications in a separate loader and then persist that to QVD. You can then just load the GUID and application name into a mapping table in your QVD Header app.

Having QVDs as a staging layer is pretty much always a good idea.

Hope that helps.

Steve