my qv when reload it alway stop at master calendar for 1 min

[paulyeo11]

Hi All,

my use my not book yoga 13 to reload my qv doc , it alway stop at master calendar for 1 min. it is normal ?

[tresesco]

might be autogeneration of the dates - is what it is taking that time. it is not normal. May be there is some unnecessary loop. can you share that section of code?

[luciancotea]

If you have a large fact table, getting the max and the min date can take a while.

[paulyeo11]

Hi Tres

Below is my code :-

tmp:

LOAD

          min(date) AS MinDate,

          max(date) AS MaxDate

RESIDENT Supplier;

CONCATENATE (tmp)

LOAD

          min(date) AS MinDate,

          max(date) AS MaxDate

RESIDENT sales_table;

CONCATENATE (tmp)

LOAD

          min(date) AS MinDate,

          max(date) AS MaxDate

//RESIDENT sales_table;

RESIDENT GL_TABLE;

CONCATENATE (tmp)

LOAD

          min(date) AS MinDate,

          max(date) AS MaxDate

RESIDENT RSV;

MaxMinDate:

NOCONCATENATE LOAD

          MIN(MinDate) AS MinDate,

          MAX(MaxDate) AS MaxDate

RESIDENT tmp;

DROP TABLE tmp;

LET varMinDate = Num(Peek('MinDate'));

LET varMaxDate = Num(Peek('MaxDate'));

LET vToday = num(today());

DROP TABLE MaxMinDate;

//*************** Temporary Calendar ***************

TempCalendar:

LOAD

          $(varMinDate) + rowno() - 1 AS Num,

          date($(varMinDate) + rowno() - 1) AS TempDate,

          date($(varMinDate) + rowno() - 1) AS D,

          year($(varMinDate) + rowno() - 1) AS Y,

          month($(varMinDate) + rowno() - 1) AS M,

          date(monthstart($(varMinDate) + rowno() - 1), 'MMM-YYYY') AS MY

AUTOGENERATE $(varMaxDate) - $(varMinDate) + 1;

Any idea to make don't sleep for 1 min ?

[tresesco]

It looks good to me. Do you have another table following this as MasterCalendar? If so, then why don't you do the necessary transformation in the TempCalendar itself?

[paulyeo11]

Hi Sir

My sample script for the master calendar as per enclosed. can you share a little bit more on :-

"do the necessary transformation in the TempCalendar itself"

I like to try it , but not sure where to touch.

Paul

[paulyeo11]

Hi Lucian,

Yes i do have large fact table. so does it mean if large fact table with slow CPU , it will sleep ?

[tresesco]

your mastercalendar table has 87 calculated fields. In that case it's very obvious that it would take some time. so you can conclude like that 'It is normal'. Better you can try to remove unnecessary fields from the mastercalendar, that would reduce yoyr load time.

[paulyeo11]

Hi Tres

Thank for help me spot my mistake. i will try to cut down those uncessary cal field. and if improve ,  i will update you.

[paulyeo11]

Hi Sir

i just check load with sample data , my reload time = 3:32 min , after remove cal field , the reload time 3:12 min.

conclusion is 10% reduce in reload time.

look like improve only little bit.