8 Replies Latest reply on Jun 19, 2013 5:03 PM by skwan

    AF custom reference

    aommeren

      Hello,

       

      I want to check if this is feasible: Instaead of an AF Table lookup reference I would like to create/use a new custom data reference.
      I have a VBscript that opens a db connection and calls a SP to acquire a parameter for a product. The return value is given for a certain product and parametername.

       

      value = f(product, parametername)

       

      The batch product changes over time and I want to get the parameters from the external system. The initial idea was to write some ACE code and get the parameters when the product cahnes and store these into PI tags. Now I think it would be better to use this SP link in an AF attribute, providing the paramtername (AF attribute name?) and the PI Point reference for the Product it would be ideal to retrieve the data on the fly.

       

      I guess there are experts around who can provide some hints and best practices....!?!

       

      Thanks,

       

      Ad

        • Re: AF custom reference
          Roger Palmen

          Hi Ad,

           

          Depending on the source database, you could try to create a view that calls the stored procedure with the product and parameter and get the data into AF using a linked AFtable.

           

          Although using a custom DR you can create almost everything, but i normally try to avoid coding when you can make it work decently using out-of-box features.

           

          Roger

            • Re: AF custom reference
              aommeren

              Hello Roger,

               

              I fully agree with your second statement.

               

              So I want to test if your suggestion can work. What you are saying is that the AF Table is just a conveyer of the product and parameter fields? The SP just returns one value for the parameter and product/batch under concern. The AF table does not hold any records itself only the record returned by the last call from an attribute to the table? Just trying to imagine how this would function.

               

              I will dig in, any references to this type of AF table use?

               

              Also for this case and out of general interest : are there guidelines and best practices for coding and registering new DR's?

               

              Thanks!

               

              Ad

                • Re: AF custom reference
                  Roger Palmen

                  Hi Ad,

                   

                  My apologies for my late reply.

                   

                   

                   

                  I find it a bit difficult to wrap my head around the problem you describe. Thus i can't really point you to a purpose-fit solution to your problem. My first idea was to use things like table valued functions. This could take the form of:

                   
                  CREATE FUNCTION FunctionTable (@ProductID varchar(200))
                  RETURNS table
                      AS
                      RETURN (SELECT [Product].[Name], [Product].[Price]
                        FROM Product
                        WHERE [Product].[ID] = @ProductID
                      );
                  

                  You can then query the table using the following query:

                   
                  SELECT * FROM FunctionTable('ProductID')
                  

                  To use this easily in AF, it will probably not work as you would want to encode the parameters into the where clause. So i really need to give this some second thoughts. 

                   

                   

                   

                  Regarding programming of Custom AF DataReferences, i raised a thread on this topic some time ago: http://vcampus.osisoft.com/discussion_hall/development_with_osisoft_sdks/f/28/t/3618.aspx?pi1172=1

                   

                  What helped me most was the series of (unofficial?) videos from Micheal van der Veeken (OSIsoft NL): http://www.youtube.com/watch?v=BGnr4XWohNs&list=PLA990A96454E144BB

                    • Re: AF custom reference
                      aommeren

                      Hi Roger,

                       

                      Sorry, for the late response. Thank you for the effort you put in, but ... I think this route gets too complicated (for me).

                       

                      In the meantime we are working on an approach to just expose the data in a View (SSIS) on the source db. This view can be linked as an AF Table which would make it easy to implement and use.

                       

                      Also I will try to program this into my first custom DR, but this is to learn and invest in some personal R&D.

                       

                      Thanks for the help and hints,

                       

                      Ad