20 Replies Latest reply on Feb 27, 2014 4:23 PM by mjarvis

    Synchronization between Oracle database and PI AF

    nunoaguiar

      Hi everyone, 

       

      My development team is facing an architectural decision and we would like your help. We must implement synchronization between an Oracle database and PI AF.

       

      On the Oracle side we have a defined schema that holds information about the assets of the company in a hierarchical way. (This is fed by several sources including SAP.)

       

      This is pretty much the type of information we want to have in PI AF, of course. We want to export all this information (stored in our own data model) from Oracle into PI AF, and then keep it synchronized over time.

       

      So, as far I know, the only way to do that is to implement a routine (whether in a service or a scheduled/triggered task) that understands our data model and uses PI AF SDK to write into the PI AF. Is this really the only way to go?

       

      We are wondering if there is any other easier way. Is there any other OSIsoft product that we can use to accomplish this task?

       

      At first we thought about PI OLEDB Enterprise, but then right in the beginning of the manual we saw it has read-only access to the PI AF. We also thought about editing the PIFD database directly, but once it is not documented (as far as I know), I believe this is not a recommended approach. Am I wrong?

       

      Regards, 

       

      Felipe Aburaya (on behalf of Nuno Aguiar)

        • Re: Synchronization between Oracle database and PI AF
          pcombellick

          Felipe,

           

          You are correct that the PIFD schema is undocumented, as it changes with each AF release.  Writing a program that uses the AF-SDK is the recommended technique to create assets (AFElements) in AF. Or, you could import an xml document via AFImport  If you have property values stored in Oracle that you would like to synchronize, consider using a linked AFTable, which can query Oracle (or other database).

           

          Regards,

           

          Paul

           

          AF Dev

            • Re: Synchronization between Oracle database and PI AF
              nunoaguiar

              Hi Paul.

               

              I am struggling to find proper documentation that explains AF Import and linked AF Tables. (Could anyone provide me the links for such docs?)

               

              I understood from you answer that a linked table can be used to sync property values for already created elements, but the elements themselves must be first created via PI AF SDK or a XML import. Did I get it right?

               

              Felipe

                • Re: Synchronization between Oracle database and PI AF
                  pcombellick

                  A "linked" AFTable is a technique to assign AFAttribute values via a query to an external database.  You are correct in observing that this is not helpful for creating AFElements.

                   

                  AFImport.exe is described in PI System Explorer 2012 User Guide.  AFTable is documented in the AFSDK.chm file.

                   

                   

                   

                  Regards,

                   

                  Paul

                   

                   

                    • Re: Synchronization between Oracle database and PI AF
                      mjarvis

                      Roger,

                       

                      I would like to know how you are managing different environments for Development / Test / Acceptance/ Production. What tools do you currently use? How often do you need to make changes?

                       

                      Regards,

                       

                      Mike Jarvis

                       

                      OSIsoft Product Manager

                        • Re: Synchronization between Oracle database and PI AF
                          Roger Palmen

                          Hi Mike,

                           

                          I've been involved in two different projects that had these requirements to some degree. One completed project, and one still under design (not by me, but i keep my ears open). Imho, this is the key problem of Enterprise Asset Data Management, and having a solid governance structure for Asset data throughout the organisation is key.

                           

                          DTAP:

                           

                          One can argue a lot about the necessity of a DTAP environment, but if PI is a smaller part of the entire solution this is more common. Management was by developing a release on Dev, and then prepare either a full release (start from scratch) or an incremental release (based on previous release) and push that through to Test, Accept and Prod.

                           

                          Tools (looking at AF and PI parts only):

                          • In the completed project, we had Excel as the source of Asset information, which pulled data together from a number of source systems. Then using VBA, we would use the AFSDK to build the Asset Model from scratch (no incremental updates). And the PI tags.
                          • In the project under design, the idea was to use a bespoke application (probably a .NET service, but could use other platforms too like SQLserver) to pull the data from the source systems, do a comparison between source and AF, and from that build a Create/Update/Delete list. Creates would be done automatically, where as Updates and Deletes would be reported for manual processing.

                          I do however doubt the feasibility of the latter solution. In my opinion, a key requirement for this to work is that the identification of Assets in the source systems and AF, and the subsequent logic to determine what to create / update / delete must be rock solid. The difficulty is that the source systems often do not provide sufficient information to make a good decision. A simple example would be the rename of an Asset. Would that lead to a Create or an Update of the Asset in AF? What are the required implications for data history? To make that decision, more information is needed to feed the business logic.

                           

                           

                           

                          Frequency:

                          • In the completed project, changes were done only through releases. Thus frequency would be e.g. monthly. This matches the feedback cycle the solution was looking at (weekly plans).
                          • In the project under design, changes should be done daily. This was more operational, where changes from day to day should be kept in sync as the solution was looking at hourly feedback cycles.
                          It's a tough problem, for which there is no one-size fits all. Best practices should help here.
                  • Re: Synchronization between Oracle database and PI AF

                    Is Oracle the backend database for an Asset Management System such as IBM-Maximo or SAP-PM or something else?

                      • Re: Synchronization between Oracle database and PI AF
                        nunoaguiar

                        Hi, Gopal.

                         

                        Yes, the Oracle is the backed DB for SAP-PM.

                         

                        Felipe

                          • Re: Synchronization between Oracle database and PI AF

                            What industry is this for? And what all asset meta data (other than Flocs and Equipment names) are you planning to bring to AF?

                             

                            What other mechanisms do you have in place to read the SAP Flocs and Equipments?

                              • Re: Synchronization between Oracle database and PI AF

                                How many assets will you have? And, how many AF Elements?

                                  • Re: Synchronization between Oracle database and PI AF
                                    nunoaguiar

                                    I believe we have something around a thousand AF elements. (rough estimation)

                                      • Re: Synchronization between Oracle database and PI AF

                                        Please look at the linked AF table feature - I'm assuming that you have about 10,000 records in Oracle that sync. once a day w/AF table.

                                          • Re: Synchronization between Oracle database and PI AF
                                            nunoaguiar

                                            "I understood from you answer that a linked table can be used to sync property values for already created elements, but the elements themselves must be first created via PI AF SDK or a XML import. Did I get it right?"

                                              • Re: Synchronization between Oracle database and PI AF

                                                You can also look at AF Builder (Excel add-in) to bulk load AF Elements and Attributes initially.   Add/Change/Move of Equipments after the initial bulk load can be a challenge - how are you getting that in the first place from SAP and other sources to Oracle?

                                                  • Re: Synchronization between Oracle database and PI AF
                                                    nunoaguiar

                                                    The data gets there in several ways. Particularly from SAP, it comes through a web service for integration.

                                                     

                                                    But the core of our platform is the Oracle DB. Not SAP, not the PI System. That's why Oracle must be the place where PI AF must get the updates from.

                                                     

                                                    Please confirm if is possible to create/edit elements in AF using the linked tables feature.

                                                     

                                                    Regards,

                                                     

                                                    Felipe

                                                      • Re: Synchronization between Oracle database and PI AF
                                                        skwan

                                                        Nuno:

                                                         

                                                        With out-of-the-box software, you would first create the AF objects, then "connect" the AF objects to your Oracle data.  As mentioned earlier, one way of linking them would be to use AFTables.  As to how you would create the AF objects, you have several options:

                                                         

                                                        1) Use PI System Explorer software, which is provided with AF

                                                         

                                                        2) Use AF Builder (soon to be renamed to PI Builder), which is provided with AF and is an Excel Addin

                                                         

                                                        3) Programmatically using the AF SDK

                                                         

                                                        4) XML transform from the source to AF using the provided XML Schema.  This would require some development work.  Once this transform is done, you can import it into AF.

                                                         

                                                        5) CSV import, in a way, similar to XML import and AF Builder.

                                                         

                                                        Unfortunately we do not have a way to automatically create AF objects based on your Oracle source at the moment.

                                                          • Re: Synchronization between Oracle database and PI AF
                                                            Roger Palmen

                                                            Hi Steve,

                                                             

                                                            Interesting option #4. Is that more-or-less officially supported/ recommended?

                                                             

                                                            Ofcourse we do have the OSIsoft.AF.xsd file that describes the file format of the import / exported XML schema, but although i'm not an XML expert at all, it does not seem to support versioning. So what will happen with the XSD in future releases of AF?

                                                             

                                                            Also the CRUD actions that are the result of this XML during import are still a bit unclear to me. Well, to that extent that i do agree this can be used for an initial AF model build, but for an automated AF model synchronisation with an external system, that's another thing. (worth a separate post...)

                                                              • Re: Synchronization between Oracle database and PI AF
                                                                Asle Frantzen

                                                                We're doing a lot of the same things with one of our clients, and while the end-goal is fully automating the element creation, we have created a semi-automatic process today.

                                                                 

                                                                We started by creating a pair of "to-be-created" and "to-be-deleted" queries for each asset type. These are put into SQL Server Reporting Services reports, which we subscribe to by email. Once a week we get the report delivered, and get an idea of the amount of work to be done (if any).

                                                                 

                                                                Then we have an Excel file which use the same queries to populate rows of elements to be created or deleted. After updating the SQL query all the other necessary columns (needed for AF Builder) are populated, and we can create/delete everything in one operation.

                                                                 

                                                                The third and last step is a new Excel file which contains a lot of logic about all the attributes needed for each for each asset type. This one is a bit more work, since you have to do this for each element created. You have to fill out the necessary identification, and then you get suggestions for tag names for those attributes we don't have a 100% proof way of identifying. This would of course be very easy to do if the orgainzation had one unique naming convention for their assets, but usually you'll have 2-3 to pick from. But at least we put in all the domain knowledge from the equipment specialists in this Excel sheet (using VBA and formula's), so that people without that knowledge still can do the job.

                                                                 

                                                                We aim for a fully automated process, but at least for the attributes we still have a way to go. The solution would probably include renaming tags which don't fit into the main naming convention, and that's a bit more work than we have time for now. But in the long run, our client sees this happening. (And it'll be possible because AF is the main way to connect to their data. Nobody uses tag names directly, which means we can change them as long as we keep the AF attributes updated).

                                                                  • Re: Synchronization between Oracle database and PI AF
                                                                    Roger Palmen

                                                                    Asle,

                                                                     

                                                                    Same experiences here using Excel for building the entire AFstructure.

                                                                     

                                                                    Creating new Elements is the easy part (although defining 'new' can be a challenge), but Updated and Deletes are always done by an engineer who can estimate the impact. Moving assets around in the hierarchy is quite tricky from a functionality point of view.

                                                                     

                                                                    Add Development / Test / Acceptance / Production environments into the mix, and this tooling becomes closely linked to your change and configuration management... and due to the complexity it keeps our jobs safe! :-)

                                                  • Re: Synchronization between Oracle database and PI AF
                                                    nunoaguiar

                                                    Hi Gopal.

                                                     

                                                    Before we take this path I need to say the backend DB is Oracle, but that is another instance we do not have access to.

                                                     

                                                    All the data must be put together in our Oracle instance before going anywhere, and that does not come only from SAP.

                                                     

                                                    How we get data from SAP is part of the solution we cannot change. Our interest is specifically the integration between Oracle and AF.

                                                     

                                                    The industry is power generation.

                                                     

                                                    Felipe