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" 
GROUP BY U."ID"
ORDER BY 5 DESC;

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";

-- LEAVING WHERE EMPTY - USE WHATEVER YOU NEED

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?

Thanks,
Krzysztof

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