topic Re: QlikSense Tasks Gantt Waterfall Chart & Analyse Corresponding QVDs in Management & Governance

QlikSense Tasks Gantt Waterfall Chart & Analyse Corresponding QVDs

cheenu_janakira — Mon, 05 Oct 2020 19:34:11 GMT

Hi everyone,

Latest version (October 2020) here, also comes with a script log analyser that links back to the task and shows the QVD dependencies.

I have been trying to find some visualisation of QlikSense Tasks as a Gantt waterfall and tried working on the Ops Monitor app to add these. I finally chose to go sideways and make a mix between a 'QMC Sense Tasks Gantt-Waterfall' and a previously developed 'QVD Analyser' so that I could look at the full flow from Task > App > QVD files, tables and fields.

Special thanks to bill.markham‌ for his comprehensive feedback and editing and improving this script/app to its final stage.

Special thanks to hic‌‌ for sharing his folder analyser script.

Special thanks to Kai Hilton-Jones, whose GitHub - kaihj/qlik-sense-timeline: Qlik Sense Timeline Chart chart you need to install as an extension to view the Tasks Gantt Waterfall. You then need to configure this chart with:

Task Name (sorted by Expression: "MaxString([Reload Start Time])")
Reload Start
Reload Finish

On the "Task Set Sequence Number", choose one of the higher value to see only one days worth of reloads (a 24 hour set). I have set the back-end code so that the sequence takes all tasks after COB (post 18h00) and groups them with the tasks running until next COB (so that all overnight loads are grouped). Please change the code if you want a different time horizon.

4 things need to be changed in the script:

Binary load: Point to your own Ops Monitor (do not include ".qvf" extension if running this app on the server directly). You will need to create a LIB connection to the Apps folder where the original Ops Mon is stored. (Or use an INCLUDE function to attached "OpsMonScript.txt" file).
The "vQMC_QVD_Store" variable: It should point to a LIBRARY folder where you would like to STORE (and load from) the QVDs from the Ops Monitor data model from the above BINARY load.
Edit the "Full_Paths_To_Folder" table so that you have a full list of LIBRARY connections to where you STORE all QVDs for which you would like to collect their XML Header info for further visibility and analysis.
(Optional) Setting the server name in variable "vQlikSenseServer" allows you to open the relevant app via URL hyperlink.

IMPORTANT NOTE: Due to the potential pervasiveness of script variable values in some Sense scripts which would end up in script errors, I include another file with the script of this app. I could be that you would require to copy/paste elements into a new/fresh file in order to allow the script to "clear out" and start with a new variable value.

Hope this helps.

Kr,

Cheenu

Re: QlikSense Tasks Gantt Waterfall Chart & Analyse Corresponding QVDs

Anonymous — Sat, 25 Feb 2017 16:06:59 GMT

Many thanks for sharing this qvf, sounds really useful - I'll give it a go.

Re: QlikSense Tasks Gantt Waterfall Chart & Analyse Corresponding QVDs

cheenu_janakira — Sat, 25 Feb 2017 17:50:24 GMT

Hi Bill,

I've made a couple of slight changes on the front-end and refreshed the attached app. It also looks at file sizes.

Please give me your feedback and let me know how it goes and if I've missed anything out and can make the implementation/integration process easier.

Cheerio,

Cheenu

Re: QlikSense Tasks Gantt Waterfall Chart & Analyse Corresponding QVDs

Anonymous — Sat, 25 Feb 2017 20:51:28 GMT

Unfortunately the reload fails like this :

App_Info_Time << App

Lines fetched: 43

App_Info << App

Lines fetched: 43

Task_Info << Task

(QVD (row-based) optimized)

Lines fetched: 28

Task_Name << TaskName

(QVD (row-based) optimized)

Lines fetched: 52

The following error occurred:

Field '_reloadSummaryTaskId' not found

The error occurred here:

