Finding Register Reads With Values That Go Backwards

We recently came across an issue where register reads may go backwards due to a quirk in our AMI system.  I was tasked with mining the EnergyIP data to find these.  While not perfect, this script does the job.  Below script will only look 15 days in the past.  You may want to go back further.



SELECT *
  FROM (  SELECT meter.udc_id,
                 rr.channel_id,
                 rr.insert_time,
                 rr.local_read_time,
                 rr.cum_read register,
                 /* lag function return the previous record value in a sorted and/or grouped list */
                 LAG (
                    cum_read,
                    1,
                    0)
                 OVER (
                    /* group by meter AND channel so we don't evaluate the previous record */
                    /* if the meter was changed out */
                    PARTITION BY meter.udc_id, rr.channel_id
                    ORDER BY meter.udc_id, rr.channel_id, rr.local_read_Time)
                    AS prior_register
            FROM emapp.register_reads rr,
                 emapp.lookup_mv_meters meter,
                 emapp.lookup_mv_channels1 channel
           WHERE     channel.channel_type = 'Cumulative Consumption'
                 AND channel.logical_channel_no = 1
                 AND channel.channel_id = rr.channel_id
                 AND rr.utc_read_time > SYSDATE - 15
                 AND channel.meter_id = meter.meter_id
        ORDER BY meter.udc_id, rr.channel_id, rr.utc_read_time ASC)
 WHERE /* skip the first interval */
       prior_Register > 0
       /* register appeared to go backwards */
       AND register < prior_register
       /* omit cases that are likely due to differences in granularity between actual registers */
      /* and End of Interval snapshots due to decimals and rounding */
       AND prior_register - register > 1;

Correcting LP and Register Data Processed Delivered With Wrong Multiplier (7.2)

Every now and then our AMI system gets the wrong multiplier set for a module.  Since it delivers the data fully multiplied to EnergyIP, the data is stored incorrectly.  Fixing this can be daunting, so I put together a little script to help.  This script can easily be modified to divide the existing values should the multiplier have been set to high.  In our case its usually that the multiplier was set too low.

USE AT YOUR OWN RISK


/* This script takes arguments and will MULTIPLY reading values to correct them */
/* use with caution */
/* update 6/7/2013 */
/* This script requires a function call to convert UTC to our local time, mountain time */

/* the function */

CREATE OR REPLACE FUNCTION AMFC.to_mountain_time (DATE_INPUT IN DATE)
   RETURN DATE DETERMINISTIC
IS
   MT_DATE   DATE;
/******************************************************************************
   NAME:       to_mountain_time
   PURPOSE:

   REVISIONS:
   Ver        Date        Author           Description
   ---------  ----------  ---------------  ------------------------------------
   1.0        9/7/2012   asanchez       1. Created this function.

   NOTES:

   Automatically available Auto Replace Keywords:
      Object Name:     utc_to_mountain_time
      Sysdate:         9/7/2012
      Date and Time:   9/7/2012, 3:39:25 PM, and 9/7/2012 3:39:25 PM
      Username:        asanchez (set in TOAD Options, Procedure Editor)
      Table Name:       (set in the "New PL/SQL Object" dialog)

******************************************************************************/
BEGIN
   MT_DATE :=
      TO_DATE (
         TO_CHAR (
            FROM_TZ (
               TO_TIMESTAMP (TO_CHAR (DATE_INPUT, 'MM/DD/YYYY HH:MI:SS PM'),
                             'MM/DD/YYYY HH:MI:SS PM'),
               'UTC')
               AT TIME ZONE 'US/Mountain',
            'MM/DD/YYYY HH:MI:SS PM'),
         'MM/DD/YYYY HH:MI:SS PM');



   RETURN MT_DATE;
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      NULL;
   WHEN OTHERS
   THEN
      -- Consider logging the error and then re-raise
      RAISE;
END to_mountain_time;
/

/* The script */

VAR before_date VARCHAR2(50);
VAR register_channel_id VARCHAR2(50);
VAR lp_channel_id VARCHAR2(50);
VAR multiplier_adjustment NUMBER;

EXEC :before_date := '05/01/2013 23:59:00';
EXEC :register_channel_id := '1-4A5QB';
EXEC :lp_channel_id := '1-4A5Q8';
EXEC :multiplier_adjustment := 10;

/* registers */

  SELECT local_read_time, cum_read rr_before
    FROM emapp.register_reads
   WHERE     channel_id = :register_channel_id
         AND local_read_time <= TO_DATE (:before_date, 'MM/DD/YYYY HH24:MI:SS')
ORDER BY local_read_time DESC;

