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?
The Challenge
The Solution
There are a few options:
- Continue with the scheduling estimation across multiple platforms;
- Create PowerShell scripts for each app to be reloaded and trigger these at the end of an Alteryx workflow using the 'Run command' tool;
- Develop a process for creating PowerShell scripts and triggering Qlik tasks via an automated process from Alteryx.
Pro's and Con's:
- Inefficient and adds risk.
- Would provide some level of automation, however adds an administration and management overhead due to creation of multiple PowerShell scripts.
- 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
Step one - The PowerShell script
- Authenticate against the Qlik Server;
- Connect to the Qlik Server;
- Define the Qlik app to be reloaded;
- Identify the Qlik (QMC) Task to be triggered;
- Trigger QMC task.
# 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'
}
- Connection certificate location;
- Qlik server to connect to;
- App we want to reload;
- Location to write out any check files.
Building this out in Alteryx
'# 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.
Comments
Post a Comment