-
Re: Problem with backfilling from SQL table with old data
Lal_Babu_ShaikOct 11, 2018 12:01 PM (in response to Tomas Stark)
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
-
Re: Problem with backfilling from SQL table with old data
sraposoOct 11, 2018 2:20 PM (in response to Tomas Stark)
1 of 1 people found this helpfulHi 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-
Re: Problem with backfilling from SQL table with old data
skwanOct 11, 2018 10:58 PM (in response to sraposo)
1 of 1 people found this helpfulHow 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
-
Re: Problem with backfilling from SQL table with old data
Tomas StarkOct 12, 2018 12:43 PM (in response to skwan)
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
-
Re: Problem with backfilling from SQL table with old data
bbregenzerOct 23, 2018 10:37 PM (in response to Tomas Stark)
1 of 1 people found this helpfulHi, 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.
-
Re: Problem with backfilling from SQL table with old data
Tomas StarkOct 25, 2018 11:42 AM (in response to bbregenzer)
Thank you, Brent!
That did the trick.
Regards,
Tomas
-
-
-
-