Using Time Series with the Power Demand Cartridge

This section describes how Oracle Time Series can be used with the Power Demand cartridge. With Time Series, you can apply the many time series and time scaling functions, such as various averages (arithmetic, cumulative, moving) and scaleup, to the power demand data.

This section assumes that you are familiar with time series concepts and basic usage information for Time Series. For detailed conceptual and usage information, see the Oracle8i Time Series User's Guide.

To use Time Series with the power demand data, you must perform the following steps:

1. Identify the timestamp column and other columns of interest in the power demand table.

2. Define the calendars to be used.

3. Create a mapping table to map regions to an hourly calendar.

4. Create a reference-based view.

5. Formulate time series queries.

This section does not discuss creating the underlying table for time series data or loading the data, because these operations were done in "Creating and Populating the Power Demand Table".

The following operations are not illustrated in this section, but should be included for use in a production environment:

• Creating a security view that uses INSTEAD OF triggers for actions that can modify time series data

• Validating the consistency of time series data

  Note: The PowerDemand_Tab table is not defined as an index-organized table, but as a regular table. Although index-organized tables are recommended for use with Time Series for performance reasons, such tables cannot include columns of the VARRAY datatype, which is used to define the power demand grid (Sample.CellDemandValues, defined using the PowerGrid_Typ object type, which is VARRAY(100) of NUMBER).

Identifying Columns to be Used

Time Series requires that each data row include the following:

• A timestamp

  In this example, the timestamp is the SampleTime attribute of the PowerDemand_Typ object type, which is used to define the Samples column of the PowerDemand_Tab table.

• A qualifier

  In this example, the qualifier is the region column of the PowerDemand_Tab table. In a financial time series, the qualifier might be the stock ticker symbol (for example, ACME for Acme Corporation.)

• One or more columns with data to be operated on by Time Series functions.

  In this example, the TotGridDemand, MaxCellDemand, MinCellDemand, and CellDemandValues attributes of the PowerDemand_Typ object type can be used with Time Series functions. In a financial time series of daily stock market data, the columns to be operated on by Time Series functions might include the opening, closing, high, and low prices, and the total volume of shares traded.

Defining the Calendars

To define the calendars, you must create their definitions in a table of calendars. If the table of calendars does not already exist, create it first. (The calendar table might not exist because this is your first use of Time Series; or a calendar table might exist for another application, but you choose to place power demand calendars in a separate table.)

The following statements create a table named PowerDemand_calendars and defines the first of three calendars, this one named PowerReadingsCal_Hourly. Explanatory notes follow the example.

-- Create the table for the calendars.
CREATE TABLE PowerDemand_calendars of ORDSYS.ORDTCalendar;

-- Create three calendars: first one for hourly readings, the
-- other two for daily and monthly scaleup operations.
INSERT INTO PowerDemand_calendars 

VALUES(
  ORDSYS.ORDTCalendar(
    0, 

    'PowerReadingsCal_Hourly', 

    3, 

    ORDSYS.ORDTPattern(ORDSYS.ORDTPatternBits(1), 

      to_date('01-01-1998 01','MM-DD-YYYY HH')),
    to_date('01-01-1998 01','MM-DD-YYYY HH'), 

    to_date('01-01-2008 01','MM-DD-YYYY HH'), 
    ORDSYS.ORDTExceptions(), 

    ORDSYS.ORDTExceptions() 
    )
  );

Notes on the preceding example:

PowerDemand_calendars is a table of ORDSYS.ORDTCalendar objects.

0 (zero) for calendar type (caltype) indicates that this is an exception-based calendar.

PowerReadingsCal_Hourly is the name of this calendar.

3 is the frequency code for hour.

The pattern is defined as having all timestamps included (1). Because there are no off timestamps (that is, power readings are taken for all hours), any hourly timestamp can be used for the anchor date ('01-01-1998 01' is used in this example).

The calendar begins at 1:00 AM on 01-Jan-1998 and ends at 1:00 AM on 01-Jan-2008

There are no off-exceptions (that is, no hours when readings are not taken) and no on-exceptions (which are impossible in this case anyway, because there are no zeroes in the calendar pattern).

The following statements create two additional calendars, for use with scaleup operations in which hourly power demand readings are scaled up to compute daily and monthly values:

INSERT INTO PowerDemand_calendars
VALUES(
  ORDSYS.ORDTCalendar(
    0,
    'PowerReadingsCal_Daily',
    4,
    ORDSYS.ORDTPattern(ORDSYS.ORDTPatternBits(1),
      to_date('01-01-1998','MM-DD-YYYY')),
    to_date('01-01-1998','MM-DD-YYYY'),
    to_date('01-01-2008','MM-DD-YYYY'),
    ORDSYS.ORDTExceptions(),
    ORDSYS.ORDTExceptions()
    )
  );
INSERT INTO PowerDemand_calendars
VALUES(
  ORDSYS.ORDTCalendar(
    0,
    'PowerReadingsCal_Monthly',
    6,
    ORDSYS.ORDTPattern(ORDSYS.ORDTPatternBits(1),
      to_date('01-01-1998','MM-DD-YYYY')),
    to_date('01-01-1998','MM-DD-YYYY'),
    to_date('01-01-2008','MM-DD-YYYY'),
    ORDSYS.ORDTExceptions(),
    ORDSYS.ORDTExceptions()
    )
  );

