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?
8 thoughts on “Qlik Sense Repository Database – Part 3 – 3 Useful Queries”
I’m looking to start a reload task from the repository database, is this possible?
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.
Dear , thanks for all these valuable information , however I am looking for the table name where we can found measures formulas in QSR tables.
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.
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?
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:
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.
I have a query regarding bookmarks in QLIK SENSE PostgreSQL DB.
We tried to copy bookmarks from one QVF to another in PostgreSQL DB by updating app id in „AppObjects” table.
but unfortunately those are not reflecting in new app where I wanted to copy.
any suggestions please. I tried a lot for this one
Thank you for reaching out, and sorry for probably not having any solid answer.
The only option that comes to my mind is to update AppId for bookmarks in PostgreSQL DB of Qlik Sense from one to another.
It is also described here: https://community.qlik.com/t5/New-to-Qlik-Sense/Move-app-to-another-stream-but-keep-users-bookmarks/m-p/1351235
However as you’ve said, you have already tried it and it didn’t work.
Qlik Community people might have more expertise on the matter, or if you are a Qlik Client with access to support portal I would try raising a support request for this case.