13 Replies Latest reply: Nov 7, 2017 10:28 AM by Bill Markham RSS

    QlikSense Tasks Gantt Waterfall Chart & Analyse Corresponding QVDs

    Cheenu Janakiram

      Hi everyone,

       

      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
          Bill Markham

          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 Janakiram

              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
                  Bill Markham

                  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 Janakiram

                      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
                          Bill Markham

                          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
                              Bill Markham

                              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 Janakiram

                                  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:

                                  001 Tasks Gantt.png

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

                                  002 Task intensity - number of records and tasks by day hour.png

                                  3. Task apps and their file size on disk

                                  003 Tasks Apps and FileSizes.png

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

                                  004 QVD info summary.png

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

                                  005 QVD Field details.png

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

                                  006 Fields shared across QVDs.png

                                    • Re: QlikSense Tasks Gantt Waterfall Chart & Analyse Corresponding QVDs
                                      Cheenu Janakiram

                                      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
                                          Bill Markham

                                          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 Janakiram

                                              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 Janakiram

                                                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

                              • Re: QlikSense Tasks Gantt Waterfall Chart & Analyse Corresponding QVDs
                                Agrim Sharma

                                Hi,

                                this is amazing that you guys have found the timeline in Qlik Sense latest versions, i am grateful.

                                i would be very very helpful if you could share a video of complete working of timeline in qlik sense, as there are very few sources to understand this.

                                 

                                Thanks a lot

                                Regards,

                                Agreim