Visokio website     Downloads     Video tutorials     KnowledgeBase  
Demo: Preserve local edits when refreshing from source? - Visokio Forums
Demo: Preserve local edits when refreshing from source?
  • pbalfe July 5, 2013 9:07AM
    Apologies if this has been asked - I had a search through the Forums and could not find it.

    At a high level - I have an input from a DB with some filters set up for calls for a service desk. I use the Field Organiser to add in 4 additional fileds - These will be Y/N tags for quality of certain fields in the DB. Loads into Omniscope no problem.
    I then go into Omniscope and see all the data and the new fields. What I do then is get someone to look at a Table View and add a Y/N into the new columns. So far so good. I can report on them as usual.

    But when I next do a refresh from the source data - naturally the information I added in Omniscope is not there and so everything is overwritten. What I want to do is find a way to 'protect' the data I add in Omniscope from not being over written... but cannot find out a setting of filter on how to do this? Any advice?
  • pbalfe July 5, 2013 9:15AM
    As an additon to this - I can't use the export back to source as I do not want to add the changes we made to the source DB. Also I tried to create a snap shot and use this as my new source - but then when my original DB updates I have no way of adding int he new data. :(
  •     tjbate July 5, 2013 9:51AM
    Peter - This is a common issue, and exists in spreadsheets as well, not just Omniscope. The solution involves 'shadowing' the remote data source to avoid importing data you know has already been locally edited and not changed, and using Merge/Join blocks to detect and channel the already-edited valid fields, and the new/changed raw incoming fields to different files for editing/saving, then finally appending the edited versions of the new/changed records/rows to the master 'Catalogue' file of corrected data.

    If you have some control over your database source, try to write the import query loading the data so as to avoid re-importing 'old' records that you have certainly already corrected or enriched and that have not changed. If you have no control over the query, and are being given an entire 'dump' each time, you can use a 'timeslicing' Source IOK file that 'shadows' the remote data source, imports it periodically, but applies a Validate data block which you can manage to avoid importing data you know has not changed since you last edited the files.

    You also need to have some kind of unique identifier in each row to enable you to use a Merge/Join block on both the Edited and Unedited IOK files, channelling the matches to use only the edited fields for the master file, ignoring the incoming 'raw' fields from the Unedited file.

    The next step, similar to what you have done, is add a [Flag] field in the incoming data, essentially with at least two values, 'Edited' and 'Unedited'. You can automatically populate all incoming new rows of raw data with the Flag value 'Unedited' by default.

    If you repeat the multi-file workflow sequence illustrated below every day, only recent likely-unedited records should come in from the source database, and only records you have not yet edited will be channelled off to the manual editing file, and once you open, edit and save that file, and re-open the EditedMasterCatalogue file, the changed records you have just edited will be automatically imported and appended to the bottom of the EditedMasterCatalogue file, ready for the next cycle.

    As so often in Omniscope, a multi-IOK file 'workflow' solution is the answer...
  • pbalfe July 5, 2013 10:12AM
    Hi there...Many thanks for the text above - seems what I wanted is not so simple at all. Is it possible to get an example of this so I can see the structure as I unfortunately got a bit lost in trying to work out the flow of information from the above.

    Many thanks again
  •     tjbate July 7, 2013 2:05PM
    Peter - Attached is a multi-file workflow providing a general solution to this common problem. Download and unzip this folder with all files in the same directory. There are 4 files:
    1.) IncomingRawDataSource.xlsx - a spreadsheet which simulates a remote database where new records with raw values are being added every day, and 'Ancient' records can become new records at any time when the Last Changed timestamp changes due to a changed simulate these changes by making manual changes to the spreadsheet following the existing pattern, then saving the spreadsheet.
    2.) IncomingRawDataValidated.iok - an Omniscope file that applies validation rules to the remote data source, to avoid always importing rows/records that do not need to be manually reviewed or edited, because they have not changed and the valid, edited values are already in the local Omniscope Master Catalogue file.
    3.) EditedMasterCatalogueFile.iok - an Omniscope file used to both check incoming data to see if it has already been edited, and later to append manually edited data coming from another file to the updated master copy of this file.
    4.) ChangedRowstoEditandSave.iok - an Omniscope working file which should contain only new and changed rows for the remote data source, which may need to be manually edited. If empty, it is because you have not added any new rows and/or changed any timestamps and flags in the spreadsheet that is simulating activity in the remote database.

    In the workflow, these files are opened, refreshed and saved in the following sequence:

    1 (make edits and save) > 2 (save)> 3 (save)> 4 (make edits/change flags, save) > then open 3 repeat as necessary

    Each time you run the sequence, archive 'timeslice' files are also saved...the contents of the EditedMasterCatalogueFile.iok can also be output as a database table for creating an internal relational data warehouse accessible by other applications, not just Omniscope.


It looks like you're new here. If you want to get involved, click one of these buttons!

Sign In Apply for Membership