CSV adapter adds large number of data streams: limits and methods

[Chen0917]

Good afternoon!

I have some questions about the CSV adapter creation in openECA manager.

1. Is there any limit on the number of the columns in one CSV file to be adapted as input or any size limit?

2. Is there any limit on the number of the data streams(measurements) for the whole platform?

3. If the answers to 1 and 2 are No, given that we have a CSV file to be adapted as input in the openECA manager which contains a large number(for example, 200) of columns, i.e. measurements, is there any method that we can use to not manually add them one by one as input data streams in one CSV adapter?

Please let me know if I haven't been specific enough.

Thank you very much!

[ritchiecarroll]

There are no defined limits (columns nor streams) - there may be practical limits.

Automation of defined measurements in a CSV to columns can be tricky - my suggestion would be using a script to generate SQL to insert/define new measurements then another script to define column mappings in connection string.

Hope that helps!

Thanks,
Ritchie

[Chen0917]

Hi Ritchie,

Thank you very much! I think this helps a lot. I will try the SQL method and provide update under this question when I have some results.

Thanks,
Chen

[Chen0917]

Hi Ritchie,

Just a follow-up question. If using the SQL script to create input data, is there any way to create an off-line data set other than the time-stamped data streaming for the off-line applications?

Besides, is there any documentation for the Connection String for the MySQL input adapter creation?

Thanks,
Chen

[marco.gonzalez]

Hi Chen.

I think this is what you are looking for:

GridProtectionAlliance/openPDC/blob/master/Source/Documentation/wiki/Connection_Strings.md#mysqladaptersmysqlinputadapter

[Chen0917]

Hi Marco,

That is very helpful! Thank you very much!

I am trying to build a MySQL script now. I was wondering if there is any existing and available server that we could use or we should use our own computer as the localhost server.

Thanks,
Chen

[marco.gonzalez]

Hi Chen.

I think it's easiest to setup your own local MySQL server to do tests. It's much faster to do debugging and trial and error programming.

Good luck!

[Chen0917]

Hi Marco,

I got it. I will try to setup a local server.

Thanks a lot!

Best,
Chen

[Chen0917]

Hi all,

Here is the update.

I have been trying to integrate my own data into the openECA platform these days. The total number of the measurements is 192 and the total number of the timestamps is 1800.

The method I used is:

• Step 1: manually create the measurements in Metadata(as shown in the following figure).
  

• Step 2: generate CSV Inputs adapters in Adapters(as shown in the following figure).
  

Each adapter will deal with 20 measurements. The connection strings I used is as following:

Filename=C:\Program Files\openECA\Server\line_measured_data_chunk_5.csv;
AutoRepeat=True; 
SimulateTimestamp=True; 
TransverseMode=True; 
ColumnMappings={0 = Timestamp; 1 = PPA:154; 2 = PPA:155; 3 = PPA:156; 4 = PPA:157; 5 = PPA:158; 6 = PPA:159; 7 = PPA:160; 8 = PPA:161; 9 = PPA:162; 10 = PPA:163; 11 = PPA:164; 12 = PPA:165; 13 = PPA:166; 14 = PPA:167; 15 = PPA:168; 16 = PPA:169;17 = PPA:170; 18 = PPA:171; 19 = PPA:172; 20 = PPA:173;};

We have found a restriction of the number of the measurements each CSV adapter can connect which is 20.

• Step 3: the integration results also show a restriction of the number of the measurements that can be adapted in total is 80, because the 81st measurement cannot be shown in the Monitoring -> Trend Real-time measurements(as shown in the following figure).
  

Therefore, we have some questions:

• 1) Are the two restrictions of the number of measurements aforementioned valid? Or they were created due to our misunderstanding or incorrect implementation.

• 2) If one measurement does not show up in the Monitoring -> Trend Real-time measurements window, does that mean that the openECA has not adapted it or it has been adapted but just cannot be shown in the monitor?

• 3) If the restrictions are valid, is there any possibility that in future versions of the openECA, such restrictions could be increased to some larger limits?

We will really appreciate it if you could give us some instructions on such problems.

Thanks,
Chen

[StephenCWills]

Hi Chen,

Although there may be some upper limits in practice, we don't impose any arbitrary restrictions on the number of columns in the CSV file or the number of measurements per device. Personally, I just set up a (completely contrived) test with a single CSV adapter bringing in data from 100 different signals and it seemed to have worked just fine.

Connection String:

Filename=C:\Users\swills\Desktop\ECATestData.csv;
AutoRepeat=True;
SimulateTimestamp=True;
TransverseMode=True;
ColumnMappings={0 = Timestamp;1 = PPA:1;2 = PPA:2;3 = PPA:3;4 = PPA:4;5 = PPA:5;6 = PPA:6;7 = PPA:7;8 = PPA:8;9 = PPA:9;10 = PPA:10;11 = PPA:11;12 = PPA:12;13 = PPA:13;14 = PPA:14;15 = PPA:15;16 = PPA:16;17 = PPA:17;18 = PPA:18;19 = PPA:19;20 = PPA:20;21 = PPA:21;22 = PPA:22;23 = PPA:23;24 = PPA:24;25 = PPA:25;26 = PPA:26;27 = PPA:27;28 = PPA:28;29 = PPA:29;30 = PPA:30;31 = PPA:31;32 = PPA:32;33 = PPA:33;34 = PPA:34;35 = PPA:35;36 = PPA:36;37 = PPA:37;38 = PPA:38;39 = PPA:39;40 = PPA:40;41 = PPA:41;42 = PPA:42;43 = PPA:43;44 = PPA:44;45 = PPA:45;46 = PPA:46;47 = PPA:47;48 = PPA:48;49 = PPA:49;50 = PPA:50;51 = PPA:51;52 = PPA:52;53 = PPA:53;54 = PPA:54;55 = PPA:55;56 = PPA:56;57 = PPA:57;58 = PPA:58;59 = PPA:59;60 = PPA:60;61 = PPA:61;62 = PPA:62;63 = PPA:63;64 = PPA:64;65 = PPA:65;66 = PPA:66;67 = PPA:67;68 = PPA:68;69 = PPA:69;70 = PPA:70;71 = PPA:71;72 = PPA:72;73 = PPA:73;74 = PPA:74;75 = PPA:75;76 = PPA:76;77 = PPA:77;78 = PPA:78;79 = PPA:79;80 = PPA:80;81 = PPA:81;82 = PPA:82;83 = PPA:83;84 = PPA:84;85 = PPA:85;86 = PPA:86;87 = PPA:87;88 = PPA:88;89 = PPA:89;90 = PPA:90;91 = PPA:91;92 = PPA:92;93 = PPA:93;94 = PPA:94;95 = PPA:95;96 = PPA:96;97 = PPA:97;98 = PPA:98;99 = PPA:99;100 = PPA:100}

Test Data:
http://www.gridprotectionalliance.org/NightlyBuilds/Temp/ECATestData.csv

Thanks,
Stephen

[Chen0917]

Hi Stephen,

Thanks a lot for the reply!

I have tried the MySQL configuration and finally made it work. All of the 192 measurements now can be shown in the Monitor window as shown in the following figure.

It seems that the reason is I didn’t set the column header of each measurement in the CSV file. So I am guessing that openECA won’t recognize the measurements if the first entry of the column is a numerical value.

But it finally works. Thank you very much! I really appreciate it!

Best,
Chen