Connecting to Databases

Omniscope includes a Database Wizard to help establish direct connections to database tables and reporting views. Omniscope will import the results of any SQL statement/query that returns a table. The Omniscope Database Wizard is accessed via the File > Connect to database command, documented here.

In order to connect directly to a database (rather than importing a delimited .CSV or .TSV data file exported from a database) you need to know the type of database and its connection details. Using the Database Wizard, you 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 so that every refresh re-creates the reporting view, drawing the latest information from multiple transactional tables within the database(s) to create a single, 'flattened' tabular view of the data for scrubbing, analysis and reporting.

Suggestions: Database tables are operational and transactional in nature. They are usually not intended to be shared with humans without some translation of the codes used in the tables. Rather than access a transactional table, try to use a reporting view that also translates obscure codes and other internal values to values meaningful to the humans receiving your reports. You can complete the process using the column re-name option in Omniscope, which remembers the translation from the names used in the database tables/reporting views. Also, it is better to include every record in the view, rather than making  too many pre-selections in the query assembling the reporting view. Errors in fields can result in some records not being included in the view if you are being too selective when assembling the reporting view. First try just dumping everything (e.g. people and all data linked to people, places, things etc.) into a few large reporting views extracted from the database(s). Open these views in Omniscope and save them- effectively creating 'data marts' in .IOK file format. You can then correct/scrub and filter/hide unwanted data visually, and create 'child' Omniscope files which will refresh from the (server-based)  'data mart' .IOK files, which in turn refresh 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 .IOK/.IOM 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 or personal 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 data is refreshed from a linked source. For more detail on using merge files to separate data from different sources, see  File > Import into current file > Merge data from another file in the Omniscope User Guide.

Connecting via ODBC

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, 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. Open the Omniscope Database Wizard under File > Connect to database and follow through the steps.

Connecting via JDBC

If you have a database such as Oracle or PostgreSQL, or wish to customise the database import behaviour, you can connect via JDBC using a JDBC JAR driver file obtained from the 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.

In the Database Wizard, 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 page, 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. Finally, when connecting via JDBC, you can rename fields at import time, on the last page of the wizard, allowing you to change unreadable fields such as "CUST_ADR" to more reader friendly "Customer Address".

Guided examples of JDBC database connection parameters:

• MS SQL Server 2000 & 2005 using JDBC
• Oracle version 10g using the Oracle Thin JDBC driver
• Oracle version 8.1.7 using the Oracle Thin JDBC driver

Working with databases

Saving the connection and refreshing

To save the connection details you have entered into the Database Wizard for subsequent refreshes, save the current file as an .IOK or .FFC file using File > Save as.... The new file will contain a highly-compressed snapshot of the data as it was when imported, plus all the database connection parameters you entered into the Wizard. Next time you open your file, you will see the old data, but you can refresh from the database view specified in the Wizard by choosing File > Import into Current File > Refresh from source.

Setting Field (column) data types

Unlike text-based data file import (such as .XLS or .CSV), Omniscope does not automatically inspect and convert data types when the linked source is a direct connection to a relational database. 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).

Editing data from databases

At present, database connections in Omniscope are only 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 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, pars-able .XML Corrections file such that the Administrator use simple tools to import the corrections made in Omniscope back to the source database.

Warning: Preserving Edits - If you make changes to your Omniscope file, such as correcting the data type of a field, or editing data values, or adding/deleting records (rows), and you have not submitted the changes to be made back to the database administrator, your changes will be lost if you later refresh from source (see Saving the connection details, above). You can prevent accidental Refreshes from source by ticking Unlink from source in the File > Export > Export data dialogue, but this will also remove your database connection parameters from the .IOK file.