WIMS Data Structures

Each WIMS facility database contains the exact same table definitions as well as triggers and stored procedures. 

See also: WIMS Database Overview - MS SQL Server, Hach WIMS Key Table Database Diagram

WIMS uses several tables to store its data. This data is usually brought in by SCADA/LIMS interfaces, ADOCALC, and user manually entered data.  The data storage tables are organized by the frequency of the data they are storing. WIMS uses the narrow data slotted storing format, meaning that for a particular frequency slot (hourly variables allow storing 24 value per day), there can only be one record.
DATATBL is used to store daily data (VARTYPE= C, T, or P). It is uniquely indexed on two columns: DATESTAMP & VARID. VARID is a foreign key to VARDESC.VARID. DATESTAMP column stores the date of the current datapoint. 

Column descriptions:

AUDITUSER – Contains username of the user who last changed the datapoint
AUDITTIMESTAMP – Contains the date stamp of the last change
DATESTAMP – part of the table’s unique index
VARID – part of the table’s unique index
CURVALUE – floating point value of the current datapoint
TEXTVALUE – text representation of the current datapoint’s value
STATUS – holds the data approval level for the current datapoint
FORCED – Indicates whether a value has been forced in place of a calculation (i.e calculation result override)

Daily detail data (Hourly, 15 Min data, etc…) is stored in the DATADD+ tables. Their structures resemble the one of DATATBL.  For example:
DATADDH - Stores the hourly frequency data for variables with a VarType of H, N, and B.
DATADD4 - Stores the 4 hour frequency data for variables with a VarType of 4,G, and E.
DATADDF - Stores the 15 Minute frequency data for variables with a VarType of F,V, and X.
DATADD3 - Stores the 30 Minute frequency data for variables with a VarType of 3,W, and Q.
DATADD5 - Stores the 5 Minute frequency data for variables with a VarType of 5, Y, and R.
DATADD1 - Stores the 1 Minute frequency data for variables with a VarType of 1, S, and A.
Daily detail sample comments are stored in the DataDDx_C where x is the Vartype.  For example:  DATADDH_C is the sample comments table for hourly data.
Daily detail audit trail is stored in the DataDDx_AT table where x is the VarType.  For example: DATADDF_AT is the audit trail table for 15 Minute data.

WIMS uses stored procedures SEEKDATEVARID, SEEKDDH, SEEKDDF, SEEKDD5, SEEKDD4, SEEKDD3, SEEKDD1 to seek data in DATATBL, DATADDH, DATADDF, DATADD5, DATADD4, DATADD3, DATADD1 respectively.

TABLE LISTING:

APPROVALFILTER - Rarely used.  Holds an sql query that can be used on the Data Approval Form (Select Filter button) to load a customized recordset on that form.

APPROVALINI - The list of approval levels for the facility.

AREA - The area setup information.

AREA_I - The area additional info.

BIOSOILTEXTURE - Not Used

BOD - Not Used

BODCRIT - The BOD Criteria settings. 

