Measurement data retrieval with SQL

#1

We have an older version of openPDC (1.4 I think), running with SQL Server. In that schema we see a TimeSeriesMeasurement table that appears to have the majority of the actual measurement values.

We upgraded to a Windows Server 2008R2 system running openPDC v2.3 with MySQL. In this schema, we do not see the same TimeSeriesMeasurement table. We have every other table in a side-by-side comparison with the old version on SQL Server, except for this one. Has this been removed from current versions of openPDC? Is it relocated elsewhere? Is there an extra step we have to complete to get that table back?

We installed with the sample data scripts.

We want to be able to rapidly query the database to extract the most recent measurements from our 8x PMUs and feed that into a web application.

Thanks,
Jason

#2

Hi Jason,

The TimeSeriesMeasurement table was most likely created so that the AdoOutputAdapter would have a destination table for measurement values. You will need to create that table again. Since you probably still have your old database, you may not need this, but the script used to create the table probably looked something like this.

CREATE TABLE TimeSeriesMeasurement(
       SignalID NCHAR(36) NOT NULL,
       Timestamp VARCHAR(24) NOT NULL,
       Value DOUBLE NOT NULL
);

To get data into the table, you will need to configure the AdoOutputAdapter. If you migrated the data from the v1.4 database, you may not need to do anything as the adapter would already be there. If not, you should be able to refer to the adapter in your v1.4 openPDC Manager to get the configuration to match. And if you can’t do that, the link below provides some guidelines for how to configure it.

EDIT: Copied from thread 571880 on CodePlex.

Acronym: PPA
Name: Primary Phasor Archive
Type Name: AdoAdapters.AdoOutputAdapter
Assembly Name: AdoAdapters.dll
Connection String: BulkInsertLimit=500; DataProviderString={ AssemblyName={MySql.Data, Version=6.9.5.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d}; ConnectionType=MySql.Data.MySqlClient.MySqlConnection; AdapterType=MySql.Data.MySqlClient.MySqlDataAdapter }; DbConnectionString={ Server=localhost; Database=openPDC; Uid=openPDC; Password=openPDC }; TableName=TimeSeriesMeasurement; IDFieldName=SignalID; TimestampFieldName=Timestamp; ValueFieldName=Value

Note the places I marked in bold. You will need to change the version number to match your version of MySQL Connector/Net. You may also need to modify the DbConnectionString property to connect to your MySQL database with the proper credentials.

If you are setting this up as a CustomOutputAdapter (as opposed to a Historian), you will need to set the InputMeasurementKeys property the same way you did for the MySQL adapter in your screenshot.

Thanks,
Stephen

#3

Stephen,

I used the info in your reply and at the link you sent. I have created the table by running a query with the create statements. I see that now in the database schema.

I have also UPDATED my PPA from the mysql adapter to the AdoAdapter as suggested. Is there anything else I need to do in order to see data in the timeseriesmeasurement table? Save and then initialize? Restart openPDC service?

We’re just not getting any records in the table yet.

-Jason

#4

Some more information.

This is the error we get in our MySQL Workbench logs. Not sure exactly what the ‘type’ field that it thinks is Null is:

2016-11-21T22:10:03.045323Z	 1571 Query	INSERT INTO ErrorLog (Source, Type, Message, Detail) VALUES ('No Source', 'System.InvalidOperationException', 'Failed to load adapter "PPA" [ADOAdapters.AdoOutputAdapter] from "C:\\Program Files\\openPDC\\AdoAdapters.dll": Value cannot be null.
Parameter name: type', '(Inner Exception)
Date and Time:         11/21/2016 5:09:58 PM
Machine Name:          WIN-KUPIQUEG1GI
Machine IP:            fe80::3d98:6490:2b4c:2df7%12
Machine OS:            Microsoft Windows NT 6.1.7601 Service Pack 1
Current User ID:       NT SERVICE\\openPDC
Current User Name:     
Current User Phone:    
Current User Email:    

Application Domain:    openPDC.exe
Assembly Codebase:     C:/Program Files/openPDC/openPDC.exe
Assembly Full Name:    openPDC, Version=2.3.1.0, Culture=neutral, PublicKeyToken=null
Assembly Version:      2.3.1.0
Assembly Build Date:   10/16/2016 12:01:30 AM
.Net Runtime Version:  4.0.30319.42000

Exception Source:      mscorlib
Exception Type:        System.ArgumentNullException
Exception Message:     Value cannot be null.
Parameter name: type
Exception Target Site: CreateInstance

---- Stack Trace ----
   System.Activator.CreateInstance(type As Type, nonPublic As Boolean)
       openPDC.exe: N 13800168
   System.Activator.CreateInstance(type As Type)
       openPDC.exe: N 00012
   GSF.TimeSeries.Adapters.AdapterCollectionBase\`1.TryCreateAdapter(adapterRow As DataRow, adapter As T&)
       openPDC.exe: N 00442


(Outer Exception)
Date and Time:         11/21/2016 5:10:03 PM
Machine Name:          WIN-KUPIQUEG1GI
Machine IP:            fe80::3d98:6490:2b4c:2df7%12
Machine OS:            Microsoft Windows NT 6.1.7601 Service Pack 1
Current User ID:       NT SERVICE\\openPDC
Current User Name:     
Current User Phone:    
Current User Email:    

Application Domain:    openPDC.exe
Assembly Codebase:     C:/Program Files/openPDC/openPDC.exe
Assembly Full Name:    openPDC, Version=2.3.1.0, Culture=neutral, PublicKeyToken=null
Assembly Version:      2.3.1.0
Assembly Build Date:   10/16/2016 12:01:30 AM
.Net Runtime Version:  4.0.30319.42000

Exception Source:      
Exception Type:        System.InvalidOperationException
Exception Message:     Failed to load adapter "PPA" [ADOAdapters.AdoOutputAdapter] from "C:\\Program Files\\openPDC\\AdoAdapters.dll": Value cannot be null.
Parameter name: type

---- Stack Trace ----

###############

This is our Connection String:

BulkInsertLimit=500; DataProviderString={ AssemblyName={MySql.Data, Version=6.2.5.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d}; ConnectionType=MySql.Data.MySqlClient.MySqlConnection; AdapterType=MySql.Data.MySqlClient.MySqlDataAdapter }; DbConnectionString={ Server=localhost; Database=openpdc; Uid=root; Password=sqldba}; TableName=timeseriesmeasurement; IDFieldName=ID; TimestampFieldName=Timestamp; ValueFieldName=Value

#5

Yes, you will need to save and then initialize the PPA historian. Monitor the openPDC Console or check the error log to see if there are any errors. Make sure you have selected PPA in the Historian field combo box when configuring your input devices.

#6

Ok, we see the same error in the openPDC Console:

[Output Adapter Collection] Failed to load adapter “PPA” [ADOAdapters.AdoOutputA
dapter] from “C:\Program Files\openPDC\AdoAdapters.dll”: Value cannot be null.
Parameter name: type

#7

I see it now. The type name for the adapter should be AdoAdapters.AdoOutputAdapter, not ADOAdapters.AdoOutputAdapter. It is case-sensitive.

#8

Thanks Stephen, good catch.

So in the timeseriesmeasurement table, what is the primary key? An autonumber? We just have ID, Timestamp, and Value, but it seems like the ID field gets repeated after the first round of measurements.

I think this makes sense because the IDFieldName is simply the name for the measurement, correct? We would expect that to be repeated once every measurement?

I’m thinking our solution here is to add a new Primary Key field to timeseries measurement that is just an AutoNumber.

Thoughts?

#9

Yeah, I’ve taken it up as a general practice to add autoincrementing primary keys to all my tables so that sounds like a good idea to me. As you noted, the ID field of the measurement object is actually the ID of the signal (it matches the SignalID column of the Measurement table). Each individual measurement taken for the same signal will have the same ID.

#10

Hi
We are facing the same problem to get data into MYSQL data base … table didn’t create by itself.is it possible to give a pictorial guideline how to set this up

#11

Download this document for a barebones pictorial walkthrough, starting from the Configuration Setup Utility.

#12

openHistorian%20to%20MYSQL-page-008

#13

I have followed each steps but when i creates the table the table shows empty? i have created the table before adding any devices and also tried to create the table after adding a devices… please let me know the reasons for empty table.

#14

It could be the order of operations. Try initializing the PPA historian.

#15

Find out the problem. Thank you

#16

Hi stephen.

i have a concern. my timeseriesmeasurement table is not static. i have a create a new timeseriesmeasurement in openpdc data base each time i want to store data in it. So like i accidentally shut down my pc then all my data is gone. How to create a fixed table in the database??

#17

That sounds like something wrong with the installation of MySQL. CREATE TABLE is the standard way to create a permanent table in SQL, and there is no reason I know of for the table to exist only temporarily. I would suggest Googling for reasons why MySQL would erase your table on restart.