Qlik Sense Repository Database - Part 2
Welcome in second part of a three 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.
List of Essential Tables
Apps
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)
AppObjects
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)
DataConnections
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)
EngineServices
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)
EngineServiceSettings
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)
ExecutionResults
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)
ExecutionResultDetails
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)
ExecutionResultDetailExecutionResults
Mapping table between ExecutionResultDetails and ExecutionResults tables.
Two attributes that are primary keys of mentioned tables (ExecutionResultDetail_ID, ExecutionResult_ID)
Extensions
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)
LocalConfigs
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)
PrintingServices
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)
ProxyServiceCertificates
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)
ProxyServices
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)
ProxyServiceSettings
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)
RepositoryServices
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)
RepositoryServiceSettings
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)
SchedulerServices
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)
SchedulerServiceSettings
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)
ServerNodeConfigurations
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)
ServiceClusterSettings / SharedPersistenceProperties
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)
UserDirectories
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)
UserDirectorySettings
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)
Users
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)
UserAttributes
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)
VirtualProxyConfigServerNodeConfigurations
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)
VirtualProxyConfigs
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)
VirtualProxyConfigProxyServiceSettings
Mapping table between VirtualProxyConfigs and ProxyServiceSettings tables.
Two attributes that are primary keys of mentioned tables (VirtualProxyConfig_ID, ProxyServiceSettings_ID)
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,
Krzysztof
Want to be up to date with new posts?
Use below form to join Data Craze Weekly Newsletter!