• State Not Answered
  • Replies 2 replies
  • Subscribers 2 subscribers
  • Views 68 views
  • Users 0 members are here
Options
Related

Custom Data Reference - SQLServer

Mark Fitzowich

I’m using a custom data reference (SQLServer) in PI AF to query forecasted volume data from our SQL database and write the results to an attribute. The query returns one data point per month, spanning from the start of the current month to 18 months out. Here's an example of the format returned in PI AF:

2025-04-01 12:00:00AM 1000m3

2025-05-01 12:00:00AM 1000m3

 

The custom data reference works as I expect and need it to. The issue comes in PI Vision when it loses functionality. In PI AF the value will output '1000m3' but in PI Vision it shows 'No Data'. Furthermore, when you hit 'Trend' or 'Time Series Data' in PI AF, it will display the data.

 

I believe the issue in PI Vision stems from the fact that it is essentially trying to look at the value "right now". But since there are only values at 2025-04-01 12:00:00AM and 2025-05-01 12:00:00AM it causes a "No Data" error. My solution to this was to change the timeframe in PI Vision from 8h to 1mo to include the period at which there is a data point. However, this results in the same "No Data" error.

 

My next train of thought was to create an analysis of this custom data reference attribute. It's purpose is to just write the attribute to a PI Point at a set interval, say every day. This works upon first glance, and when I "evaluate" in the Analyses tab it outputs the 1000m3 value. But when I go to the PI Point it returns "Calc Failed" as the value and states "System Digital State" as the status. I suppose this makes sense since it is running into the same issue as PI Vision, it is trying to find the value "right now" but there is only data at 2025-04-01 12:00:00AM and 2025-05-01 12:00:00AM, so it gets "No Data", resulting in "Calc Failed".

 

After this I decided I would just backfill the data so that the 2025-05-01 12:00:00AM value of 1000m3 could be read for "right now" and it could start outputting the value to the PI Point. However, when I go to backfill, I get the error: "Error(s) in retrieving data for input attributes. [Attribute]: Login failed for user [usercredentials]". This is strange because I am an admin and the [usercredentials] it uses do not match my account [mfitzowich]. This then creates a new issue which involves DBAs and SQL server access, etc. Something that I suppose could be fixed to resolve this issue, but I would rather keep the solution native to PI AF or PI Vision.

 

I am wondering if anyone has any experience with custom data references (SQLServer) that uses a SQL query to write to an attribute?

 

I am also wondering if this is just a current issue and if once the custom data reference reads the 2025-06-01 12:00:00AM on June 1, 2025 the Analysis I made will have data to read and ultimately write to a PI Point.

 

Attached are screenshots of:

-Error in PI Vision

-'Time Series Data' in PI AF

-Equivalent SQL query output in database


image.png

image_1.png

image_2.png
Here is my custom data reference (I have used generic names in some places to retain sensitive information).

DataTable:[schema].[table];
ValueColumn:[BudgetVolume];
TimestampColumn:[TransactionDate];
Server:[server_name]
Database:[database_name];
WhereClause:[Scenario] = 'base' AND [AS_LocationCode]='Facility' AND [Category] = 'volume' AND [Account] = 'account#';
SourceTimeZone:[Mountain Standard Time];
ExpectedDataType:[Double];
IntegratedSecurity:[SSPI];
ConnectionTimeout:[10];

 

One final note: PI AF’s expression limitations make it difficult to filter this SQL data further without modifying the source. I’d prefer to keep all processing within PI rather than create new SQL views if at all possible.

 

Any insight would be helpful.

  • 0

    I jumped over to the Customer Portal and issued a search on: "PI Vision custom data reference". KB 42702 may be of interest to you.

     

    Full title is: A custom data reference attribute shows "No Data" in PI Vision and returns error: Attempting to connect to PI AF Server '<AF Server Name>' as '<Domain/End User>' instead of original user '<PIVisionServiceAppPool Account>'

     

    Cause of problem:

    PI Vision 2020 and later handles Custom Data References differently to the standard built-in AF Data References when retrieving data and requires Kerberos delegation of the client user to AF Server. This can result in the above issue if the Custom Data Reference does not take into account the change in user context from the PI Vision Service Account to the impersonated end user when making requests.

     

     

  • 0 in reply to Rick_Davin_3.0

    Hi Rick,

     

    Thanks for the reply. Reading the documentation I understand why the error is occurring now. That being said, I'm wondering what the best solution moving forward would be.

     

    The documentation states:

    "(*) Settings to Disable Impersonation: We do not recommendation disabling end user impersonation for these features as they were implemented as a security feature."

    So this rules out changing Kerberos delegation for the custom data reference.

     

    A second option would be granting the 'pisrvuser' account access to the SQL server so that the PI Point Analysis can be backfilled.

     

    A third option would be creating a SQL view that posts the data more frequently than once a month (say hourly) so that the Analysis has data to read and run the calculations on.

     

    I think these options should be last resort though. I want to keep the solution native to PI.

     

    I'm wondering if I have my Analysis as this:

    PrevVal('Monthly Water Volume In - 704030', '*')

    With a periodic schedule of 1h.

    If come 2025-06-01 12:00:00AM when a new data point is written to the custom data reference if this Analysis will start working and 'Calc Failed' will no longer show up, since it actually has a data point to reference for PrevVal.

     

    Since this will take to the end of the month to confirm, I am wondering if you are able to confirm that this will work, or provide a solution in the interim to confirm this.