Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I am facing issue while reloading Operations Monitor's data. The last run was on 27-Mar-2018 and it was executed without any issues then. After couple of months, today I have tried reloading the data again but unfortunately this time the execution keeps on running at TimeDIM-1 temp table level and due to which CPU utilization is reaching 100%.
I am attaching a screenshot of it and need your help.
I guess the issue is at intervalmatch() in the code. Let me also share the piece of code where the issue is (a guess):
// Build a table of every minute between my start and end date
drop table Range;
do while startdate < enddate
tempDateTimeList:
load
timestamp($(startdate) + (1/(24*60))*(recno()-1),'YYYY-MM-DD h:mm') as DateTime
autogenerate (24*60);
let startdate = num($(startdate) + 1,'###0.#####','.') ;
loop
// To create hour sort order which sorts backward from now(reload) -- for 24-Hour summary charts
Let hour_now = maxLogTimeStamp_Hour;
hour_temp:
mapping Load
recno()-1 & ':00' as Hour,
if($(hour_now)-(recno()-1)>=0, $(hour_now)-(recno()-1),23+($(hour_now)-(recno()-1))+1) as hour_sort
autogenerate (24);
//Build time table
TimeDIM:
LOAD
DISTINCT DateTime,
Year(DateTime) as Year,
MonthName(DateTime) as Month, // replace with date(DateTime, 'MMM YYYY') for better performance
//WeekName(DateTime) as Week,
WeekStart(DateTime) as [Week Beginning],
date(dayname(DateTime),'MMM-DD') as Day,
WeekDay(DateTime) as Weekday,
makedate(year(DateTime),month(DateTime),day(DateTime)) as Date,
Hour(DateTime)&':00' as Hour,
ApplyMap('hour_temp',Hour(DateTime)&':00' ) as hour_sort,
Minute(DateTime) as [Minute of Hour],
timestamp(floor(DateTime,1/(24)),'MMM-DD hh:00') as [Hour Timeline],
timestamp(floor(DateTime,10/(24*60)),'MMM-DD hh:mm') as [Ten-Minute Timeline],
timestamp(floor(DateTime,5/(24*60)),'MMM-DD hh:mm') as [Five-Minute Timeline],
timestamp(floor(DateTime,1/(24*60)),'MMM-DD hh:mm') as [One-Minute Timeline]
resident tempDateTimeList;
Drop table tempDateTimeList;
//Interval Match dates
inner join Intervalmatch (DateTime) Load LogEntryPeriodStart-(1/(24*60)), LogTimeStamp Resident LogContent;
datetime:
NoConcatenate
Load
hour_sort, DateTime,Year, Month,[Week Beginning], Day, Weekday, Date, Hour,[Minute of Hour],[Hour Timeline],[Ten-Minute Timeline],[Five-Minute Timeline],[One-Minute Timeline],
(round(num([LogEntryPeriodStart-(1/(24*60))]+1/(24*60)),0.0006)&'|'&round(num(LogTimeStamp),0.0006)) as _TimeDIM_Link // LINK w/ LogContent
RESIDENT TimeDIM
order by DateTime DESC;
drop table TimeDIM;
Thanks,
Ravi