Data Refresh Options

Keeping data up-to-date in central and remotely-distributed files

Omniscope files include a highly-compressed snapshot of all the data imported. Access to the original linked data source is NOT needed for you or others to view and explore data already imported into an Omniscope file. You can send only the portable, compact Omniscope file to a friend (who does not have access to the original spreadsheet or data files) and they will still be able to explore the full copy of the data in the Omniscope file. 

Omniscope files usually contain data imported from data files or relational database reporting views that change regularly. Omniscope remembers the location and format of the external data imported,  one or more spreadsheet XLS/CSV files, database reporting views exported and saved as a CSV or TSV files, or direct SQL statement that returns a table from relational tables from one (via ODBC) or more (via JDBC) relational databases, or a folder of files or images on an Internet server or your PC. Remembering its 'linked data source' enables Omniscope to refresh the data in IOK files on opening, whenever the source data has been changed.

If the central data source 'data mart' is itself an Omniscope file (which is itself refreshing from back-end data warehouses and other sources) there is an auto-refresh option to keep the data in distributed 'child' Omniscope files refreshed over the local netwrok or open Internet. If the central 'datamart' IOK file and the distributed 'child' IOK files were created with a full Enterprise Publishing licensed install, then both Professionals and free Viewers will be enabled to periodically refresh their remote data sets from the central 'data mart' IOK file from which they were created. If the files were created using an activated Professional (rather than a full Enterprise Server Commercial Publisher), then other activated Professionals with access to the linked source files can auto-refresh, but a new IOK file needs to be created and distributed to users of the free Viewer whenever the data changes.

Refresh from linked data sources on file opening:

Upon opening, IOK files automatically detect (from the time stamps) that their data source(s) contain changed data. Depending on file refresh settings (see File > Save As dialog) the file either automatically refresh the data, or may ask the user if he/she want to refresh the data from source before displaying the Omniscope file. Moving the linked data source file(s) location relative to the Omniscope file will break the link. You can delete or restore any Omniscope file's link to external data sources at any time. When opened, the IOK file will automatically detect that its data source has been changed and will ask you if you want to refresh from the new source before opening. This refresh affects data only, not the overall configuration of the file.

Refresh direct from relational databases:

If Omniscope is unable to automatically detect changes to the external linked data source (for example, if the source is a database table being accessed via ODBC or JDBC protocols), you can configure the file (via File > Save As) to always refresh on opening, or just manually refresh the file after opening by choosing Data > Refresh from source.  If you have added Connector-based fields such as Bloomberg fields to your data set, and have access to Bloomberg on your PC, you can use Data > Refresh Bloomberg data to refresh these fields at any time. This refresh affects data only, not the overall configuration of the file.

Warning: Omniscope does not currently support partial refresh of data tables. Any Omniscope data (not Formula) columns not contained in the external linked source will be overwritten or lost if the Omniscope file is refreshed (Formula columns are re-calculated). If you have added data columns from sources other than the linked source, i.e. maintain a lot of local data in your working Omniscope file such as columns of notes, or pasted copies of e-mail correspondence, or references to pictures or map coordinate references which are not in the linked data source, you must divide your working IOK file into two separate Omniscope files prior to refreshing only the columns originating from the linked source. This is easy to do. Once you have refreshed the Omniscope file containing only data columns from the external source, you can join both parts of the working file back together again.

Although any number of distributed Omniscope IOK files can refresh directly from the same relational database reporting views, all users need access rights for these views, and if the number of users becomes large relative to the load on the database, it may be better to link the database to one central copy of the Omniscope file, and let distributed users refresh their copies remotely from that central IOK file, rather than directly from the database.  In this configuration, the central Omniscope file is acting as a more scalable, visual and configurable 'datamart', not relational/SQL-compliant, but a 'datamart' nonetheless.

Linking Omniscope files to other Omniscope files:

In addition to other types of data files, Omniscope IOK files can be configured with other IOK files (including a copy of itself) as linked data sources. This option has the advantage that only the updated data set can be delivered to the distributed User Omniscopes, without changing what may have become personalised Omniscope configuration(s) of Views, Filters, named Queries and Report Pages. A large-scale Source Omniscope file running on an always-on server can serve as a 'data mart', providing the data source for a wide range of different end-user Omniscope files refreshing from it. The central 'data mart' IOK file should in turn be refreshing itself via continuous, always-on access to reporting views drawn from your relational data warehouse or analytical 'staging' database.

Automatic refresh from live data sources

You can now configure any number of Omniscope Desktops to automatically refresh their file data from a Source IOK file running on a server elsewhere, over your local network or across the web. If the Source IOK file is in turn refreshing itself directly from the original source database view/table(s), this configuration provides a scalable, near real-time live data refresh solution. While distributed Users are working on their files, auto-refresh awaits a pause in their activity. Each time Users re-open their copy of the auto-refresh linked file, it either refreshes only the data, or returns to the file to the default opening configuration of the Source file, depending on settings. To allow Users to maintain their own individualised configurations and import only the latest data when working, use the Refresh from linked data source. Alternatively, auto-refresh can be configured to Refresh by reloading IOK file which returns the distributed copies to the default configuration on refresh.

For more information, see the KnowledgeBase sections on Auto-Refresh.

I can't refresh from source!

If your linked data source has moved or is inaccessible, Omniscope will be unable to refresh from source. If you imported from a source file such as a .CSV or Excel .XLS spreadsheet, the linked source file must always remain in the same relative location compared to the IOK file. If you have moved either file independently, you can re-establish (re-locate) the source file by choosing Data > Edit source. If you imported directly from a database, check that the database server is started, and is accessible across your network if it is running on a different PC or server.

Unlinking/Re-linking to source data files

At any time, you can deliberately remove the persistent link between an IOK file and its linked data source by choosing Unlink from source from the File > Export menu, or by un-ticking the Linked to source check box when saving the IOK file using the File > Save as dialog.

Also, any time you are prompted to refresh from source or save back to source, there is always the option to click Unlink or Skip to prevent overwriting the source file. You can also reconfigure the linked source file at any time by choosing Data > Edit source which will preserve any field conversions and formulae applied to the previous source.

Parameterised refresh

Option 1: using the free Viewer, and client-side filtering

Option 2: using Desktop edition, and server-side SQL filtering

Making changes to source data

Exporting changes back to linked data files - be careful!

Omniscope allows you to edit cells in the Table View, right clicking on the cell to edit cell data, and right-clicking row headers to add/remove records (rows). You can also add and remove fields (columns) using the Data > Manage Fields. When you have finished your edits, save your work in the IOK file as usual by choosing File > Save. If you originally imported from a linked data file (such as a .CSV or .XLS file), you may be asked if you also want to save back to source - i.e. to save your changes back to the linked source file. At any time, you can also manually save your edits back to the source file by choosing File > Export > Export back to source. {Professional & Enterprise Editions only}

Warning: Whenever you choose to save back to source the entire source data file will be completely overwritten with your latest edits from the the Omniscope file. If you have deleted fields, these will also be deleted in the source file (therefore usually better to just hide them). If your source file is an Excel XLS file, all formatting and formulae will be deleted as the export is only for cell data. If you have a multi-tab spreadsheet, all the other tabs will be lost.

Exporting changes directly back to relational databases - be very careful!

Omniscope does not support saving changed data sets directly back into to database tables. If you need to save changes back to a database, collect your changed records in a Named Query (such as 'Errors for correction') then export a .CSV or .XML file containing the changed records, and perhaps a 'Comments' column explaining the changes. You or your database administrator can then use a database utility to import your changes directly into the transactional database table(s).