This project has moved. For the latest updates, please go here.

Inner Joins in Filter Syntax for InputMeasurementKeys and OutputMeasurements

May 22, 2013 at 5:47 PM
I have several custom adapters that take long series of InputMeasurementKeys (over 100 keys in some cases), with related series of OutputMeasurements. The two sets of measurements have a strong relationship via the SignalReference. My Question: instead of an extremely long text in the ConnectionString, which is difficult to maintain as new signals are added, could I use an Inner Join in the Filter syntax, as in:

InputMeasurementKeys={FILTER ActiveMeasurements
May 22, 2013 at 5:53 PM
I have several custom adapters that take long series of InputMeasurementKeys (over 100 keys in some cases), with related series of OutputMeasurements. The two sets of measurements have a strong relationship via the SignalReference. My Question: instead of an extremely long text in the ConnectionString, which is difficult to maintain as new signals are added, could I use an Inner Join in the Filter syntax, as in:

InputMeasurementKeys={FILTER ActiveMeasurements am
                                                INNER JOIN [openPDC15R1].[dbo].[ActiveMeasurement] am2
                                                    ON am2.SignalReference = REPLACE(am.SignalReference, '#MW', '#MWAVG')
                                            WHERE am.SignalType = 'CALC'
                                                AND am2.SignalType = 'CALC'
                                                AND am.SignalReference LIKE '%#MW'
                                           AND am2.SignalReference LIKE '%#MWAVG'
                                            ORDER BY am.SignalID};
OutputMeasurements={FILTER ActiveMeasurements am
                                                INNER JOIN [openPDC15R1].[dbo].[ActiveMeasurement] am2
                                                    ON am2.SignalReference = REPLACE(am.SignalReference, '#MWAVG', '#MW')
                                            WHERE am.SignalType = 'CALC'
                                                AND am2.SignalType = 'CALC'
                                                AND am.SignalReference LIKE '%#MWAVG'
                                           AND am2.SignalReference LIKE '%#MW'
                                            ORDER BY am.SignalID};
what I hope to get are two lists that include only measurements where both MW and MWAVG exist, and sorted the same way. That is, so that the correct input measurement matches the correct output measurement.
Coordinator
May 22, 2013 at 6:06 PM
You can't do joins in filter expressions - however, signal reference is already available in the active measurements.
May 22, 2013 at 6:07 PM
Perhaps more readable...
InputMeasurementKeys={FILTER ActiveMeasurement am
                          INNER JOIN ActiveMeasurement am2
                              ON am2.SignalReference = am.SignalReference + '#VPASLOPEAVG'
                          WHERE am.SignalType = 'VPHA'
                            AND am2.SignalType = 'CALC'
                          ORDER BY am.SignalID};
OutputMeasurements={FILTER ActiveMeasurement am
                       INNER JOIN ActiveMeasurement am2
                           ON am.SignalReference = am2.SignalReference + '#VPASLOPEAVG'
                       WHERE am.SignalType = 'CALC'
                         AND am2.SignalType = 'VPHA'
                       ORDER BY am.SignalID};
May 22, 2013 at 6:40 PM
Thanks for the quick response. Perhaps you could add 'add joins to filter expressions' to some wish-list. It would simplify some ConnectionStrings, and avoid online edits of the same.
Coordinator
May 24, 2013 at 3:22 PM
I may not totally understand your naming convention and the relationship of measurement records, but wouldn't something like this work:
InputMeasurementKeys={FILTER ActiveMeasurements WHERE SignalReference LIKE '*#VPASLOPEAVG' AND SignalType = 'CALC'}
Are you trying to restrict measurements where the associated source is just a voltage phase angle? Does that mean you have measurements suffixed with #VPASLOPEAVG that are not associated with voltage phase angles?

Thanks,
Ritchie
May 24, 2013 at 3:35 PM
What I was describing were sets of InputMeasurementKeys/Output Measurements. For instance, the AverageFrequency adapter, like my averaging adapters, has two very long lists of measurements that must match by position. What I'd like are filter expressions that can be assured to return ordered lists of the correct measurements, such as normal FREQ measurements as input, and measurements with SignalReferences created from the source FREQ measurement SignalReference concatenated with a special tag (#FRQAVG).

Your filter example described what would be an OutputMeasurement query, but unsorted. I need two queries (filters) that return one or the other element from matched pairs, sorted identically.

This would provide a neater ConnectionString, which isn't the important property; now when a new frequency signal is added, and an associated FRQAVG measurement created, the next initialization of the adapter would pick it up. Currently I either have to 'edit' the ConnectionString in such cases (ug!), or drop the adapter and regenerate it - which is what I am currently doing. Since I created these adapters programatically, and automatically, this isn't too bad.


May 24, 2013 at 3:40 PM
One point; even with the AverageFrequency adapter, I am creating these programatically, and not using the provided display. In any case, there is no such display for my similar adapters (average unwrapped voltage phase angle, average voltage phase angle slope, average MW, and average MVAR). We cannot depend on some engineer to maintain these lists manually, even if displays exist.
Coordinator
May 24, 2013 at 3:41 PM
So you want to have a filter expression that allows you to select the ordered "inputs" to a calculation and its "outputs" based on their signal reference relationship - which would, in normal SQL parlance, require a join or sub-query... Got it.
May 24, 2013 at 3:43 PM
Exactly! Or with any other deterministic relationship between pairs of measurements.
Coordinator
May 24, 2013 at 5:37 PM
OK - I am adding a "SELECT" based option to the input and output definitions. This is different from FILTER expressions or manual point specification in that it actually opens a database connection to get needed point definitions instead of using cached metadata.

This creates an important caveat that the database be available when the openPDC initializes these adapters. Traditionally the openPDC can be restarted successfully with or without database access by using the last known good configuration, however using adapters configured with a "SELECT" statement when the database is unavailable will fail to initialize. If you accept this caveat and use this feature, your expressions could look like:
InputMeasurementKeys={SELECT am.ID, am.SignalID FROM ActiveMeasurement am
                          INNER JOIN ActiveMeasurement am2 ON am2.SignalReference = am.SignalReference + '#VPASLOPEAVG'
                          WHERE am.SignalType = 'VPHA' AND am2.SignalType = 'CALC'
                          ORDER BY am.SignalID};
OutputMeasurements={SELECT am.ID, am.SignalID, am.PointTag, am.Adder, am.Multipler FROM ActiveMeasurement am
                       INNER JOIN ActiveMeasurement am2 ON am.SignalReference = am2.SignalReference + '#VPASLOPEAVG'
                       WHERE am.SignalType = 'CALC' AND am2.SignalType = 'VPHA'
                       ORDER BY am.SignalID};
FYI, in this example the ordering by SignalID may not be desired since this is a random Guid - if you are targeting original creation order you may want to sort by ID instead.

Note the select fields specified are the minimum for input measurement keys and output measurements. Output measurements has more required fields since it allows for dynamic linear adjustment via adder and multiplier. These would have already been applied to inputs so you only need the keys there.

I'll send you a note when the roll down is complete for this item.

Thanks,
Ritchie
May 24, 2013 at 8:56 PM
Thanks very much!!! I have only one problem with this: I assume this will require a new release, in that this isn't just a modified DynamicCalculator, but a change in the way ConnectionStrings are parsed. We recently took what was supposed to be Alstom's final release of openPDC 1.5, as far as ISO-NE is concerned. However, already we know we will need updated versions of PIAdapters and DynamicCalculator. It is getting complicated due to timing (almost at the end of DOE). I will let my managers know of this change, and perhaps a miracle will occur.

In any case, in the future this will be a nice addition.
May 28, 2013 at 8:17 PM
One possible (perhaps) improvement is to use one query to return both streams. This can be done since both queries in your example actually return all of the needed data for both streams. Using a new tag Measurements:
Measurements={SELECT am.ID, am.SignalID, am.PointTag, am.Adder, am.Multiplier,
                                    am2.ID, am2.SignalID, am2.PointTag, am2.Adder, am2.Multiplier 
                            FROM ActiveMeasurement am
                                INNER JOIN ActiveMeasurement am2 
                                    ON am2.SignalReference = am.SignalReference + '#VPASLOPEAVG'
                             WHERE am.SignalType = 'VPHA' AND am2.SignalType = 'CALC'};
the order by is not needed in this case. The first set of returns values are for the input, the second set for the output.
May 30, 2013 at 8:03 PM
Another approach (more work on your part, less on mine) would retain the previous format of the FILTER syntax, but use a template for the output measurements.
This would allow addition and removal of inputs, and always resulting in matching outputs. This is essentially what my quality-control program does, but with much more difficulty.

The adapter would use the template to both find the matching outputs, if they exist, or to create them if not. For instance, the first example would result in output measurements matching the input measurements by concatenating '#FRQAVG' to the PointTag of the input. The additional lines fill in the measurement. The second example would result in an output measurement by taking the input's SignalReference, replaceing '#MW' with '#MWAVG'. This one also defines the Multiplier as SQRT(3).
OutputMeasurementTemplage={Output.PointTag=Input.SignalReference + "#FRQAVG";
                           Output.SignalReference=Output.PointTag;
                           Output.Description="One-Second Average Frequency for %Input.PointTag";
                           Output.DeviceID=Input.DeviceID;
                           Output.HistorianID=Input.HistorianID;
                           Output.SignalType=CALC;
                           Output.Adder=0.0;
                           Output.Multiplier=1.0};
                           
OutputMeasurementTemplage={Output.PointTag=REPLACE(Input.SignalReference, "#MW", "#MWAVG");
                           Output.SignalReference=Output.PointTag;
                           Output.DeviceID=Input.DeviceID;
                           Output.HistorianID=Input.HistorianID;
                           Output.SignalType=CALC;
                           Output.Adder=0.0;
                           Output.Multiplier=SQRT(3)};