topic Re: Counting Simultaneous Tasks in Charts in App Development

Counting Simultaneous Tasks in Charts

JGMDataAnalysis — Thu, 16 Nov 2023 20:28:07 GMT

Hi! Could you tell me if there is a chart aggregation function that allows me to count simultaneous tasks?
I'm currently generating the "Datetimes" table and then using the Aggr function on the measure.
The problem is that "Datetimes" has more than 40 million records and performance is considerably degraded by using the Aggr function.

Is there a more efficient way to obtain the same result?

@rwunderlich, @marcus_sommer, @sunny_talwar

Re: Counting Simultaneous Tasks in Charts

marcus_sommer — Fri, 17 Nov 2023 06:27:52 GMT

The question is more for what the timestamp-table is really needed? Means at first - is the time-part really essential? If, could the timestamp be separated into dates and times? Do you need to keep this information within an extra table or could the to the from-to period related information be joined/mapped to the facts and such intermediate logic dropped again?

It means at all to transfer the essential logic into the data-model by optimizing it instead of optimizing the UI performance.

Re: Counting Simultaneous Tasks in Charts

JGMDataAnalysis — Tue, 21 Nov 2023 12:53:43 GMT

Thank you very much, @marcus_sommer.
I split the TaskDateTime field into TaskDate and TaskTime, respectively. By making this modification I noticed an improvement in performance, but I am still not satisfied with the result.
Then I tried joining the timestamp-table to the fact table but in this case I saw no difference. For this reason I decided to leave them separately.
As a basic premise, I need to calculate the concurrency of tasks based on the filters applied by users.
To perform this calculation I must consider the tasks that were running in each second, beyond the fact that in the charts I am going to use year, month, date or hour as dimensions.

Re: Counting Simultaneous Tasks in Charts

vincent_ardiet_ — Tue, 21 Nov 2023 13:17:59 GMT

You can create another table and take advantage of the way Qlik is dealing with cardinality:

refTask:
Load FieldValue('TaskID',RecNo()) as ID#,FieldValue('TaskID',RecNo()) as TaskIDKey AutoGenerate FieldValueCount('TaskID') ;

And then, if you count TaskIDKey you don't need to put the Distinct and so this is using multiple threads and should offer better performances.

Re: Counting Simultaneous Tasks in Charts

JGMDataAnalysis — Tue, 21 Nov 2023 13:56:55 GMT

Thanks, @vincent_ardiet_.
I never considered the optimization you propose because some time ago I read an @hic article explaining that the performance difference between Count and Count Distinct existed in older versions.

https://community.qlik.com/t5/Design/A-Myth-About-Count-distinct/ba-p/1476053

However, I could do some tests.

Re: Counting Simultaneous Tasks in Charts

vincent_ardiet_ — Tue, 21 Nov 2023 14:02:06 GMT

Indeed, I'm also curious to know the results.

Re: Counting Simultaneous Tasks in Charts

marcus_sommer — Tue, 21 Nov 2023 14:21:39 GMT

Some more questions. The fact-table or the resolved DateTime has 40 M of records? Further separating dates and times leads then to expressions like?

max(aggr(count(distinct TaskID), TaskDate, TaskTime))

or maybe:

max(aggr(count(distinct TaskID), Year, Month, Date, Hour))

Beside this the question - is an aggr() really needed by a table-chart with the dimension of: Year + Month + Date + Hour? Because the number of distinct tasks within the applied dimensions would the normal: count(distinct TaskID) return. By a KPI-object it might be different but here it would a single aggregation while the table-chart will perform it for each dimension-value.

If minutes and second aren't relevant on the dimension-level they might be skipped in the aggr() and/or you could try to work with multiple DateTime- and/or Time-fields - means removing of granularity and providing a complete time-field with 86400 values and one without seconds and 1440 values and the next would already the hour with just 24 values - the last maybe also with a date respectively date + hour. Of course the extra fields will increase the size of the data-model but choosing the appropriate one within the relevant UI expressions may improve the performance.

Further impact on the performance has the rest of the data-model. The best compromise in regard of simplicity, maintainability, efforts and performance is usually a star-scheme model. Especially link-table models should be avoided especially if the UI performance is the bottleneck.

Re: Counting Simultaneous Tasks in Charts

JGMDataAnalysis — Tue, 21 Nov 2023 18:02:53 GMT

Thanks again, Marcus.
In principle, I must provide more information about the real context of the data.
What I called "tasks" in the example correspond in reality to "actions" associated with a
Provisioning System of a Telecommunications company.
These "actions" last a few seconds, except those executed through a CRON that can last several hours.
Under these circumstances, I understand that I am obliged to consider up to the second to
correctly calculate the number of concurrent "actions", regardless of whether the charts group up to the hour.

Answering the questions:
- The resolved DateTime has 40 M of records
- max(aggr(count(distinct TaskID), TaskDate, TaskTime))

Data Model Viewer:

If for the following example I used the measure max(aggr(count(distinct TaskID), Year, Month, Date, Hour)) I would get 2 as a result, which would not be correct because only one task ran simultaneously.

Am I misunderstanding what you conveyed to me or, even worse, how Aggr works?

Re: Counting Simultaneous Tasks in Charts

vincent_ardiet_ — Tue, 21 Nov 2023 16:29:23 GMT

If you are sure to have one event each second, you could also try to sort the table by actionid and time, and using previous you can check if the same task was running 1 second before, create a flag with 0 in this case, else 1. In your UI you can then sum this flag.

Re: Counting Simultaneous Tasks in Charts

JGMDataAnalysis — Tue, 21 Nov 2023 17:27:04 GMT

