Qlikview performence issue

[#1]

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

[Anonymous]

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

--> aggr() is slow...

[#3]

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

[Anonymous]

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

[marcus_sommer]

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

[#6]

Yes Robin data available at year-month level only

[#7]

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

[Anonymous]

could you post your script?

[#9]

Sorry Robin I can't