Building scalable analytics automation

 

There are many occasions when following completion of an Alteryx workflow, we'd like to reload our Qlik Apps.  Much of the time, that involves making some estimation as to when the app reload should be scheduled based on average time it takes for the workflow to complete, plus an arbitory additional period of time, 'just in case'.

Much of the time, that works pretty well.  Workflows run our nightly processes so triggering the app reload 30 minutes later doesn't impact business processes or users needs.

When we begin to introduce user interaction with our workflows through Gallery or though Qlik based functionality, the challenges begin.  

A recent project found us in the realms of Qlik app reloads and supporting user driven app reloads.

Using the VizLib Collaboration tools, we introduced a platform to manage product data from the stage of ideation.  VizLib forms provide the functionality from a user perspective to 'add' data from within a Qlik Sense app, reloading an app when the user submits their form.  

In our recent project, we needed the data from the form to also be pulled in to other apps.  How do we achieve that?

• Wait for the next scheduled reload of the app;
• Think outside the box and find a way to automat the process.

Yes, you guested it, we looked in to the second option.  In comes Qlik-CLI (Qlik Command line interface).

What is Qlik-CLI?

Qlik-CLI is a PowerShell module which supports the management of Qlik Sense environments via command line scripts.  Allowing for the viewing and editing of configuration, task and other functions accessible via API through PowerShell commands and scripts.  In our case we used the Qlik-CLI available from Qlik Branch (Adam Haydon).  Check out the link to find out more about how to go about the installation of Qlik-CLI.

The Challenge

Users enter data though a Qlik Sense app  (app 1) containing input forms, on completion of the form, when the user selects 'submit' this triggers the data to be written to the defined source and a reload of the data within the app. However, there is a further need to reload other apps, so that they can also ingest the data entered within app 1.

As the reload of app 1 is triggered from within the VizLib form and not via QMC based tasks, it's difficult to chain reloads for multiple apps.  How do we achieve this?

The solution

The fundamentals

Using the cmdlets within the Qlik-Cli we build up a PowerShell script to trigger app reloads, frm there, we need to think a little more creatively.

First lets look at the way we build up the script, I use Microsoft Visual Studio Code to build out my scripts, but you can use your prefered PowerShell ISE.

First thing we need to do is set up a connection to the Qlik Environment, to do this we need to referance a certificate exported from the Qlik QMC.

1. Within the QLik QMC navigate to certificates and export a set of certificates for the machine you'll be running the PowerShell scripts from. (Check out the Qlik Help on certificates for more info).
2. Save the exported certificates to an accessible location on your machine.

Now we can begin scripting.  

Get a full list of available cmdlets within Qlik-CLI here.

Using the cmdlett Get-PFxCertificate we activate the certificate to authenticate our machine to the Qlik Server.

# Activate connection certificate

Get-PFxCertificate "C:\Qlik Sense Certs\[machine name]\client.pfx" 

Next step is to connect to the Qlik environment.  Using the cmdlett Connect-Qlik, [server name] identifies the Qlik environment to connect to and future cmdlets will use this session to invoke commands.

#Connect to dQlik Server Node

Connect-Qlik -Computername [server name]

Once we are connected, in order to trigger a reload we need to identify the reload task we want to trigger.  We do this by firstly retrieving the task details based on the app name. The cmdlet Get-QlikReloadTask with a filter retrieves the task meta data for use in the next step.  

$Task = Get-QlikReloadTask -filter "app.name eq '[App name]'" 

Set this to a variable $Task to make referencing this later a little easier, we only need use $Task rather than the full command.

To then trigger the reload task for our second app, we use the cmdlet Start-QlikTask passing in the variable $Task to get the task Id.  The 0 in the square brackets is an array pointer, if there are multiple tasks associated to the App we filtered on, these are held in the array.  In this case we only have one so this resides in the first place of the array.

Start-QlikTask $Task[0].id

To ensure that the task completes before we move on, we include a 'pause' using the cmdlet Wait-QlikExecution, again passing in the task id as our control parameter.

Wait-QlikExecution -TaskId $Task[0].id

We can check on the status of a task using the Get-ReloadTask this time we use the -Id parameter and pass in the reload task Id

Get-QlikReloadTask -Id $Task[0].id 

Below is an example of the output from the Get-QlikRelaodTask cmdlet

id                  : 6eca2542-0db7-4a46-8941-71cf9f00dbe7

createdDate         : 2020/11/23 14:09

modifiedDate        : 2020/11/23 14:09

modifiedByUserName  : [User Name]

customProperties    : {}

app                 : @{id=7387f270-75da-4823-be93-00b3722e70d3; name=PDH - 3.1 - PIF - Admin(1); appId=; publishTime=1753/01/01 00:00; published=False; stream=; savedInProductVersion=12.763.6; migrationHash=504d4e39a7133ee172fbe29aa58348b1e4054149; availabilityStatus=NotApplicable; privileges=}

isManuallyTriggered : False

operational         : @{id=24c7c2b9-a9d5-49fe-bcff-cd8ebb917f9b; lastExecutionResult=; nextExecution=1753/01/01 00:00; privileges=}

name                : Reload task of [App name]

taskType            : Reload

enabled             : True

taskSessionTimeout  : 1440

maxRetries          : 0

tags                :

privileges          :

schemaPath          : ReloadTask