In simple terms, I have tasks that start and end. Based on this, I should be able to calculate the maximum number of tasks that ran simultaneously in a given year, month, week, date or date + hour according to the selections made by the user. For this last reason, I consider that it is not possible to precompute a flag because I cannot know in advance which registers will come into play.

It helps me to think of it as one-second partitions into which tasks will fit according to their duration.

Re: Counting Simultaneous Tasks in Charts

vincent_ardiet_ — Wed, 22 Nov 2023 08:02:32 GMT

Ok I see, indeed difficult to get rid of the count in this case...
Have you tried my proposal to avoid the Distinct with the refTasks table? Is it giving better performance or not?

Re: Counting Simultaneous Tasks in Charts

marcus_sommer — Wed, 22 Nov 2023 08:20:33 GMT

Yes, it makes it more clear and I missed that you don't want to count all tasks else only the ones which runs simultaneously. Further that's intended to show the max. value of them in regard to a certain period-dimension and some selections within them. Especially the last requirement is quite hard to optimize by a pure UI approach because it will always need an aggr() to evaluate the results of the count-aggregation. Any kinds of pre-aggregation seems not to be feasible but I wouldn't discard them too easily as impossible.

Beside this I could imagine that an approach like suggested from @vincent_ardiet_ might be suitable to improve the UI performance by evaluating some essential information already within the data-model by using interrecord-functions. Maybe with something like this:

t: load id, start, end,
if(start < previous(end), 1, 0) as flag,
if(start < previous(end), previous(id)) as id_join
resident source order by start;

m: mapping load id_join, 1 resident t where flag = 1;

x: load id, start, end, if(flag, flag, applymap('m', id, 0)) as flag
resident t;

It's a bit simplified and may need some more if-conditions and/or flag-fields respectively more logic to handle the overlapping of n records and/or challenges in regard to the sorting.

The above approach would be applied within the facts and not in the separate DateTime-table and checked the previous end-date against the current start-date and flagged it as well as providing the previous id which is used within the later mapping to add the flag also this record - because they belong together.

With such a flag as selection or as a set analysis condition the virtual-tables behind the count() and the on-top aggr() could be reduced and the UI performance should become better. Of course it will depend on the rate of how many records could be exclude - by just 2% - 3% it won't be probably noticeable but by > 30% it should be significantly.

Further thinkable would be to calculate the timestamps of the records against each other to get their differences and group them as within 1 or 24 hours or a day/month and various other stuff which might be also usable as restrictions within the calculation or maybe even as base for any pre-aggregation.

Re: Counting Simultaneous Tasks in Charts

JGMDataAnalysis — Wed, 22 Nov 2023 17:25:55 GMT

Thanks, Marcus.
The flag you propose allows you to identify those "tasks" that do not overlap with any other at any time.
I understand that I should generate a similar flag but in the separate DateTime-table, according to the following:

INNER JOIN (Datetime) LOAD TaskDate, TaskTime, If(Count(Distinct TaskID) > 1, 1, 0) AS Flag RESIDENT Datetime GROUPED BY TaskDate, TaskTime ;

RangeMax(Max({<Flag = {1}>} Aggr(Count({<Flag = {1}>} Distinct TaskID), TaskDate, TaskTime), 1)

For the following example (as I understand it) the flag that you suggested would mark all tasks at 1 and
as a consequence, all their 1 second partitions would remain.

On the other hand, with the flag calculated on the table you could keep filtering only those partitions
that have more than one associated task.

I did a quick test on the real data and was able to notice that the implementation of the flag
in the DateTime-table allows me to exclude approximately 35% of the records, which is reflected
in the UI performance.

I would like to explore the last optimization option you mentioned but I didn't fully understand it.
Can you include a simple example in code?

Further thinkable would be to calculate the timestamps of the records against each other to get their differences and group them as within 1 or 24 hours or a day/month and various other stuff which might be also usable as restrictions within the calculation or maybe even as base for any pre-aggregation.

Re: Counting Simultaneous Tasks in Charts

JGMDataAnalysis — Wed, 22 Nov 2023 17:27:36 GMT

I did the test but didn't notice any difference, unfortunately.
Thanks, Vincent.

Re: Counting Simultaneous Tasks in Charts

marcus_sommer — Wed, 22 Nov 2023 18:12:35 GMT

Your method to use a self-joining aggregation-load to add the flag to the DateTime-table is definitely an alternatively. I'm not sure in which table the flag would improve the UI performance more. Personally I would assume it's the shorter fact-table and I would also think that the interrecord-approach may run faster as the aggregation-load. But you could just test it against each other.

By the last I hinted for the possibility not just to create a flag else also to calculate the time-differences between the current start-time and the previous end-time and in a second measure these differences might be accumulated and the next measure might be to group these accumulations into hours/dates/months. In a similar way might the above flag or another one be used to accumulated the number of tasks within a row - means the flag-value mustn't be just 0 or 1 else might be 2, 3, 4, 5, .... and in the set analysis the condition is: flag = {">=1"}

Like above mentioned I'm not sure how to use such offset/grouping and/or further/extended flag-values in a sensible manner to reduce the data-set for the aggr() or maybe further aggregations in the script but there might be pattern which are useful - especially if there are clear rules how to handle hour/date/month overlapping tasks which would it makes easier to reset flags/accumulations in their regard as well as to the risk of counting them twice.

At last if the second-parts are needed to determine the belonging to overlapping tasks and the flag would fulfill this requirement but the lowest granularity to display the counting is the hour you may remove the minutes and seconds from the data-model respectively you don't need to create them (if tries to create the flags within the fact-table and only afterwards creating the DateTime-table).