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.
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.
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?
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'
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?
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
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:
In additon the the PI Processbook help for VBA, You may find this document helful:
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.
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.
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.
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.
Dim MyDataset As Dataset
Dim MyDatasets As Datasets
Dim MyOdbcDataset As ODBCDataset
StringName = StringName1.Value
Set MyDatasets = ThisDisplay.Datasets
'Set error handling back to normal
On Error GoTo 0
MyDatasets.Add "DatasetName", MyOdbcDataset, True, 1, True, pbDatasetODBC
Set MyOdbcDataset = MyDatasets.GetDataset("DatasetName")
.DataSourceName = "DataSoureName"
.Description = "Description "
.Query = "select * from ..................where object_code = '" & StringName & "' ...