We have now successfully triggered a reload of a task via the command line.  Check the QMC for your task and you should see it either running or it's last run time will be within the last minute or so (depending on how quick you check).

Now, how do we put that in to an operational scenario?

Building the script

Using the steps we have walked through, we build these up in to a script, with a few additional steps and save as a PowerShell script (.ps1)

# Activate connection certificate

Get-PFxCertificate "C:\Qlik Sense Certs\[Machine Name]\client.pfx"

#Connect to dQlik Server Node

Connect-Qlik -Computername [Qlik Server Name]

#Check for file

$CheckFile = 'C:\_Temp\CheckFile_1.txt'

$Flagfile = 'C:\_Temp\CheckFile_2.txt'

$FileExists = Test-Path $CheckFile

#Get Task ID from App name 

IF ($FileExists -eq $True)

{

    Remove-Item $CheckFile

}

Else 

{

    $Task = Get-QlikReloadTask -filter "app.name eq 'App1'" 

    Start-QlikTask $Task[0].id

    

    Wait-QlikExecution -TaskId $Task[0].id

    Get-QlikReloadTask -Id $Task[0].id 

    Write-Output $Task[0].name ' has complete'

}

Walking through the script, 

1. We authenticate with the certificate and then connect to the Qlik environment;
2. We define variables for check and flag files ($CheckFile, $FlagFile), then create the variable to verify the check file exists ($File Exists), using the cmdlet Test-Path passing in the path from the variable $CheckFile;
3. The IF statement first checks for the exitance of the Check File, 
1. If it exists, it is deleted and the script exits.
2. If it does not exist, the set of cmdlets to instigate a task reload within the QMC are triggered.

Once we have built this script up and saved as a PowerShell script file, we can instigate the script and run the reload via any platform which can activate external scripts, Windows Scheduler, Alteryx (cmd tool), or even Qlik Sense.

Trigger the script from Qlik Sense

Prerequisites

There are a few security considerations before looking at triggering externals scripts from Qlik Sense. In the standard mode which Qlik Sense runs as by default, access to relative or absolute file paths are restricted, only library paths are allowed.  In order to support execution and access to relative / Absolut paths, Qlik Sense has to be configured to run in 'Legacy' mode (turn off standard mode within the Qlik Engine).

Before proceeding, read the Qlik help pages on disabling standard mode.  Otherwise here is what you need to do:

Navigate to the Qlik Management Console (QMC), Engines, Central and expand the 'Advanced' option on the right hand menu.

From here, deselect the 'Standard Mode' check box and select apply.  You'll be prompted to restart the Qlik Sense Engine.

Access to relative and absolute file paths will ne be available across your Qlik Sense environment (use with caution).

Finally, we need to add a line in to the .ini file to allow external application execution.

You must run in legacy mode (applicable for Qlik Sense and Qlik Sense Desktop).

You need to set OverrideScriptSecurity to 1 in Settings.ini (applicable for Qlik Sense).

Settings.ini is located in C:\ProgramData\Qlik\Sense\Engine\ and is generally an empty file.

To make the changes, do the following:

• Make a copy of Settings.ini and open it in a text editor.
• Check that the file includes [Settings 7] in the first line.
• Insert a new line and type OverrideScriptSecurity=1.
• Insert an empty line at the end of the file.Save the file.
• Substitute Settings.ini with your edited file.
• Restart Qlik Sense Engine Service (QES).

Warning: If you set OverrideScriptSecurity to enable Execute, any user can execute files on the server. For example, a user can attach an executable file to an app, and then execute the file in the data load script.

Now to the cool part, putting this all in to your Qlik app and triggering the reload of one app from another.

In the script editor of your app add in a tab at the end and add the following (changing the script paths, etc.. you your own appropriate locations):

execute PowerShell.exe  "& 'C:\[path to PowerShell Script]\ReloadTask_WithFileCheck.ps1'";

CheckFlag_Tmp:

Load * Inline [Now];

STORE * FROM CheckFlag_Tmp INTO 'C:\[Path to location for check files]\CheckFile.csv'(txt);

Drop Table CheckFlag_Tmp;

The execute statement triggers the PowerShell to reload the second app, whilst the Store creates a blank file which is used in the PowerShell to check if the app has been reloaded.

In our use case, we wanted both apps to trigger a reload of each other. In order to avoid a infinite reload loop, in the PowerShell script, we check for the existence of the check files from both apps. If they exist, they are removed and the script exits. Eliminating the possibility of a never ending reload loop.

#Check for file

$CheckFile = 'C:\_Temp\CheckFile_1.txt'

$Flagfile = 'C:\_Temp\CheckFile_2.txt'

$FileExists = Test-Path $CheckFile

$FileExists2 = Test-Path $FlagFile

IF ($FileExists -eq $True,

$FileExists2 -eq $True)

{

    Remove-Item $CheckFile

Remove-Item $FlagFile

}

Else 

{

    $Task = Get-QlikReloadTask -filter "app.name eq 'App1'" 

    Start-QlikTask $Task[0].id

    

    Wait-QlikExecution -TaskId $Task[0].id

    Get-QlikReloadTask -Id $Task[0].id 

    Write-Output $Task[0].name ' has complete'

}

That's how to trigger the reload of a Qlik sense app from a second app using the Qlik-CLI.  In my next post on this, I'll be going through how we can utilise the Qlik-CLI from Alteryx workflows.