Omniscope can be part of broad data integration, management, reporting, presentation and publishing solutions. Activated editions of Omniscope can connect to and refresh from data files and repositories such as transactional database tables, data warehouses, analytic data structures, etc. via standard protocols such as ODBC and JDBC. Omniscope's direct database connection can import the results of any SQL statement that returns a table. Omniscope also contains a non-SQL wizard that enables anyone to perform merges and joins to assemble and import a flat tabular structure, and these operations are remembered and re-executed on file open or refresh.
Omniscope .IOK/.IOM files can have other larger .IOK/.IOM files as their linked sources and can refresh on opening, as well as auto-refresh from these linked source files. This means that a set of large, entity-level (e.g. people and all things joined to people, places and all things joined to places, things and all things joined to things) Omniscope files automatically refreshing from the data warehouse can act as 'datamarts' with many 'child' .IOK/.IOM files each having the central .IOK/IOM data mart file(s) as their linked source. This architecture maintains a single data source in the database, yet eliminates the 'SQL bottleneck' that arises from the need to write and maintain a proliferation of SQL queries tied to ever-changing reports. Using Omniscope, end users themselves can open/assemble the source files they need for a given report or analysis. Once their file is configured, it will auto-refresh not directly from the database but rather via the automatically-refreshed .IOK/.IOM data mart files on the server. No direct database access or 'retail' query loads, and no SQL, or other types of commands required of the user.
Below we discuss data import and refresh options including importing/refreshing data from delimited or XML-tagged data files or direct connections to relational transactional and analytical databases. Web integration options are discussed here [1]. Omniscope also supports an outside browser option [2]that allows menus of multiple files to be browsed and selected from within Omniscope before any .IOK file is opened.
Omniscope files running in activated editions remember their linked sources, and refresh their data from the linked source in various configurable ways:
Note: free Viewers are not activated and will only open existing Omniscope .IOK files, and will not check linked sources for updates/refresh. Only activated Omniscopes will check linked sources for updates/refresh, either on opening, or via real-time auto-refresh.
A given solution architecture can employ multiple options for refreshing Omniscope files on always-on servers and from there to distibuted local or remote client installations. For example, a leading asset manager uses the Server Edition Scheduler to refresh a server-based Omniscope Source.IOK file every 30 seconds. About 15 Omniscope Desktops use the Background Auto-Refresh option to keep an open connection to the Source.IOK file, and refresh the data continuously...effectively a (near) real-time update solution.
The Data > Background Auto refresh feature enables any number of distributed copies to import and display continuously-changing data 'broadcast' from a specified Source .IOK file running on an always-on server or another desktop installation of Omniscope. Auto-refresh can be implemented between any Master Report Source .IOK file and any number of distributed copies of the same file over either a local network or the open web.
If the Master Report IOK Source is 'owned' by a Server/Publisher Edition, then the IOK files are empowered to background auto-refresh in free Viewers, as well as activated desktops. If the
The discussion below covers first the distributed client-side Omniscope User file configuration, then discusses some of the options for configuring the server-side Source .IOK file refresh arrangements.
The distributed copies of the Report.IOK files on the client desktops are modified copies of their Master Report Source.IOK file, and the copies sart with the same background auto refresh settngs as those configured in the Master Report. These settings inform each Omniscope installation with the Report.IOK file open to continuously montitor for changes to the data in the Master Report Source.IOK file, which must be open somewhere accessible. When a change occurs to the data set in the Master Report Source.IOK file (such as another user, or the server, updating and saving it), each open distributed copy automatically reloads the latest data set from the Source.IOK file.
Using auto-refresh is not the same as delivering and re-opening an updated .IOK file. Auto refresh only loads updated data in the already open file. User views and preferences are not be reloaded to avoid interrupting the Users' current workflow in Omniscope. Whenever the Users' Omniscope becomes idle (i.e. they stop using Omniscope for a few seconds) the Users' current filtered views will be updated to show the latest data.
To set up or modify the User file settings, choose Data > Automatic refresh. This reveals the Auto refresh drop-down menu on the Main Toolbar and starts the polling process.
When an update is available, this button will appear highlighted with the text 'Update available pending'. If you are busy using Omniscope and an update is retrieved, clicking Display latest update will display the newest available data. Clicking the little drop-down arrow shows a menu which allows you to configure auto refresh options and which indicates status. From this menu, you can start and stop the automatic refresh checks by ticking Polling enabled. You can also turn a sound on/off when an update becomes available, and suppress error reporting.
Selecting Refresh by reload is the behaviour outlined above; alternatively, if users have access to the source database, you can use Refresh from source which will continuously hit the data source for the current data.
Timings allows you to customise the rate at which the User Omniscope instances check for updates and other timings. You can configure the interval between update checks (this is how often Omniscope looks to see if the file has changed; if so, it is immediately retrieved). You can configure the interval between a failed update/retrieval and a retry, as well as the number of retries permitted (errors are only reported if all retries fail).
You can also add a randomisation to these timings, if desired, allowing you to scatter network bandwidth and file access and reduce any congestion that might otherwise occur if all clients attempt to update at the same time.
Errors can occur when using automatic refresh if the server is in the middle of updating the file when Omniscope attempts to retrieve it. We have built in several measures to address this problem. IOK files are locked, which, for supported operating and file systems, prevents two processes accessing the file at the same time. Automatic retry-on-error is also configurable, as are randomised cycle timings.
The server side of the live data automatic refresh functionality can be configured many different ways. Below is a general approach based on Omniscope Enterprise Edition running on an always-on server with the Scheduler enabled and with direct access to the source database.
On the server are two .IOK files:
Typically, there will be scheduled processes that combine data from different sources and update a central database from which the Source file reporting views/tables are drawn. Whenever the Source database view(s)/table(s) are updated, the Omniscope server installation is invoked to update/refresh the Source .IOK file from the database.
Scheduling can be done in various ways, and at any interval...we have client installations that refresh the Source file on a 30-second cycle time. Auto-refresh to the User file clients does not have to be on the same cycle time as the refresh of the Source .IOK file.
Assuming you wish to use the Scheduler included in Omniscope Enterprise, you would start the Scheduler from the Visokio Program folder or Settings > Enterprise > Scheduler (Enterprise Edition only). More on using the Scheduler [4].
Using the menus available in the Scheduler, the Omniscope server task is represented as an XML Action, usually a Refresh from Source operation. This action opens the Master .IOK file and does the following:
The Visokio Scheduler can now be used in a 'non-forked' mode. This won't be applicable if you are using your own scheduling system; however, if you do choose to use the Scheduler, you can now choose non-forked task execution.
In the Scheduler settings dialog, Fork scheduled execution, when un-ticked, allows scheduled tasks to execute in the same Java VM process as the Omniscope scheduling loop. This avoids the JVM startup time which can save over 10 seconds. Additionally a timings option has been added to "chain action" and "file action" allowing you to analyse performance. For more information, see Using the Scheduler [4]
Warning: The Scheduler must always be running for this to work. You can now configure this as a Windows service [5].
In case you want to use your own external trigger, rather than the time-based Scheduler, to execute XML Actions scripts you have defined, below we describe how to author your own XML Actions file and deliver it to the Scheduler for immediate (rather than time-based) execution.
You can edit or create XML actions using Settings > Enterprise > Edit Enterprise Action Descriptor. You can also edit the XML directly, although this is not recommended in most cases as the Visokio user interface for editing has guidance information.
The Omniscope Scheduler now includes other processes as well as the scheduling loop. One of these is the new "watch folder" process. This process watches a folder continuously while the Scheduler is running. To execute an XML action file on demand, copy it into the watch folder, typically:
C:\Program Files\Visokio Omniscope\scheduler\watch
Assuming the Scheduler is running, this file will immediately be executed then deleted. After testing, refer to the Scheduler log to check for errors: 'http://localhost:24679'/
If you are repeatedly testing changes to a file, be careful to copy and not move the XML file to the watch folder, as it is deleted after. Also be sure to refresh the web browser showing the log frequently to check for errors.
Note: trigger files dropped into the watch folder are executed in sequence and in no specific order, using the same JVM as the Scheduler. It is not currently possible to execute multiple tasks in parallel.
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.
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.
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.
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.
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 [3].
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.
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.
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.
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).
Links:
[1] http://kb.visokio.com/kb/web-integration
[2] http://kb.visokio.com/node/363
[3] http://kb.visokio.com/node/370
[4] http://kb.visokio.com/node/139
[5] http://kb.visokio.com/node/325