Table Level Notes in HydroTrek
This document explains what tables are used in the specified databases during each program of the HydroTrek software suite.
This is a current list as of April 2023.
HT-EX
This is the first that needs to be run. It takes data from whatever raw form the utility uses such as an ODBC Historian and converts it into a SQLite database so that we can use it in other programs.
The input data source can be multiple types. Including but not limited to Historian, SQLite, postgres, SQLServer, MySQL... What is required for this data source is that the data has to be in the format of ID, Datetime, Value.
In our current projects we use SQLite to simulate the data. The database is called a historian database and contains 1 table with the data in the format above.
HT-EX outputs data into a database called network_extracted.This is always a SQLite database.
Tags vs. Objects
An object is a physical object. Like a tank, Pipe, Pump... A scada tag is a piece of information that we can measure from an object. Such as Tank Level or Pipe flow. The Scada Object is the parent and the Scada Tag is the child.
Keeping this in mind will make the tables easier to understand.
Extracted Database
The tables required for the database are as follows:
- RawData - This is the table that the HT-EX is inserted into. This is at the Scada Tag level. (Tank_1_level, Time stamp, Value)
- ScadaTag - This table contains the valid scada tags for the database. It will contain references to other tables such as ScadaObject, DataType, and DataSource (Tank_1_Level)
- ScadaObject - List of all Scada objects in the database. Contains parent table of ScadaTag and contains reference to Display Group(Tank 1)
- ScadaObjectType - What the Scada object is (Tank, Pump, Pipe...)
- DataType - How the Scada value is defined. What units, Decimal Accuracy and what type of entity is the data type for example Level is in feet...
- DataSource - Which data source a particular Scada Tag comes from.
- DisplayGroup - Which display group a given scada object belongs to.
These tables are also duplicated when we go to Postgresql.