topic Re: Application taking more time!!! in QlikView

Application taking more time!!!

Mon, 10 Aug 2015 07:41:08 GMT

Hi All

Can any one guide me on the following issues, I have had a Qvw file with size 500 MB. I am taking it in to the other qvw file as resident ,

and I am transforming the fact table here,

Fact:

LOAD

ACCOUNTID,

num(trim(DEPTID)) AS DEPTID,

num(trim(GLMCU)) AS GLMCU,

num(trim([BRANCH NUMBER])) AS [BRANCH NUMBER],

num('20'&(FISCALYEAR+1),'##0.0') AS FISCALYEAR,

([SALES]/100) AS [SALES],

Period AS Period,

AddYears('1/1/1900',num(left(DATE,len(DATE)-3)))+num(right(DATE,3))-1 AS [FiscalDate] ,

AutoNumber(num('20'&(FISCALYEAR+1),'##0.0')&Period) AS Key,

Date(AddYears('1/1/1900',num(left(DATE,len(DATE)-3)))+num(right(DATE,3))-1,'MM-DD-YYYY') AS JulianDate,

Date(AddYears('1/1/1900',num(left(DATE,len(DATE)-3)))+num(right(DATE,3))-1,'MM-DD-YYYY') AS DATE,

num(Date(AddYears('1/1/1900',num(left(DATE,len(DATE)-3)))+num(right(DATE,3))-1,'MM-DD-YYYY')) AS DateNum,

DOCTYPE AS [DOC NUMBER],

DESCRIPTION,

[ABAP DESCRIPTION],

FIRMTYPE AS [Account Type],

FIRMTYPE,

BRANCH,

DOCTYPE,

DOCNUM,

num(DCDTNUM) AS DCDTNUM ,

DATE,

ReportID,

DFGH

Resident TEMPFact;

and I am calculating the other aggregation table with

Sum(SALES) AS AGGR_SALES with KEY1,FISCALYEAR,PERIOD etc.

WHEN I run this application I am facing problem with reload time. App taking more time 3 hours how can I improve the reload time here.

Regards

John

Re: Application taking more time!!!

Gysbert_Wassenaar — Mon, 10 Aug 2015 07:51:36 GMT

Try to use a preceding load instead of a resident load to create your final fact table.

Re: Application taking more time!!!

Mon, 10 Aug 2015 08:01:10 GMT

Thanks Gysbert Wassenaar.

Here I would like to make a separate fact table with aggregated information on specific columns. I didn't think so it is possible with Preceding load. please excuse me if I am wrong in this and let me know the correct method to work around

Regards

John

Re: Application taking more time!!!

Gysbert_Wassenaar — Mon, 10 Aug 2015 08:03:49 GMT

I don't see a Group By clause in the load statement for your fact table, so you don't seem to be calculating any aggregations.

Re: Application taking more time!!!

Mon, 10 Aug 2015 08:44:47 GMT

Yes I have not added the script yet. just I stated those words above sorry for the misleading, the group by clause script is,

AGGR:

KEY1,

Sum([Net Posting]) AS [Net Posting]

Resident TempF099

Group By

FISCAL_YEAR,

Period,

[BRANCH NUMBER],

DEPTID,

GLMCU,

DateNum,

KEY1,

;

Regards

John

Re: Application taking more time!!!

Gysbert_Wassenaar — Mon, 10 Aug 2015 09:09:12 GMT

I still don't see why you need a resident load to create your fact table from TEMPFact.

Re: Application taking more time!!!

Mon, 10 Aug 2015 09:21:53 GMT

Because TEMPFact table is coming from a binary load,[fact.qvw application is having this fact table I am loading that application into new application and I am performing the transformations according to my requirements.]

Re: Application taking more time!!!

maxgro — Mon, 10 Aug 2015 09:29:55 GMT

3 hours for a 500 mb .qvw?

did you drop the fact table after the group by resident load?

or could you post the script and the .log?

Re: Application taking more time!!!

Gysbert_Wassenaar — Mon, 10 Aug 2015 09:40:07 GMT

Ok, try storing that TEMPFact table into a qvd file directly after the binary load. Then drop that table and use the newly created qvd file to create the final fact table and for the aggregated table. You might want to create that qvd with another qlikview document so you don't need to binary load all the other data from that first qvw file too.

Re: Application taking more time!!!

Mon, 10 Aug 2015 10:45:49 GMT

Hi Massimo Grossi,

Yes it is taking 3 hours, could you please let me know how much time an optimized application will take time to reload

on 8 GB RAM personal edition system. My Application has 20 Million records.

Regards

John

Re: Application taking more time!!!

Gysbert_Wassenaar — Mon, 10 Aug 2015 10:50:57 GMT

20 billion records might be simply too big for 8GB. Take a good look at the ram usage of your machine during reload.

Re: Application taking more time!!!

maxgro — Mon, 10 Aug 2015 10:58:46 GMT

there is something I don't understand

500 mb .qvw

a lot of fields in fact table

20 billion of records

8 gb ram

the numbers of fields and records seems too big for a 500 mb qvw

Re: Application taking more time!!!

Mon, 10 Aug 2015 11:05:07 GMT

Sorry Wrong Typo it 20 Million

Re: Application taking more time!!!

Mon, 10 Aug 2015 11:05:36 GMT

Sorry Wrong Typo its 20 Million