PI to Aces Interface
Callout
Document
April 2003
About
This Document
This Callout Guide is a living document and has a limited amount of editing capability. To save corrections and notes to it, press <Ctrl>S after your edits. Press <Ctrl>P to print the document on the default printer. Resize this window to taste.
The Callout Guide is also a functional Web Browser. Enter a URL in the address bar above to
navigate to it or click the Globe
icon to go to your default search site.
Click the Home icon to return
to this document.
Background
PSE periodically receives incoming Energy Scheduling and Accounting (ESA) data from a variety of sources and stores them in a redundant Oracle at PSE. Among the sources are WECC PID data from a X.25 WAN, an EMS interface and manually entered data using a ESA VMS application.
Additionally, PSE processes Caminus ACES Schedule and Meter tags in a separate archive. Finally, data of varying types and sources is kept in a Pi trending archive at PSE.
PSE_PI_ACES Theory of Operation
When data is inserted into the ESA Oracle, table level triggers update a table called ‘HOURLYDATA_CHANGED’. PSE_PI_ACES periodically polls this table for any new rows that may have been inserted during intervening polling intervals. Rows in HOURLYDATA_CHANGED refer to rows of ESA DAY and HOUR data in HOURLYDATA. Each row in HOURLYDATA contains 25 columns of hourly ESA data (an additional hour beyond 24 is included in order to deal with Day Light Savings time transitions). This process forms the primary input set for PSE_PI_ACES. PSE_PI_ACES repeats incoming ESA data into a Caminus ACES scheduling system and a Pi trending archive. PSE_PI_ACES is a uni-directional, inbound only interface into ACES and Pi.
Pi Trending Archive
This is a data historian provided by Osisoft. It is intended to provide a fast trending tool for real time plotting applications. PSE_PI_ACES gets the ESA Account ID and Day from HOURLYDATA. The ESA Account ID is typically a 4-digit number. PSE_PI_ACES uses the ESA account ID to look up Pi points in a memory resident cache established during initialization. PSE_PI_ACES writes all 25 (if DST) hours of ESA data values and data qualities using the DAY specified in HOURLYDATA. Current data is applied to the real time snapshot. Past data is overwritten in the archive. Future data is rejected by PSE_PI_ACES. Pi cannot store future dates. The Pi tag names that hold ESA Quality data are named after the corresponding Value tag names but are prefix with a ‘Q’ character. The Quality tags are Pi Digital tags. The Value tags vary in data type.
PSE_PI_ACES inspects the Pi archive value at the DAY and HOUR specified in HOURLYDATA. If the value there differs from the incoming ESA value, the Pi archive timestamp is compared to the current system time. If the Pi archive timestamp is older then the current time by 60 minutes and if the incoming ESA data is not manually entered (Quality code 4), PSE_PI_ACES stores Quality code 16 in both the corresponding Pi ‘Q’ tag and the ACI. This Quality code designates the data for that hour as ‘Aces Review Needed’.
ACES Communication Interface
(ACI)
Scheduled and historical data can be imported into ACES via the ACES Communication Interface (ACI). When data is loaded into the import tables, database triggers automatically cause the ACES application servers to process the data in the import tables.
There are at least two methods available to get data into the import tables:
ACI
Importer Utility. This utility is
intended to accomplish batch loads using formatted CSV flat files.
Manual
insertions of data into the ACI import tables. This is the method used by
PSE_PI_ACES.
PSE_PI_ACES only inserts the current hours’ data into the ACI import tables. The other 24 are discarded. Any DAY specified in HOURLYDATA is inserted including both past and future dates. PSE_PI_ACES creates Oracle PL/SQL scripts that contain the code necessary to interact with the ACI and the corresponding data values and qualities. These scripts are then applied to the ACI and stored in folders named after the current day. This makes it possible to audit or replay the ACI data in a different execution thread.
PSE_PI_ACES
System Diagram
The following diagram shows the ESA Account ID mapping and data flow:
PSE_PI_ACES
Installation
PSE_PI_ACES.bat and PSE_PI_ACES.mdb should be copied into the same folder that contains PSE_PI_ACES.exe. Both of these resources are static and never change during normal processing. PSE_PI_ACES is intended to run as a Windows service. To install PSE_PI_ACES as a Windows service, start PSE_PI_ACES with the following parameter: C:\> PSE_PI_ACES INSTALL or C:\> PSE_PI_ACES UNINSTALL to uninstall PSE_PI_ACES:
PSE_PI_ACES
Initialization
PSE_PI_ACES follows the convention used by Osisoft for Pi interfaces. That is to say, it parses a batch file that is named after the interface’s executable. The batch file contains the actual Pi interface executable name followed by a variable series of parameters that specify PSE_PI_ACES run time behavior. The parameters are organized as /VALUE=(KEY) pairs. The batch file must contain a single line only. New line characters within the parameter stream will confuse PSE_PI_ACES. Items in the batch file are case insensitive. The following example PSE_PI_ACES.bat file discusses these parameters and their effects on PSE_PI_ACES.
PSE_PI_ACES.exe
/PiServer=(pidatasvr:O)
/PiUser=(piadmin) /database=(ESA.PUGET.COM) /User=(esa) /Pass=(esa)
/BackupDB=(ESA2.PUGET.COM) /ACIArchive=(C:\PI\dat\ACIArchive\) /POLLING=(30)
/ACES_LUT=(C:\PSE_PI_ACES\PSE_PI_ACES.mdb) /ONLINE=(TRUE) /VERBOSE=(FALSE)
|
Parameter |
Meaning |
|
PSE_PI_ACES.exe |
The executable name this batch file belongs to |
|
/PiServer |
Specifies the Pi server node and point source to use |
|
/PiUser |
Specifies the account to use on PiServer |
|
/database |
Specifies the Primary ESA Oracle to connect to at startup |
|
/Backup |
Specifies the Secondary ESA Oracle to connect to at the first fail over |
|
/User |
Specifies the Oracle User Name to use on both ESA servers |
|
/ Pass |
Specifies the Oracle Password to use on both ESA servers |
|
/ACIArchive |
Specifies the root folder to start creating ACI SQL scripts in |
|
/ACES_LUT |
Delivery Point cache is made from EnergyAccountLookup Table |
|
/ONLINE |
Set this to FALSE to cause the interface to process HOURLYDATA but bypass all writes or deletes into ESA, Pi or ACI. |
|
/VERBOSE |
Log more events into PIPC.LOG |
|
/POLLING /ENABLE_ACI |
Specifies the time between input samples. Set to False to disable the ACES interface. |
This shows PSE_PI_ACES after successfully initializing:
The upper portion shows a running log of messages destined for PIPC.LOG. The bottom frame displays run time statistics. The status bar at the bottom shows the Primary ESA host and Pi server in addition to other dynamic status messages.
PSE_PI_ACES Run
Time Operation
During data transfers, the interface shows it’s progress while processing the current HOURLYDAYA_CHANGED snapshot. Running accumulators that describe the amount of traffic PSE_PI_ACES has experienced and any input exceptions are shown in the status accumulator frame.
There are currently two types of input exceptions:
No
Pi Model
The incoming ESA Account ID has no matching Pi tag or EnergyAccountLookup row.
The incoming ESA Account is discarded if that point does not exist in Pi or ACI. This is not an exclusionary exception and it may be possible for ESA Accounts to exist in one target but not the other. Only the archive that isn’t modeled is ignored.
Other
Exceptions
These are all others of varying types. Enable verbose logging by setting /VERBOSE=(TRUE) in the initialization bat file to log each exception and success in PIPC.log. Most typical other exceptions are HOURLYDATA that specifies a future Pi date (API status -11046)
Future data is conveyed to ACI but rejected by Pi and this accounts for the difference between Pi Updates and ACES Updates in the status accumulator frame. The Cumulative counter shows all the HOURLYDATA_CHANGED rows processed so far. Last show the total rows from HOURLYDATA_CHANGED in the current polling interval. Average is Cumulative divided by the total number of polling intervals. Max is the largest HOURLYDATA_CHANGED sample yet taken. Pi Updates and ACES Updates are accumulations of all the ESA Accounts that have successfully transferred since the interface were started (Up Since).
Run Time Tools
It is possible to interact with PSE_PI_ACES in certain ways while it is running and processing data. Run Time Tools are accessed from menu items shown at the top of the run time form:
The Modify Polling Interval menu item let’s you change the amount of time between HOURLYDATA_CHANGED samples while the interface is running. You can specify 1 – 60 seconds (including fractions of seconds). The default value to use at startup is specified in PSE_PI_ACES.bat as parameter /POLLING.
The input box shows the current Polling Interval before any edits.
Replay AcctID and DAY allows you to specify an ESA Account ID and DAY and inject those values into the HOURLYDATA_CHANGED processing loop as if they had been gathered from ESA. This permits online playback of selected HOURLYDATA. This menu item is disabled while PSE_PI_ESA is processing data from HOURLYDATA_CHANGED and is only available in the intervening period between input samples. While injecting ESA Account ID and DAY data, input polling is disabled and re-enabled after the online replay completes:
Select an ESA DAY and enter an Account ID. Click Replay to write the HOURLYDATA into Pi and ACI.
Resetting the interface causes PSE_PI_ACES to spawn a copy of itself and exit. Stopping the interface causes PSE_PI_ACES to exit.
Troubleshooting
Notes
16-Apr-2003 Jeff Martin
Check for duplicate ESA points in the point cache.
This doc isn’t 100% sync’d to the application yet..
13-Apr-2003 Jeff Martin
Starting Troubleshooting Notes. Add new notes to the TOP of old ones so that the most current notes come first.