6 Replies Latest reply on Oct 25, 2018 11:42 AM by Tomas Stark

Problem with backfilling from SQL table with old data

Tomas Stark Oct 11, 2018 11:40 AM

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

Re: Problem with backfilling from SQL table with old data

Lal_Babu_Shaik Oct 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
Like Show 0 Likes(0)

Actions
Re: Problem with backfilling from SQL table with old data

sraposo Oct 11, 2018 2:20 PM (in response to Tomas Stark)

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
1 of 1 people found this helpful
Like Show 0 Likes(0)

Actions
- Re: Problem with backfilling from SQL table with old data
  
  skwan Oct 11, 2018 10:58 PM (in response to sraposo)
  
  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
  1 of 1 people found this helpful
  
  Like Show 0 Likes(0)
  
  Actions
  - Re: Problem with backfilling from SQL table with old data
    
    Tomas Stark Oct 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
    
    Like Show 0 Likes(0)
    
    Actions
    - Re: Problem with backfilling from SQL table with old data
      
      bbregenzer Oct 23, 2018 10:37 PM (in response to Tomas Stark)
      
      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.
      1 of 1 people found this helpful
      
      Like Show 1 Likes(1)
      
      Actions
      - Re: Problem with backfilling from SQL table with old data
        
        Tomas Stark Oct 25, 2018 11:42 AM (in response to bbregenzer)
        
        Thank you, Brent!
        
        That did the trick.
        
        Regards,
        Tomas
        
        Like Show 0 Likes(0)
        
        Actions

Go to original post