0 Replies Latest reply: Jun 8, 2018 4:17 AM by Ravi Krishna Reddy Chinta RSS

    Issue in reloading Qlik Sense "Operations Monitor" Dashboard's data

    Ravi Krishna Reddy Chinta

      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.

       

      Operations Monitor - Copy.jpg

       

      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