11 Replies Latest reply: Sep 2, 2015 4:04 PM by Steve Dark RSS

    Trying to work out "Rate"

    Imran Rahim

      I have a simple sheet showing
      Batch# , Table_Name ,Start_TIme ,End_Time, Rows_Loaded

       

      to which I have added Duration using the function =interval(End_Time-Start_Time,'mm')

      I want to now add in there the rate of load i.e Rows_Loaded / Duration

      However the figure I end up seems to be applying the formula to all the records for that Table....not to that Batch#

       

      Can someone please give me some advice? Thanks

        • Re: Trying to work out "Rate"
          Tresesco B

          Could you explain the requirement with a small example?

            • Re: Trying to work out "Rate"
              Imran Rahim

              the data source gives you:

              Batch# , Table_Name ,Start_TIme ,End_Time, Rows_Loaded

              1 , Clienttab, 01:00, 02:00, 1200

              1, Orderstab,01:30,03:00, 900

              2, Clienttab, 01:00, 02:00, 2400


              expecting the following

              Batch# , Table_Name ,Start_TIme ,End_Time, Rows_Loaded, Rate_of_Load

              1 , Clienttab, 01:00, 02:00, 1200, 20

              1, Orderstab,01:30,03:00, 900,10

              2, Clienttab, 01:00, 02:00, 2400 ,40


              =interval(End_Time-Start_Time,'mm') works fine but

              Rows_Loaded/ interval(End_Time-Start_Time,'mm') gives some ridiculously high figure....i clearly not grouping properly



                • Re: Trying to work out "Rate"
                  Jonathan Dienst

                  oracleman wrote:

                  =interval(End_Time-Start_Time,'mm') works fine but

                  Rows_Loaded/ interval(End_Time-Start_Time,'mm') gives some ridiculously high figure....i clearly not grouping properly

                  Its probably not the grouping that is the problem. QV date/times are in units of days (like Excel). Interval() is a formatting function - it does not change the underlying value. So you are recording rows per day, not per minute. you need this:

                   

                       Rows_Loaded / (End_Time-Start_Time) / (24 * 60)

              • Re: Trying to work out "Rate"
                Jyothish KC

                Hi Oracleman,

                 

                Are you trying to find the time taken by the application to load?

                 

                Regards

                KC

                  • Re: Trying to work out "Rate"
                    Imran Rahim

                    Trying to find how quickly each table's data is being loaded into our datawarehouse by looking at number of rows loaded and the time taken in minutes

                      • Re: Trying to work out "Rate"
                        Mark O'Donovan

                        Hi there,

                         

                        Have a look at this post from stevedark, very useful:

                         

                        http://www.quickintelligence.co.uk/qlikview-load-performance/

                         

                        Thanks

                         

                        Mark

                         

                        techstuffy.tv

                          • Re: Trying to work out "Rate"
                            Imran Rahim

                            Thanks Mark....will take a look. My particular problem is that we have stats for how long loads into our datawarehouse tables took from source systems. We want to be able to analyse load times. Typically they are from vsam systems into Oracle, The Oracle system (a dwh) will keep track of how long these loads took on each day (batch#) for each table.

                            By looking at the stats for the load times for each table on each day, we can see whether load performance is being affected by other activity on the host.

                              • Re: Trying to work out "Rate"
                                Chris Cammers

                                Hey oracleman,

                                 

                                The problem you are having is that your times are not really being interpreted as "time" and then interval does not return an integer rather the 'mm' format represents a mask for a time value which is some fraction of 1. I took your sample data and wrote a sample script to take you through the various functions you need to use to convert the string to a time value and then convert the duration to an integer. The preceding loads are not necessary but I think it is helpful to separate the steps.

                                 

                                Load

                                Batch#,

                                Table_Name,

                                Start_Time,

                                End_Time,

                                Duration,

                                Rows_Loaded,

                                //Calculate the actual per minute rate

                                Rows_Loaded/Duration as [Load_Rate(PerMinute)];

                                Load

                                Batch#,

                                Table_Name,

                                Start_Time,

                                End_Time,

                                //calculate the Duration and return minutes as an integer

                                Num#(Interval(End_Time-Start_Time,'mm')) as Duration,

                                Rows_Loaded;

                                Load

                                Batch#,

                                Table_Name,

                                //Convert the time strings to time values

                                Time(Num(Start_Time,'hh:mm')) as Start_Time,

                                Time(Num(End_Time,'hh:mm')) as End_Time,

                                Rows_Loaded

                                Inline [

                                Batch# , Table_Name ,Start_Time ,End_Time, Rows_Loaded

                                1 , Clienttab, 01:00, 02:00, 1200

                                1, Orderstab,01:30,03:00, 900

                                2, Clienttab, 01:00, 02:00, 2400

                                ];

                              • Re: Trying to work out "Rate"
                                Steve Dark

                                Thanks for the mention Mark.

                                 

                                Another good way of getting more granular with the logging is inserting into a table as you go, something like this:

                                 

                                Log:

                                LOAD
                                   'Start Customer Table' as Step,

                                   Date(now(), 'DD MMM YYYY hh:mm:ss') as Time

                                AUTOGENERATE(1)

                                ;

                                 

                                At the end you could then do a resident load and a peek-1 to calculate the durations for each step.

                                 

                                It all depends how much information you require.  You could also add other information to that table, such as row count of the last table loaded and system information such as machine and user name running the load.  Information could be persisted by storing to a QVD with a date suffix.

                                 

                                Steve