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.
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.
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
EVENTS - The list of WIMS Events
EVENTS_AT - Events audit trail table
EVENTS_I - Events addtional information table.
EVENTTYPE - List of Event Types.
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
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_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.
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