Analytics Process Automation with Alteryx and Qlik Sense.

Following up from my last post which talked through how we can utilise the power of Qlik-CLI to trigger app reloads from app load scripts, I'm going to talk through how we can expand out the use of the Qlik-CLI functionality to trigger app reloads from Alteryx.

If you've not yet read part one of this blog series and want to understand more about Qlik-CLI within Qlik Sense before you begin; you can read that post here.

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 Qlik-CLI needs to be installed on any machine from where you want to trigger a Qlik task, in the case of Alteryx, we installed the Qlik-CLI on the Alteryx Server.  For more information on installing Qlik-CLI, check the first post in this series, 'Building Scalable Analytics Automation'.

The Challenge

There are many scenarios where we have complex data blending taking place within our Alteryx workflows which we want to be visualised and presented within our Qlik Sense apps.  Both separate platforms with independent and disconnected scheduling, this results in some level of 'estimation' needed to ensure that we space out the relevant schedules on the Alteryx and Qlik servers to ensure the Alteryx workflow completes before the Qlik Sense app needs to refresh and ensuring all data is up to date. 

From here we assume that Qlik-CLI is installed and configured on the Alteryx server.  

The Solution

So, how can we manage Alteryx and Qlik tasks? 

There are a few options:

1. Continue with the scheduling estimation across multiple platforms;
2. Create PowerShell scripts for each app to be reloaded and trigger these at the end of an Alteryx workflow using the  'Run command' tool;
3. Develop a process for creating PowerShell scripts and triggering Qlik tasks via an automated process from Alteryx.

Pro's and Con's:

1. Inefficient and adds risk.
2. Would provide some level of automation, however adds an administration and management overhead due to creation of multiple PowerShell scripts. 
3. Build it once, use it regularly solution provides a single point of management and flexibility to reuse with no or little remedial action.

The approach

In order to build out a reusable solution, the aim is to use the Alteryx Macro functionality to produce a new 'Alteryx tool' which will dynamically create the PowerShell script, write the file to a common location and then execute the PowerShell script to trigger the Qlik task.

Step one - The PowerShell script

Before we attempt to dynamically build the PowerShell script, we need to understand what components of the Qlik-CLI we need to utilise and the steps needed though the script in order to trigger the Qlik task.

So, what do we need to do?

1. Authenticate against the Qlik Server;
2. Connect to the Qlik Server;
3. Define the Qlik app to be reloaded;
4. Identify the Qlik (QMC) Task to be triggered;
5. Trigger QMC task.

What does that look like in PowerShell?

# 1. Authenticate connection certificate
Get-PFxCertificate "C:\Qlik Sense Certs\[Machine Name]\client.pfx"
# 2. Connect to Qlik 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
 
IF ($FileExists -eq $True)
{
    Remove-Item $CheckFile
}
Else 
{

        # 3. Get Task details from App name 

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

# 4 / 5. Get Task ID from App name and trigger task 

    Start-QlikTask $Task[0].id

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

    Get-QlikReloadTask -Id $Task[0].id 
    Write-Output $Task[0].name ' has complete'
}

From this we can easily identify the core aspects of the script which we need to look to transfer to dynamic. 

1. Connection certificate location;
2. Qlik server to connect to;
3. App we want to reload;
4. Location to write out any check files.

Building this out in Alteryx

My approach to building this out in Alteryx was to first use a simple workflow to create a formula tool which  dynamically adds the 4 components, above.  From there, transpose that to a macro and add in the relevant inputs to support the reusability of the macro.

The Data input is a Text input which includes 5 columns, reach representing the components outlined earlier, in this instance there are tow check file locations, hence 5 columns rather than 4.

The formula tool builds out the PowerShell script text (see script below), substituting hard coded values for those (identified in red) configured in the Text input tool (1).

'# Call PIF Admin App reload (Triggered from PIF App)' + "
" + '# Activate connection certificate' + "
" + 'Get-PFxCertificate ' + '"' + [CertificateLocation] + '"' + "
" + '#Connect to Qlik Server Node' + "
" + 'Connect-Qlik -Computername ' +[ServerName] + "
" + '#Check for file' + "
" + '$CheckFile = "C:\_Temp\CheckFile_PIF.txt"' + "
" + '$Flagfile = "C:\_Temp\CheckFile_PIFAdmin.txt" ' + "
" + '$FileExists = Test-Path $CheckFile' + "
" + '#Get Task ID from App name - ' + [AppName] + "
" + ' IF ($FileExists -eq $True)' + "
" + '{' + "
" + 'Remove-Item $CheckFile' + "
" + '}' + "
" + 'ELSE' + "
" + '{' + "
" + '$Task = Get-QlikReloadTask -filter "app.name eq ' + "'" + [AppName] + "'" + '"' + "
" + 'Start-QlikTask $Task[0].id' + "
" + 'Wait-QlikExecution -TaskId $Task[0].id' + "
" + 'Get-QlikReloadTask -Id $Task[0].id' + "
" + ' Write-Output $Task[0].name ' + 'has complete' + "
" + '}'

The script includes " at the end and beginning of each line, that's due my tidy script mind, to ensure that each line is pushed to a new line correctly when the text field is written to the PowerShell script file.  Each line is concatenated together (+ concatenation modifier) within the formula to create a complete text field.  The " (quotes) at the end and beginning of each line, with a charge return between each is used to help format the PowerShell script in to a nice readable format.

The formula tool (2) builds out the script as a text field, adding the dynamic entities from the fields within the preceding text input tool. The data is then cleansed via the auto field tool (3) to ensure that the script field does not loose any characters through truncation, etc... The script field is then selected via the select tool (4) as the only field to progress through the remainder of the workflow.  The script is then written to a PowerShell script file, through the Output tool (5).  

In order to create the PowerShell file, the 'Output Data' tool needs to be configured to use a Flat ASCII (*.flat) file format.

In order to mitigate any risk of the file taking to long to be created, the wait tool (6) pauses the workflow for a few seconds. Finally the run command tool (7) then triggers the PowerShell script to trigger the required Qlik task.

Now we have an workflow which can trigger a Qlik based app reload task, we could reuse this and alter the inputs from the text input tool each time it is to be reused.  Whilst that would meet the requirement, it's not particularly reusable, there is a need to either rebuild this into each workflow or a 'copy and paste' exercise from one workflow to the next.

In comes the Alteryx Macro.....

By adding in a few control parameters in to the workflow to allow dynamic changes to the five components, we can make the workflow a reusable tool within Alteryx.

Each column from the text input tool has a corresponding control parameter and update tool, with each update tool associated to a column within the text input tool.

When the macro is used within a workflow, these become the macro inputs, the rest of the workflow works in exactly the same was as the steps we have just walked through.

Hopefully this has given you a little insight and food for thought in to how you can utilise the power of the Qlik-CLI from with Alteryx to build a scalable, end to end analytics process automation process to bring data from source to the end user.   

Where next?

Consider the use case of Sales Forecasting, maybe an Alteryx Gallery app which requires the user to input or provide a link to the data then run the workflow to update the data, this data feeds a Qlik based app.  Providing the ability to trigger the workflow and then update the app to the end user brings self service analytics process automation, a truly powerful concept to enhance the user experience. Possibly the next step in our journey! 

It would be great to hear about the innovative ways in which you utilise the Qlik-CLI.  Feel free to share your experiences in the comments section.

If you have any questions, feel free to get in touch with me.  

More Info

Qlik

Qlik Branch - Qlik- Cli