UPDATE emapp.register_reads
   SET cum_read = cum_read * :multiplier_adjustment
 WHERE     channel_id = :register_channel_id
       AND local_read_time <= TO_DATE (:before_date, 'MM/DD/YYYY HH24:MI:SS');

  SELECT local_read_time, cum_read rr_after
    FROM emapp.register_reads
   WHERE     channel_id = :register_channel_id
         AND local_read_time <= TO_DATE (:before_date, 'MM/DD/YYYY HH24:MI:SS')
ORDER BY local_read_time DESC;

/* intervals */

  SELECT local_interval_time, lp_value lp_before
    FROM emapp.lp_intervals
   WHERE     channel_id = :lp_channel_id
         AND local_interval_time <=
                TO_DATE (:before_date, 'MM/DD/YYYY HH24:MI:SS')
ORDER BY local_interval_time DESC;

UPDATE emapp.lp_intervals
   SET lp_value = lp_value * :multiplier_adjustment
 WHERE     channel_id = :lp_channel_id
       AND local_interval_time <=
              TO_DATE (:before_date, 'MM/DD/YYYY HH24:MI:SS');

  SELECT local_interval_time, lp_value lp_after
    FROM emapp.lp_intervals
   WHERE     channel_id = :lp_channel_id
         AND local_interval_time <=
                TO_DATE (:before_date, 'MM/DD/YYYY HH24:MI:SS')
ORDER BY local_interval_time DESC;

/* lp batch update */

  SELECT end_read lpb_before
    FROM emapp.lp_batch
   WHERE     channel_id = :lp_channel_id
         AND amfc.to_mountain_Time (utc_end_time) <=
                TO_DATE (:before_date, 'MM/DD/YYYY HH24:MI:SS')
ORDER BY utc_end_time DESC;

UPDATE emapp.lp_batch
   SET end_read = end_read * :multiplier_adjustment
 WHERE     channel_id = :lp_channel_id
       AND amfc.to_mountain_Time (utc_end_time) <=
              TO_DATE (:before_date, 'MM/DD/YYYY HH24:MI:SS');

  SELECT end_read lpb_after
    FROM emapp.lp_batch
   WHERE     channel_id = :lp_channel_id
         AND amfc.to_mountain_Time (utc_end_time) <=
                TO_DATE (:before_date, 'MM/DD/YYYY HH24:MI:SS')
ORDER BY utc_end_time DESC;

Pruning data from billing tables in EnergyIP 7.2

I've published live, custom, cross system Oracle Apex reports linking the billing request table in EnergyIP to the billing information in our Customer Information System (Ventyx).  Performance was starting to take a turn for the worse since I didn't want to further index the tables for fear of breaking a future application of service packs or upgrades.  Also,  the table was getting fairly large (1.5 million rows) and it is not partitioned.

The right thing to do is probably to use dbms_redefinition to partition the table(s).  I'll push for a product enhancement instead :).

Since the source of record for our billing was our CIS system I decided that pruning was in order.  I came up with the following to prune all but the last ~90 days of data from the billing tables (adhering to constraints).  Take a backup/data pump export first!


DELETE FROM emapp.billing_comp_tracking
      WHERE billing_request_id IN
               (SELECT billing_request_id
                  FROM emapp.billing_request
                 WHERE utc_request_start_time < SYSDATE - 90);

DELETE FROM emapp.billing_data_change_event
      WHERE billing_request_id IN
               (SELECT billing_request_id
                  FROM emapp.billing_request
                 WHERE utc_request_start_time < SYSDATE - 90);

DELETE FROM EMAPP.BILLING_EXPORT_LOG
      WHERE billing_request_id IN
               (SELECT billing_request_id
                  FROM emapp.billing_request
                 WHERE utc_request_start_time < SYSDATE - 90);

DELETE FROM emapp.billing_detail
      WHERE billing_request_id IN
               (SELECT billing_request_id
                  FROM emapp.billing_request
                 WHERE utc_request_start_time < SYSDATE - 90);

DELETE FROM emapp.billing_sum_check_detail
      WHERE billing_sum_check_log_id IN
               (SELECT billing_sum_check_log_id
                  FROM emapp.billing_sum_check_log
                 WHERE billing_request_id IN
                          (SELECT billing_request_id
                             FROM emapp.billing_request
                            WHERE utc_request_start_time < SYSDATE - 90));

DELETE FROM emapp.billing_sum_check_log
      WHERE billing_request_id IN
               (SELECT billing_request_id
                  FROM emapp.billing_request
                 WHERE utc_request_start_time < SYSDATE - 90);

DELETE FROM emapp.billing_request
      WHERE utc_request_start_time < SYSDATE - 90;