6 Replies Latest reply on Dec 7, 2017 11:57 PM by messingerj

    SQL Commander for BI vs TSQL

    roeseaj

      Hi PI Community, Working with our BI team to implement reports against AF data. OLEDB E is up and linked server created. It appears we can T-SQL our way to a solution without SQL Commander. Is that a common approach or just as good as using SQL Commander if you have a BI resource to build the joins or are there some other reasons that make SQL commander preferable?

       

      Thanks for your input !

      Bert

        • Re: SQL Commander for BI vs TSQL
          messingerj

          PI SQL Commander is a tool for developing and testing queries against the PI OLEDB Enterprise Provider natively, without having to deal with the overhead of the integration layer during the query development phase. Once your queries are tested and shown to be returning the expected data in SQL Commander, you would then incorporate the query directly into your integration layer - in your case it sounds like SQL Server using a linked server object. You can't really automate SQL Commander to produce your reports.

           

          John

          1 of 1 people found this helpful
            • Re: SQL Commander for BI vs TSQL
              roeseaj

              Hi John, Thanks for the response and agree with what you are saying. I didn't expect SQL Commander to automate any of the actual reporting.

               

              What our BI team has seen is that once the linked server is set up through OLEDB E they can start working through the data structures, finding the entity relationships and building the data objects the BI system could then pull from for its reporting. This means tying together a lot of guids and unfriendly entities that don't look like AF to end up with what is in AF. All totally doable in SQL but time consuming and I assume may be more fragile if there is an update to OLEDB E and or AF.

               

              My understanding with SQL Commander is that it uses the API to guide you through that process so you can build reporting elements out of AF. Tweak in a manner similar to SQL and then expose those final entitles to the BI. Thus taking less time to develop and being more robust considering OLEDB E and AF upgrades.

               

              Is that correct? Is there any point over or under stated?

               

              Many Thanks,

              Bert

                • Re: SQL Commander for BI vs TSQL
                  vkaufmann

                  The schema is going to be the same regardless if you expose the provider via a linked server or via the commander, so there is really limited benefit using one over the other. However, the commander will help provide a list of sample and default queries to help get you started and also allows you to use native PI time abbreviations over something that must be understood by a SQL server. On the other hand, the commander doesn't expose the entire list of functions that you might be able to leverage in a linked server environment.

                   

                  Using linked servers with the PI OLEDB providers will often require to use OPENQUERY() functions to get adequate performance from the providers as well due to issues with the SQL server optimizer incorrectly optimizing the data calls necessary for most queries. In the end, the commander is a good starting point to test new queries but does not provide a 1:1 mapping of what your queries will look like when using them against a linked server. In regards to sensitivity to AF and OLEDB upgrades, there really is no getting around that. The enterprise provider is heavily reliant on both the AF server and AF client installed where you are leveraging the provider, so any changes made to these would impact any queries made using the commander or a linked server.

                   

                  --Vince

                  1 of 1 people found this helpful
                  • Re: SQL Commander for BI vs TSQL
                    messingerj

                    Hi Bert,

                     

                    I'm not sure I fully understand your comment:

                    This means tying together a lot of guids and unfriendly entities that don't look like AF to end up with what is in AF. All totally doable in SQL but time consuming and I assume may be more fragile if there is an update to OLEDB E and or AF.

                    Are you referring to JOINs between tables on object ID's, such as

                     

                    INNER JOIN NuGreen.Asset.ElementAttribute ea ON ea.ElementID = eh.ElementID
                    

                     

                    or am I missing something here? If the above is an example of what you are referring to, then this is just a part of dealing with AF hierarchies in a SQL based environment, and you are not dealing directly with guids in your query per se. As Vincent Kaufmann mentioned, the schema exposed by the OLEDB provider is the same regardless of what environment you access it from.

                     

                    My understanding with SQL Commander is that it uses the API to guide you through that process so you can build reporting elements out of AF. Tweak in a manner similar to SQL and then expose those final entitles to the BI.

                     

                    SQL Commander has a query compendium with numerous examples of how to use the schema, but the development of your reporting elements also somewhat dependent upon how you have modelled your data in AF. You also need to use the SQL Commander application if you need to create any TVF's for use in your queries. There's no other guidance that I'm aware of in SQL Commander for query development.

                     

                    Regards,

                    John

                      • Re: SQL Commander for BI vs TSQL
                        roeseaj

                        Hi John, honestly, I'm probably very confused...

                         

                        So, this is what we have working and is what I would call the SQL method which does rely on OLEDB E. It returns values though not transposed (which can be done on the ETL side but with the current query its one element at a time). Works but seems to not be making the best use of OLEDB E and SQL Commander.

                        SELECT top 1000 ea.Name, eh.Path,

                        a.*

                        FROM [LINKEDPIAF].[AFDB].[Asset].[ElementHierarchy] eh

                        INNER JOIN [LINKEDPIAF].[AFDB].[Asset].[ElementAttribute] ea ON ea.ElementID = eh.ElementID

                        INNER JOIN [LINKEDPIAF].[AFDB].[Data].[Archive] a ON a.ElementAttributeID = ea.ID

                        WHERE

                        --a.ElementAttributeID = N'00000035-0000-0000-1e00-000000000000' -- root elements

                        --AND

                        eh.Path like N'\Utilities\1015%' and  -- element hierarchy path has plant numbers and names

                        ea.Name like N'JY__15' and            -- JY__15 are Power Meters

                        a.Time >= '2017-12-05 13:00:00'

                        OPTION (FORCE ORDER)

                         

                        This is what we are able to build with SQL Commander,

                        --****** Object: Custom Function ft_TransposeArchive_Util_Power Script Date: 12/7/2017 10:56:44 AM ******

                         

                        SELECT eh.Path + eh.Name Element, ta.*

                        FROM [AFDB].[Asset].[ElementHierarchy] eh

                        INNER JOIN [AFDB].[DataT].[ft_TransposeArchive_Util_Power] ta

                             ON ta.ElementID = eh.ElementID

                        WHERE eh.Path = N'\' AND ta.StartTime = DATE('*-1h') AND ta.EndTime = DATE('*')

                        OPTION (FORCE ORDER, IGNORE ERRORS, EMBED ERRORS)

                         

                        Which generates,

                        SELECT [ElementID]

                              ,[JY0115]

                              ,[JY0215]

                              ,[JY0315]

                              ,[JY0415]

                              ,[JY0515]

                          FROM [LINKEDPIAF].[AFDB].[DataT].[ft_TransposeArchive_Util_Power]

                        GO

                         

                        In SQL Commander, it executes with no errors but doesn't return any rows either. If I do a select query of the transpose function in SQL it throws the following error,

                        OLE DB provider "PIOLEDBENT" for linked server "LINKEDPIAF" returned message "[AFDB.DataT.ft_TransposeArchive_Util_Power Tbl1002 function table] 'ElementID', 'StartTime', 'EndTime' argument columns are not restricted or are not restricted properly.

                        Possible reasons:
                        - Invalid WHERE condition (use 'ElementID = ... AND StartTime = ... AND EndTime = ...')
                        - Join order (reorder tables in FROM clause and use 'OPTION (FORCE ORDER)' query hint)".
                        Msg 7320, Level 16, State 2, Line 1
                        Cannot execute the query "SELECT "Tbl1002"."ElementID" "Col1005","Tbl1002"."JY0115" "Col1006","Tbl1002"."JY0215" "Col1007","Tbl1002"."JY0315" "Col1008","Tbl1002"."JY0415" "Col1009","Tbl1002"."JY0515" "Col1003" FROM "AFDB"."DataT"."ft_TransposeArchive_Util_Power" "Tbl1002"" against OLE DB provider "PIOLEDBENT" for linked server "LINKEDPIAF".

                         

                        So it appears SQL Commander and the transpose functions make it easier to build queries but requires adding the openquery command and other information to make it work... At this point, I'm not sure where one approach appears more efficient than the other over the long term.

                         

                        Thanks,

                          • Re: SQL Commander for BI vs TSQL
                            messingerj

                            Hi Bert,

                             

                            When using the PI OLEDB Enterprise provider in a SQL Server environment, you definitely want to wrap your provider specific SQL within SQL Server's OPENQUERY function. This becomes a pass through query, and prevents SQL Server from trying to directly parse the provider specific commands and subsequently throwing errors.

                             

                            Looking at the specifics of your query, I believe you are not properly referencing the transpose function, but are rather trying to JOIN the function table ft_TransposeArchive_Util_Power. When you created the transpose function in SQL Commander, two objects should have been created - the previously mentioned table, and a function called TransposeArchive_Util_Power. In the object explorer of SQL Commander, this second object will appear under the Functions folder, as per the following example:

                             

                            Your query should probably be re-written similar to the following to get the rows you are expecting:

                             

                            SELECT eh.Path + eh.Name Element, ta.*
                            FROM AFDB.Asset.ElementHierarchy eh
                            INNER JOIN AFDB.Asset.Element e ON eh.ElementID = e.ID
                            INNER JOIN AFDB.Asset.ElementAttribute ea ON ea.ElementID = eh.ElementID
                            CROSS APPLY AFDB.DataT.TransposeArchive_Util_Power
                            (
                                 eh.ElementID,
                                 '2017-12-05 13:00:00',
                                 N'*'
                            ) ta
                            WHERE eh.Path like N'\Utilities\1015%'
                            AND ea.Name like N'JY__15'
                            OPTION (FORCE ORDER, EMBED ERRORS)
                            

                             

                            One of the key components to get your transposed data is the CROSS APPLY statement to pass in parameters to the TransposeArchive_Util_Power function.

                             

                            Have a look at the examples in the query compendium available from PI SQL Commander, under the 'Transposed Data' examples folder.

                             

                            Regards,

                            John

                            1 of 1 people found this helpful