BODRECS - The BOD Manager sample/bottle information.  Contains the detailed BOD information (Bottle #, Sample Volume, Initial DO, etc...) for each BOD bottle.

BODXREF - The BOD Manager Sample Setup information.

CALCGRP - The saved calculation group for use with Calc Data Block (Load and Save Group buttons)

COMMENTS - Contains result comments for daily type variables.

DATADD1 - Data table for 1 minute variables

DATADD1_AT - Data audit trail table for 1 minute variables

DATADD1_C -  Result comments table for 1 minute variables

DATADD1_I - The data additional information table for 1 minute variables.

DATADD3 - Data table for 30 minute variables

DATADD3_AT - Data audit trail table for 30 minute variables

DATADD3_C -  Result comments table for 30 minute variables

DATADD3_I - The data additional information table for 30 minute variables

DATADD4 - Data table for 4 hour variables

DATADD4_AT - Data audit trail table for 4 hour variables

DATADD4_C -  Result comments table for 4 hour variables

DATADD4_I - The data additional information table for 4 hour variables

DATADD5 - Data table for 5 minute variables

DATADD5_AT - Data audit trail table for 5 minute variables

DATADD5_C -  Result comments table for 5 minute variables

DATADD5_I - The data additional information table for 5 minute variables

DATADDF - Data table for 15 minute variables

DATADDF_AT - Data audit trail table for 15 minute variables

DATADDF_C -  Result comments table for 15 minute variables

DATADDF_I - The data additional information table for 15 minute variables

DATADDH - Data table for hourly variables

DATADDH_AT - Data audit trail table for hourly variables

DATADDH_C -  Result comments table for hourly variables

DATADDH_I - The data additional information table for hourly variables

DATATBL - Data table for daily variables

NOTE - See Comments table for Daily variable result comments.

DATATBL_AT - Data audit trail table for daily variables

DATATBL_I - The data additional information table for daily variables

EGROUP - Entry Form Groups

EGROUPACCESS - Security matrix that defines access to different Entry Form Groups

EVENTFILTERS -

EVENTS - The list of WIMS Events

EVENTS_AT - Events audit trail table

EVENTS_I - Events addtional information table.

EVENTTYPE - List of Event Types.

EVENTTYPEACTIONS -

EXPORTHDR - Basic header information on an Excel Export List.

EXPORTVARS - Variables for an  Excel Export List 

FILES - Simulates a file system for WIMS clients. Files are stored as Binary Objects (BLOBS).  For example, when a spread report definition is saved, the spreadsheet is saved as a blob in the FILES table. 

GGROUP - Graph Groups

GGROUPACCESS - Security matrix that defines access to different  Graph Groups

GRAPHCORR - The settings for saved correlation graphs

GRAPHLIST -

GRAPHPROB - The settings for saved Probability graphs.

GRAPHSAMPLE - The settings for saved QC Analysis Graphs

GRAPHTS - The settings for saved time series graphs.

HDE -  Daily detail form header information such as an hourly or 15 minute data entry form.

HDEVARS - Variable list for a daily detail form.

ICONS - No longer used.  May contain the list of shortcuts imported from an OPS 32 database.

LC_FILTERSETTINGS - Lab Cal Saved Calendar Filters

LC_LABSECTION - Lab Cal Lab Section List Table.

LC_LASTSAMPLENUM - For each date, holds the the highest sample number assigned to that date.   

LC_MDLDETAIL - MDL (Method Detection Limit) Study detail (i.e. trial) table.

LC_MDLHEAD - MDL (Method Detection Limit) Study header table.

LC_METHOD - Lab Cal Method list table

LC_PEOPLE - Personnel list table.  Used in BOD Manager and Lab Cal.

LC_QCTEST

LC_QCTYPE - The Lab Cal QC Type list.

LC_SAMPLE - Lab Cal Sample Header information table.

LC_SAMPLECUSTODY - Lab Cal Sample's Chain of Custody Information

LC_SAMPLEDEF - Lab Cal Sample setup (definition) header infomation.

LC_SAMPLEDEFTEST - Lab Cal Sample setup test schedule (detail) infomation.

LC_SAMPLETEST - Lab Cal Sample test result information (i.e. analyzed by, analysis start and complete time)

LC_SAMPLETYPE - Lab Cal Sample Type List table

LC_SCHEDCUSTOM - Lab Cal Custom Schedule.  For example, when scheduling a sample and you choose Custom Schedule, this table holds the dates for the custom schedule. 

LC_SCHEDCUSTTEMP - A temporary table used during scheduling of custom schedules.

LC_SCHEDTEMP - A temporary table used during scheduling of custom schedules.

LC_SCHEDULE - Lab Cal Schedule information.   

LC_TEST - Lab Cal Test List Table

LC_USERDEF - Sets the captions for the user defined fields in the LC_SAMPLE and LC_SAMPLEDEF table

LC_WORKSHEETS - Holds the header information for the for Lab Cal Worksheets (i.e. BOD Manager).

LC_WS_STATUS - Lab Cal Worksheet Status lookup table.

LC_WSGROUP - Future

LC_WSGROUPACCESS - Future

LIMITS - Regulatory limits for variables.  Links to VarDesc.VarID

LOCATION - Contains the list of Locations/sampling points. Variables (VARDESC) links to this table via VARDESC.LOCID=LOCATION.LOCID to set the variable's location

LOCTYPE - Location type lookup table.

LOGBOOK - Logbook list.

LOGBOOKACCESS - Security matrix that defines access to different Logbooks

LOGBOOKENTRIES - Contains the Logbook entries (i.e. comments) for each logbook.

LOOKUPTABLES - Lookup table data.

MDE – List of Monthly Data Entry (MDE) forms.

MDEVars – List of variables for each Monthly Data Entry (MDE) form.

PICKVAR - Variable Browser settings.

PICKVARFILTERS - Variable Browser Filters

PPBASIN - Activated Sludge basin setup information for WIMS Modeling.

PPCLARIFIER - Activated Sludge clarifier setup information for WIMS Modeling.

PPFLUXCURVES - Activated Sludge flux curves setup information for WIMS Modeling.

PPPARAMETERS - Saved coefficients (parameters) used for WIMS Modeling.

SETTINGS - Holds Facility specific settings such as LockDate, VARSETUP_LOCKED, etc...

SPFORMS - Custom Data Entry Form list.

SPRPTS – Contains a list of saved Spread Reports

SPRPTSGROUP – List of available Spread Report Groups

SPRPTSGROUPACCESS – Security matrix that defines access to different Spread Report Groups

UDCAPS - Sets the captions for the 10 user defined fields in the Vardesc table.

VARDESC - Stores information about all parameters/variables tracked by WIMS.

VARDESC_AT – Audit Trail information for the VarDesc table.

VARDESC_DEL – Deleted records from the VarDesc table.  A variable can be restored from this table.  Data is not removed from the Data tables when a variable is deleted (the records are orphaned), therefore the data will be recovered when the variable is restored.

VAREQ – Historical Equations for variables.

VARQC – Quality Control Limits for variables. 

ORACLE NOTES:

All WIMS software (clients, adocalc, serversetup, interfaces) attempts to connect to Oracle as OPSROOT. The OPSROOT user is also a schema owner. His schema contains tables, sequences, triggers and stored procedures.

OPSROOT.FACILITY is the table that contains the list of all WIMS facilities. To get the list, run a query such as:
select filename,name from opsroot.facility

The ‘filename’ field contains the oracle user for the particular facility. OPSRTUT is the user for the Tutorial facility. OPSRTUT is again a schema owner. All tables, sequences, stored procedures, and triggers that belong to the Tutorial facility are owned by OPSRTUT.

For instance, to get a list of tables for OPSRTUT, run the following:
SELECT table_name "Table" FROM sys.dba_all_tables WHERE owner = 'OPSRTUT' AND nested <> 'YES' ORDER BY 1

To get a list of stored procedures, run :
SELECT object_name FROM sys.dba_objects WHERE object_type = 'PROCEDURE' AND owner = 'OPSRTUT' ORDER BY 1