I'm struggling to write a script that will help me check for new data in my data source.
My setup currently runs every half an hour to refresh data come hell or high water (I would like this to be a shorter period ideally. High/immediate data availability is a requirement of this system). This is obviously resource intensive, hammers the database as well as my QV server. The job takes about 6 minutes to run. I have Publisher set up and the completion of my QVD creator script triggers my application rebuilding. There is clearly no point in all this running on the dot every half an hour if there is no new data.
What I require is the following:
- My QVD creator script checks a field in the database (we have audit logs noting when data has changed)
- This is compared to a field saved into a QVD used to store the last load.
- If there is more recent data, run the script to import the new data, otherwise exit the whole job gracefully, including the application refresh
NB this is not an incremental load, although similar. The data is replaced completely rather than incrementally changed
The bit I'm struggling with here is the syntax of my If statement - how to compare the different dates, how to handle dates in variables etc.
Thanks for your reply. In truth I've been deliberately avoiding using incremental load due to the complexity of the underlying data - the main data souce is an Oracle database containing 20 materialised views built on >130 tables, and propagating the modification date to that lot is no mean task! It's probably time to bite the bullet and finally implement incremental load... Can anybody help me with these follow-on questions?
As everything hangs off a single table at the top of my data hierarchy, is there a efficient way to avoid having to attach a modified date to every table?
My second question above isn't answered by any of the documentation I've seen about implementing incremental load. If no changes are made to the tables, what's the best way to prevent execution of the triggered job to build my app? Throw an "exception" somehow to bring down the job?