I am working in a project team on openPDC proof of concept. I admit I am new in openPDC subject. I have two questions regarding how openPDC cooperates with external databse.
We would like to configure external database (for PoC, SQLite) with separate tables for PMU’s, based on our schema. Will openPDC send data to such external DB? Or the DB schema needs to be created according to internal openPDC database?
Second issue is whether the data sent as output stream are validated. I mean if some dataframes from PMU are incorrect are they rejected or all data as obtained are forwarded to output streams (meaning our external DB)?
I would appreciate any comments or hints on such issues.
The database created by the openPDC software is intended only for configuration data. It’s actually fairly uncommon to use this database for saving PMU data. However, the request is common enough that we developed the AdoOutputAdapter to write measurements into a relational database table. It would be possible to write these measurements back to the openPDC database or an external database. Using multiple AdoOutputAdapters, you can write data to multiple tables or databases so it would indeed be possible to set up your proof of concept.
The AdoOutputAdapter doesn’t really care about the schema of the database that writes to, but the table it’s writing to will have to be mapped to fields of the IMeasurement interface via adapter configuration. It’s designed to simply write to the table whenever it receives a new value, so I’d highly recommend at least three fields in your PMU tables for a signal identifier, a timestamp, and the value itself.
Thank you very much for your fast reply. It definitely solves my question regarding an external database.
Since you state that the adapter is designed in order to write to the table whenever it receives a new value, I would like to clarify if all data even with incorrect CRC, of dataframe, for instance, are directed to the databse. I would appreciate your comment on it.
Frame checksum validation in openPDC is applied at the point of ingress into openPDC. Therefore, it is independent of the destination for the data, whether it’s the database, the local historian, another IEEE C37.118 stream, or some collection of multiple destinations. That is to say, if checksum validation is disabled in the input device’s configuration, it will pass data into openPDC’s routing engine to be consumed by the AdoOutputAdapter and written to the database regardless of the CRC.
Checksum validation can be controlled for each frame type independently by adding the checkSumValidationFrameTypes parameter to the connection string for the input device. The value of this setting is a comma separated list of frame types for which validation should be enabled. Valid frame types come from the following list.
NoFrames
ConfigurationFrame
DataFrame
HeaderFrame
CommandFrame
AllFrames
For example, to disable checksum validation specifically on data frames, add the following setting to your input device’s connection string: checkSumValidationFrameTypes=ConfigurationFrame, HeaderFrame, CommandFrame. To ignore all checksums, add the following setting to your input device’s connection string: checkSumValidationFrameTypes=NoFrames.
By default, validation is enabled for all frame types, which means data would not be passed along to the AdoOutputAdapter and would not be written to the database in the case of an invalid CRC.
I would like to ask a question as a continuation of the thread with an external data base. According to our client requirements we should set a connection of openPDC to an external database in SQLite.
In my project team we have found a definition of the Connection String for MySQL at one of the threads.
Could you please comment on how this would be different or problematic to define for SQLite? How we should redefine it for SQLite external database? My team colleague responsible for this connection states that he obtains errors with ajusting the string to SQLite. Any hint or comment would be most welcome.
I’m a little confused as openPDC supports SQLite natively. You can even migrate your database from any of the supported RDBMS to SQLite. Just use the ConfigurationSetupUtility that comes with openPDC.
When you setup a database using SQLite you can check the ConnectionString and DataProviderString in the openPDC.exe.config file.
thank you so much for your fast reply. We have implemented what you suggested, tested parameters of the Conncection String and we are closer to the success but not yet there. In detail, the current situation is that data from Shelby test set are queued.
Our current Connection String is the following:
BulkInsertLimit=500; DataProviderString={AssemblyName={System.Data.SQLite, Version=2.6.0, Culture=neutral, PublicKeyToken=db937bc2d44ff139}; ConnectionType=System.Data.SQLite.SQLiteConnection; AdapterType=System.Data.SQLite.SQLiteDataAdapter}; DbConnectionString={Database=“C:\Users\user224\PycharmProjects\openPDC\test_database.db”;}; TableName=TimeSeriesMeasurement; IDFieldName=SignalID; TimestampFieldName=Timestamp; ValueFieldName=Value; InputSourceIDs=PPA; InputMeasurementKeys=3647f729-d0ed-4f79-85ad-dae2149cd432
We will aprreciate any comment or hint on how to proceed to send data to an SQLite database via ADO.
I see, you are trying to archive measurements into SQLite. Firstly, you can certainly do this, but I do not think this is a solution that is going to scale very well - I would certainly consider using a time-series database for this type of task, e.g., the openHistorian. In the case of openHistorian, you can setup an STTP connection to your openPDC and immediately receive all data and have it archived, with trending and graphing tools at your disposal, e.g., Grafana.
However, if you really need to archive data into SQLite (or other database) because it suits your current needs, you can use the built-in AdoOutputAdapter. Note that in order for this to work the database will need a table to hold the measurement results. For detailed information on this, see this post:
One difference for SQLite is that in addition to a proper ConnectionString (as you posted above), you will also need to set a proper DataProviderString, for a SQLite ADO connection, this data provider string will be as follows:
Also, I now see you have the correct data provider string when I examined your connection string.
I also see that you are archiving to this folder: C:\Users\user224\PycharmProjects\
Since openPDC runs, by default, as the NT SERVICE\openPDC user, this user will not automatically have read/write access to this folder.
I would suggest “right-clicking” on the folder in Windows, going to security, then adding NT SERVICE\openPDC (from local machine) with read/write access to the folder. When you do this, openPDC should be able to write to the database.
Thank you so much for your answers and suggestions. These are very important points we need to test. The idea on our side it to have an external database of “pure” phasor data to be processed later (in a way that we don’t define yet, just want to store the data). Now we are at the proof-of-concept level and that is why we focus on SQLite. We will test your suggestions asap.
thanks a lot! Indeed, it worked and we have a connection to an external database.
However, we have noticed that the data are sent to a particular table (TimeSeriesMeasurement) with fixed fields, namely ID, Timestamp, Value. We would like to have separate tables for each PMU with defined by us columns in each table. We have found this thread Configuration to get data from database and we understand that there is a step between sending data from openPDC to a database of our schema through the timeSeriesMeasurement table. There should be a process to obtain values for a particular PMU from Value field and store them in a correspoding table. Please correct my understanding if needed.
If you can suggest an effective way of obtaining data from ADO to be stored in a database of tables for each PMU, we will appreciate and go further with PoC.
It sounds to me like you’re looking for a mapping between individual measurement signals and the PMU that produced the measurements. Since your openPDC is receiving data from your PMUs, this mapping should already be available by querying the configuration database.
SELECT
Device.Name,
Measurement.SignalID
FROM
Measurement JOIN
Device ON Measurement.DeviceID = Device.ID
Assuming your openPDC configuration database is also a SQLite database, this query can be joined with your TimeSeriesMeasurement table like in the example from the thread you referenced. This can even be done if the configuration and data are in separate databases by using ATTACH DATABASE (https://sqlite.org/lang_attach.html).
SELECT
Device.Name,
TimeSeriesMeasurement.Timestamp,
TimeSeriesMeasurement.Value
FROM
TimeSeriesMeasurement JOIN
Measurement ON Measurement.SignalID = TimeSeriesMeasurement.ID JOIN
Device ON Measurement.DeviceID = Device.ID
Furthermore, since you are planning to pivot the data, I thought I should share a resource I recently found that suggests there is a better way to implement a pivot using GROUP BY and FILTER rather than using JOIN to repeatedly alias the same table.
