16 Replies Latest reply on Dec 9, 2011 10:21 PM by aabrodskiy

    PI OLEDB Enterprise 2010 R3

    aabrodskiy

      I noticed there is an upcoming release of PI OLEDB Enterprise on the RoadMap with support of LEFT OUTER JOINs - that is great and much needed functionality!

       

      I would like to suggest another small but highly demanded feature to add into that release, if still possible.

       

      When we query Snapshot or Archive tables, we can specify IGNORE ERRORS, EMBED ERRORS in the OPTION of the query, and thus avoid query failing when there are any problems in some of the attributes in the snapshot/archive (e.g. formula isn't calculating, wrong tag name in a PI Point DR, etc).

       

      Can we use the same options on a global level, by specifying them in the connection string, as it was done with ALLOW EXPENSIVE?

       

      That way we could query Snapshot/Archive directly from SQL via Linked Server without the need to use passquery...

       

      Currently using OPENQUERY and dynamic SQL + supplying parameters into dynamic SQL slows down our solution a lot. Given that overall performance when working with remote data in SQL isn't great, I am trying to gain any performance boost possible.

       

      Thank in advance.

       

       

       

      Alex

       

      Senior Consultant, RTDM Solutions Architect

       

      Wipro Technologies

        • Re: PI OLEDB Enterprise 2010 R3

          Alex Brodskiy @ Wipro

          I noticed there is an upcoming release of PI OLEDB Enterprise on the RoadMap with support of LEFT OUTER JOINs - that is great and much needed functionality!
          Good to read your enthusiasm :)

           

          Alex Brodskiy @ Wipro

          I would like to suggest another small but highly demanded feature to add into that release, if still possible.
          I'll have to run this by the team, but I think your suggestion makes sense. As far as timing, I cannot provide a definitive answer now because, as you can expect , we are in the last stretch to finalize the 2010 R3 release... finishing the implementation of a couple things and then a heavy testing phase. I will have to check with the team on the feasability and time estimate for this, and see how this fits in the overall scope. I will comment back here...

           

          ****

           

          With that said, I would like to suggest a simple and effective workaround: define a view in PI SQL Commander (with the desired error options) and then query it from SQL. Does that sound like a good approach in your case?

            • Re: PI OLEDB Enterprise 2010 R3
              aabrodskiy

              Steve,

               

              That sounds like a workaround and I will give it a try, the only downside I see is that we will have to re-create those views each and every time I deploy the new structure onto testing and production environment (I am already doing that for transpose functions).

               

              Due to the nature of AF Database sync - we have to delete AF Database on the testing/production servers and import XML into a clean database every time we make any changes in the Development environment (our changes may also incluide deletion of elements/attributes, which does not get updated if we just import XML into existing database).

               

              I am trying to develop some synching tool for AF Databases (like I have developed for PI Tags in the past), but in the meantime that procedure is a bit painful for us...

               

              Do you have any ideas how we could improve that?

               

              Thanks,

               

              Alex

                • Re: PI OLEDB Enterprise 2010 R3
                  Bannikov

                  Alex Brodskiy @ Wipro

                  Due to the nature of AF Database sync - we have to delete AF Database on the testing/production servers and import XML into a clean database every time we make any changes in the Development environment (our changes may also incluide deletion of elements/attributes, which does not get updated if we just import XML into existing database).

                   

                  We have the same problem -- each time we copy AF model from development to product we shouldn't forget to recreate all Transposed functions (otherwise our application will fail in some places - during reporting, for example

                   

                  I think that adding some scripting capability to re-create transposed functions in a batch will be very useful...

                   

                   

                   

                   

                    • Re: PI OLEDB Enterprise 2010 R3
                      michaelh

                      Sergey Bannikov

                      each time we copy AF model from development to product we shouldn't forget to recreate all Transposed functions

                       

                      The Transpose functions and tables are stored in the Configuration AF Database.

                       

                      The Element  \OSIsoft\PI SQL\Database Objects\<db>\DataT ( and it's children ) could be exported and imported at the target node, where you imported <db>.

                       

                      Additionally:

                       

                      1. Views are stored there as well, Alex

                       

                      2. Make sure to create exactly the same hierarchy, manipulating the Configuration database is at your own risk  (SCNR ;)

                        • Re: PI OLEDB Enterprise 2010 R3
                          aabrodskiy

                          Michael,

                           

                          Michael Hesselbach

                          The Transpose functions and tables are stored in the Configuration AF Database

                           

                          yes, I figured that a while back, when started investigating mysterious disappearance of transpose functions and views on renaming/deleting AF databases

                           

                          Michael Hesselbach

                          2. Make sure to create exactly the same hierarchy, manipulating the Configuration database is at your own risk  (SCNR ;)

                           

                          This is probably the most important to the point why I refrain using it.

                           

                           

                           

                          I can formulate though a more generic question for all of us to think about for future releases of AF:

                           

                          We need to be able to easily deploy AF Databases from one environment to another (e.g. from Development to Testing, and then to Production), and here are various challenges we are dealing with

                          • Partial synchronisation of AF Databases. I.e. we develop centralized templates, UOM, and hierarchy design. And then from some point down in the hierearchy, each Division has its own data. So when we need to change certain templates, or remove some, or create new templates, new UOMs, etc.  - we need to be able to sync the changes into production databases.
                          • Different PI Server for Tags in AF Attributes. E.g. we have Development PI Server, Testing PI Server, and Production PI Server. Although most of the tags can be the same, we still need to play with them, thus a requirement for separate PI Server on every environment. So when we move AF Database from a Development Server to a Testing Server, we have to manually edit XML file to change the PI Server in all AF Attributes. Not a huge effort, but would be good to have a way to do it easier.
                          • Automate scripting of all OLEDB objects, so that they can be moved along with an AF Database (I mean transpose functions, viewes, etc).

                          I feel like this all would require a mature sync tool, rather than export/import... But that is a reality which comes with enterprise projects, when everything needs to be developed/tested before it comes to production servers... Particularly, when several sub-divisions are being managed by an HQ COE (headquarters centre of excellence) and any changes need to be centralised to cover the needs of all Assets.

                           

                          What do other community members think on this subject?

                           

                           

                           

                          Alex Brodskiy

                           

                          Senior Consultant, RTDM Solutions Architect

                           

                          Wipro Technologies

                            • Re: PI OLEDB Enterprise 2010 R3

                              Tomas Potrusil

                              There is a syntax for creating the transpose functions using PI OLEDB Enterprise alone. It is not documented anywhere; it's an internal way how PI SQL Commander creates the functions (and can be revealed if you enable logging in PI OLEDB Enterprise). That also means that there is no support for it and the syntax can change at any time. So use it at your own risk
                              Thanks for sharing this Tomas, I think this can prove useful until we have a more elegant solution - which Alex briefly referred to in his "AF manageability" post. I know Tomas said it, but I really do need to stress the fact that this is not documented, not supported, and provided 'as is' for you to use at your own risk.

                               

                              Sergey Bannikov

                              I suggest that maybe this syntax will be go public sometimes in a new version of OLEDB Enterprise.
                              Chances are this will not go public in a future version of PI OLEDB Enterprise. As Tomas pointed out, we reserve the right change this syntax at any time and therefore should be considered as something we use internally only.

                               

                              Sergey Bannikov

                              I only wonder how many other undocumented features are hidden from end-users of this product
                              Not too many, don't worry!  But keep hanging out on vCampus, this is the best way to learn about the in's and out's of the PI Data Access products

                                • Re: PI OLEDB Enterprise 2010 R3
                                  Bannikov

                                  Steve Pilon

                                  Chances are this will not go public in a future version of PI OLEDB Enterprise. As Tomas pointed out, we reserve the right change this syntax at any time and therefore should be considered as something we use internally only.

                                   

                                  Steve, I think that 'reserving the right to change anything' cannot prevnt make this feature public. For example, in latest version of OLEDB COM Connector the syntax of INI files was changed (and all INI files should be updated) -- but this doesn't prevent you from future changes (I know that in next version of COM Connector some changes will have effect on existion configuration). 'At any time' mean 'in next version', nothing more -- I think that you cannot remotely disable or alter this syntax in existing installation (can you?). This is normal flow of develpment -- next version has new features, and maybe some old stuff should be updated to reflect these changes, but this shouldn't prevent provide aditional service for customers.

                                   

                                  In this case I'm in complex state. One side, I've recevied answer for my problem (even two answers -- 'Configuration' DB and hidden 'SQL script'), but second side is that both ways aren't supported, and in case any troubles I'm at my own....

                                    • Re: PI OLEDB Enterprise 2010 R3

                                      @Sergey: thanks for the important questions - I'll answer in 2 parts:

                                      1. It's all about backwards compatibility and user experience... when you publicly announce/expose something and then change it, you break stuff. Of course you can document all you want, but you will indeniably break stuff because  A) not everybody reads the doc (and all of it), and  B) even an informed user may forget this or that script he/she wrote years ago, hidden on some legacy app server that's been running forever.

                                        And Murphy's law says that this script you forgot to update when you updated the OSIsoft product, plays a vital role in your collection of processes and workflows, and breaking it will cascade down and eventually break/mess with your production.

                                        As you probably guessed by now, I am not talking about this OLEDB specific script but rather the whole concept of backwards compatibility - and OSIsoft's responsibility around it.

                                      2. With that said, I would like to reiterate something I wrote in a previous post:  I think this can prove useful until we have a more elegant solution. In other words, this discussion thread revealed an issue (manageability of AF in general and custom OLEDB Views/Functions) and we believe we need to address it - but with a more elegant solution than that, which will please everybody (not just the script lovers like you and I ).

                                        This is added to our backlog and we will definitely be looking into solving this in a future release - maybe a backup/restore mechanism in PI SQL Commander? Ideas, thoughts?

                              • Re: PI OLEDB Enterprise 2010 R3
                                tomas

                                Sergey Bannikov

                                I think that adding some scripting capability to re-create transposed functions in a batch will be very useful...

                                 

                                There is a syntax for creating the transpose functions using PI OLEDB Enterprise alone. It is not documented anywhere; it's an internal way how PI SQL Commander creates the functions (and can be revealed if you enable logging in PI OLEDB Enterprise). That also means that there is no support for it and the syntax can change at any time. So use it at your own risk:

                                 

                                 

                                 
                                CREATE FUNCTION [Catalog].[Schema].[Name]
                                AS [Catalog]..TransposeSnapshot<'[Template Name]', '[Attribute Path]', [Include Attribute Subtree], [All Column As Variants]>
                                
                                CREATE TABLE [Catalog].[Schema].[Name]
                                AS [Catalog].[Schema].[Function Name]
                                

                                An example:

                                 

                                 

                                 
                                CREATE FUNCTION Test.DataT.TransposeSnapshot_Template1
                                AS Test..TransposeSnapshot<'Template1', '\', FALSE, FALSE>
                                
                                CREATE TABLE Test.DataT.ft_TransposeSnapshot_Template1
                                AS Test.DataT.TransposeSnapshot_Template1
                                

                                Besides TransposeSnapshot there are TransposeArchive, TransposeInterpolateDiscrete, TransposeInterpolateRange, vTransposeSnapshot, vTransposeArchive, vTransposeInterpolateDiscrete and vTransposeInterpolateRange templates.

                                 

                                 

                                 

                                 

                                 

                                 

                                  • Re: PI OLEDB Enterprise 2010 R3
                                    Bannikov

                                    Tomas Potrusil

                                    There is a syntax for creating the transpose functions using PI OLEDB Enterprise alone. It is not documented anywhere; it's an internal way how PI SQL Commander creates the functions

                                     

                                    Thank you for this very useful information. I suggest that maybe this syntax will be go public sometimes in a new version of OLEDB Enterprise. I only wonder how many other undocumented features are hidden from end-users of this product

                              • Re: PI OLEDB Enterprise 2010 R3
                                aabrodskiy

                                Steve,

                                 

                                Steve Pilon

                                With that said, I would like to suggest a simple and effective workaround: define a view in PI SQL Commander (with the desired error options) and then query it from SQL. Does that sound like a good approach in your case?

                                 

                                Although that is a viable workaround, Snapshot and Archive are not the only tables, which can fail when one of the attributes fails to retrieve a value. All transposed functions are also affected, so the workaround would mean creating a view for each and every trasnpose function plus Snapshot and Archive.

                                 

                                Steve Pilon

                                I'll have to run this by the team, but I think your suggestion makes sense.

                                 

                                Did you have a chance to run this by the team?

                                 

                                Thanks,

                                 

                                Alex Brodskiy

                                  • Re: PI OLEDB Enterprise 2010 R3

                                    Alex Brodskiy @ Wipro

                                    Did you have a chance to run this by the team?
                                    Yes I did run this by the team, sorry I did not post an update sooner... we all agreed it makes sense to provide these options on a global basis. And that offering that through Connection String parameters could be a valid approach to do so. Unfortunately though, as I mentioned before, it is difficult to provide a definitive answer for now... I'll know better within the next couple weeks.

                                     

                                    For one, the scope of the 2010 R3 release was already locked - although this is not the biggest roadblock, as you can imagine, it is difficult to extend too much as it aligns with a series of other 2010 R3 releases. Then as I mentioned previously we are finishing the implementation of a couple items, but more importantly is the heavy testing phase - and every single feature/setting/whatever you add generates a testing payload, as you certainly understand. Of course testing is not something we can take lightly, as this product is meant to interroperate with so many third-party components, in so many environments and configurations.

                                     

                                    All in all, like I said earlier, I'll know more within the next couple weeks and be able to post an update.

                                      • Re: PI OLEDB Enterprise 2010 R3
                                        aabrodskiy

                                        Steve,

                                         

                                        Thanks a lot for update, I do understand how difficult it must be to include every single feature in the enterprise products.

                                         

                                        Anyway, I hope we will see this implemented some time soon, may be included into the R4 if not possible into R3. In the meantime, I will be looking forward to further updates and will be trying to use workarounds you suggested.

                                         

                                        Thanks,

                                         

                                        Alex

                                          • Re: PI OLEDB Enterprise 2010 R3

                                            Alex Brodskiy @ Wipro

                                            I hope we will see this implemented some time soon, may be included into the R4 if not possible into R3
                                            Chances are we will try and tackle that in the 2012 release (there will not be a 2010 R4 release ).

                                             

                                            We released PI Server 2010, a major revision, in August 2010. This was a holistic release comprised of many other components (e.g. PI Data Access products, PI ProcessBook, PI WebParts, etc.). Then a variety of products released 2010 R2 versions and some will soon release 2010 R3 versions - there Rx releases are important but not as major as 2010 was. As you can see on the PI System roadmap, the next big release of PI Server (as a whole) is 2012, which means other products will release 2012 versions in the same time frame, possibly followed by 2012 R2/R3 until the next big release, depending on each product's roadmap.

                                             

                                             

                                          • Re: PI OLEDB Enterprise 2010 R3

                                            Steve Pilon

                                            Alex Brodskiy @ Wipro

                                            Did you have a chance to run this by the team?
                                            Yes I did run this by the team, sorry I did not post an update sooner... we all agreed it makes sense to provide these options on a global basis. And that offering that through Connection String parameters could be a valid approach to do so. Unfortunately though, as I mentioned before, it is difficult to provide a definitive answer for now... I'll know better within the next couple weeks.
                                            You'll be glad to learn that this option will make it into the PI OLEDB Enterprise 2010 R3 release, which we expect to make available before the end of the month. These "error options" will be available as connection string parameters, so that you don't have to specify them in the actual query (which syntax is not supported by some third-party products).

                                             

                                            Thanks for bringing this to our attention in the first place!