Recommendations on appending or linking historical data into a new dataset (1 Viewer)

[Danick]

I have an application that pulls data from an SAP server for analyis. I can pull as many years of data I'd like but the more data I pull, the longer it takes to download and it sometimes crashes. I'd still like to have at least 5 years of historic data in the analysis but don't want to pull it into my application every time since that data will never change.

Ideally, I'd like to download about 5 years of data only once and keep it in an archive table (ie Jan 1st 2020 to Dec 31st 2025). Then use the application normally to pull in just the time period from Jan 1st 2026 to the current date and then append or link the archived table with the new dataset. Of course the dates would have to be adjusted every year.

Any suggestions would be appreciated.

Thanks

 

[RonPaii]

Can't most of the analysis be done at the server using views, stored procedures or possibly a pass though query?

 

[The_Doc_Man]

In summary, you have archival data and more current data. If they are all the same format, they belong in the same table. As long as the dates are in the records you pull down, there should be no barrier to mixing the old and new data together. How much selectivity do you have in what you ask of the SAP server? If you can download a year of "archival" data plus a year of new data, you would be able to develop & test stuff you want to do. Then, when you start seeing your result, you can ask that SAP server for older stuff. If you do it a year at the time (vs. the whole 5 year span), crashing becomes less probable and you can take your time.

Therefore, in response to your direct question, I would vote for APPEND vs. LINK.

 

[DHookom]

Can't most of the analysis be done at the server using views, stored procedures or possibly a pass though query?

I agree with the pass-through query since these are typically very efficient. There was no mention of the number of records and fields. Also, are all fields required in the reporting?

 

[tvanstiphout]

since that data will never change

Then pull it ONCE. Something like:
insert into myLocalTable select * from mySapTable where myDateField > DMax(myDateField, myLocalTable)

 

[Ken Sheridan]

You can easily restrict the rows inserted into the local table by restricting the INSERT INTO statement by means of the NOT EXISTS predicate, so there would be no need to specify dates. This is more reliable that relying on the dates as it would also insert any rows with dates prior to the latest date in the local table which might have crept into the SAP table since the last time the rows were inserted into the local table. The INSERT INTO statement can be executed at any time to bring your local table up to date. Appropriate indexing in both tables is essential to good performance of course.