Data has not been loaded. Please correct the error and try loading again.

The fields in [$(vQMC_QVD_Store)ReloadSummary.qvd] do not include one called _reloadSummaryTaskId, here are the fields that do exist :

"Reload Start",

_proxySessionPackage,

TaskExecution,

"Reload Finish",

"Reload Result Code",

"Reload Status",

"Reload Failure",

"Reload Duration"

I am using QlikSense 3.1 SR5, what version are you using ?

Re: QlikSense Tasks Gantt Waterfall Chart & Analyse Corresponding QVDs

cheenu_janakira — Sat, 25 Feb 2017 21:03:42 GMT

Client 3.15.5.

I can see the field twice in "SUB reloadSummary" tab in the Ops Monitor. Can you check the Ops Mon script.

The field in Ops Mon is indicated as "TaskId as _reloadSummaryTaskId, //For task chain duration analysis" on line 27 of the script tab.

Can you see something similar in your version?

Re: QlikSense Tasks Gantt Waterfall Chart & Analyse Corresponding QVDs

Anonymous — Sat, 25 Feb 2017 21:33:13 GMT

At the end of tab SUB qrs_task in my Operations Monitor the field _reloadSummaryTaskId is dropped.

Drop Field _reloadSummaryTaskId;

// Field no longer needed on Reload Summary table

Re: QlikSense Tasks Gantt Waterfall Chart & Analyse Corresponding QVDs

Anonymous — Sat, 25 Feb 2017 22:31:27 GMT

I commented out that drop in my Operations Monitor and it got past that error.

But then error'ed at the end of your script as OpsMonReloadSummary.qvd does not exist. When I commented out this load then your script ran fine.

LOAD

"Reload Start",

_proxySessionPackage,

TaskExecution,

"Reload Finish",

"Reload Result Code",

"Reload Status",

"Reload Failure",

"Reload Duration"

FROM [$(vQMC_QVD_Store)OpsMonReloadSummary.qvd]

(qvd);

Re: QlikSense Tasks Gantt Waterfall Chart & Analyse Corresponding QVDs

cheenu_janakira — Sun, 26 Feb 2017 10:16:49 GMT

Hi Bill,

I'm not too sure why our Ops Monitor scripts are so different, as I am quite sure we didn't edit the script in our environment.

If the "Reload Start" and "Reload Finish" are not included in the load, then the Gantt-Waterfall showing all subsequent tasks and time slots at which they run won't be able to populate.

This is a pity, as the Gantt-Waterfall is to show the sequencing of all Sense tasks in a manner that is similar to the QlikView QMC.

Please find below the pages/objects that get generate and would allow viewing the full flow from Task to QVD.

I hope you can find a way around or hopefully that the app is still helpful. It would be good to see your script, but seeing that QVS files can't be generated anymore, it becomes hard to share script files.

1. QMC Tasks Gantt-waterfall:

2. Task Intensity during the day - # of tasks and QVD records they churn

3. Task apps and their file size on disk

4. QVD Summary Info (with URL links back to apps that created them):

5. QVD field details (data type, etc).

6. Pivot/Cross table that allows to view how QVD field names are shared across QVDs

Re: QlikSense Tasks Gantt Waterfall Chart & Analyse Corresponding QVDs

cheenu_janakira — Sun, 26 Feb 2017 10:35:54 GMT

Hi Bill,

I've attached a copy of the OpsMon script for which it works. See if you can use an INCLUDE statement rather than the BINARY, such as "$(Include=[lib://[LIBRARY Connection here]/[Sub-folder]\[Sub-folder....]\OpsMonScript.txt]);"

It will take longer to reload than binary, escpecially since I haven't been through the stripping out of the tabs/tables that are not required for the final data model. The data model for Ops Mon is quite complex and I don't want to face the difficulty of going through all the dependencies in the script.

Let me know if this works for you. I tested it in my environment and it seems to work.

Cheerio,

