QlikTools “QlikBlazor” Cache Warming Service

Introduction

This document describes a custom-built utility, developed in order to encourage Qlik Sense Engine Services to cache apps and their related objects by simulating the presence of user interaction.

Background

As mentioned before, this service is designed to simulate the presence of users opening applications in order to improve user-perceived load times. The intended result of which is a user-perceived reduction in latency, due to the wait time associated with the given application and application object’s calculations executed in advance of the user opening the given application and application objects.

Breakdown of How the Cache Warming Works


The workflow within service is multi-threaded, attaching a cache job transaction to a cache subject (tuple) consisting of:

  • User
  • Application
  • Qlik Sense Engine Server

When a cache job is defined, the service will handle the rest. Queuing new batches of transactions (tuples) to be executed upon completion of the previous batch.

Components

  • Frontend – hosts the single page angular application
  • Healthcheck – fetches engine healthcheck data from each target engine server listed in the Shared Persistence Database
  • Engine – Performs the cache warming requests based on the aforementioned tuple of user, application, and engine server
  • Repository – Web API service that exposes the entities in the Repository Database
  • Database – embedded with the Cache warming serice. PostgreSQL 12 x64. This can be changed to an external instance

Audience(s)

The audience for this document and management of this service is a Qlik Sense administrator with familiarity with the target Qlik Sense application(s)’ section access table(s) stored in Postgres.

Using (Configuring) the Service

