11 Replies Latest reply on May 29, 2017 2:44 AM by Madjid

    Process book with sql data




      Can anyone provide some guidance about what is the best method to pull data from external data source i.e. SQL DB. What is the best method to use from the following.:


      1. Using an ODBC

      2. Use ADO recordset/VBA code

      3. Develop custom data interface


      To give you some background for understanding the need for this. I've multiple assets and there is data associated with them that is process and then stored in external database. To pull data from SQL DB I can write sample query like as following:


      SELECT RPTDate, AssetName, Value FROM Assets_Daily_Data


      But this will pull data for all assets whereas I've trends in the ProcessBook as an individual asset. I hoping if it is possible to add new trace for matching assets but not sure if ODBC can handle parameters.


      I can write VBA code but not sure how recordset can be added as a trace to the process book trend.


      My last preference is to develop any interface as i'm least familiar with it, but in worst case scenario i'm willing to go this path as well.


      With above accomplished with any of the methods, cherry on the toppings would be to be able to use ProceesBook file as an SVG file over SharePoint.


      Thank you.

        • Re: Process book with sql data

          Hi Sohail,


          One option that you did not list, and which may actually be the best fit for your case, is the use of AF linked tables. With linked tables, you can build parameterized queries based on an AF hierarchy. As it seems you have multiple related assets, hopefully you have an AF structure built out for them already. In this case, you can then build AF tables to link to the external SQL data, and then just configure the ProcessBook symbols to read from the AF attributes that are reading from the AF table. You would also be able to save the ProcessBook files as SVGs and they should be viewable over PI WebParts or PI Coresight.


          Using any of the three options you listed is definitely also a possibility, but I would recommend you check out AF tables first to see if what you want to achieve can be done out-of-the-box.


          For more information, our Live Library has a section on AF tables.

            • Re: Process book with sql data

              Thanks Gavin, AF link table is easiest of all the option and it was considered first but it has limitations on how data is refreshed. I asked this question separately and was told that behavior is expected. Data in external data source updates frequently and hence using AF wasn't an option, check this https://pisquare.osisoft.com/thread/8813?sr=inbox&ru=2114. Unless I'm missing something here.

                • Re: Process book with sql data

                  Sorry for the late response, Sohail. I do not understand, what is the concern here with using AF linked tables? Which limitation with the data refreshing are you referring to? If your external data source is updating frequently, just adjust the cache refresh interval to be of a similar frequency. Let's say for example in the ProcessBook display I have a value reading from an AF attribute that is a table lookup. The table refresh interval is set to 1 minute. By default, ProcessBook will refresh every 5 seconds. It will therefore take a minimum of 12 refreshes (60/5=12) before new data in the linked SQL table shows up in ProcessBook. If I wanted it to be able to update faster, I would just have to adjust the refresh interval to less than 1 minute.


                  In any case, if you made ODBC queries, wouldn't you have to periodically poll and refresh these too anyway?

              • Re: Process book with sql data
                Eugene Lee

                Hi Sohail,


                Regarding using ODBC, it is possible to create one ODBC dataset for each Asset that you want so that you can use it for a trace. You will need to use the WHERE clause in your SQL query so that only that specific Asset is pulled out. For example:


                SELECT RPTDate, AssetName, Value FROM Assets_Daily_Data WHERE AssetName='MyAsset'

                  • Re: Process book with sql data

                    Thanks Eugene,


                    There are hundreds of assets and doing that manually for each one would not only be tedious but maintenance nightmare. Do you know if this can be done through VBA code?


                      • Re: Process book with sql data
                        Eugene Lee

                        Hi Sohail,


                        That is certainly possible. For example:


                        Set oOdbcDataset = oDatasets.Add("MyDATASET", oOdbcDataset, True, 1, True, pbDatasetODBC)
                        Set oOdbcDataset = oDatasets.GetDataset("MyDATASET")
                        With oOdbcDataset
                        .DataSourceName = "myDSN"
                        .Query = "SELECT datadate, datavalue FROM pointdata"
                        End With
                        oDatasets.SetDataset oOdbcDataset
                        • Re: Process book with sql data

                          Hello Sohail,


                          Your Processbook file can become very slow if you keep hundreds of Datasets created in the file.


                          I would recommend that

                          • You create required dataset(s) with code,  on demand, when the user click on the asset he wants to visualize.
                          • After use, you should either remove it with code or use the same dataset(s) but modify them programmatically to fit your needs.
                          • You specify a time period when querying the data for the dataset data so you don't retieve unecessary data something like this:
                            • SELECT RPTDate, Value FROM Assets_Daily_Data WHERE AssetName='MyAsset' and RPTDate=>'2015-03-11' and RPTDate<'2015-03-12'


                          Since this question is often asked, I also created a small example that shows how this can be done that works with a trend, you can find it here:

                          How to manipulate ODBC Datasets dynamically using VBA - Example that updates a trend


                          In additon the the PI Processbook help for VBA, You may find this document helful:

                          PI ProcessBook VBA Language Reference

                      • Re: Process book with sql data

                        Hello Sohail,


                        Have you find a solution to your problem?


                        It would help others if you mark the answer that helped you as correct, otherwise you can give us more details so we can further assist you.



                          • Re: Process book with sql data

                            HI All,

                            i have same concern as Sohail mentioned in his question, however i need a fit for purpose VBA coding to change the "query code". i have created a new dataset in ODBC and and i put a query as well as selected a pr defined database.

                            the query will call for a specific item-name.


                            all what i want is to change the item-name in the query using the vba.


                            Best Regards,

                              • Re: Process book with sql data

                                Hello Madjid,


                                Have you looked at the resource Patrice referenced to earlier: How to manipulate ODBC Datasets dynamically using VBA - Example that updates a trend?


                                All you need to do is to modify the query string. Patrice does this with the sub routines ValueA, ValueB and ValueC.

                                  • Re: Process book with sql data

                                    Dear Gregor,

                                    Thank you for the reply,

                                    I have tried the below vba code (Remove and Create), which works fine with me. as i use a combo box to make a StringName in the where clause as variable. whenever the StringName changes the query clause change as well.


                                    "Sub CreateODBCDataSet()

                                    Dim MyDataset As Dataset

                                    Dim MyDatasets As Datasets

                                    Dim MyOdbcDataset As ODBCDataset

                                    StringName = StringName1.Value

                                    Set MyDatasets = ThisDisplay.Datasets


                                          MyDatasets.Remove ("DatasetName")

                                          'Set error handling back to normal

                                          On Error GoTo 0


                                    MyDatasets.Add "DatasetName", MyOdbcDataset, True, 1, True, pbDatasetODBC

                                    Set MyOdbcDataset = MyDatasets.GetDataset("DatasetName")

                                    With MyOdbcDataset


                                    .DataSourceName = "DataSoureName"

                                    .Description = "Description "

                                    .Query = "select * from ..................where object_code = '" & StringName & "' ...


                                    End With


                                    MyDatasets.SetDataset MyOdbcDataset

                                    End Sub"