Qlik Sense Repository Database – Part 2 – Essential Tables

Welcome in second part of a 3 part cycle Qlik Sense Repository Database under the hood, in previous article you’ve learnt how to connect to Qlik Sense database and why it may come in handy, this one will give you an overview and brief description of „essential” – from my perspective – tables inside of Qlik Sense Repository Database.

What is worth mentioning here is that most of QS Repository Database tables names are pretty self explanatory – good job Qlik.

Notice: Below description is solely based on my understanding of the tables, please do not treat it as an official documentation, and remember that changing / manipulating with Qlik Sense Repository Database  is not supported by Qlik. Below description is based on Qlik Sense Version 3.2 SR 4 table structure it can be a point of change in future releases.

[heading align=”left”] List of Essential Tables [/heading]

[row]
[one_fourth] Apps  [/one_fourth]
[three_fourth] Table responsible for storing information about Qlik Sense applications in environment. Important attributes: Name (Name), Identifier (ID), status whether it is published or not (Published), Owner and Stream Identitifers (Owner_ID, Stream_ID) [/three_fourth]
[/row] [row]
[one_fourth] AppObjects [/one_fourth]
[three_fourth] Table responsible for storing information about Qlik Sense applications objects in environment, like sheets, bookmarks, stories. Important attributes: Name (particulary name of sheet, measure etc.) (Name), Object Type (measure, sheet etc.) (ObjectType), Identifier (ID), status whether it is published / community or self sheet (Published, Approved), Owner and Application and File Identitifers (Owner_ID, File_ID, App_ID), Engine object identifier (used when accessing particular sheet in URL (EngineObjectId) [/three_fourth]
[/row] [row]
[one_fourth] DataConnections [/one_fourth]
[three_fourth] Table responsible for storing information about Qlik Sense applications data connections in environment, folders, odbc / OleDB drivers etc. Important attributes: Name (name of data connection) (Name), Connection string (either path or connection string of data connection) (ConnectionString), Identifier (ID), username and password for data connections (Username, PasswordString – hold your horses not a plain text), Owner Identitifers (Owner_ID, File_ID, App_ID) [/three_fourth]
[/row] [row]
[one_fourth] EngineServices [/one_fourth]
[three_fourth] Table responsible for storing information about Qlik Sense Engine Services information in server node. Important attributes: Identifier (ID), Deleted (if engine row was recently deleted) (Deleted), Server node configuration identifier (ServerNodeConfiguration_ID) [/three_fourth]
[/row][row]
[one_fourth] EngineServiceSettings [/one_fourth]
[three_fourth] Table responsible for storing information about Qlik Sense Engine Service configuration in server node. Important attributes, are nearly the same as you can see in QMC under Engine configuration section: Identifier (ID), Listener ports (ListenerPortsString), Autosave option (AutosaveInterval), Document timeout on server (DocumentTimeout), Table files directory (legacy in shared persistence environment) (TableFilesDirectory), Working sets low / high (WorkingSetSizeLoPct, WorkingSetSizeHiPct), Object calculation time limit (ObjectTimeLimitSec), QRS notification port (QrsHttpNotificationPort), Creation of search index on reload (CreateSearchIndexOnReloadEnabled) [/three_fourth]
[/row] [row]
[one_fourth] ExecutionResults  [/one_fourth]
[three_fourth] Table responsible for storing information about Qlik Sense Tasks Execution Results. Important attributes, are nearly the same as you can see in Operations / Reload Monitor application; Identifier, Task, Execution, Application and Execution Node Identifier (ID, TaskID, ExecutionID, AppID, ExecutingNodeID), Execution Status (Status), Duration (or if you prefer StartTime, StopTime) (Duration), binary file reference (FileReferenceID), script log availability (ScriptLogAvailable) [/three_fourth]
[/row] [row]
[one_fourth] ExecutionResultDetails [/one_fourth]
[three_fourth] Table responsible for storing information about Qlik Sense Tasks Execution Details. Information from this table can be found under „i” icon on Task execution. Important attributes: Identifier (ID), information if row was deleted (cascade delete from Tasks table) (Deleted), Details type (DetailsType), Message (Message) [/three_fourth]
[/row] [row]
[one_third] ExecutionResultDetailExecutionResults [/one_third]
[two_third] Mapping table between ExecutionResultDetails and ExecutionResults tables. Two attributes that are primary keys of mentioned tables (ExecutionResultDetail_ID, ExecutionResult_ID) [/two_third]
[/row] [row]
[one_fourth] Extensions [/one_fourth]
[three_fourth] Table responsible for storing information about Qlik Sense Extension used in environment. Important attributes: Identifier (ID), Extension name (Name), information if row was deleted (until auto remove will persist in repository database) (Deleted), Owner identifier (Owner_ID) [/three_fourth]
[/row] [row]
[one_fourth] LocalConfigs  [/one_fourth]
[three_fourth] Table responsible for storing information about Qlik Sense Server Nodes existing in environment. Important attributes: Identifier (ID), Key (Key), Value (identifier) (Value), Hostname (name of machine / dns) (HostName) [/three_fourth]
[/row] [row]
[one_fourth] PrintingServices [/one_fourth]
[three_fourth] Table responsible for storing information about Qlik Sense Printing Services information in server node. Important attributes: Identifier (ID), Deleted (if printing row was recently deleted) (Deleted), Server node configuration identifier (ServerNodeConfiguration_ID) [/three_fourth]
[/row] [row]
[one_fourth] ProxyServiceCertificates [/one_fourth]
[three_fourth] Table responsible for storing information about Qlik Sense Proxy Services Certificates configuration in environment. Important attributes: Identifier (ID), information if row was deleted (Deleted), Generated certificate hash (X509Certificate), Proxy service identifier (ProxyService_ID) [/three_fourth]
[/row] [row]
[one_fourth] ProxyServices [/one_fourth]
[three_fourth] Table responsible for storing information about Qlik Sense Proxy Services information in server node. Important attributes: Identifier (ID), Deleted (if proxy row was recently deleted) (Deleted), Server node configuration identifier (ServerNodeConfiguration_ID) [/three_fourth]
[/row] [row]
[one_fourth] ProxyServiceSettings [/one_fourth]
[three_fourth] Table responsible for storing information about Qlik Sense Proxy Services configuration in environment. Important attributes: Identifier (ID), Listen port (ListenPort), HTTP is allowed (AllowHttp), HTTP listen port (UnencryptedListenPort), HTTPS Authentication listen port (AuthenticationListenPort), HTTP authentication listen port (UnencryptedAuthenicationListenPort), SSL Certificate Thumbrint (SslBrowserCertificateThumbprint), Listen port for REST service (RestListenPort) [/three_fourth]
[/row] [row]
[one_fourth] RepositoryServices [/one_fourth]
[three_fourth] Table responsible for storing information about Qlik Sense Repository Services information in server node. Important attributes: Identifier (ID), Deleted (if repository service row was recently deleted) (Deleted), Server node configuration identifier (ServerNodeConfiguration_ID) [/three_fourth]
[/row] [row]
[one_fourth] RepositoryServiceSettings  [/one_fourth]
[three_fourth] Table responsible for storing information about Qlik Sense Repository Services configuration in environment. Important attributes: Identifier (ID), Path for application import (I would assume that this is legacy after introduction of SharedPersistance) (AppImportFolder), information if row was deleted (Deleted) [/three_fourth]
[/row] [row]
[one_fourth] SchedulerServices [/one_fourth]
[three_fourth] Table responsible for storing information about Qlik Sense Scheduler Services information in server node. Important attributes: Identifier (ID), Deleted (if scheduler row was recently deleted) (Deleted), Server node configuration identifier (ServerNodeConfiguration_ID) [/three_fourth]
[/row] [row]
[one_fourth] SchedulerServiceSettings [/one_fourth]
[three_fourth] Table responsible for storing information about Qlik Sense Scheduler Services configuration in environment. Important attributes: Identifier (ID), Scheduler service type (Master, Slave, Both) (SchedulerServiceType), Maximum number of concurrent reload engines (MaxConcurrentEngines), Engine reload timeout (EngineTimeout) [/three_fourth]
[/row] [row]
[one_fourth] ServerNodeConfigurations [/one_fourth]
[three_fourth] Table responsible for storing information about Qlik Sense environment configuration. Important attributes: Identifier (ID), Name (node name) (Name), Hostname (machine name / DNS) (HostName), information if node is a central one (IsCentral), Node purpose (NodePurpose), Engine, Proxy, Scheduler, Printing services enabled (EngineEnabled, ProxyEnabled, SchedulerEnabled, PrintingEnabled), Service cluster identifier (ServiceCluster_ID), information if node is a candidate for a fail over machine type (FailoverCandidate) [/three_fourth]
[/row] [row]
[one_third] ServiceClusterSettings

SharedPersistenceProperties [/one_third]
[two_third] Table responsible for storing information about Qlik Sense Shared Persistence configuration. Important attributes: Identifier (ID), Root folder (RootFolder), Application folder (AppFolder), Static content root folder (StaticContenntRootFolder), Archived logs folder (ArchivedLogsRootFolder), Database host and port (most likely if Repository Database is on different machine) (DatabaseHost, DatabasePort) [/two_third]
[/row] [row]
[one_fourth] UserDirectories [/one_fourth]
[three_fourth] Table responsible for storing information about Qlik Sense User Directory in environment. Important attributes: Identifier (ID), Name (set name of User Directory service) (Name), Type of user directory service (Type), configuration to synchronize only logged in users (SyncOnlyLoggedInUsers), Last synchronization start time (SyncLastStarted), Last successful synchronization end time (SyncLastSuccessfulEnded) [/three_fourth]
[/row] [row]
[one_fourth] UserDirectorySettings  [/one_fourth]
[three_fourth] Table responsible for storing information about Qlik Sense User Directory configuration in environment. Important attributes: Identifier (ID), Parameter name (Name), Parameter value (Value), Information if parameter is a password type (Secret), Password value (SecretValueString), User directory service identifier (UserDirectory_ID) [/three_fourth]
[/row] [row]
[one_fourth] Users [/one_fourth]
[three_fourth] Table responsible for storing information about Qlik Sense Users in environment. Important attributes: Identifier (ID), User identifier (UserId), Domain or directory name (UserDirectory), User name (Name), if user is inactive (Inactive), if user is deleted (Deleted), if user is removed externally (RemovedExternally), if user is blacklisted (Blacklisted), if user can not be removed (DeleteProhibited) [/three_fourth]
[/row] [row]
[one_fourth] UserAttributes [/one_fourth]
[three_fourth] Table responsible for storing information about Qlik Sense Users Attributes from users source (Active Directory etc.) Important attributes: Identifier (ID), Attribute type (AttributeType), Attribute value (AttributeValue), External identifier (value inside of source – Active Directory etc.) (ExternalId), User identifier (User_ID) [/three_fourth]
[/row] [row]
[one_third] VirtualProxyConfigServerNodeConfigurations [/one_third]
[two_third] Mapping table responsible for storing information about Qlik Sense Virtual Proxies information in server node. Important attributes: Virtual proxy configuration identifier (VirtualProxyConfig_ID), Server node configuration identifier (ServerNodeConfiguration_ID) [/two_third]
[/row] [row]
[one_fourth] VirtualProxyConfigs [/one_fourth]
[three_fourth] Table responsible for storing information about Qlik Sense Virtual Proxy configuration in environment. Important attributes: Identifier (ID), Virtual proxy prefix (Prefix), Virtual proxy description (Description), Session Cookie name (SessionCookieHeaderName), Windows Authentication method (WindowsAuthenticationEnabledDevicePattern), Session inactivity timeout (SessionInactivityTimeout), Web sockets cross origin white lists (WebsocketCrossOriginWhiteListString), information if configuration is a default virtual proxy (DefaultVirtualProxy), Authentication method (AuthenticationMethod), SAML (SSO configuration) (SamlMetadataIdP, SamlHostUri, SamlEntityId, SamlAttributeUserId, SamlAttributeUserDirectory, SamlAttributeeSigningAlgorithm), Magic link and name for Qlik Sense Desktop login (MagicLinkHostUri, MagicLinkFriendlyName) [/three_fourth]
[/row] [row]
[one_third] VirtualProxyConfigProxyServiceSettings [/one_third]
[two_third] Mapping table between VirtualProxyConfigs and ProxyServiceSettings tables. Two attributes that are primary keys of mentioned tables (VirtualProxyConfig_ID, ProxyServiceSettings_ID) [/two_third]
[/row]

I certainly hope that you will never have a need to go inside of Qlik Sense Repository Database but sometimes you may be forced to. Feel free to use above description as a guidance, unfortunately haven’t found anything similar on official Qlik Help webpage, so I’ve decided to create this list.

Thanks,

Krzysiek

2 thoughts on “Qlik Sense Repository Database – Part 2 – Essential Tables”

  1. Ferdi Espino

    HI Would you which Qlik table to query to identify Apps which creates QVD’s ( which apps uses the command „stor”)

    1. Hello Ferdinand,

      As far as I know, you won’t be able to check it in Qlik Repository Database as this would be part of script statement which is saved in binary file, besides I would not recommend to use Repository Database for that.

      I would recommend to use either DevTool extension to get application script and look for STORE (using Total Commander search functionality or any sort of script that will browse through number of text files).
      Link to DevTool: http://qlikviewcookbook.com/2017/11/devtool-extension-for-qlik-sense/

      Alternative to that if you have access to QS Server you could install Qlik Sense EA Powertools and part of it is Qlik Sense QMC Utilities, which gives you a possiblity to Backup or Restore all your QS applications. They will be stored to JSON along with scripts etc. thus you would be easily able to find in JSON files these scripts with STORE function.
      Link to EA PowerTools: https://community.qlik.com/docs/DOC-17521
      QMC Utilities: https://github.com/eapowertools/qmcu-sclite/blob/master/README.md

      Regards,
      Krzysiek

Leave a Comment

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