Creating the Mapping Table

Create a table to map regions to calendars, and insert a row for each region. In this example, regions 1 and 2 are associated with the PowerReadingsCal_Hourly calendar. This mapping allows the reference-based view to be used.

-- Create the metadata table and insert rows for two regions.
 
CREATE TABLE PowerDemand_metadata ( 
    region number,
    calendarname varchar2(30), 
    constraint  pk_PowerDemand_metadata primary key (region));
 
INSERT INTO PowerDemand_metadata VALUES(1, 'PowerReadingsCal_Hourly');
INSERT INTO PowerDemand_metadata VALUES(2, 'PowerReadingsCal_Hourly');

Creating the Reference-Based View

Create a reference-based view for convenient and efficient access to time series data.

The following statements create a reference-based view for the power demand data:

-- Create the reference view.
 
CREATE OR REPLACE VIEW PowerDemand_rv(region,TotGridDemand,
     MaxCellDemand,MinCellDemand,CellDemandValues) AS
  SELECT meta.region,
  ORDSYS.ORDTNumSeriesIOTRef(
          'TotGridDemand_ts', Ref(cal), 'PowerCartUser.PowerDemand_Tab T',
          'T.sample.SampleTime', 
          'T.sample.TotGridDemand', 
          'region', meta.region),
  ORDSYS.ORDTNumSeriesIOTRef(
          'MaxCellDemand_ts', Ref(cal), 'PowerCartUser.PowerDemand_Tab T',
          'T.sample.SampleTime', 
          'T.sample.MaxCellDemand', 
          'region', meta.region),
  ORDSYS.ORDTNumSeriesIOTRef(
          'MinCellDemand_ts', Ref(cal), 'PowerCartUser.PowerDemand_Tab T',
          'T.sample.SampleTime', 
          'T.sample.MinCellDemand', 
          'region', meta.region),
  ORDSYS.ORDTNumSeriesIOTRef(
          'CellDemandValues_ts', Ref(cal), 'PowerCartUser.PowerDemand_Tab T',
          'T.sample.SampleTime', 
          'T.sample.CellDemandValues', 
          'region', meta.region)
  FROM PowerDemand_metadata meta, PowerDemand_calendars cal
  WHERE meta.calendarname = cal.name;

Formulating Time Series Queries

Formulating time series queries involves invoking time series or time scaling functions, or both. The power demand example includes SQL and PL/SQL queries that return the following information:

• Hourly power demand for the grid (TotGridDemand) for region 1

• Average TotGridDemand for region 1

• Cumulative average TotGridDemand for region 1

• A time series that leads a specified time series by one day (that is, uses the hourly values for one day to create corresponding hourly values for the next day)

• Moving average of TotGridDemand for region 1

• Scaleup of TotGridDemand readings for region 1 from hourly to daily

• A time series reflecting the addition of two time series (MinCellDemand and MaxCellDemand)

The following example shows the execution of the Lead and Moving average functions with power demand data. This example includes the SQL statements and the output with ECHO and SERVEROUTPUT turned on.

SVRMGR> -- Lead: Using timestamps from 1:00 through 5:00 AM on 01-Feb-1998,
SVRMGR> -- create a time series of the number of timestamps 24 hours later.
SVRMGR> -- The result is a time series from 1:00 through 5:00 AM on 02-Feb-1998
SVRMGR> -- containing the same TotGridDemand values as the corresponding
SVRMGR> -- timestamps on 01-Feb-1998.
SVRMGR> --
SVRMGR> SELECT * FROM the 
     2>   (SELECT CAST(ORDSYS.TIMESERIES.ExtractTable(
     3>       ORDSYS.TIMESERIES.Lead(ts.TotGridDemand, 24, 
     4>         to_date('01-FEB-98 01','DD-MON-YY HH'),
     5>         to_date('01-FEB-98 05','DD-MON-YY HH'))
     6>       ) AS ordsys.ordtNumTab)
     7> FROM PowerCartUser.powerdemand_rv ts
     8> WHERE region = 1);
TSTAMP    VALUE     
--------- ----------
02-FEB-98         90
02-FEB-98         89
02-FEB-98         88
02-FEB-98         87
02-FEB-98         86
5 rows selected.
SVRMGR> 
SVRMGR> --
SVRMGR> -- Compute a moving average over a window of 3 timestamps
SVRMGR> -- for region 1.
SVRMGR> --
SVRMGR> 
SVRMGR> SELECT * FROM the 
     2>   (SELECT CAST(ORDSYS.TIMESERIES.ExtractTable(
     3>                ORDSYS.TIMESERIES.Mavg(ts.TotGridDemand,3)
     4>               ) AS ordsys.ordtNumTab)
     5> FROM PowerCartUser.powerdemand_rv ts
     6> WHERE region = 1);
TSTAMP    VALUE     
--------- ----------
01-FEB-98           
01-FEB-98           
01-FEB-98         89
01-FEB-98         88
01-FEB-98         87
5 rows selected.

Figure 11-9 Power Regions and Area of Interest (Spatial)