Optimizing Dynamic Tag Queries in PI SQL DAS OLEDB

Solving PI SQL DAS OLEDB Dynamic Tag Mapping Challenges

Handling dynamic attributes in OSIsoft PI can be particularly challenging when querying archives using PI SQL DAS OLEDB. Especially when you have a situation where tag mappings in your AF (Asset Framework) application change based on operational conditions or specific parameters, retrieving accurate historical data becomes complicated.

This post delves into effectively addressing the issue of dynamic tag retrieval and how to maintain accuracy in historical data queries.

The Challenge

Consider an industrial setting where data from a seed fermentor is transferred to a production fermentor. The seed fermentor might transfer materials to different production fermentors (e.g., FE2000 or FE3000), each identified by a unique 'Prod Fermentor Number'. In a typical setup, your AF might have attributes set with string substitutions that reflect these conditions, dynamically switching the underlying PI tag based on the current 'Prod Fermentor Number'.

The problem arises when querying archived values with PI SQL DAS OLEDB. When executing a query, it sometimes fetches data associated with the currently active tag rather than the relevant tag during the historical event time frame. Consequently, this could lead to data mismatches or inaccuracies when reviewing past operations.

Proposed Solution

One effective way to handle this challenge is by revising your data architecture involving AF Analyses, which handles dynamic tag correlations and writes it to a set PI tag. Here’s a step-by-step approach:

1. Setup Analysis in AF: Create an analysis in your AF setup that captures the active ‘Prod Fermentor’ number and writes this along with any relevant data to a dedicated PI tag. This ensures that regardless of dynamic changes in tag affiliation, historical data is consistently stored.

2. Tag Energy Aggregation: The PI tag created by the analysis will aggregate data for all potential dynamic mappings. Whenever a switch occurs between production fermentors, the analysis ensures correct data attribution at all times.

3. Update SQL Queries: Adjust your PI SQL DAS OLEDB queries to reference these new consolidated PI tags. This process simplifies the retrieval process, ensuring that historical data queries correctly reflect the operational environment at any query-time.

This solution leverages AF and PI Analysis’ ability to contextually switch and store data, ultimately improving reliability in operational intelligence.

Best Practices and Tips

• Frequent Synchronization: Ensure your AF templates and analyses are frequently synchronized with PI Data Archive records. Consistent updates minimize discrepancies.
• Historic Validation: Regular audits of historical data versus operational logs can validate accuracy in retrieval setups.
• Documentation: Document every operational change in AF templates where dynamic attributes are extensively used. Having a detailed change log helps in troubleshooting and mapping queries effectively in diverse scenarios.

By using AF Analyses to manage dynamic tag mapping challenges, querying historical data via PI SQL DAS OLEDB becomes more reliable and less prone to the pitfalls of dynamic mappings. These improvements pave the way for more robust data-driven decision-making, essential for operational excellence in dynamic industrial environments.

Feel free to share your insights or additional solutions you might have implemented for similar scenarios in the comments below!