Cheenu

Re: QlikSense Tasks Gantt Waterfall Chart & Analyse Corresponding QVDs

Anonymous — Sun, 26 Feb 2017 19:29:55 GMT

Attached are my Operations Monitor and your QMC Task & QVD Analyser qvf's. They are from a Dev scratchpad server.

It is strange that your Operations Monitor seems different to mine. Mine is a recent install of 3.1 SR5 on Windows Server 2012 R2 Standard. Did you initially install an earlier version and then upgrade it to 3.1 SR5?

You qvf reloads fine now after I fiddled with the script of both qvf's.

Also I have changed it a bit for my needs so it now loads all the Reload Task data instead of doing a Group By which was loading only the latest reload of each task, set the Task Set Sequence Number to go from 00:00 to 23:59 and created a new field of Reload Start Date.

For the Gantt sheet I added list boxes for Reload Status and Reload Start Date. Best to select just one Reload Start Date and one can see all runs of Task runs that are run more frequently than once a day. I do like your Gantt Chart. I am thinking about maybe forking the extension so it can show Failed tasks in red and Success in Green and in the popup show the duration in minutes instead of hours.

There were also some variables that I delete [set to nothing] in the script as it was persisting their values across reloads and things otherwise went bad when I changed the sub folder name in this script line :

SET vQMC_QVD_Store = 'LIB://QVD OpsMon/QMC QVD\';

Sheets 2 & 3 look ok.

But Sheets 4, 5, & 6 seem to have gone awry. It was the Gantt chart I was most interested in so I am not too worried about that.

I hope to do an upgrade to 3.2 SR1 in the next week or two. It will be interesting to see how it behaves after that.

Re: QlikSense Tasks Gantt Waterfall Chart & Analyse Corresponding QVDs

cheenu_janakira — Mon, 27 Feb 2017 12:32:13 GMT

Hi Bill,

The key for me was the Gantt too. I just mixed it with the "QVD" information because it suited my needs (basically that part of the script loops through all the folders picking up the header info of QVDs: which file created it, the amount of records, the field name, field data types, etc.). So it allows to go from Task > App > QVD info & details.

If you can edit the Gantt extension for green/red on success/failed tasks, respectively, please do share it. It would be really interesting to be able to see that.

Thanks for sharing the QVFs so far. I'll put them in my enviro for the select any dates of task reloads, which could then enable a historical look at tasks times.

Regards,

Cheenu

Re: QlikSense Tasks Gantt Waterfall Chart & Analyse Corresponding QVDs

cheenu_janakira — Mon, 27 Feb 2017 13:37:58 GMT

Hi Bill,

Wow. Thanks for the change in code. It gives the full picture, as opposed to just the last run.

As per the QVD part of the script, it might seem like something "obvious", but the first line in the INLINE table is "blank/null". Could the script error come from there or is it something more pervasive?

As for script vars, it seems like they often "stick" with their original value. I have the impression it might be some sort of bug and I hope it will be fixed in future versions.

Cheerio,

Cheenu

?

xyz1 — Sun, 14 Apr 2019 17:12:01 GMT

Re: QlikSense Tasks Gantt Waterfall Chart & Analyse Corresponding QVDs

Anonymous — Tue, 07 Nov 2017 15:28:37 GMT

Below pasted from Ceehu's starting post to this thread

Special thanks to Kai Hilton-Jones, whose GitHub - kaihj/qlik-sense-timeline: Qlik Sense Timeline Chart chart you need to install as an extension to view the Tasks Gantt Waterfall.

This thread is from a fair few months ago and I can't remember much about the extension. But the good news is I think it just worked with no problems.

Re: QlikSense Tasks Gantt Waterfall Chart & Analyse Corresponding QVDs

srajqlik — Mon, 28 Nov 2022 08:35:17 GMT

Hi,

Do we have a latest version of this please?

I'd really appreciate that.

Thanks,

Steve