topic Re: Qlikview performence issue in QlikView

Qlikview performence issue

Wed, 25 Nov 2020 16:16:04 GMT

Hi Qlik heads,

I had a requirement to develop an application which has 72 Million records, and user want's to analyze this data with 3 conversion factors upon external selection (expecting a list box to choose it), the Product hierarchy would be

material ID to Plant (1:M)

material ID to Country (1:M) ,

here Conversion factor 1 available at Material ID to Plant (1:M) and Material ID to Country (1:M)

Conversion factor 2 available at Material ID to Plant (1:M), Conversion factor 3 available at Material ID to Country (1:M), and these conversion factors has 0 for some of the Material ID and plant/country combinations. based on this data I have to calculated last mean, variance, STDDEV, last 12month average.

Also they need a bar chart with Sum of the sales for each month and an average bar of last 12 months.

so, I have developed data model as follows I have multiplied the conversion factors with each combination of records

material ID to Plant (1:M)

material ID to Country (1:M) and concatenated each conversion factor data and given and extra field value for each load.

due to this I have been end up with 3 times of my original data which is 72*3 = 216 Million, after that I have created a bar chart to show the SUM(Fact) with average bar, as there is no default AVG total mode for the bar chart I have used the expression AVG(AGGR(sum(Fact),Date)) due to this My application taking 7 real mints to open and to respond for a selection.

the application size is 3. GB and when it opens in server it is consuming 12 GB of RAM,

we have 16 cpu's and 100 GB of RAM.

so , please kindly let me know is this is correct data model design or not ? if not please advise a good way to improve the performance.

hic‌ tresesco‌ jagan‌

Thanks

John

Re: Qlikview performence issue

Anonymous — Tue, 11 Apr 2017 16:06:58 GMT

perhaps you could try sum(fact) / count(distinct Date) as your average?!?

--> aggr() is slow...

Re: Qlikview performence issue

Tue, 11 Apr 2017 16:20:21 GMT

Edited:

Thanks Robin, the AVG bar is only for Total (Average value of sum value of individual months ) for all other months the expression should have SUM expression

Regards,

John

Re: Qlikview performence issue

Anonymous — Tue, 11 Apr 2017 16:31:22 GMT

Did you try to group everything per year-month on database- or script level?

Re: Qlikview performence issue

marcus_sommer — Tue, 11 Apr 2017 17:20:47 GMT

I would try to implement the needed conversion within the gui-expressions with the conversion as factor or and/or as set analysis condition - I could imagine that this would be faster as to triple all records (and of course avoiding of nested-if or aggr-constructs).

- Marcus

Re: Qlikview performence issue

Tue, 11 Apr 2017 17:57:45 GMT

Yes Robin data available at year-month level only

Re: Qlikview performence issue

Tue, 11 Apr 2017 18:04:25 GMT

Thanks Marcus, I agreed but I have several statistical calculations if I would have implement these calculations application might take more time to respond for each selection, due to this I have moved all my calculations to data model,

Regards

John

Re: Qlikview performence issue

Anonymous — Tue, 11 Apr 2017 18:10:15 GMT

could you post your script?

Re: Qlikview performence issue

Tue, 11 Apr 2017 18:38:55 GMT

Sorry Robin I can't