2 Replies Latest reply on Oct 24, 2018 2:19 PM by csawyer

    OLEDB - Asset Framework

    jrheeders

      Good day,

      Can somebody guide me in the right direction. I am not familiar with OLEDB with Asset Framework and want to get familiar with it. I found some examples under PI SQL Commander\MyProjects\PI SQL Query Compendium. I installed the NuGreen and PiOleDbTest databases and generated events. But when I try to run the SQL queries I am just getting errors the whole time.

       

      Error from PI SQL Commander:

      [\\WIN-9TFL7DK8HKH\NuGreen\NuGreen\Houston\Cracking Process\Equipment\P-214|Motor Amps] [OSIsoft.AFSDK] [-10734] PINET: Broken Connection.

       

      Error from MSSQL using the linked connection:

      OLE DB provider "PIOLEDBENT" for linked server "LINKEDAF" returned message "[NuGreen.Data.Snapshot Tbl1005] Neither 'ElementAttributeID' nor 'ElementTemplateAttributeID' column is restricted. Such a query is considered expensive.

       

       

      Possible solutions:

      - Add a join with 'ElementAttribute' or 'vElementAttribute' or 'ElementTemplateAttribute' table or, if the join is already present, reorder tables in the FROM clause and use the 'OPTION (FORCE ORDER)' query hint.

      - Add 'OPTION (ALLOW EXPENSIVE)' query hint.".

      Msg 7320, Level 16, State 2, Line 2

      Cannot execute the query "SELECT "Col1030","Col1027","Tbl1005"."Time" "Col1022","Tbl1005"."Value" "Col1023" FROM "NuGreen"."Data"."Snapshot" "Tbl1005",(SELECT "Col1029","Col1030","Col1031","Tbl1003"."ID" "Col1026","Tbl1003"."Name" "Col1027","Tbl1003"."ElementID" "Col1024" FROM "NuGreen"."Asset"."ElementAttribute" "Tbl1003",(SELECT "Tbl1001"."Path" "Col1029","Tbl1001"."Name" "Col1030","Tbl1001"."ElementID" "Col1031" FROM "NuGreen"."Asset"."ElementHierarchy" "Tbl1001" WHERE "Tbl1001"."Path"='\NuGreen\Houston\Cracking Process\Equipment\') Qry1032 WHERE "Col1031"="Tbl1003"."ElementID") Qry1033 WHERE "Tbl1005"."ElementAttributeID"="Col1026"" against OLE DB provider "PIOLEDBENT" for linked server "LINKEDAF".

       

      Is there maybe a proper document that I can follow. Some guidance would be appreciated. Thanks!

       

      Regards

      Jaco

        • Re: OLEDB - Asset Framework
          Roger Palmen

          Some hints here: KB00637 - Optimizing the Query Performance for ElementHierarchy Table in PI OLEDB Enterprise

           

          Maybe if you share the query you use.. i think the suggestion you receive might point to the sequence of tables in your query. And yes, OLEDB Ent is quite a beast to keep under control sometimes...

          • Re: OLEDB - Asset Framework
            csawyer

            On your broken connection error, I'm wondering if you don't have "Integrated Security=SSPI" in your connection parameters.  You also should verify that the AF DB is actually reachable from the point where the query is being executed (i.e., if you're not running the Data Access Server or the OLEDB server locally, then check the connection from the server where the OLEDB server actually is by opening PI System Explorer).

             

            As far as expensive joins go, this is from the PI OLEDB 2016 User Guide.   It looks like you might have missed doing an intermediate join with ElementHierarchy before reaching into the ElementTemplate/Attribute tables.  You're being stopped from running this query because as the documentation indicates it's likely to result in a slow-performing query because excessive data has to be pulled, scanned and indexed in memory before the desired target tables are then searched.

             

            OPTION (ALLOW EXPENSIVE)

            Unrestricted queries for a number of tables are considered expensive. To protect the system from unintended load, such queries will return an error.For example:

            returns the following error: Possible solutions:

            • Add a join with ElementHierarchy or Element table or, if the join is already present, reorder tables in the FROM clause and use the OPTION (FORCE ORDER) query hint.
            • Add OPTION (ALLOW EXPENSIVE) query hint.
              Setting the O
              ption overwrites the standard behavior and allows the query to run.

              Setting the Option overwrites the standard behavior and allows the query to run.