Can some flesh out a simple query that would retrieve the PI tag name and exdesc using SQL commander?
I suspect it's something like
SELECT Name,exDesc from [AFserver].[Asset].[Element]
You can't access PI Point attributes using OLEDBENT.
As far as the SQL query goes, if you have prepared a query against AF Server, and disconnect, you can't run the same query against PI Server (new connection). That's expected as each query is prepared against a specific data source.Just click "New Query" button in the top menu.
You want to query PI Server (OLEDB Classic), not AF (OLEDB ENT), to get this info.
SELECT exdesc, tag FROM [pipoint].[pipoint]
WHERE tag like 'sin*'
tried that, and I'm getting the error : -
Schema name 'pipoint' is invalid.
Are you actually connecting to PI Server? (don't connect to AF Server for this one)
Hmmm... so can PI OLEDB enterprise fetch tag attributes and snapshot data using this technique (which is what I was trying to establish with this query)
also, If I disconnect from the AF server, then connect to the PI server, I can't execute the SQL query at all (it's disabled)
There's collection of sample Queries for PI OLEDB Enterprise and PI OLEDB (classic) in PI SQL Commander. Please click
What are you trying to accomplish in your application? Using PI OLEDB Enterprise, you can
However, if you want to get PI tag information (e.g. extended descriptor), you will have to use PI OLEDB.
If you absolutely need PI tag configuration (apart from just snapshot/archive values) and would like a way to interface between the two, a food for thought is to add both as SQL linked servers. You can then write a single query to get your requested data.
I think this discussion has probably been done to death.
This discussion, coupled with another question I asked regarding the difference between PI OLEDB and PI OLEDBENT has put to bed my idea of attempting to use the AF server to pull tag attributes from PI Data Archive.
Your last reply expresses a good portion of frustration which is sad because we have tried helping you. In addition I have the impression that there's a misunderstanding about tag attributes.
Tag or PI Point attributes is configuration data that affects the PI Data Archive only and has nothing to do with Asset Framework (AF). The Asset Framework introduces the ability to organize Tags in a relational, asset centric view.
Assuming you have implemented a structure in AF that describes assets in your facility, these assets have attributes that can refer to Tags (PI Point Data Reference) and you are interested in querying data from your asset based structure, PI OLEDB Enterprise would be one tool to use.
If you are indeed interested in querying Tag (PI Point) attributes, PI OLEDB (classic) would be one out of multiple options.
In case you have a use case with an AF structure and would have to query Tag (PI Point attributes) this still can be done e.g. through linked server connections as suggested by Daphne.
The more details you describe about your use case, the higher becomes the chance a solution suggested by somebody in the community includes what you are looking for.
Sorry. I think you misunderstood my intention.
I was trying to say that I have enough information to go back to the customer to say they need a different product to the one they say they have purchased, and I was trying to work within their toolset. After a day of testing I concluded they needed a different product, so saw no point in getting everyone else to pursue a pointless task of trying to pull PI tag attributes through PI AF - it wasn't going to happen.
I was just using SQL Commander to prove the point, which with everyone's help I did. I can only apologise first for the misunderstanding, and second to thank everyone for their efforts in helping me understand which products could (or could not) perform what tasks.
Thank you for clarifying.
You’re very welcome. J
A colleague stumbled across this discussion and contacted me to make me aware of an option introduced by Asset Based Analytics that allows to show PI Tag Attributes as Element Attributes. Please see these screeshots
Thanks for update.
It looks as though elements need to be created in AF that reference the required tag attributes I want to pull.
As far as I understand the licensing model, each AF element is regarded as a tag; so pulling ALL the classic tag attributes into AF could be expensive (1 tag per tag attribute). Am I right?
AF elements do not count as PI Tags. AF elements are hierarchical information that is stored in a backend SQL database, and is only limited by how much information your SQL server can store. AF attributes that are using PI Point data references are used to retrieve information stored in PI tags and present them within the hierarchical model in AF. In this example, the PI tag is already created (since you are trying to retrieve information from it), no additional PI tags are needed.
In Gregor's example, 4 additional static attributes are created since output attributes from Asset-based analytics do not necessarily have to be mapped to PI tags. With that said, we recommend mapping output attributes from analyses to PI tags because there could be significant performance issues with many analyses writing to static AF attributes. This is because each change (from a calculation) will result in an entry in the SQL database, which can grow very large depending on how many/how frequent the calculations are. In your case, since tag configurations are relatively constant, you could configure the analyses as very infrequent periodic calculations to minimize the impact on performance. However, it all depends on how many of such calculations you are targeting.
I think there are going to be "on-demand" analysis calculations in AF 2.7, which might be useful for your scenario.
Retrieving data ...