9 Replies Latest reply on Nov 7, 2018 1:54 PM by Koerkel

    Roll Up Analysis

    Koerkel

      I have two different types of meters; one measuring at 15 minute intervals and the other measuring at 60 minute intervals.  I want to sum the measurement up to the transformer (the parent).  I found that if I do a standard roll up analyses, PI wants to interpolate values on each quarter hour for the hourly meters.  Thus my rollup value was higher than the actual sum from the meters.  I corrected this by adding a PI point to my 15 minute meters to sum the entire hour to the ordinal hour thus faking my 15 minute meters to look as if they were hourly meters.  Here is the analysis I used:

      if Minute('*')<>0 then NoOutput() 

      else TagMean('Wh Delivered Int Raw value', '*-50m', '*') * EventCount('Wh Delivered Int Raw value', '*-50m', '*')*'CTPTRatio'/1000

       

      For my hourly meters I simply converted the raw measurement into the proper units as so:

      'Wh Delivered Int Raw value'*'CTPTRatio'/1000

      I then sum the values from these analyses to obtain the hourly summed value.  This works for the most part, however, I am still receiving 96 values per day on my transformer when I expected 24 values. The values on the each quarter of the hour equals the values summed to the previous ordinal hour.  How can I roll up the measurements and only have 24 values?  Here is a simple example of what my data looks like:

       

            

      Meter summed to the hour and converted to KWH Meter Raw data in WH
      Transformer A
      Wh Load
      Meter 1
      kWh Delivered Interval
      Meter 1
      Wh Delivered Int Raw value
      Number of Values:96Number of Values:24Number of Values:96
      10/31/2018 0:150.50810/31/2018 1:000.5110/31/2018 0:15128
      10/31/2018 0:300.50810/31/2018 2:000.5110/31/2018 0:30129
      10/31/2018 0:450.50810/31/2018 3:000.51510/31/2018 0:45126
      10/31/2018 1:000.5110/31/2018 4:000.51610/31/2018 1:00127
      10/31/2018 1:150.5110/31/2018 5:000.50810/31/2018 1:15127
      10/31/2018 1:300.5110/31/2018 6:000.50210/31/2018 1:30128
      10/31/2018 1:450.5110/31/2018 7:000.49810/31/2018 1:45127
      10/31/2018 2:000.5110/31/2018 8:000.49210/31/2018 2:00128
      10/31/2018 2:150.5110/31/2018 9:000.49810/31/2018 2:15128
      10/31/2018 2:300.5110/31/2018 10:000.49810/31/2018 2:30129
      10/31/2018 2:450.5110/31/2018 11:000.49510/31/2018 2:45129
      10/31/2018 3:000.51510/31/2018 12:000.49810/31/2018 3:00129
      10/31/2018 3:150.51510/31/2018 13:000.50410/31/2018 3:15130
      10/31/2018 3:300.51510/31/2018 14:000.510/31/2018 3:30129
      10/31/2018 3:450.51510/31/2018 15:000.50610/31/2018 3:45129
      10/31/2018 4:000.51610/31/2018 16:000.51410/31/2018 4:00128
      10/31/2018 4:150.51610/31/2018 17:001.32910/31/2018 4:15129
      10/31/2018 4:300.51610/31/2018 18:002.69910/31/2018 4:30127
      10/31/2018 4:450.51610/31/2018 19:002.76910/31/2018 4:45127
      10/31/2018 5:000.50810/31/2018 20:002.81210/31/2018 5:00125
      10/31/2018 5:150.50810/31/2018 21:002.85410/31/2018 5:15126
      10/31/2018 5:300.50810/31/2018 22:002.88810/31/2018 5:30125
      10/31/2018 5:450.50810/31/2018 23:002.85810/31/2018 5:45126
      10/31/2018 6:000.50211/1/2018 0:002.77910/31/2018 6:00125
      10/31/2018 6:150.502 10/31/2018 6:15124
      10/31/2018 6:300.502 10/31/2018 6:30124
      10/31/2018 6:450.502 10/31/2018 6:45125
      10/31/2018 7:000.498 10/31/2018 7:00125
      10/31/2018 7:150.498 10/31/2018 7:15123
      10/31/2018 7:300.498 10/31/2018 7:30123
      10/31/2018 7:450.498 10/31/2018 7:45123
      10/31/2018 8:000.492 10/31/2018 8:00123
      10/31/2018 8:150.492 10/31/2018 8:15125
      10/31/2018 8:300.492 10/31/2018 8:30124
      10/31/2018 8:450.492 10/31/2018 8:45124
      10/31/2018 9:000.498 10/31/2018 9:00125
      10/31/2018 9:150.498 10/31/2018 9:15125
      10/31/2018 9:300.498 10/31/2018 9:30124
      10/31/2018 9:450.498 10/31/2018 9:45124
      10/31/2018 10:000.498 10/31/2018 10:00125
      10/31/2018 10:150.498 10/31/2018 10:15123
      10/31/2018 10:300.498 10/31/2018 10:30124
      10/31/2018 10:450.498 10/31/2018 10:45124
      10/31/2018 11:000.495 10/31/2018 11:00124
      10/31/2018 11:150.495 10/31/2018 11:15124
      10/31/2018 11:300.495 10/31/2018 11:30124
      10/31/2018 11:450.495 10/31/2018 11:45124
      10/31/2018 12:000.498 10/31/2018 12:00126
      10/31/2018 12:150.498 10/31/2018 12:15127
      10/31/2018 12:300.498 10/31/2018 12:30127
      10/31/2018 12:450.498 10/31/2018 12:45125
      10/31/2018 13:000.504 10/31/2018 13:00125
      10/31/2018 13:150.504 10/31/2018 13:15125
      10/31/2018 13:300.504 10/31/2018 13:30125
      10/31/2018 13:450.504 10/31/2018 13:45125
      10/31/2018 14:000.5 10/31/2018 14:00125
      10/31/2018 14:150.5 10/31/2018 14:15126
      10/31/2018 14:300.5 10/31/2018 14:30126
      10/31/2018 14:450.5 10/31/2018 14:45126
      10/31/2018 15:000.506 10/31/2018 15:00128
      10/31/2018 15:150.506 10/31/2018 15:15128
      10/31/2018 15:300.506 10/31/2018 15:30129
      10/31/2018 15:450.506 10/31/2018 15:45128
      10/31/2018 16:000.514 10/31/2018 16:00129
      10/31/2018 16:150.514 10/31/2018 16:15129
      10/31/2018 16:300.514 10/31/2018 16:30204
      10/31/2018 16:450.514 10/31/2018 16:45400
      10/31/2018 17:001.329 10/31/2018 17:00596
      10/31/2018 17:151.329 10/31/2018 17:15673
      10/31/2018 17:301.329 10/31/2018 17:30671
      10/31/2018 17:451.329 10/31/2018 17:45673
      10/31/2018 18:002.699 10/31/2018 18:00682
      10/31/2018 18:152.699 10/31/2018 18:15689
      10/31/2018 18:302.699 10/31/2018 18:30691
      10/31/2018 18:452.699 10/31/2018 18:45693
      10/31/2018 19:002.769 10/31/2018 19:00696
      10/31/2018 19:152.769 10/31/2018 19:15699
      10/31/2018 19:302.769 10/31/2018 19:30700
      10/31/2018 19:452.769 10/31/2018 19:45705
      10/31/2018 20:002.812 10/31/2018 20:00708
      10/31/2018 20:152.812 10/31/2018 20:15711
      10/31/2018 20:302.812 10/31/2018 20:30712
      10/31/2018 20:452.812 10/31/2018 20:45715
      10/31/2018 21:002.854 10/31/2018 21:00716
      10/31/2018 21:152.854 10/31/2018 21:15720
      10/31/2018 21:302.854 10/31/2018 21:30721
      10/31/2018 21:452.854 10/31/2018 21:45722
      10/31/2018 22:002.888 10/31/2018 22:00725
      10/31/2018 22:152.888 10/31/2018 22:15720
      10/31/2018 22:302.888 10/31/2018 22:30717
      10/31/2018 22:452.888 10/31/2018 22:45713
      10/31/2018 23:002.858 10/31/2018 23:00708
      10/31/2018 23:152.858 10/31/2018 23:15702
      10/31/2018 23:302.858 10/31/2018 23:30695
      10/31/2018 23:452.858 10/31/2018 23:45692
      11/1/2018 0:002.779 11/1/2018 0:00690
        • Re: Roll Up Analysis
          skwan

          David,

          How do you schedule your Rollup analyses?  Do they run every hour, or every 15 minutes?  (I'm assuming here that you're using Periodic scheduling).

          --

          Steve Kwan

            • Re: Roll Up Analysis
              Koerkel

              For the meter, I have the analysis schedule on event triggered.  For the transformer, it was set originally to event triggered but I ended up with a value every 15 minutes.  I tried daily and only got one value.  Then I switched it to hourly but doesn't work for me because data comes in once a day for the previous day, thus the last value reported yesterday which is the midnight read gets recorded to all the hours for today.

            • Re: Roll Up Analysis
              Roger Palmen

              Whjy not use TagTot and a hourly-scheduled Analysis? Or an analysis only triggered on the hourly data?

                • Re: Roll Up Analysis
                  Koerkel

                  How would you use tagtot for a rollup analysis? 

                    • Re: Roll Up Analysis
                      Roger Palmen

                      You can't, but the expression you posted was an expression analysis, not a rollup analysis. Or are we talking about different things here?

                       

                      But then again, you calculate using the TagMean and the EventCount, which is not really a time-weighted Total and Event-weighted totals are not supported yet. But then again, if data is incoming neatly every 15 minutes, this is again time-weighted, so a regular TagTot should work too.

                        • Re: Roll Up Analysis
                          Koerkel

                          I use an expression analysis at the child level to get the values on my 15 minute meters summed up to the ordinal hour, which is working great.  The problem is the roll up analysis.  When I roll the values up to the parent (transformer), I get the hourly sum every 15 minutes. 

                           

                          In my data above I have meter 1 reports 128 Wh at 0:15, 129 Wh at 0:30, 126 at 0:45 and 127 Wh at 1:00.  My expression at the meter level nets me .51 kWh (Note I change units).  For my rollup at the transformer, I get .51 kWh reported at 1:00, 1:15, 1:30, and 1:45.  I really only want the value at 1:00.

                    • Re: Roll Up Analysis
                      Koerkel

                      So changing the transformer rollup to hourly did not work.  Since data for the 15 minute meters
                      comes in twice a day (first half of the day is reported at 1 PM; second half of
                      the day is reported at 1 AM), I got the midnight ready repeated for every hour
                      from midnight to 11 AM and the noon read repeated from noon to 11PM.

                       

                      I’m trying this now:

                      I created a temp attribute on the transformer.  I have the transformer rollup assigned to this temp attribute.
                      I then created the following expression analysis and assigned it to the kWh Total for transformers:

                       

                      if Minute('*')<>0 then NoOutput() 

                      else 'temp value for rollup'

                       

                      Let's hope for better results. 

                        • Re: Roll Up Analysis
                          bbregenzer

                          Hi, David.

                          So it sounds like the issue is with the time delay in data arrival.  If you use event scheduling and the delayed data arrives in time-order, then every thing triggers properly but you get the extra values at the 15 minute intervals in your rollup.  If you elect for hourly periodic scheduling on the rollup, then you get only values on the hour but they are "incorrect" because the actual data hasn't arrived at the trigger time and you are using old data.  I think your solution above should work: elect for event triggering for the meters and transformer rollup, and then use an extra expression analysis to capture the values of the rollup only at the top of the hour.

                          I think an alternative would be to continue to use event triggers at the meter level and then use 1 hour, periodic scheduling at the transformer rollup but with one extra setting added: auto-recalculation.  You would get "incorrect" values for these hourly rollups until the late arriving data came in.  Once this data arrives, because their time stamps pre-date the most recent trigger time of the rollup, the auto-recalc. should kick in and correct the values in the rollup.  See this KB for more information: KB01790 - Automatic Recalculation with the Analysis Service: Technical Details

                          Please let us know what you think and keep us posted on your progress.

                          1 of 1 people found this helpful
                        • Re: Roll Up Analysis
                          Koerkel

                          Success.  It appears the intermediate step was the key.  My solution to roll up the meter to an attribute on the transformer and then run an expression analysis on the attribute seems to work.

                           

                          Brent, for your benefit, I always have the Automatic Recalculation turned on for all of my analysis and the hourly scheduling did not net the desired results but thank you for the suggestion.  I do appreciate the support.

                          2 of 2 people found this helpful