Qlik Sense Repository Queries - Part 3

This is 3rd and the last part of cycle Qlik Sense Repository Database under the hood, in previous article you’ve learnt about essential tables inside of Qlik Sense Repository Database, this one will give you 3 SQL scripts that you may find useful or needed when working with QlikSense.

Notice: Remember that changing / manipulating with Qlik Sense Repository Database on it’s own is not supported by Qlik.

Get size of tables in PostgreSQL Qlik database

Can be used to validate biggest tables inside of your Qlik Sense Repository Database.

-- Get size of tables in PostgreSQL Qlik database.

  SELECT relname as "Table",
         pg_size_pretty(pg_total_relation_size(relid)) as "Size",
         pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid)) as "External Size"
    FROM pg_catalog.pg_statio_user_tables 
ORDER BY pg_total_relation_size(relid) DESC;

Get number of user attributes, sorted from highest number

Can be used to check users with biggest amount of attributes – especially useful when attributes are frequently changed in Active Directory (and user is a frequent user) – meaning that query to AD from QS each time will take a bit of time.

-- Get number of user attributes, sorted from highest number

  SELECT U."ID" as "Identifier",
         U."UserId" as "UserIdentifier",
	 U."Name" as "UserName",
         U."Deleted" as "UserDeleted",       
         count(UA."ID") as "NumberOfAttributes" 
    FROM "Users" U
    JOIN "UserAttributes" UA on UA."User_ID" = U."ID" 

Get Engine ID for particular application object

Useful when you want to access particular sheet or bookmark or story of an application, based on Engine Object Identifier you can build custom URI to access just this particular object not following standard path HUB > Stream > App > Objects.

-- Get Engine ID for particular application object

SELECT A."ID" as "AppIdentifier",
       A."Name" as "AppName",
       A."Published" as "IsPublished",
       A."Deleted" as "IsDeleted",
       AO."ObjectType" as "AppObjectType",
       AO."Published" as "IsObjectPublished",
       AO."Approved" as "IsObjectApproved",
       AO."Name" as "AppObjectName",
       AO."Description" as "AppObjectDescription",
       AO."EngineObjectId" as "AppObjectEngineObjectId",
       AO."Deleted" as "IsAppObjectDeleted",
       U."UserId" as "AppObjectOwnerIdentifier",
       U."Name" as "AppObjectOwnerName"       
  FROM "Apps" A
  JOIN "AppObjects" AO ON A."ID" = AO."App_ID"
  LEFT JOIN "Users" U ON U."ID" = AO."Owner_ID";


I hope that you will find above scripts useful (if needed), please let me know what do you think, maybe you have created something that you are using and is worth sharing?


Want to be up to date with new posts?
Use below form to join Data Craze Weekly Newsletter!

Data Craze Weekly

Weekly dose of curated informations from data world!
Data engineering, analytics, case studies straight to your inbox.

    No spam. Unsubscribe at any time.

    The administrator of personal data necessary in the processing process, including the data provided above, is Data Craze - Krzysztof Bury, Piaski 50 st., 30-199 Rząska, Poland, NIP: 7922121365. By subscribing to the newsletter, you consent to the processing of your personal data (name, e-mail) as part of Data Craze activities.

    This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.