I'm looking for an option to write data via AF to an Table, very much like the PI DR write option. Did some build a DR like the table DR with a data write option? Looking forward hearing youyr thoughts on this subject.
Can you describe more details regarding your use case? Where is the source data from (i.e. PI Server, AF Server, PI OLEDB Enterprise)? Does the destination SQL table belong to a third-party database and what is the business logic like?
I ask because there may already be solutions out there to accomplish the goal. For example, if you'd like to write AF data (Elements, Attributes, etc) to a SQL table to integrate with other SQL products, then we already have PI OLEDB Enterprise with exposes the AF data as a set of SQL views that can be accessed via an OLEDB consumer. So in this case, the data is already being "written" to SQL for you.
However, your use case may be more specific, so if you can provide more details, we may be able to come up with a more tailored solution.
sorry, for th elate reply, but here it is:
We like to use the write to SQL via AF voor a web basedapplication. This application reads PI data. This data is validated by anOperator. The validated and possible corrected numbers are then send to aproduction database. The customer like to have the AF database as the master ofthe configuration. Thus, adding, modifying and removing elements is the onlydesired system configuration. Therefore we like to have a write though anelement, just like the PI DR. WE likethe data write function of the PI DR and the PI tag create. We like to havesomething similar for and SQL DR.
It seems like a custom data reference might work for you. You basically would overload our Setvalue method and when this is called, the appropriate value will be inserted into SQL instead of PI.
As Dan mentioned, a custom Data Reference is an option. You can find tutorials and examples in the Tech Support download site. The examples here may be of interest.
You can override the SetValue, UpdateValue, or UpdateValues method of the data reference. Keep in mind you will need .NET 4.0+ and at least AF SDK 2.5 to access these methods (Rich Data Access).
The custom data reference is simply the implementation of the update methods. It will be up to the client (validation) application to retrieve the configuration information from AF, and write to SQL database via calls to the DataReference.UpdateValue.
Keep in mind a potential difficulty mapping your AF structure to a relational database. The former is more object-oriented while the latter is set-oriented. There will be some issues to think about regarding the proper way to map attributes/elements to SQL tables/rows. How is SQL affected if AF elements/attributes are added, edited, deleted?
There is also consideration regarding syncing PI data to SQL, such as handling out of order data, late arriving data, etc. and how SQL will be notified.
The custom DR will allow you to achieve these objectives but please keep in mind the above considerations.
Another option is to still do this through a PI DR. The PI DR can then write to SQL if you have the tag configured to write as an output tag in an instance of the PI RDBMS interface. This approach may not be as customized as you are looking for, but it's another option for a simple way to write back to SQL from AF without the need for custom code, and if you already have access to the RDBMS interface.
I'd go for Gavin's option.
A bit off-topic:
In similar cases (data validation for utilities) we have been able to use PI or AF to support the data validation process. E.g. in a combination of EventFrames can both deliver information on the status of validating out-of-bounds conditions, but also track the "validated up to this time" timestamp by continuously extending the endtime of an EventFrame indicating the time period for which the data is validated.
Retrieving data ...