9 Replies Latest reply on Jan 20, 2014 9:22 AM by AlistairFrith

    Replacing Oracle DB with PI+AF - Migrating existing connections

    AlistairFrith

      Hi, we are starting a project to replace a somewhat wasteful and inefficient Oracle system with PI+AF and one thing we need to do is update the systems that currently get their data from Oracle to get it from PI/AF instead.

       

      They have one table in the Oracle DB which contains everything the client systems need:

      • Tag Name / ID
      • Extended Descriptor + various other PIPoint attributes 
      • Timestamp
      • Value
      • Status
      • Various other fields
      • A 'Copied' flag

      They have a number of systems that use SQLServer Linked Tables to connect to this Oracle table and have SQL Server Stored Procedures that grab everything they are interested in for all the 'un-copied' records, and then set the records to 'copied'. I can change these stored procedures but they still need to get the same data from the new system.

       

      They have one other system that uses another Oracle database. That database also links to the same table (but I don't know via what protocol) and uses the same general algorthm to get its data. Again, I can change the stored procedure but it must still get the same data.

       

      I am given to understand that AF and PI support SQL Server Linked Tables through OLEDB Enterprise but what about this connection from another Oracle database? Can I do the same thing there?

       

      Also, how would I go about modifying queries like this: 

       
      /* Pull data from Mart_Data_Archive  */
      set @sql = N'INSERT INTO UV.dbo.MART_DATA_ARCHIVE_load
                     ([DM_MARTNAME],
                      [DM_ID],
                      [DM_TAGNAME],
                      [DM_EXDESC],
                      [TIME],
                      [VALUE],
                      [STATUS],
                      [ENGUNITS],
                      [READING_FROZEN],
                      [SOURCE_SYSTEM_ID],
                      [SOURCE_SYSTEM_RANK],
                      [DATE_LOADED],
                      [COPIED_TO_MART],
                      [CHANGE_PENDING])
           SELECT * FROM OPENQUERY(LinkMDAP,
                     ''SELECT DM_MARTNAME,
                     DM_ID,
                     DM_TAGNAME,
                     DM_EXDESC,
                     TIME,
                     VALUE,
                     STATUS,
                     ENGUNITS,
                     READING_FROZEN,
                     SOURCE_SYSTEM_ID,
                     SOURCE_SYSTEM_RANK,
                     DATE_LOADED,
                     COPIED_TO_MART,
                     CHANGE_PENDING
           FROM MDASYS.MART_DATA_ARCHIVE
           WHERE (DM_MARTNAME = ''''UV'''') AND (COPIED_TO_MART = ''''X'''')
             '')'
      

      There are the obvious PI value attributes here: tagname, timestamp, value and status. But there is also the extended descriptor, and a few other values that might be best stored or calculated in AF Attributes. Should I try to build up one query that grabs it all, or several queries that I then build into the final dataset within the stored procedure?

       

      Any advice would be gratefully received!

       

      Alistair

       

       

        • Re: Replacing Oracle DB with PI+AF - Migrating existing connections
          xwang

          Hi Alistair,

           

          As far as I know, the newest Oracle Database version might not support OLEDB, it seems like just support the old ODBC.  If you need to do the data exchange between this Oracle Database and PI, please try PI RDBMS interface.  I believe there should be some changes of your SQL code when using PI RDBMS interface.  Please read the examples in the manual of this interface, you will get the idea on how to change your code.  If you need to link the table in this Oracle to PI AF Server, please create a new link, and try to build a "Microsoft OLEDB Provider for Oracle" link or "Microsoft OLEDB Provider for ODBC Drivers" link.  

           

          Xi Wang

           

          vCampus team

            • Re: Replacing Oracle DB with PI+AF - Migrating existing connections
              Marcos Vainer Loeff

              Hello,

               

              The other option would be to wait for PI ODBC 2014 target for Q2 2014. Below is the description of this product:

               

              "This is a totally revamped version of the PI ODBC Driver which provides performance and feature set equivalent to its OLE DB and JDBC counterparts from the PI Data Access SQL family. That includes access to the PI Asset Framework (AF).The driver will come in both 32 and 64 bit versions."

               

              Hope this helps!!

                • Re: Replacing Oracle DB with PI+AF - Migrating existing connections
                  AlistairFrith

                  Thanks guys,

                   

                  The existing destination DB is running on Oracle 11g if that helps.

                   

                  The phase of the project that needs this connectivity has a firm deadline at the end of April so I don't think waiting for PI ODBC 2014 is an option. Will the RDBMS interface read data from AF? And will it transfer data from PI that comes in out-of-order or that is subsequently adjusted? The users are able to manually insert values into 'corrected' versioins of the SCADA tags and these then adjust a 'final' tag or AF attribute and it is that tag or attribute that needs to be transferred to Oracle.

                   

                  --- Alistair

                    • Re: Replacing Oracle DB with PI+AF - Migrating existing connections
                      Marcos Vainer Loeff

                      Hi Alistair,

                       

                      As RDBMS is Unint based interface, it won't be able to communicate with PI AF Server, but only to the PI Server, sending and receiving values from PI Points. As RDBMS is not a supported interface here in vCampus, I encourage contacting OSIsoft TechSupport in order to check if this interface will satisfy all the requirements of your project.

                        • Re: Replacing Oracle DB with PI+AF - Migrating existing connections
                          AlistairFrith

                          I think that various other requirements will demand access via AF attributes (units of measure conversions being just one such)  so it sounds like, in the absence of PI ODBC 2014 and assuming that Oracle 11g cannot connect via the OLEDB Enterprise provider or any other direct method, we will have to look at intermediate SQL tables. That's a bit horrible and presumably could add a significant performance hit.

                           

                          Alistair

                            • Re: Replacing Oracle DB with PI+AF - Migrating existing connections
                              mhalhead

                              Hi Alistair,

                               

                              What about using something like SSIS (or one of the other ETL packages - I don't really know Oracle) to push the data rather than doing a pull.

                                • Re: Replacing Oracle DB with PI+AF - Migrating existing connections
                                  mhamel

                                  @Alistair: Have you thought of using Oracle Database Extensions for NET? This allows creating .NET stored procedures. I would see a good way to extract data from ORACLE and push it back on PI Data Archive  (PI DA) and PI AF using the AF SDK (2.5 and later). You could create the asset you want on the PI AF side and fill up the time-series data on the PI DA side.

                                   

                                  As Michael pointed out, you can also use SSIS package to pull data out. You would than need to connect to ORACLE database via an ODBC driver as the support to OLEDB connectivity has been dropped starting of version 11g. On the web, you will see several warnings of using ODBC drivers with SSIS 2008 R2 and 2012 package. In general, the major complain is that the data transfer is very slow. Depending of the load of data to transfer this is something you might have to consider.

                                   

                                  As another option I can think of it would be to use a data dump into *.csv file that you could use with a PI RDBMS interface to feed the PI DA. You could use a PowerShell script to create, update and load your AF structure. If you haven't started coding with your PowerShell, you will find that is easy and fast. You stay focus on the task to accomplish.

                                   

                                  To respond to your quote:

                                   

                                  Alistair

                                  I think that various other requirements will demand access via AF attributes (units of measure conversions being just one such)  so it sounds like, in the absence of PI ODBC 2014 and assuming that Oracle 11g cannot connect via the OLEDB Enterprise provider or any other direct method, we will have to look at intermediate SQL tables. That's a bit horrible and presumably could add a significant performance hit.

                                   

                                  IMHO, I wouldn't try this option.

                                   

                                  I hope that this will help you.

                                   

                                   

                                   

                                   

                                    • Re: Replacing Oracle DB with PI+AF - Migrating existing connections
                                      Roger Palmen

                                      Another option could be to pass through SQLserver. On could create a Linked Server in SQLServer to PI AF using th OLEDB Enterprise provider, and then connect to SQLserver from Oracle. That should work using Oracle's heterogenous services. This acts as a 'bridge' between OLEDB (not really supported by Oracle) and ODBC (supported by Oracle).

                                       

                                      Depending on volumes and other technical details this might or might not be an option. This is the kind of territory where one needs to experiment, measure and weigh the pro's and con's.

                                        • Re: Replacing Oracle DB with PI+AF - Migrating existing connections
                                          AlistairFrith

                                          The other developer has done a little research and independently suggested we write some stored procedures in SQL Server to do the queries and then call them from Oracle, which sounds like Roger's suggegstion. I think this might be the first thing to try. Note that the main thing we are doing with this interface is get data out of PI and and AF and into Oracle so things like the RDBMS interface (or any other classic PI interface) are probably not going to be viable.

                                           

                                          Thanks for the suggestions so far.

                                           

                                          Alistair