Qlik Sense Repository Database – Part 3 – 3 Useful Queries

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.

[heading align=”left”] Get size of tables in PostgreSQL Qlik database [/heading]

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;

 

[heading align=”left”] Get number of user attributes, sorted from highest number [/heading]

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;

 

[heading align=”left”] Get Engine ID for particular application object [/heading]

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,

Krzysiek

6 thoughts on “Qlik Sense Repository Database – Part 3 – 3 Useful Queries”

    1. Hello Sam,

      Thank you for your comment, I honestly don’t believe that there is an option to trigger a task straight from Repository Database (I may be wrong), however even if there is such option I would not recommend it.
      Don’t know exactly what is your business case but, Qlik-CLI may come in handy: https://github.com/ahaydon/Qlik-Cli
      It’s a PowerShell module that provides a command line interface for managing a Qlik Sense environment.

      An example of how to trigger a task usign Qlik-CLI is described under following link: https://qliktech.hosted.jivesoftware.com/thread/287893

      Hope that helps.

      Regards,
      Krzysiek

    1. Hello Yasser,

      Thank you for your comment, I don’t think that in QS Repository Database is such table, there are hashes to object but not measures formulas. Those in my opinion (99% sure) resides in .qvf / binary file of your end user application.

      Don’t know exactly what your business case is, but maybe if you are looking for measures, you can leverage using external file for keeping and maintaining them, check out QlikView (QlikSense) Vars in InQlik-Tools: https://github.com/inqlik/inqlik-tools, created by Vadim Tsushko. With such approach you will keep all your measures in an external easily maintainable files.

      Hope that helps.

      Regards,
      Krzysiek

  1. Hazem Rashad

    Many thanks for this series of articles.
    I was wondering if you can provide answers to a couple of questions concerning the PostgreSQL database:
    1. What is the Repository database initial size?
    2. How often does this database grow and at what rate?
    3. Are any large objects (images, documents etc.) stored in any of these database tables?

    1. Hello Hazem,

      Thank you for your comment. Unfortunately, I don’t have a Qlik Sense installation right now to check everything but hopefully below explanation would be useful.

      1. Repository initial size – wonder why this question 😉 haven’t checked it honestly.
      If you have a fresh installation you can check DB size using following query:

      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;

      2. DB grows constantly, in majority of cases with every action taken in Qlik Sense, be it adding user, creating self service sheet, adding stream etc. Different tables are populated.

      3. For versions I’ve analyzed, there were no large objects stored in database itself. They were stored on server disk (like content images for apps etc.). Although better question would be what is stored that makes database repository huge. Then the anwer would be look for your active directory records if you use one and working in big enterprise or for example Self service sheets objects.

      Regards,
      Krzysiek

Leave a Comment

Twój adres email nie zostanie opublikowany. Pola, których wypełnienie jest wymagane, są oznaczone symbolem *