12 Replies Latest reply on Oct 5, 2011 12:05 PM by achalesh

    PI and Matlab connection

    achalesh

      I am first time OSI-PI user and trying to pull archived data from OSI-PI server to Matlab environment for further analysis. I am using PI OLEDB Provider with MATLAB to pull process data for calculations into MATLAB. I followed white paper and Matlab code provided in VCampus library . I am able to connect to PI server through Matlab and pull the data but at present I am able to extract data for only one tag at at time for user defined time stamp . I can use for loop in Matlab and reapeat this process but it may not be efficient way to pull the data.

       

       Is there anyway to pull the data for user defined time duration and user defined mutiple tags in Matlab environment?

       

       

       

       

        • Re: PI and Matlab connection
          hanyong

          Hi Achalesh,

           

          It is possible to have a PI OLEDB SQL query that gets data for multiple tags. It is just a matter or how the query is written. One example, to get today's archive data for the tags CDT158 and SINUSOID, we can write something like:

           

          SELECT tag, time, value FROM piarchive..picomp2 WHERE tag IN ('cdt158', 'sinusoid') AND time > 't'

           

          the output from this query will be something like: 

           

          7801.SQLqueries.jpg

           

          Note: PI OLEDB still uses multiple PI SDK calls in the background to get values for each tag, then combine the results together. 

           

          This would be an example. If you have more questions about the queries that you can use for PI OLEDB Provider. You can post them in PI SQL Data Access - OLEDB/JDBC forum.

            • Re: PI and Matlab connection
              achalesh

              Hi Han,

               

              Thanks for your suggestion. It solved my problem but I have one more question. I want the data table in following form

               

              time                               cdt158        sinusoid

               

              09/29/2011 09:00          56                  2.7

               

              09/29/2011 09:01          51                  3.4

               

              09/29/2011 09:02          46                  6.2

               

              What SQL query will get me data in above form.

               

              Thanks

               

              Achalesh

                • Re: PI and Matlab connection
                  hanyong

                  For PI OLEDB Provider to return result in this format, you can join the picomp2 or piinterp2 tables multiple times. You can refer to this previous post where I posted a sample query that does this.

                    • Re: PI and Matlab connection

                      @Achalesh: to add up to Han Yong's suggestion, I would like to invite to look into PI OLEDB Enterprise (which is centered around PI Asset Framework) rather than the classic PI OLEDB Provider (which can only go the PI Data Archive).

                       

                      As you can see further down in the discussion thread that Han Yong referred to, we implemented transpose functions/tables in PI OLEDB Enterprise to do exactly what you want, without the need of a more complex query. In the classic PI OLEDB Provider, you do need that type of query Han Yong suggested.

                        • Re: PI and Matlab connection
                          achalesh

                          Steve,

                           

                          I am currently using Matlab files provided in Vcampus library. Will those files work with OLEDB enterprise. I am not sure what changes I need to make in those Matlab files to use PI OLEDB Enterprise.

                            • Re: PI and Matlab connection
                              hanyong

                              The sample provided with the white paper on vCampus library generally illustrates connecting from Matlab to PI Server using PI OLEDB Provider and performing various tasks like querying archive data or current data and performing tag search etc.

                               

                              You can modify the connection string to use PI OLEDB Enterprise to connect to the AF Server. For example, a connection string using PI OLEDB Provider is:

                              Provider=PIOLEDB.1;Data Source=<your PI Server>;Integrated Security=SSPI

                              To connect to AF server using PI OLEDB Enterprise, the connection string can be modified to:

                              Provider=PIOLEDBEnt.1;Data Source=<your AF Server>;Initial Catelog=<your AF database>;Integrated Security=SSPI

                              Of cause the way you would query the AF Server through PI OLEDB Enterprise will be different from querying the PI Server. I would recommend that you first install PI OLEDB Enterprise and try querying the AF Server using PI SQL Commander (a client tool that is installed together with PI OLEDB Enterprise). The PI OLEDB Enterprise user guide available on the Library should be helpful to you too.

                               

                              Another good alternative that you can also consider is to work through the Using PI OLEDB Enterprise 2010 training, in the Library section, under Training - Documentation as well.  

                                • Re: PI and Matlab connection

                                  @Achalesh: as Han Yong's answer seems to indicate, the classic PI OLEDB Provider and PI OLEDB Enterprise are fairly different products (although they both use the OLE DB standard to communicate). The classic PI OLEDB Provider represents the classic way of working with PI Systems - that is, it talks to the PI Data Archive in a tag-based manner. As you can see in the user manual, the table structure it proposes focuses on PI Points and their data.

                                   

                                  PI OLEDB Enterprise embraces today's concepts and methods: access to the PI System in an asset-based fashion. Specifically, it communicates to the PI Asset Framework (where you typically organize everything) and rely on AF Elements and their Attributes to access data from the PI Data Archive. In other words, your choice between the 2 options will depend on how you prefer to access data in the PI System and whether you have an Asset Structure created already.

                                   

                                  I strongly encourage you to look into some of the documentation Han Yong alluded to, as well as the nice series of tutorials that come installed with PI OLEDB Enterprise (in the PI SQL Commander utility). Also, please do not hesitate to post in the SQL Data Access forum if you need help or additional pointers on how to make the best out of PI OLEDB Enterprise.

                            • Re: PI and Matlab connection
                              achalesh

                              Han,

                               

                              I tried to run suggested query through Matlab using PI OLEDB connection but it's not working. I think this query was suggested for SQL server use. I can't use PIOLEDB Enterprise because I am not using asset framework. My aim is to read data from Piarchive file for some analysis purpose. I am not using any production system.

                               

                              Here is the query which I tried based upon your suggestion.

                               

                              Select time, sinusoidu, sinusoid from (Select tag, time, CONVERT(float, value) as 'tagval' from PI.piarchive..piinterp2 where tag like 'sin*' and time between '28-Jun-2010' and '1-jul-2010') as stbl PIVOT ( AVG(tagval) for tag in (sinusoidu, sinusoid)) as pvtorder by time

                                • Re: PI and Matlab connection
                                  Ahmad Fattahi

                                  This is a fairly involved query you are making. I would suggest you break down the problem into smaller pieces by starting from a very simple query (similar to the one Han Yong posted above to get values of tag cdt158). Would a simple query like this work? If not, would it work on PI OLEDB Provider? Do you see any errors on the client or in the PI SDK logs?

                                  • Re: PI and Matlab connection
                                    kilgored

                                    Achalesh,

                                     

                                    I interpreted your query as needing to return 4 columns, two per tag (sinusoid and sinusoidu) - one being the interpolated value and the other being the average. While there are many ways to construct such a query - below is an example that does work. You can add/remove other INNER JOINS and their related column select's as needed to change the number of columns or where their data is gathered from.

                                     
                                    SELECT sinusoidu.time, sinusoidu.value AS sinusoidu_interp, sinuavg.value AS sinusoidu_average, sinusoid.value AS sinusoid_interp, sinavg.value AS sinusoid_average
                                    FROM piinterp2 sinusoidu 
                                    INNER JOIN piinterp2 sinusoid ON sinusoid.tag='sinusoid' AND sinusoid.time = sinusoidu.time
                                    INNER JOIN piavg sinavg ON sinavg.tag='sinusoid' AND sinavg.time=sinusoidu.time
                                    INNER JOIN piavg sinuavg ON sinuavg.tag='sinusoidu' AND sinuavg.time=sinusoidu.time
                                    WHERE sinusoidu.tag ='sinusoidu'
                                         AND sinusoidu.time BETWEEN 't+8h' AND '*' 
                                         AND sinusoidu.timestep='+1h'
                                    OPTION(FORCE ORDER)
                                    

                                     

                                      • Re: PI and Matlab connection
                                        hanyong

                                        Nice post Dennis!

                                         

                                        @Achalesh: The query that you have posted doesn't work if you run the query directly through PI OLEDB Provider because PIVOT is not a supported operator in PI OLEDB Provider.

                                         

                                        I believe you have gotten a sample of this query from the other discussion thread that I've referred you to. As mentioned in the post, for the query to work PI Server should be setup as a Linked Server in Microsoft SQL Server. And you have to run the query through SQL Server instead of querying PI Server directly.

                                         

                                        Hence I've pointed you to the specific post on the thread which illustrate how you can use INNER JOINs to return result in the format that you want. Basically Dennis' example above also illustrates the same idea.

                                          • Re: PI and Matlab connection
                                            achalesh

                                            Han,

                                             

                                            I have got some help from technical support team and I am able to execute following query from Matlab environment. Thanks for your suggestions. here is the query which was suggested to me.

                                             

                                            SELECT i1.time, i1.value AS "CDT158", i2.value AS "Sinusoid" FROM piarchive..piinterp2 AS i1, piarchive..piinterp2 AS i2  WHERE i1.tag = 'cdt158' AND i1.time BETWEEN 'y' AND 't' AND i1.timestep = '1h' AND  i2.tag = 'sinusoid' AND i2.time=i1.time