A SCADA/EMS Source Database Management System

To manage a supervisory control for a power system with 60 substations each with at least one RTU and local HMI, 50000 points of supervision, 7 regional control centers and one main center of operation, interconnection with other generation, transmission and distribution utilities plus the independent system operator (ISO) is not an easy task.

There are power system expansions happening in a monthly basis that must be handled in a fast and precise way. All the systems in the substation and in the control centers must be updated at once to support the expansions as the supervisory data must be delivered to the ISO before the energization of the new equipment.

Maintaining each system configuration apart from each other is an herculean task as there are many different systems, from different vendors, at many levels, that must be updated simultaneously and frequently.

To manage all these systems we developed a single source database that models the SCADA systems and their interconnections and also the EMS representation.

There is a single database implemented with MySQL that are manipulated by 2 applications, one for the SCADA model and one for the EMS model and a set of scripts, one for each type of system that must have its configurations generated.

The database consists of SCADA tables and EMS tables.

SCADA tables are:

  • Supervision Nodes – intelligent components of the SCADA system (IED) that have logical points and data connections with other nodes. Can be a RTU, a data concentrator, a protection relay, an HMI or a control center system.
  • Connections – are the data connection that have the physical points interchanged between the supervision nodes. Nodes of origin and destination, type of protocol and link addresses are attributes of the data connection.
  • Logical Points (of Nodes) – lists of points that are contained in each node.
  • Physical Points (of Connections) – lists of physical points presents in each connection between nodes. Addresses, ASDU types and conversion factors are attributes of the physical points.

EMS tables are:

  • Stations – substations and power plants.
  • Bays – sets (arrangement) of interconnected equipments of substations.
  • Connectors – switchgears and breakers.
  • Supervision Points – point definitions, type, tag, to which connector the point belongs.
  • Point Type – what kind of measures or states a point can be, kV, MW, breaker state, etc.
  • Calculations.


EMS Editor

This application allows to model substations organized by bay and connectors, enter parameters for equipment like transformers, transmission lines, reactors and capacitor banks. Make connections (LIGS in the SAGE system idiom) between equipments and connectors. Create virtual (not yet instantiated anywhere) typed point definitions pertaining to connectors and bays.



The point list can alternatively be imported by a special worksheet prepared by the substation project staff. The worksheet is imported by a PHP script into the database system, and after this operation the worksheet is discarded.

SCADA Editor

In this application are modeled the many SCADA nodes and connections of the supervisory system.



This software presents lists of points that can be filtered by many criteria to be selected and inserted into nodes and connections as well as cut and paste tools for connection data.

Generator Scripts

The scripts simply makes appropriated queries to the (MySQL) database and process the resulting data to put it in the form expected by the systems that are to be configured.

I prefer PHP for creating scripts as it is already used by our intranet site but many other languages can be used.

List of generated data by scripts we use now:

  • SAGE system DAT files for the main Transmission Operation Center (more than 2 million lines of text files).
  • SAGE System customized tabular screens for all substations.
  • Historical data recording system configuration.
  • Substation local HMI files system for 50 substations.
  • Substation local HMI initial screens, all standard objects created and linked. Drawings must be adjusted by hand.
  • Substation local data concentrator configuration files.
  • Regional telecontrol centers data concentrator and HMI configuration files.

To add a new type of SCADA system to be managed is mostly studying its configuration files and creating a new generator script for it.

Concurrency of editing

The applications can be used by many people simultaneously, e.g. one can edit the EMS model of a substation while another can edit de SCADA model of other substation system. Only must be avoided to edit the same model of the same substation by more than one person at a time. This scheme greatly speed things up. When everybody finished the editings, the scripts can be used to create the configuration and point database files for each desired system.

Database Control

The database is backed up and versioned by its dumps. Also we use triggers to monitor and register modifications (what, when and who) to the main tables of the database. A hot backup server is at times synchronized to the main server by means of the excellent SQLYog SJA tool.

We keep all the source tables inside the Innodb memory buffer for better performance.

MySQL has a plethora of free tools to edit, manage, model, synchronize, backup, etc. and has excellent support by many scripting languages and development tools.


The database referential integrity, the data entry applications and the generator scripts are three additional levels of data integrity and consistency checks before the final configuration files are delivered to the runtime systems. This is a significative factor that avoid many type of errors and problems that are commonly made when editing configuration files manually. Also all the systems managed at all levels are guaranteed to be consistent and standardized.


3 thoughts on “A SCADA/EMS Source Database Management System

  1. Very impressiva article. Can you explain how new substations are inserted in the sustém? Is there a script for this task?

    1. Dear Mr. Cássio,

      Realmente muito interessante e oportuno o seu questionamento.
      A resposta é não, tem que ser inserido diretamente no MySQL.
      Todavia, há que se compreender que um sistema, que foi desenvolvido para resolver os problemas de uma única empresa, tenha arestas a aparar. Isto é normal.
      Para resolver esta sua questão, seria adequado manter um contrato de manutenção com os criadores desta ferramenta.
      Outra opção seria esperar pela versão 2.0 do SGBF, que terá características bem mais profissionais, que atendam a maioria das empresas com sistemas supervisório do mesmo porte neste planeta. O custo poderá ser meio salgado, mas os benefícios certamente o justificarão.

      Best regards,
      Ricardo Olsen

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s