7 Replies Latest reply on Oct 29, 2013 6:58 AM by Gregor

    Integration to SAS

    Kenneth.Rice

      Has anyone sucessfully integrated PI to SAS? If so, can you share how? please

        • Re: Integration to SAS
          Asle Frantzen

          Can you provide more details about 'SAS' and what you need here?

          • Re: Integration to SAS
            mheere

            Hi Kenneth,

             

            We have a number of customer who are using SAS to analyze PI data, but I can't say that they're all doing it the same way.  What I'd call the "primary" method is to leverage the PI OLEDB provider from within Enterprise Guide.  This can be quirky since EG seems to like to issues "Select Top 1 * from table X" type queries to pull the structure.  PI OLEDB doesn't support this - you need the time constraints on things like the archive tables.  However, with a little creative view construction it works pretty well.

             

            Same concept works just as well if you're writting SAS code directly and not using EG.  Setting up the connections on the SAS server is tougher, but that's a SAS administration issue more than anything.

             

            Often times it turns out to be helpful to put SQL server in between PI and SAS.  You setup PI as a linked server in SQL, the have SAS query SQL to get to PI.  Sounds like an unecessary hop, but it gives you a place to create views and stored procedures with meta-data that SAS understands clearly.  Whatever magic you need to make the PI SQL code work under the hood is hidden from the SAS users.  If you go this route, make sure you tell everyone that the data is really comming from PI, or they'll start getting very disapointed in the rest of your relational databases :)

             

            Matt

             

             

             

             

              • Re: Integration to SAS
                Kenneth.Rice

                Asle & Matt,

                 

                Thank you both for your responses. Asle, I can not tell you much about SAS other than its a Statisical Analysis tool, http://www.sas.com , I do not know a lot about the product other than I have an internal customer that has used it against some SCADA data stored in an Oracle Table that we are looking to decommission in favor of PI. So I am the lucky one that gets to convert her to SAS connected to PI.

                 

                Matt,

                 

                I do know that we have tried to use the "primary" method and have run into the "Select Top 1..." Issue although I did not know the exact syntax I could tell it was trying to pull to much data during initial connection. Is there a place we can "constrain" the initial query with something like select tag from archive where time is between x and y? We prefer to stick with just EG through PIOLEDB to PI Server, we do not have a SAS Server so Im told and to set up a "linked server" some would argue why we getting rid of Oracle to just set up a SQL Server.

                 

                Can we alter the Select Top 1" query if so how and where? Do you happen to have screen shots.

                 

                Thanks in advance

                  • Re: Integration to SAS
                    mheere

                    Ideally, EG would let you configure the queries that it uses to drive its wizards.  This is unlikely, but it's the best solution so you should start there.

                     

                    Assuming that's a bust, you have two options for constraining things on the data end:

                     

                    1.  Create views in PI OLEDB.  These definitions are ultimately stored back on the PI server, and so are available to any PI user once they're setup.  Problem is that I don't know just how dynamic these can be.  For instance, you can setup a view into the archive that contains a pre-defined start/end time.  This works well for getting the wizards in EG to "play nice".  What I don't know is if you can then later over-ride these definitions easily, or if you have to somehow "trick" EG into switching from the view back to the native archive table once you have everything built so that the EG queries actually get all the data they're looking for.

                     

                    2.  Create the views or stored procedures in SQL Server, having them hit PI on the back end.  This is not the same thing as just moving from Oracle to SQL.  In this case the data still lives in PI.  You're just using the query processor in SQL server to present an interface that the relatively dumb (from a connectivity management standpoint anyway) EG client can easily understand.  You can do things in SQL server like check the incomming query for proper constraints and optinally add them if the client didn't.  You need to be fairly proficient in T-SQL to do these things, but they are possible.

                     

                    Sorry no screen shots.  Having to recall all of this from my last job, so no access to SAS anymore (some would say this is a plus :) )