Hi,
we try to backfill data to PI tags from a SQL database. It works fine, but some of the SQL data has timestamps older than 1970-01-01 00:00:00.
We can live with that, but when we try to backfill (or preview), PI SE will not accept the backfill even though we state the Start time to be 1970-01-01.
PI SE should check the Start Time, and if it is 1970-01-01 or later, backfilling should be possible even though there are older data in the SQL database.
Regards,
Tomas
Hi Tomas
Could you please check input attributes timestamp? Looks like input attributes are having timestamp less the minimum value.
KB00605 - How to access data created before 1970
Thanks,
Lal
Hi Tomas,
Are you doing any calculations with Asset Analytics or are you simply trying to retrieve data from SQL and write it to PI Points?
Asset Analytics isn't designed to work with a large amount of time series data from table lookups. The PI Interface for Relational Database (https://techsupport.osisoft.com/Downloads/File/b54a863b-04dc-4a59-bff6-e4d13a54da63) is designed to do this.
There are a few reasons why, the main one is that linked tables are cached within the PI Analysis Service's memory space. Of course large table objects would result in poor performance for table lookups and this could bottleneck the service and affect other analyses.
Thanks,
Seb
How did you configure the Table Lookup? Do you have a Time Column (TC)? Are you interpolating? Are you SQL table values being treated as "stepped"?
As a reminder, please consider the consequences of what you're doing. Looking at your screen shot, you've asked for a Preview of 48 years of data. What is your data density? In the Preview pane, you're trending 17816 days of data. Was that your intention? Perhaps you may consider tightening up the time range in your Preview so that the client doesn't have to retrieve so much data, which would of course giving you a better user experience. (Unless of course you have daily, monthly or yearly data in which case what you're doing would be fine.)
--
Steve Kwan
Hi Steve,
we use a table lookup with a TC column and a data column in the SQL database.
All data is manually entered data with at the most 10 to 20 readings per year, so VERY FEW values.
My initial question was why PI SE doesn't check that we only want data from 1970-01-01 (as shown in the screenshot), instead of giving the error message that there actually is data that is older than that.
Regards,
Tomas
Hi, Tomas.
What is the WHERE clause in your linked table definition? I haven't had a chance to test this but I'm wondering if you could filter out old data with something like WHERE TimeStamp > '1970-01-01 00:00:00' so the the pre-1970 data is never retrieved from the foreign table in the first place. Please let us know what you think.
Thank you, Brent!
That did the trick.
Regards,
Tomas
on Oct 11, 2018
Hi, Tomas.
What is the WHERE clause in your linked table definition? I haven't had a chance to test this but I'm wondering if you could filter out old data with something like WHERE TimeStamp > '1970-01-01 00:00:00' so the the pre-1970 data is never retrieved from the foreign table in the first place. Please let us know what you think.