Please see menu at left...
=========================================
Omniscope can import data from files in supported file formats [1], or via direct connections to reporting view(s)/table(s) in relational databases using standard protocols such as ODBC and JDBC. Connectors can be developed for specific applications when performance or other special requirements do not permit standard protocols to be used.
Omniscope can import data exported from relational database tables and other sources if they have been formatted in a supported file format [1], such as Comma Separated Value (.CSV) flat files, in the flavour exported by Excel (without 65,000 record limit).
Omniscope can also import XML data files, in the Visokio XML data schema [2] intended for flat file data transmission in XML. If you already have a different XML output schema implemented, it is easy to apply XSL [3] transforms on import.
Refreshed source data files can be provided either as files on a local disk or network share, or as HTTP or FTP URLs to intra-/extranet servers or public websites (the latter requiring any necessary proxy servers to be configured in Omniscope). For more information see Proxy Settings [4].
Omniscope can import from any ODBC or JDBC [5] data source. This typically means a database, although it is possible to implement an ODBC or JDBC interface to a spreadsheet, or a proprietary system. It is also possible to develop bespoke Connectors to optimise the update/refresh cycle and deal with other limitations of ODBC and JDBC-based connections. A SQL statement returning a table can be embedded in the Omniscope database connection wizard and will be re-evaluated on
A: If your 'data mart' source .IOK/.IOM files are the linked data source for users' child .IOK/.IOM files, users can customise and re-configure their Omniscope working/reporting files however they wish. Whenever users open their file, only the data (not the tab configurations or query/filter settings will be refreshed, leaving their individually-personalised view configurations, filter settings and tabs settings unchanged.
In addition to opening files accessible to the local machine via the File > Open File dialog, Omniscope can also open displaying a menu of available source/reporting files displayed in the external Outside Browser view. This view is available with no specific .IOK/.IOM file selected, and is intended to provide a web-page based menu of files for users to select from while in Omniscope. This option is not available from the free Viewer. More detail on using the Outside Browser [6].
Visokio applications are standalone installed applications. Visokio does not currently provide API type integration options for embedding Omniscope as a visual module within a larger application. To integrate with a proprietary data system, you must consider Omniscope to be standalone clients with various options for refreshing the data in batches (even at 1 second intervals) as discussed above.
*Genuinely real-time data, updating continuously, is not directly possible in Omniscope, for usability and architectural reasons. Instead, data updates are received and displayed in batches when the application is idle or the user explicitly requests. This can be done as often as every second, depending on bandwidth, dataset size & update efficiency. For all but the very strictest real-time requirements, this near-real time live approximation is more than 'live' enough.
Importing data from delimited data files: Omniscope data sources include delimited tabular data that has been exported in a supported data file format from relational (SQL-compliant) database transactional tables or reporting views, analytical OLAP 'cubes' and other business intelligence data repositories; data warehouses, 'clouds' and 'data marts', etc.
Importing data from XML files: Omniscope also imports and exports tagged data in XML format, assuming it is (or can be transformed on the fly using XSL) a tabular layout using the very simple Visokio XML schema. The Visokio Omniscope XML Schema is essentially the equivalent of a CSV file expressed in XML. See XML schema [2]for more details.
Opening other application data file formats: Omniscope either currently supports (or plans to support) almost all of the most appropriate data import and export file formats, whether used by desktop-installed applications, such as Microsoft Office/Excel, Corel WordPerfect X3/4, or open-source files from suites such as Open Office, IBM Lotus Symphony (and others) or by online, remote document hosting services such as Google Docs & Spreadsheets (with live synchronisation of edits), Microsoft Office Live, etc.
Omniscope Connectors: A growing number of direct and web-services-based Connectors are available to import and synchronise data to/from remote 'cloud' sources.
Note: Omniscope can also import the results of any SQL statement that returns a table directly from relational databases. Importing data using either ODBC (one database, multiple tables) or JDBC (multiple databases, multiple tables) standard protocols is configurable using the Omniscope File > Connect to database dialogue as explained in the Database Connections [7] section. Direct connections to MDX-compliant multi-dimensional data sources will be available soon.
Tabular data files usually use a special character as a delimiter, a way of separating values and associated headers from the subsequent values.
Omniscope supports the most common implementation of .CSV, which is more a de-facto than a formal standard. Because CSV is not a formally-documented standard, some databases will export CSV files with different conventions from those used in spreadsheets, for example the treatment of single double quotes in text. If you are having trouble getting all records imported from a CSV file received direct from a database, try opening and saving the file in a spreadsheet, then importing the spreadsheet file into Omniscope.
Omniscope recognises and opens .TSV files.
Omniscope imports and exports column lists of values from a field (column) as .TXT files. Note that some files with a .TXT extension are actually tables with a tab delimiter. You must change the extension on these files to .TSV in order to open them as tables. (see .TSV files above)
These files tend to be used in countries where the comma is used in numeric values as a decimal separator. These files may have .CSV or sometimes .TXT extensions. Viewing the first lines of the file in a text editor like Windows WordPad will show if the delimiter being used is a semi-colon. If you find that that your .CSV file is a semi-colon delimited file, you must change the file extension to .CSV (if it is not already). Omniscope will then permit you to import the data by setting the delimiter to semi-colon. To do this, use File > Open file to point at the .CSV file. Be sure to tick the box at lower right 'Customise data import behaviour'. This will display a list of additional options, including the option to change the delimiter character from the default comma to, in this case, a semi-colon.
Some files with .TXT and other file extensions actually use other characters as delimiters. For example, a common SAP and SAS data file delimiter is the | (pipe) character. To open a file with an unusual delimiter, first change the file extension to .CSV, then use File > Open file to point at the file. Be sure to tick the box at lower right 'Customise data import behaviour'. This will display a list of additional options, including the option to change the delimiter character from the default comma to whatever character is used in your file.
Omniscope inter-operates with Excel files for import & export, provided that the copy of Excel is installed locally on the same machine. Excel interoperability is currently on the Windows operating system only. Omniscope interoperability with Excel is constantly being improved, and will soon be available on the Mac operating system as well. For information regarding support for Flash DataPlayers by Microsoft Office documents/applications, please see the DataPlayer document compatibility list)
Omniscope supports Quattro Pro .QPW v9X3 & X4 files. Using Quattro Pro, Lotus 1-2-3 and Borland database files (Paradox & dBase) can be opened, saved as .QPW or other supported file formats on this page, and imported into Omniscope. For information regarding support for Flash DataPlayers by Corel/WordPerfect documents/applications, please see the DataPlayer document compatibility list.
Omniscope currently does not, but will soon support the Open Docs standard for tabular data files. There is a plug in to upload Open Office documents directly from OO.org into Google Docs, the spreadsheet of which is supported in Omniscope (see below). For information regarding support for Flash DataPlayers by Open Docs documents/applications, please see the DataPlayer document compatibility list.
Online document/office suite services are sometimes used to enable collaboration on the assembly, scrubbing and approval of data and image sets, together with associated web links, map coordinates, etc. Importing online data sets into Omniscope can greatly assist this process, especially during the scrubbing and approvals phase. Omniscope can also be configured to refresh from these data sources, providing live dashboards that can be deployed as Omniscope files and exportable DataPlayers.
Support for collaborative Google Docs & Spreadsheets is currently supported. Depending on demand, support for other emerging online application suites may also be added.
Omniscope is continuously being expanded to import many other data file types.
Apache .LOG Web Access log files
Omniscope Professional & Enterprise will open Apache Web Access .LOG files directly for visualisation, analysis and reporting.
If you would like Omniscope to open/import or export other file formats associated with applications you control, please contact us [8].
This section contains articles covering various aspects of importing data directly into Visokio applications from SQL-compliant relational database tables/reporting views using the Database Connection dialog and standard ODBC or JDBC protocols. Omniscope can import the results of any SQL statement that returns a single, flat table using either ODBC (one database, multiple tables) or JDBC (multiple databases, multiple tables) protocols. If you have large datasets, the JDBC option will perform better with most relational databases.
Connecting to Relational Databases [9] - contains various examples of making connections to transactional tables or (preferably) reporting views generated by relational databases from various different vendors.
MS Access ODBC [10]- examples for 2007,
MS SQL Server JDBC [11] - examples for 2008, 2005 and 2000 versions
Oracle 10g Thin JDBC [12] - example for recent versions of Oracle
Oracle 8.1 JDBC [13] - example for older versions of Oracle
MySQL 5.1 JDBC [14] - example for MySQL
Generic JDBC [15] - general example that should work with most datbase vendors that support JDBC connections
Connection-caching [16] - database connection caching is supported by Omniscope. This is mainly of use in Enterprise server installations, and should be ignored for typical desktop use as it will provide little benefit.
DSN-less ODBC [17] - describes a method of connecting/refreshing multiple machines on a network without the administrative overhead of configuring the data source on each machine.
ODBC & JDBC [18] - two standard protocols for connecting to relational databases, these options are sufficient for connecting to SQL-based transactional tables or reporting views. Some data sources which do not support these standards, for example MDX-based multi-dimensional 'cubes'.
Note: Importing and refreshing from delimited data files (including data files exported from relational databases) is covered in the section on importing/exporting to/from data files [1].
Omniscope includes a Database Connection dialogue to help establish direct connections to database tables and reporting views. The dialogue is accessed via the Main Toolbar: File > Connect to database command. Using the dialogue, anyone can import the results of any SQL statement/query that returns a table.
In order to connect directly to a database (rather than importing a delimited .CSV or .TSV data file that has been exported from a database) you need to know the type of database and its connection details. From the Database Connection dialogue, enter the connection details for your database and choose which database table or reporting view you wish to retrieve data from. By default, this will create a persistent linked data source relationship between this database table/reporting view and the .IOK (Omniscope) files you are creating.
If you connect via ODBC, you can access multiple transactional tables, but only one database. If you connect via JDBC, you can retrieve data from multiple tables in multiple databases, and performance will also be better. If you do not know how to write the SQL query that assembles the reporting view you want to import into Omniscope, ask your Database Administrator to add the query in Omniscope. Every subsequent refresh re-creates the reporting view, drawing the latest information from transactional table(s) within the database(s) to create a single, 'flattened' tabular view of the data for scrubbing, analysis and reporting.
ODBC is a technology for connecting to most databases and requires you to set up a named ODBC data source in Windows. This is the approach needed to connect to an Access .MDB database, for example. In Windows XP, for example, go to Control Panel, Administrative tools and choose Data Sources. Click Add, choose the Access driver (or otherwise, depending on your type of database), choose the appropriate .MDB file and enter a suitable Data Source Name.
If you have a database that supports JDBC, or wish to customise the database import behaviour, you can connect via JDBC using a JDBC JAR driver file obtained from your database vendor or 3rd party. This may need to be specific to your database version as well as vendor. You will need to know how to construct the "JDBC URL" for your database. If your database is covered in the examples, follow the instructions on the example page for your database. If your database is not covered in the example list, see the JDBC generic instructions [15].
The Database Connection dialogue sequence of screens allows you to choose JDBC and enter the JDBC JAR driver path such as:
"C:\Program Files\MyDatabase\Java\JDBC.jar", and the JDBC URL such as "jdbc:mydb:products".
Enter the username and password, if necessary, and choose the table or view you want to look at.
On the next sceen, you can customise the SQL statements, allowing you to JOIN multiple tables on the fly. Only change these values if you are familiar with the implementation of SQL for your database vendor and version.
Relational Databases as linked sources
Any Omniscope file serves as a template file for future data refreshed versions of the file. Omniscope template files remember and refresh from their data sources. If the source is a relational database, Omniscope will re-execute the embedded SQL statement and any subsequent merges, joins and aggregations on every refresh.
To save the connection details you have entered into the Database Connection dialogue for subsequent refreshes, save the current file using File > Save. The saved file will contain a (highly-compressed) snapshot of the data as it was when imported, plus all the database connection parameters, including the SQL statement that returns the table you entered into the dialogue. You can configure refresh behavior each time you (as owner) save the file. You can test refreshing from the database specified in the Database Connection dialogue by choosing Data > Refresh from source.
Unlike text-based delimited data file import (such as XLS or CSV), Omniscope when the linked source is a direct connection to a relational database does not automatically inspect and convert data types. Instead, the data type descriptions of the fields in your database are relied upon to determine data type in Omniscope. If your database declares the field "CUST_AGE" as Text ("Char", "String" or "Varchar"), Omniscope will treat it as Text also. To avoid the need for manual changes, you must structure your source database correctly by declaring the field "CUST_AGE" as a numeric field (for example).
At present, relational database connections in Omniscope only go one way, i.e. they only import a copy of the data in the database table or reporting view. You can make edits but cannot submit your changes directly back to the database table automatically. If you plan to edit/scrub data from databases in Omniscope, you will need to agree a way of submitting documented corrections and additions back to the Database Administrator, usually by exporting a .CSV or a human-readable, parse-able .XML corrections file such that the Administrator can use simple tools to import the corrections made in Omniscope back to the source database.
Suggestions: Don't make too many pre-selections in the SQL statement query assembling the reporting view. Errors in fields can result in some records not being included in the reporting view if you are being selective. Try just dumping all records for each major entity (e.g. people and all fields linked to people, places and all fields linked to places, things and all attrributes linked to things etc.) into a few large reporting views extracted from the database(s). Open these views in Omniscope and save them on the server - effectively creating 'data marts' in Omniscope file format. You can then re-name/correct/scrub and filter/hide unwanted data visually and create 'child' Omniscope files which will refresh from the (server-based) 'data mart' Omniscope files, which are in turn automatically refreshing from the data warehouse.
Warning: Preserving changed and merged data: Omniscope does not currently support partial refresh from database tables/views. Refreshing from linked data sources will overwrite all the data in a given Omniscope file with data from the linked source table only. If you have changed data in the Omniscope file manually, or merged/pasted columns of data from other sources, such as spreadsheets or departmental databases, your changes will be lost on file refresh from the linked source. If you are using Omniscope to merge data from multiple sources, keep your merged data and commentary in separate .IOK, .CSV or .XLS 'merge files' to avoid those values being lost every time the main data file is refreshed from a linked source. You can prevent accidental refreshes from source by not ticking the option to maintain the link to source, but this will also remove your database connection parameters from the Omniscope file.
Omniscope now provides direct MS Access support out of the box, which uses ODBC internally to make the connection.
Microsoft Access 2007 and earlier do not support 64-bit ODBC. Support for 64-bit ODBC is available from Access 2010.
The Omniscope Database Connection dialog accessible under File > Connect to database guides you through the connection parameters.
As with SQL Server 2005, the same configuration applies. You will need to have downloaded and installed the latest Microsoft SQL Server JDBC Driver [19].
The same connection settings for SQL Server 2005 apply.
The Driver jar path is different however, you must enter:
"C:\Program Files\Microsoft SQL Server JDBC Driver 2.0\sqljdbc_2.0.1803.100_enu\sqljdbc_2.0\enu\sqljdbc4.jar" or similar (depending on your installation).
Use "Connect to database" as usual, specifying a username and password, choosing a table, and optionally customising the SQL query.
As with SQL Server 2000, your database server must be configured to use mixed mode authentication. The server must also have the TCP/IP network protocol enabled. You will need to have downloaded and installed the Microsoft SQL Server JDBC Driver [19]. Do not use the "sa" administrative login; instead create a dedicated user for the database(s) you need to connect to.
Open the Omniscope Database Connection dialog under File > Connect to database. Choose the generic "JDBC" option on the first page.
There is a specific option for SQL Server 2000. You will need to know the database server details (host name and port) and login details (username and password). At present, due to limitations in the Microsoft JDBC driver, your database must be configured to use Mixed Authentication and not Trusted/Windows Authentication (see here [21] for more information).
A: Try the following known solutions:
Below is a guided example to connecting to an Oracle 10g database using the Oracle Thin JDBC driver.
Database version: Oracle 10g version 10.1.0.2.0
JDBC driver: 'Thin' JDBC driver for Java 1.4.
Database server host name: localhost
Database server port: 1521
Database instance SID: “orcl”
Table or View name in database: TEST
Unless you're using a really old version of Oracle this approach should work. Alternatively, see the Oracle 8.1.7 [23] page.
You will need the Oracle JDBC driver. This is a file called “ojdbc14.jar” and is normally installed by default alongside the database server/tools. Alternatively it can be found in the download archive or installation CD, or on the Oracle website. This is a pure Java driver for connecting to local or remote Oracle databases.
Choose File > Connect to database and the Database Connection dialogue will appear:
Once the Omniscope has finished opening the data, you’ll see a Table, Chart and Pie View, and a set of devices on the right. You can change to different views (visualizations such as Graph and Map) by clicking “Table view”. You can filter data by dragging sliders and clicking checkboxes on the right. The green/red barometer at the top will show you records that have been included or excluded. The Reset button resets all filters to show all records.
It is recommended that you save an IOK file (from the File menu) to save going through the database dialogue again for this particular database table. In future, you can open this IOK file, and optionally refresh data from the database (picking up any updated records).
Omniscope comes with embedded jTDS driver which supprts connectivity to Sybase connectivity.
You do not have to install any drivers for connecting to Sybase.
Column-oriented Sybase databases may not be supported by jTDS driver. In this case you may need to use JDBC (Advanced) option.
Please read SybaseIQ section below for more information.
Also try installing Omniscope on the database server directly, if possible, to rule out networking issues and to ensure you have the correct database connection settings.
Oracle 8.1.7 or similar versions may connect successfully using the same approach as for Oracle 10g [25]. However, here is a tried and tested approach with this older version of Oracle. The process is the same as for Oracle 10g but with the following changes:
You will need the Oracle JDBC driver. This is a file called “classes12.zip” and can be downloaded from Oracle (search for “Oracle JDBC driver” and look for “classes12.zip” for Oracle 8.17). It should be on the Oracle CD and/or in the installation directory.
You will need to have downloaded and installed the latest database driver Connector/J 5.1 [26] includes support for the latest JDBC functionality.
Open the Omniscope Database Connection wizard under File > Connect to database. Choose the generic "JDBC data source (advanced)" option on the first page.
Follow through the rest of the Database Connection wizard as usual, specifying a username and password, choosing a table, and optionally customising the SQL query.
You can set up a direct connection to many types/brands of database which the Database Connection dialogue does not explicitly support. Providing your database vendor offers a JDBC driver or ODBC connectivity, you should be able to use this to allow Omniscope to connect directly to your database.
If you are unable to get this to work, please post the problem on the forums [28].
If you are successful, please let us know what the settings were and where you obtained the JDBC driver from, so we can update our KnowledgeBase and consider adding explicit support for your database vendor to Omniscope.
Database connection caching is supported by Omniscope. This is mainly of use in Server installations, and should be ignored for typical desktop use as it will provide little benefit. Connection caching allows an Omniscope instance to retain and reuse database connection objects, which can improve performance. By default, desktop Omniscope installations cache 3 connections for up to 1 minute after use.
Connection caching has the following options:
Name | Meaning | Default |
visokio.dbcc.maxall | Maximum number of cached connections for any data source | 5 (8*) |
visokio.dbcc.maxper | Maximum number of cached connections for each data source | 3 (5*) |
visokio.dbcc.maxage | Maximum age of cached connections (in milliseconds) - e.g. 5 minutes is 5x60x1000 = 300000 | 60000 (300000*) |
visokio.dbcc.debug | True/False - If true, database connection caching status and activity is printed to the console - useful for debugging server configuration | false |
* Use zero for these options to disable caching - connections will be created and discarded immediately before and after use. The numbers in brackets are the defaults used when the Omniscope process is in headless / silent mode.
These options can be configured by editing installconfig.properties in the installation folder (typically C:\Program Files\Visokio Omniscope) and adding/changing the ADDITIONAL_JVM_ARGS property. For example, the following turns on debug output and a 2 second expiry:
ADDITIONAL_JVM_ARGS=-Dvisokio.dbcc.debug=true -Dvisokio.dbcc.maxage=2000
If you are running Omniscope Server / Scheduler as a service, you instead need to edit "C:\Program Files\Visokio Omniscope\service\wrapper.conf", by adding lines such as:
wrapper.java.additional.1=-Dvisokio.dbcc.debug=true
wrapper.java.additional.2=-Dvisokio.dbcc.maxage=2000
Restart the service after making changes. Warning: at present this "wrapper.conf" file is overwritten on re-installation of Omniscope. Look in "wrapper.log" for debug output when running as a service.
The Omniscope Database Connection wizard supports ODBC data sources. Normal use of ODBC connections entails configuring an ODBC data source on each client PC requiring connectivity to the database for refresh. In Windows XP, for example, this configuration is done on the client using Control Panel, Administrative Tools, Data Sources. When distributing an .IOK file to numerous desktops within an organisation, if 'refresh from source' functionality is desired, this could require the same ODBC data source to be manually configured on every PC... potentially time-consuming for central administrators
Fortunately, it is possible to configure a "DSN-less" ODBC database connection within Omniscope. This encapsulates the full database connection parameters within the IOK file, and (providing the ODBC database driver is available) requires no administration on each client PC. This allows Omniscope to refresh data from the underlying database automaticallywhenever the .IOK file is opened on the client PCs. Omniscope can also be refreshed by users 'on-demand' using Data > Refresh from source.
Start Omniscope, and choose File > Connect to database. The Database Connection Wizard will open. Select ODBC and click Next. You will see the ODBC configuration page with the field Data Source Name.
Normally, when using a user- or system- configured ODBC data source, you would enter the name of the data source (the DSN) here. Instead, to avoid configuring the data source on all client machines,
you must enter the full DSN-less ODBC connection string. This is a line of text containing name=value definitions separated by semi-colons. Any values containing spaces are enclosed in curly braces. This describes the parameters of the ODBC data source.
For example, to connect to a sample Access database, you might use the following in the Data Source Name (DSN) field:
All ODBC DSN-less connections begin with DRIVER= however all other parameters are database vendor-specific, although they often are similar.DRIVER={Microsoft Access Driver (*.mdb)};DBQ=C:\db1.mdb
[ODBC]
DRIVER=Microsoft Access Driver (*.mdb)
UID=admin
UserCommitSync=Yes
Threads=3
SafeTransactions=0
PageTimeout=5
MaxScanRows=8
MaxBufferSize=2048
FIL=MS Access
DriverId=25
DefaultDir=C:\
DBQ=C:\db1.mdb
To convert the above to the full connection strring:
This gives you the full connection string. In this example, the string becomes:
DRIVER={Microsoft Access Driver (*.mdb)};UID=admin;UserCommitSync=Yes;Threads=3;SafeTransactions=0;PageTimeout=5;
MaxScanRows=8;MaxBufferSize=2048;FIL={MS Access};DriverId=25;DefaultDir=C:\;DBQ=C:\db1.mdb
Note: This is more verbose than absolutely necessary, the following will suffice, in this example:
DRIVER={Microsoft Access Driver (*.mdb)};DBQ=C:\db1.mdb
Finally, paste this connection string into the ODBC Data Source Name field of the Omniscope Database Connection wizard, as described above.
The following examples have not been verified:
Oracle: DRIVER={Oracle ODBCDriver};UID=Kotzwinkle;PWD=whatever;DBQ=instl_alias;DBA=W
AS400: Driver={Client Access ODBC Driver (32-bit)};System=myAS400
Excel: Driver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq=c:\somepath\mySpreadsheet.xls;DefaultDir=c:\somepath
SQL Server: Driver={SQL Server};Server=MyServerName;Database=myDatabaseName
http://www.asp101.com/articles/john/connstring/default.asp [29] (scroll down to ODBC DSN-less)
http://www.databasejournal.com/features/mssql/article.php/1491011 [31]
ODBC and JDBC are industry standard protocols for connecting directly to tables and reporting views in relational databases. It is relatively easy for you or your Database Administrator to connect Omniscope to ODBC/JDBC-compliant databases directly using the Omniscope Database Connection dialogue.
ODBC - Open Database Connectivity provides a standard software API method for using database management systems (DBMS). The designers of ODBC aimed to make it independent of programming languages, database systems, and operating systems. For more detail, see Wikipedia [32]
JDBC - Java Database Connectivity is an API for the Java programming language that defines how a client may access a database. It provides methods for querying and updating data in a database and is oriented towards relational databases. The Java Platform, Standard Edition includes the JDBC API together with an ODBC implementation of the API enabling connections to any relational database that supports ODBC. For more detail, see Wikipedia [33]
Omniscope can import the results of any SQL statement/query that returns a table using either protocol. Using ODBC it is possible to connect to tables/reporting views drawn from only one database. Using JDBC, it is possible to connect to joined reporting views/tables across multiple databases. In general, JDBC connections perform somewhat better than ODBC.
For examples of how to define and refresh both ODBC and JDBC connections, see Database Connectons [7]
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 [34]. Omniscope also supports an outside browser option [6]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 [36].
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 [36]
Warning: The Scheduler must always be running for this to work. You can now configure this as a Windows service [37].
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 [35].
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).
This article describes how Omniscope builds links to related files given a base folder, how the link field and the base folder interact, and what bearing the IOK file's location has on the link configuration.
Links are created by choosing a base folder and using field(s) to build up the rest of the link. The base folder provides a context for the link building, but isn't always relevant depending on the link configuration and field contents.
Let's consider the following example:
Base folder: "D:\Files"
Link configuration: [Link] (i.e. just a single field "Link" with no other fields or link text)
In this case the resulting link for individual records would be as follows for the given "Link" field values:
Link field contents | Resulting link | Notes |
C:\file.txt | C:\file.txt | Because the link field contains a fully absolute path, this overrides the base folder. In this case the base folder is entirely irrelevant. No matter what the base folder is or where the IOK file is saved, this will always be the link used. |
\file.txt | D:\file.txt | The link field is partially absolute. It does not contain a drive letter, but has a leading backslash. This means that the base folder's drive letter is used, but the base folder's path is ignored. |
file.txt | D:\Files\file.txt | The link field contains a simple filename. This is the normal usage, and creates a link to a file within the base folder. |
Subfolder\file.txt | D:\Files\Subfolder\file.txt | The link field contains a subfolder and filename, resulting in a link to a file within a subfolder of the base folder. |
Links:
[1] http://kb.visokio.com/kb/data-file-formats
[2] http://kb.visokio.com/xmldata
[3] http://www.w3schools.com/xsl/
[4] http://kb.visokio.com/node/236
[5] http://kb.visokio.com/node/192
[6] http://kb.visokio.com/node/363
[7] http://kb.visokio.com/kb/dbconnections
[8] http://kb.visokio.com/company#contact
[9] http://kb.visokio.com/kb/database-connections
[10] http://kb.visokio.com/kb/ms-access-odbc
[11] http://kb.visokio.com/kb/ms-sql-server
[12] http://kb.visokio.com/kb/oracle-thin-jdbc
[13] http://kb.visokio.com/kb/oracle-8.1.7
[14] http://kb.visokio.com/kb/mysql-jdbc
[15] http://kb.visokio.com/kb/generic-db
[16] http://kb.visokio.com/kb/dbcc
[17] http://kb.visokio.com/kb/db/dsn-less-odbc
[18] http://kb.visokio.com/odbc-jdbc
[19] http://msdn.microsoft.com/en-us/sqlserver/aa937724
[20] http://msdn.microsoft.com/en-us/library/ms378428.aspx
[21] http://support.microsoft.com/default.aspx?scid=kb;en-us;313100#6
[22] http://support.microsoft.com/default.aspx?scid=kb;en-us;313178
[23] http://kb.visokio.com/node/367
[24] http://kb.visokio.com/kb/proxysettings
[25] http://kb.visokio.com/node/366
[26] http://dev.mysql.com/downloads/connector/j/5.1.html
[27] http://dev.mysql.com/doc/refman/5.0/en/connector-j.html
[28] http://forums.visokio.com/
[29] http://www.asp101.com/articles/john/connstring/default.asp
[30] http://www.oracle.com/technology/docs/tech/windows/odbc/htdocs/817help/sqoraFormat_of_the_Connection_String_.htm
[31] http://www.databasejournal.com/features/mssql/article.php/1491011
[32] http://en.wikipedia.org/wiki/ODBC
[33] http://en.wikipedia.org/wiki/JDBC
[34] http://kb.visokio.com/kb/web-integration
[35] http://kb.visokio.com/node/370
[36] http://kb.visokio.com/node/139
[37] http://kb.visokio.com/node/325
[38] http://forums.visokio.com/?CategoryID=36