To create a new job, the interface for the service (located on http://localhost:5099) is laid out in the way you would need to provision a new job.

To create a new cache job definition, you have to complete (add or update) entries in the entities that come before it (left to right in the navigation). prompts are pretty self explanatory, but I will step through the process of creating a cache job definition here:

  1. Create an Environment Configuration + Engine Server Record
    1. Export a windows fromat certificate from the QMC
    2. Place the exported certificate in a directory accessible by the QlikBlazor Service
    3. Navigate to http://localhost:5099/environment-configuration
    4. Name the environment
    5. Place a reference to the certificate (full literal path) in the Certificate Literal Path field (do not put quotes)
    6. Click the new Engine Server Button
    7. Add the first engine server for the environment
    8. Submit
  2. Add more engine servers Skip this if you are satisfied with just the one server created in step 1
    1. Navigate to http://localhost:5099/engine-server
    2. Fill every field
  3. Create an entity section access mapping We are basing this entity mapping on a schema like this: create table rando_users ( id serial not null constraint rando_users_pk primary key, userid text ); alter table rando_users owner to postgres; create unique index rando_users_id_uindex on rando_users (id); INSERT INTO public.rando_users (id, userid) VALUES (1, 'win-bd534v5552i\administrator'); A connection string file that looks like this: Host=localhost;Database=qlikapp_test;Username=postgres;Password=postgres;Port=5433 and a where clause file that looks like this: 1=1
    1. Create a file called connectionstring.txt. This will need to be a Redshift dsn or postgres format connection string. This will be used eventually in the entity section access mapping section.
    2. Create a file called whereclause.txt. This will be the where clause used to fetch the users from the redshift/postgresql database connection string referenced in 3. If there is nothing to filter, then use “1=1” as the only content in that file. The “Where” keywaord is provided for you, so only include the text that follows. The result will be a sql statement constructed from the elements provided in the form
    3. Create a Qlik Sense Tag that will be part of the target applications.
    4. Tag the appropriate Qlik Sense Applications The result is something like this: Note:
    • No quotes or any other special characters are to be entered in this form
    • The Last Visited Minimum filters users that have not signed in in the last given days. This aims to curb overcaching
  4. Create a Cache Job Definition
    1. Fill out all the fields. Note:
    • This is what allows the service to know which entity section access to use with the given environment + servers

Installation

Prerequisites

  • Windows Server 2012R2 or greater
  • At least 16GB of Memory
  • At least 12 CPU Cores
  • Redshift of PostgreSQL for (for section access table)

Install the Cache Warming Service

  1. Close the windows service manager (service.msc), if open
  2. Open a command or powershell window as an administrator
  3. Navigate to the downloaded location of the QlikBlazor.Installer.exe
  4. execute the installation
  5. follow the prompts
  6. Start these services in this order:
    1. QlikBlazorDatabase
    2. QlikBlazorRepository
    3. QlikBlazorFrontend
    4. QlikBlazorEngine
    5. QlikBlazorHealthcheck

Install External Postgres Database (Optional)

***Ignore this step, if you will rely on the embedded PostgreSQL***
  1. Download PostgreSQL Version 12 64 bit
  2. Create the QlikBlazor Database
Create Database QlikBlazor
  1. Within the newly created database, run the QlikBlazor create script:
CREATE TABLE IF NOT EXISTS "__EFMigrationsHistory" (
    "MigrationId" character varying(150) NOT NULL,
    "ProductVersion" character varying(32) NOT NULL,
    CONSTRAINT "PK___EFMigrationsHistory" PRIMARY KEY ("MigrationId")
);

CREATE TABLE public."ConfigurationSets" (
    "Id" uuid NOT NULL,
    CONSTRAINT "PK_ConfigurationSets" PRIMARY KEY ("Id")
);

CREATE TABLE public."EntitySectionAccesses" (
    "Id" uuid NOT NULL,
    "SectionAccessTableName" character varying(255) NOT NULL,
    "MapUserIdField" character varying(255) NOT NULL,
    "ConnectionStringPath" character varying(255) NOT NULL,
    "Modified" timestamp without time zone NULL,
    "Created" timestamp without time zone NULL,
    CONSTRAINT "PK_EntitySectionAccesses" PRIMARY KEY ("Id")
);

CREATE TABLE public."EnvironmentConfigurations" (
    "Id" uuid NOT NULL,
    "Name" character varying(255) NOT NULL,
    "CertificateLiteralPath" character varying(255) NOT NULL,
    "Modified" timestamp without time zone NULL,
    "Created" timestamp without time zone NULL,
    CONSTRAINT "PK_EnvironmentConfigurations" PRIMARY KEY ("Id")
);

CREATE TABLE public."CacheJobDefinitions" (
    "Id" uuid NOT NULL,
    "Name" character varying(255) NOT NULL,
    "EnvironmentConfigurationId" uuid NOT NULL,
    "EntitySectionAccessId" uuid NOT NULL,
    "Modified" timestamp without time zone NULL,
    "Created" timestamp without time zone NULL,
    CONSTRAINT "PK_CacheJobDefinitions" PRIMARY KEY ("Id"),
    CONSTRAINT "FK_CacheJobDefinitions_EntitySectionAccesses_EntitySectionAcce~" FOREIGN KEY ("EntitySectionAccessId") REFERENCES public."EntitySectionAccesses" ("Id") ON DELETE CASCADE,
    CONSTRAINT "FK_CacheJobDefinitions_EnvironmentConfigurations_EnvironmentCo~" FOREIGN KEY ("EnvironmentConfigurationId") REFERENCES public."EnvironmentConfigurations" ("Id") ON DELETE SET NULL
);

CREATE TABLE public."EngineServers" (
    "Id" uuid NOT NULL,
    "DisplayName" character varying(255) NULL,
    "Hostname" character varying(255) NOT NULL,
    "EnginePort" smallint NOT NULL,
    "QrsPort" smallint NOT NULL,
    "EnvironmentConfigurationId" uuid NOT NULL,
    "Modified" timestamp without time zone NULL,
    "Created" timestamp without time zone NULL,
    CONSTRAINT "PK_EngineServers" PRIMARY KEY ("Id"),
    CONSTRAINT "FK_EngineServers_EnvironmentConfigurations_EnvironmentConfigur~" FOREIGN KEY ("EnvironmentConfigurationId") REFERENCES public."EnvironmentConfigurations" ("Id") ON DELETE SET NULL
);

CREATE TABLE public."CacheJobs" (
    "Id" uuid NOT NULL,
    "Status" integer NOT NULL,
    "Finished" timestamp without time zone NULL,
    "CacheJobDefinitionId" uuid NOT NULL,
    "Created" timestamp without time zone NULL,
    "Modified" timestamp without time zone NULL,
    CONSTRAINT "PK_CacheJobs" PRIMARY KEY ("Id"),
    CONSTRAINT "FK_CacheJobs_CacheJobDefinitions_CacheJobDefinitionId" FOREIGN KEY ("CacheJobDefinitionId") REFERENCES public."CacheJobDefinitions" ("Id") ON DELETE CASCADE
);

CREATE TABLE public."CacheJobTransactions" (
    "Id" uuid NOT NULL,
    "Finished" timestamp without time zone NULL,
    "CacheJobId" uuid NOT NULL,
    "Created" timestamp without time zone NULL,
    "Modified" timestamp without time zone NULL,
    CONSTRAINT "PK_CacheJobTransactions" PRIMARY KEY ("Id"),
    CONSTRAINT "FK_CacheJobTransactions_CacheJobs_CacheJobId" FOREIGN KEY ("CacheJobId") REFERENCES public."CacheJobs" ("Id") ON DELETE CASCADE
);

CREATE INDEX "IX_CacheJobDefinitions_EntitySectionAccessId" ON public."CacheJobDefinitions" ("EntitySectionAccessId");

CREATE INDEX "IX_CacheJobDefinitions_EnvironmentConfigurationId" ON public."CacheJobDefinitions" ("EnvironmentConfigurationId");

CREATE INDEX "IX_CacheJobs_CacheJobDefinitionId" ON public."CacheJobs" ("CacheJobDefinitionId");

CREATE INDEX "IX_CacheJobTransactions_CacheJobId" ON public."CacheJobTransactions" ("CacheJobId");

CREATE INDEX "IX_EngineServers_EnvironmentConfigurationId" ON public."EngineServers" ("EnvironmentConfigurationId");

INSERT INTO "__EFMigrationsHistory" ("MigrationId", "ProductVersion")
VALUES ('20191127003906_Intitial', '3.1.0');

DROP TABLE public."ConfigurationSets";

ALTER TABLE public."EnvironmentConfigurations" ALTER COLUMN "Modified" TYPE timestamp without time zone;
ALTER TABLE public."EnvironmentConfigurations" ALTER COLUMN "Modified" DROP NOT NULL;
ALTER TABLE public."EnvironmentConfigurations" ALTER COLUMN "Modified" SET DEFAULT TIMESTAMP '2019-12-08 22:07:10.748235';

ALTER TABLE public."EnvironmentConfigurations" ALTER COLUMN "Created" TYPE timestamp without time zone;
ALTER TABLE public."EnvironmentConfigurations" ALTER COLUMN "Created" DROP NOT NULL;
ALTER TABLE public."EnvironmentConfigurations" ALTER COLUMN "Created" SET DEFAULT TIMESTAMP '2019-12-08 22:07:10.748294';

ALTER TABLE public."EntitySectionAccesses" ALTER COLUMN "Modified" TYPE timestamp without time zone;
ALTER TABLE public."EntitySectionAccesses" ALTER COLUMN "Modified" DROP NOT NULL;
ALTER TABLE public."EntitySectionAccesses" ALTER COLUMN "Modified" SET DEFAULT TIMESTAMP '2019-12-08 22:07:10.742376';

ALTER TABLE public."EntitySectionAccesses" ALTER COLUMN "Created" TYPE timestamp without time zone;
ALTER TABLE public."EntitySectionAccesses" ALTER COLUMN "Created" DROP NOT NULL;
ALTER TABLE public."EntitySectionAccesses" ALTER COLUMN "Created" SET DEFAULT TIMESTAMP '2019-12-08 22:07:10.743314';

ALTER TABLE public."EntitySectionAccesses" ADD "QlikTagName" text NOT NULL DEFAULT '';

ALTER TABLE public."CacheJobTransactions" ALTER COLUMN "Modified" TYPE timestamp without time zone;
ALTER TABLE public."CacheJobTransactions" ALTER COLUMN "Modified" DROP NOT NULL;
ALTER TABLE public."CacheJobTransactions" ALTER COLUMN "Modified" SET DEFAULT TIMESTAMP '2019-12-08 22:07:10.741102';

ALTER TABLE public."CacheJobTransactions" ALTER COLUMN "Created" TYPE timestamp without time zone;
ALTER TABLE public."CacheJobTransactions" ALTER COLUMN "Created" DROP NOT NULL;
ALTER TABLE public."CacheJobTransactions" ALTER COLUMN "Created" SET DEFAULT TIMESTAMP '2019-12-08 22:07:10.726468';

ALTER TABLE public."CacheJobs" ALTER COLUMN "Modified" TYPE timestamp without time zone;
ALTER TABLE public."CacheJobs" ALTER COLUMN "Modified" DROP NOT NULL;
ALTER TABLE public."CacheJobs" ALTER COLUMN "Modified" SET DEFAULT TIMESTAMP '2019-12-08 22:07:10.745242';

ALTER TABLE public."CacheJobs" ALTER COLUMN "Created" TYPE timestamp without time zone;
ALTER TABLE public."CacheJobs" ALTER COLUMN "Created" DROP NOT NULL;
ALTER TABLE public."CacheJobs" ALTER COLUMN "Created" SET DEFAULT TIMESTAMP '2019-12-08 22:07:10.745204';

ALTER TABLE public."CacheJobDefinitions" ALTER COLUMN "Modified" TYPE timestamp without time zone;
ALTER TABLE public."CacheJobDefinitions" ALTER COLUMN "Modified" DROP NOT NULL;
ALTER TABLE public."CacheJobDefinitions" ALTER COLUMN "Modified" SET DEFAULT TIMESTAMP '2019-12-08 22:07:10.746133';

ALTER TABLE public."CacheJobDefinitions" ALTER COLUMN "Created" TYPE timestamp without time zone;
ALTER TABLE public."CacheJobDefinitions" ALTER COLUMN "Created" DROP NOT NULL;
ALTER TABLE public."CacheJobDefinitions" ALTER COLUMN "Created" SET DEFAULT TIMESTAMP '2019-12-08 22:07:10.746257';

INSERT INTO "__EFMigrationsHistory" ("MigrationId", "ProductVersion")
VALUES ('20191208220710_AddedQrsTagName', '3.1.0');

ALTER TABLE public."EnvironmentConfigurations" ALTER COLUMN "Modified" TYPE timestamp without time zone;
ALTER TABLE public."EnvironmentConfigurations" ALTER COLUMN "Modified" DROP NOT NULL;
ALTER TABLE public."EnvironmentConfigurations" ALTER COLUMN "Modified" SET DEFAULT TIMESTAMP '2019-12-10 20:46:52.340315';

ALTER TABLE public."EnvironmentConfigurations" ALTER COLUMN "Created" TYPE timestamp without time zone;
ALTER TABLE public."EnvironmentConfigurations" ALTER COLUMN "Created" DROP NOT NULL;
ALTER TABLE public."EnvironmentConfigurations" ALTER COLUMN "Created" SET DEFAULT TIMESTAMP '2019-12-10 20:46:52.340341';

ALTER TABLE public."EntitySectionAccesses" ALTER COLUMN "QlikTagName" TYPE character varying(255);
ALTER TABLE public."EntitySectionAccesses" ALTER COLUMN "QlikTagName" SET NOT NULL;
ALTER TABLE public."EntitySectionAccesses" ALTER COLUMN "QlikTagName" DROP DEFAULT;

ALTER TABLE public."EntitySectionAccesses" ALTER COLUMN "Modified" TYPE timestamp without time zone;
ALTER TABLE public."EntitySectionAccesses" ALTER COLUMN "Modified" DROP NOT NULL;
ALTER TABLE public."EntitySectionAccesses" ALTER COLUMN "Modified" SET DEFAULT TIMESTAMP '2019-12-10 20:46:52.333954';

ALTER TABLE public."EntitySectionAccesses" ALTER COLUMN "Created" TYPE timestamp without time zone;
ALTER TABLE public."EntitySectionAccesses" ALTER COLUMN "Created" DROP NOT NULL;
ALTER TABLE public."EntitySectionAccesses" ALTER COLUMN "Created" SET DEFAULT TIMESTAMP '2019-12-10 20:46:52.334978';

ALTER TABLE public."EntitySectionAccesses" ADD "LastVisitedDays" bigint NOT NULL DEFAULT 0;

ALTER TABLE public."CacheJobTransactions" ALTER COLUMN "Modified" TYPE timestamp without time zone;
ALTER TABLE public."CacheJobTransactions" ALTER COLUMN "Modified" DROP NOT NULL;
ALTER TABLE public."CacheJobTransactions" ALTER COLUMN "Modified" SET DEFAULT TIMESTAMP '2019-12-10 20:46:52.332346';

ALTER TABLE public."CacheJobTransactions" ALTER COLUMN "Created" TYPE timestamp without time zone;
ALTER TABLE public."CacheJobTransactions" ALTER COLUMN "Created" DROP NOT NULL;
ALTER TABLE public."CacheJobTransactions" ALTER COLUMN "Created" SET DEFAULT TIMESTAMP '2019-12-10 20:46:52.316981';

ALTER TABLE public."CacheJobs" ALTER COLUMN "Modified" TYPE timestamp without time zone;
ALTER TABLE public."CacheJobs" ALTER COLUMN "Modified" DROP NOT NULL;
ALTER TABLE public."CacheJobs" ALTER COLUMN "Modified" SET DEFAULT TIMESTAMP '2019-12-10 20:46:52.337196';

ALTER TABLE public."CacheJobs" ALTER COLUMN "Created" TYPE timestamp without time zone;
ALTER TABLE public."CacheJobs" ALTER COLUMN "Created" DROP NOT NULL;
ALTER TABLE public."CacheJobs" ALTER COLUMN "Created" SET DEFAULT TIMESTAMP '2019-12-10 20:46:52.337159';

ALTER TABLE public."CacheJobDefinitions" ALTER COLUMN "Modified" TYPE timestamp without time zone;
ALTER TABLE public."CacheJobDefinitions" ALTER COLUMN "Modified" DROP NOT NULL;
ALTER TABLE public."CacheJobDefinitions" ALTER COLUMN "Modified" SET DEFAULT TIMESTAMP '2019-12-10 20:46:52.338084';

ALTER TABLE public."CacheJobDefinitions" ALTER COLUMN "Created" TYPE timestamp without time zone;
ALTER TABLE public."CacheJobDefinitions" ALTER COLUMN "Created" DROP NOT NULL;
ALTER TABLE public."CacheJobDefinitions" ALTER COLUMN "Created" SET DEFAULT TIMESTAMP '2019-12-10 20:46:52.338209';

INSERT INTO "__EFMigrationsHistory" ("MigrationId", "ProductVersion")
VALUES ('20191210204652_lastvisitedays', '3.1.0');

ALTER TABLE public."EnvironmentConfigurations" ALTER COLUMN "Modified" TYPE timestamp without time zone;
ALTER TABLE public."EnvironmentConfigurations" ALTER COLUMN "Modified" DROP NOT NULL;
ALTER TABLE public."EnvironmentConfigurations" ALTER COLUMN "Modified" SET DEFAULT TIMESTAMP '2019-12-11 03:48:03.320619';

ALTER TABLE public."EnvironmentConfigurations" ALTER COLUMN "Created" TYPE timestamp without time zone;
ALTER TABLE public."EnvironmentConfigurations" ALTER COLUMN "Created" DROP NOT NULL;
ALTER TABLE public."EnvironmentConfigurations" ALTER COLUMN "Created" SET DEFAULT TIMESTAMP '2019-12-11 03:48:03.320642';

ALTER TABLE public."EntitySectionAccesses" ALTER COLUMN "Modified" TYPE timestamp without time zone;
ALTER TABLE public."EntitySectionAccesses" ALTER COLUMN "Modified" DROP NOT NULL;
ALTER TABLE public."EntitySectionAccesses" ALTER COLUMN "Modified" SET DEFAULT TIMESTAMP '2019-12-11 03:48:03.314856';

ALTER TABLE public."EntitySectionAccesses" ALTER COLUMN "Created" TYPE timestamp without time zone;
ALTER TABLE public."EntitySectionAccesses" ALTER COLUMN "Created" DROP NOT NULL;
ALTER TABLE public."EntitySectionAccesses" ALTER COLUMN "Created" SET DEFAULT TIMESTAMP '2019-12-11 03:48:03.315722';

ALTER TABLE public."EntitySectionAccesses" ADD "SplitCharacter" text NOT NULL DEFAULT '';

ALTER TABLE public."CacheJobTransactions" ALTER COLUMN "Modified" TYPE timestamp without time zone;
ALTER TABLE public."CacheJobTransactions" ALTER COLUMN "Modified" DROP NOT NULL;
ALTER TABLE public."CacheJobTransactions" ALTER COLUMN "Modified" SET DEFAULT TIMESTAMP '2019-12-11 03:48:03.313604';

ALTER TABLE public."CacheJobTransactions" ALTER COLUMN "Created" TYPE timestamp without time zone;
ALTER TABLE public."CacheJobTransactions" ALTER COLUMN "Created" DROP NOT NULL;
ALTER TABLE public."CacheJobTransactions" ALTER COLUMN "Created" SET DEFAULT TIMESTAMP '2019-12-11 03:48:03.300624';

ALTER TABLE public."CacheJobs" ALTER COLUMN "Modified" TYPE timestamp without time zone;
ALTER TABLE public."CacheJobs" ALTER COLUMN "Modified" DROP NOT NULL;
ALTER TABLE public."CacheJobs" ALTER COLUMN "Modified" SET DEFAULT TIMESTAMP '2019-12-11 03:48:03.317884';

ALTER TABLE public."CacheJobs" ALTER COLUMN "Created" TYPE timestamp without time zone;
ALTER TABLE public."CacheJobs" ALTER COLUMN "Created" DROP NOT NULL;
ALTER TABLE public."CacheJobs" ALTER COLUMN "Created" SET DEFAULT TIMESTAMP '2019-12-11 03:48:03.317848';

ALTER TABLE public."CacheJobDefinitions" ALTER COLUMN "Modified" TYPE timestamp without time zone;
ALTER TABLE public."CacheJobDefinitions" ALTER COLUMN "Modified" DROP NOT NULL;
ALTER TABLE public."CacheJobDefinitions" ALTER COLUMN "Modified" SET DEFAULT TIMESTAMP '2019-12-11 03:48:03.318733';

ALTER TABLE public."CacheJobDefinitions" ALTER COLUMN "Created" TYPE timestamp without time zone;
ALTER TABLE public."CacheJobDefinitions" ALTER COLUMN "Created" DROP NOT NULL;
ALTER TABLE public."CacheJobDefinitions" ALTER COLUMN "Created" SET DEFAULT TIMESTAMP '2019-12-11 03:48:03.318839';

INSERT INTO "__EFMigrationsHistory" ("MigrationId", "ProductVersion")
VALUES ('20191211034803_splitcharacter', '3.1.0');

ALTER TABLE public."EnvironmentConfigurations" ALTER COLUMN "Modified" TYPE timestamp without time zone;
ALTER TABLE public."EnvironmentConfigurations" ALTER COLUMN "Modified" DROP NOT NULL;
ALTER TABLE public."EnvironmentConfigurations" ALTER COLUMN "Modified" SET DEFAULT TIMESTAMP '2019-12-12 13:05:59.627437';

ALTER TABLE public."EnvironmentConfigurations" ALTER COLUMN "Created" TYPE timestamp without time zone;
ALTER TABLE public."EnvironmentConfigurations" ALTER COLUMN "Created" DROP NOT NULL;
ALTER TABLE public."EnvironmentConfigurations" ALTER COLUMN "Created" SET DEFAULT TIMESTAMP '2019-12-12 13:05:59.627461';

ALTER TABLE public."EntitySectionAccesses" ALTER COLUMN "Modified" TYPE timestamp without time zone;
ALTER TABLE public."EntitySectionAccesses" ALTER COLUMN "Modified" DROP NOT NULL;
ALTER TABLE public."EntitySectionAccesses" ALTER COLUMN "Modified" SET DEFAULT TIMESTAMP '2019-12-12 13:05:59.620458';

ALTER TABLE public."EntitySectionAccesses" ALTER COLUMN "Created" TYPE timestamp without time zone;
ALTER TABLE public."EntitySectionAccesses" ALTER COLUMN "Created" DROP NOT NULL;
ALTER TABLE public."EntitySectionAccesses" ALTER COLUMN "Created" SET DEFAULT TIMESTAMP '2019-12-12 13:05:59.621455';

ALTER TABLE public."EntitySectionAccesses" ADD "DatabaseType" text NOT NULL DEFAULT '';

ALTER TABLE public."CacheJobTransactions" ALTER COLUMN "Modified" TYPE timestamp without time zone;
ALTER TABLE public."CacheJobTransactions" ALTER COLUMN "Modified" DROP NOT NULL;
ALTER TABLE public."CacheJobTransactions" ALTER COLUMN "Modified" SET DEFAULT TIMESTAMP '2019-12-12 13:05:59.619038';

ALTER TABLE public."CacheJobTransactions" ALTER COLUMN "Created" TYPE timestamp without time zone;
ALTER TABLE public."CacheJobTransactions" ALTER COLUMN "Created" DROP NOT NULL;
ALTER TABLE public."CacheJobTransactions" ALTER COLUMN "Created" SET DEFAULT TIMESTAMP '2019-12-12 13:05:59.604583';

ALTER TABLE public."CacheJobs" ALTER COLUMN "Modified" TYPE timestamp without time zone;
ALTER TABLE public."CacheJobs" ALTER COLUMN "Modified" DROP NOT NULL;
ALTER TABLE public."CacheJobs" ALTER COLUMN "Modified" SET DEFAULT TIMESTAMP '2019-12-12 13:05:59.6243';

ALTER TABLE public."CacheJobs" ALTER COLUMN "Created" TYPE timestamp without time zone;
ALTER TABLE public."CacheJobs" ALTER COLUMN "Created" DROP NOT NULL;
ALTER TABLE public."CacheJobs" ALTER COLUMN "Created" SET DEFAULT TIMESTAMP '2019-12-12 13:05:59.624227';

ALTER TABLE public."CacheJobDefinitions" ALTER COLUMN "Modified" TYPE timestamp without time zone;
ALTER TABLE public."CacheJobDefinitions" ALTER COLUMN "Modified" DROP NOT NULL;
ALTER TABLE public."CacheJobDefinitions" ALTER COLUMN "Modified" SET DEFAULT TIMESTAMP '2019-12-12 13:05:59.625285';

ALTER TABLE public."CacheJobDefinitions" ALTER COLUMN "Created" TYPE timestamp without time zone;
ALTER TABLE public."CacheJobDefinitions" ALTER COLUMN "Created" DROP NOT NULL;
ALTER TABLE public."CacheJobDefinitions" ALTER COLUMN "Created" SET DEFAULT TIMESTAMP '2019-12-12 13:05:59.625395';

INSERT INTO "__EFMigrationsHistory" ("MigrationId", "ProductVersion")
VALUES ('20191212130559_databasetype', '3.1.0');

ALTER TABLE public."EnvironmentConfigurations" ALTER COLUMN "Modified" TYPE timestamp without time zone;
ALTER TABLE public."EnvironmentConfigurations" ALTER COLUMN "Modified" DROP NOT NULL;
ALTER TABLE public."EnvironmentConfigurations" ALTER COLUMN "Modified" SET DEFAULT TIMESTAMP '2019-12-13 09:01:03.756245';

ALTER TABLE public."EnvironmentConfigurations" ALTER COLUMN "Created" TYPE timestamp without time zone;
ALTER TABLE public."EnvironmentConfigurations" ALTER COLUMN "Created" DROP NOT NULL;
ALTER TABLE public."EnvironmentConfigurations" ALTER COLUMN "Created" SET DEFAULT TIMESTAMP '2019-12-13 09:01:03.756276';

ALTER TABLE public."EntitySectionAccesses" ALTER COLUMN "Modified" TYPE timestamp without time zone;
ALTER TABLE public."EntitySectionAccesses" ALTER COLUMN "Modified" DROP NOT NULL;
ALTER TABLE public."EntitySectionAccesses" ALTER COLUMN "Modified" SET DEFAULT TIMESTAMP '2019-12-13 09:01:03.749201';

ALTER TABLE public."EntitySectionAccesses" ALTER COLUMN "Created" TYPE timestamp without time zone;
ALTER TABLE public."EntitySectionAccesses" ALTER COLUMN "Created" DROP NOT NULL;
ALTER TABLE public."EntitySectionAccesses" ALTER COLUMN "Created" SET DEFAULT TIMESTAMP '2019-12-13 09:01:03.750322';

ALTER TABLE public."EntitySectionAccesses" ADD "DatabaseSchema" text NOT NULL DEFAULT '';

ALTER TABLE public."CacheJobTransactions" ALTER COLUMN "Modified" TYPE timestamp without time zone;
ALTER TABLE public."CacheJobTransactions" ALTER COLUMN "Modified" DROP NOT NULL;
ALTER TABLE public."CacheJobTransactions" ALTER COLUMN "Modified" SET DEFAULT TIMESTAMP '2019-12-13 09:01:03.747591';

ALTER TABLE public."CacheJobTransactions" ALTER COLUMN "Created" TYPE timestamp without time zone;
ALTER TABLE public."CacheJobTransactions" ALTER COLUMN "Created" DROP NOT NULL;
ALTER TABLE public."CacheJobTransactions" ALTER COLUMN "Created" SET DEFAULT TIMESTAMP '2019-12-13 09:01:03.731013';

ALTER TABLE public."CacheJobs" ALTER COLUMN "Modified" TYPE timestamp without time zone;
ALTER TABLE public."CacheJobs" ALTER COLUMN "Modified" DROP NOT NULL;
ALTER TABLE public."CacheJobs" ALTER COLUMN "Modified" SET DEFAULT TIMESTAMP '2019-12-13 09:01:03.752781';

ALTER TABLE public."CacheJobs" ALTER COLUMN "Created" TYPE timestamp without time zone;
ALTER TABLE public."CacheJobs" ALTER COLUMN "Created" DROP NOT NULL;
ALTER TABLE public."CacheJobs" ALTER COLUMN "Created" SET DEFAULT TIMESTAMP '2019-12-13 09:01:03.75273';

ALTER TABLE public."CacheJobDefinitions" ALTER COLUMN "Modified" TYPE timestamp without time zone;
ALTER TABLE public."CacheJobDefinitions" ALTER COLUMN "Modified" DROP NOT NULL;
ALTER TABLE public."CacheJobDefinitions" ALTER COLUMN "Modified" SET DEFAULT TIMESTAMP '2019-12-13 09:01:03.753805';

ALTER TABLE public."CacheJobDefinitions" ALTER COLUMN "Created" TYPE timestamp without time zone;
ALTER TABLE public."CacheJobDefinitions" ALTER COLUMN "Created" DROP NOT NULL;
ALTER TABLE public."CacheJobDefinitions" ALTER COLUMN "Created" SET DEFAULT TIMESTAMP '2019-12-13 09:01:03.753943';

INSERT INTO "__EFMigrationsHistory" ("MigrationId", "ProductVersion")
VALUES ('20191213090104_databaseschema', '3.1.0');

ALTER TABLE public."EnvironmentConfigurations" ALTER COLUMN "Modified" TYPE timestamp without time zone;
ALTER TABLE public."EnvironmentConfigurations" ALTER COLUMN "Modified" DROP NOT NULL;
ALTER TABLE public."EnvironmentConfigurations" ALTER COLUMN "Modified" SET DEFAULT TIMESTAMP '2019-12-16 07:25:35.469225';

ALTER TABLE public."EnvironmentConfigurations" ALTER COLUMN "Created" TYPE timestamp without time zone;
ALTER TABLE public."EnvironmentConfigurations" ALTER COLUMN "Created" DROP NOT NULL;
ALTER TABLE public."EnvironmentConfigurations" ALTER COLUMN "Created" SET DEFAULT TIMESTAMP '2019-12-16 07:25:35.469254';

ALTER TABLE public."EntitySectionAccesses" ALTER COLUMN "Modified" TYPE timestamp without time zone;
ALTER TABLE public."EntitySectionAccesses" ALTER COLUMN "Modified" DROP NOT NULL;
ALTER TABLE public."EntitySectionAccesses" ALTER COLUMN "Modified" SET DEFAULT TIMESTAMP '2019-12-16 07:25:35.462611';

ALTER TABLE public."EntitySectionAccesses" ALTER COLUMN "Created" TYPE timestamp without time zone;
ALTER TABLE public."EntitySectionAccesses" ALTER COLUMN "Created" DROP NOT NULL;
ALTER TABLE public."EntitySectionAccesses" ALTER COLUMN "Created" SET DEFAULT TIMESTAMP '2019-12-16 07:25:35.463704';

ALTER TABLE public."EntitySectionAccesses" ADD "WhereClauseLiteralPath" text NOT NULL DEFAULT '';

ALTER TABLE public."CacheJobTransactions" ALTER COLUMN "Modified" TYPE timestamp without time zone;
ALTER TABLE public."CacheJobTransactions" ALTER COLUMN "Modified" DROP NOT NULL;
ALTER TABLE public."CacheJobTransactions" ALTER COLUMN "Modified" SET DEFAULT TIMESTAMP '2019-12-16 07:25:35.461037';

ALTER TABLE public."CacheJobTransactions" ALTER COLUMN "Created" TYPE timestamp without time zone;
ALTER TABLE public."CacheJobTransactions" ALTER COLUMN "Created" DROP NOT NULL;
ALTER TABLE public."CacheJobTransactions" ALTER COLUMN "Created" SET DEFAULT TIMESTAMP '2019-12-16 07:25:35.444847';

ALTER TABLE public."CacheJobs" ALTER COLUMN "Modified" TYPE timestamp without time zone;
ALTER TABLE public."CacheJobs" ALTER COLUMN "Modified" DROP NOT NULL;
ALTER TABLE public."CacheJobs" ALTER COLUMN "Modified" SET DEFAULT TIMESTAMP '2019-12-16 07:25:35.465955';

ALTER TABLE public."CacheJobs" ALTER COLUMN "Created" TYPE timestamp without time zone;
ALTER TABLE public."CacheJobs" ALTER COLUMN "Created" DROP NOT NULL;
ALTER TABLE public."CacheJobs" ALTER COLUMN "Created" SET DEFAULT TIMESTAMP '2019-12-16 07:25:35.465913';

ALTER TABLE public."CacheJobDefinitions" ALTER COLUMN "Modified" TYPE timestamp without time zone;
ALTER TABLE public."CacheJobDefinitions" ALTER COLUMN "Modified" DROP NOT NULL;
ALTER TABLE public."CacheJobDefinitions" ALTER COLUMN "Modified" SET DEFAULT TIMESTAMP '2019-12-16 07:25:35.466928';

ALTER TABLE public."CacheJobDefinitions" ALTER COLUMN "Created" TYPE timestamp without time zone;
ALTER TABLE public."CacheJobDefinitions" ALTER COLUMN "Created" DROP NOT NULL;
ALTER TABLE public."CacheJobDefinitions" ALTER COLUMN "Created" SET DEFAULT TIMESTAMP '2019-12-16 07:25:35.467071';

INSERT INTO "__EFMigrationsHistory" ("MigrationId", "ProductVersion")
VALUES ('20191216072535_WhereClause', '3.1.0');

ALTER TABLE public."EnvironmentConfigurations" ALTER COLUMN "Modified" TYPE timestamp without time zone;
ALTER TABLE public."EnvironmentConfigurations" ALTER COLUMN "Modified" DROP NOT NULL;
ALTER TABLE public."EnvironmentConfigurations" ALTER COLUMN "Modified" SET DEFAULT TIMESTAMP '2019-12-23 21:43:08.533211';

ALTER TABLE public."EnvironmentConfigurations" ALTER COLUMN "Created" TYPE timestamp without time zone;
ALTER TABLE public."EnvironmentConfigurations" ALTER COLUMN "Created" DROP NOT NULL;
ALTER TABLE public."EnvironmentConfigurations" ALTER COLUMN "Created" SET DEFAULT TIMESTAMP '2019-12-23 21:43:08.533237';

ALTER TABLE public."EntitySectionAccesses" ALTER COLUMN "Modified" TYPE timestamp without time zone;
ALTER TABLE public."EntitySectionAccesses" ALTER COLUMN "Modified" DROP NOT NULL;
ALTER TABLE public."EntitySectionAccesses" ALTER COLUMN "Modified" SET DEFAULT TIMESTAMP '2019-12-23 21:43:08.526973';

ALTER TABLE public."EntitySectionAccesses" ALTER COLUMN "Created" TYPE timestamp without time zone;
ALTER TABLE public."EntitySectionAccesses" ALTER COLUMN "Created" DROP NOT NULL;
ALTER TABLE public."EntitySectionAccesses" ALTER COLUMN "Created" SET DEFAULT TIMESTAMP '2019-12-23 21:43:08.527993';

ALTER TABLE public."CacheJobTransactions" ALTER COLUMN "Modified" TYPE timestamp without time zone;
ALTER TABLE public."CacheJobTransactions" ALTER COLUMN "Modified" DROP NOT NULL;
ALTER TABLE public."CacheJobTransactions" ALTER COLUMN "Modified" SET DEFAULT TIMESTAMP '2019-12-23 21:43:08.525513';

ALTER TABLE public."CacheJobTransactions" ALTER COLUMN "Created" TYPE timestamp without time zone;
ALTER TABLE public."CacheJobTransactions" ALTER COLUMN "Created" DROP NOT NULL;
ALTER TABLE public."CacheJobTransactions" ALTER COLUMN "Created" SET DEFAULT TIMESTAMP '2019-12-23 21:43:08.512593';

ALTER TABLE public."CacheJobTransactions" ADD "ApplicationId" text NULL;

ALTER TABLE public."CacheJobTransactions" ADD "EngineHostname" text NULL;

ALTER TABLE public."CacheJobTransactions" ADD "UserId" text NULL;

ALTER TABLE public."CacheJobs" ALTER COLUMN "Modified" TYPE timestamp without time zone;
ALTER TABLE public."CacheJobs" ALTER COLUMN "Modified" DROP NOT NULL;
ALTER TABLE public."CacheJobs" ALTER COLUMN "Modified" SET DEFAULT TIMESTAMP '2019-12-23 21:43:08.53026';

ALTER TABLE public."CacheJobs" ALTER COLUMN "Created" TYPE timestamp without time zone;
ALTER TABLE public."CacheJobs" ALTER COLUMN "Created" DROP NOT NULL;
ALTER TABLE public."CacheJobs" ALTER COLUMN "Created" SET DEFAULT TIMESTAMP '2019-12-23 21:43:08.530224';

ALTER TABLE public."CacheJobDefinitions" ALTER COLUMN "Modified" TYPE timestamp without time zone;
ALTER TABLE public."CacheJobDefinitions" ALTER COLUMN "Modified" DROP NOT NULL;
ALTER TABLE public."CacheJobDefinitions" ALTER COLUMN "Modified" SET DEFAULT TIMESTAMP '2019-12-23 21:43:08.531081';

ALTER TABLE public."CacheJobDefinitions" ALTER COLUMN "Created" TYPE timestamp without time zone;
ALTER TABLE public."CacheJobDefinitions" ALTER COLUMN "Created" DROP NOT NULL;
ALTER TABLE public."CacheJobDefinitions" ALTER COLUMN "Created" SET DEFAULT TIMESTAMP '2019-12-23 21:43:08.531199';

INSERT INTO "__EFMigrationsHistory" ("MigrationId", "ProductVersion")
VALUES ('20191223214308_cachetransactionsupdate', '3.1.0');
  1. Stop all of the QlikBlazor Services
  2. Within the QlikBlazor HOME you chose in the install phase, enter the Repository folder’s appsettings.json file. You will need to use the syntax described here to connect to a postgres 12 instance.

Uninstalling

  1. Close the windows service manager (service.msc), if open
  2. Open a command or powershell window
  3. Navigate to the downloaded location of the QlikBlazor.Installer.exe
  4. execute the installation
  5. follow the prompts

Monitoring the Services

  • QlikBlazorDatabase
    • PostGreSQL instance. The logging and monitoring of this can be done with standard postgres monitoring tools
  • QlikBlazorRepository
    • .\logs – directory listens for errors, debug, and information about the the service and its execution. The syntax used in the .\appsettings.json in logging can be found here.
  • QlikBlazorFrontend
    • Dashboard displays jobs (batches of job transactions). Each completed job represents one instance of a cache job definition (cache job)
  • QlikBlazorEngine
    • .\logs – directory listens for errors, debug, and information about the the service and its execution. The syntax used in the .\appsettings.json in logging can be found here.
  • QlikBlazorHealthcheck
    • .\logs – directory listens for errors, debug, and information about the the service and its execution. The information level logging will print the engine healthcheck data harvested from each engine server listed in the repository. The syntax used in the .\appsettings.json in logging can be found here.

Appendices

  • Postgres – Short form for PostgreSQL
  • QMC – Qlik Sense Management Console
  • App – Qlik Sense Application (QVF)
  • Concurrency – The # of users and apps open (engine sessions) served within a given hour on Qlik Sense Engine