File > Connect to database

Omniscope connects to databases using standard protocols known as ODBC and JDBC.  In general, JDBC performs faster than ODBC. The Omniscope Database Connection Wizard lets you define a connection and pass any standard SQL expression that results in a flat table output.  If you do not have authorisation to execute SQL statements, or don't know the SQL required to generate the table you want, your Database Administrator may have to create the appropriate comprehensive reporting view(s). This means using database reporting tools to draw data from multiple transactional tables to create a single, 'flattened' tabular view of your data for human consumption via Omniscope, i.e. scrubbing, analysis, reporting and presentational purposes.

Some organisations have additional high-performance analytical and reporting databases which perform joins, aggregations, sorts and scans on data assembled from multiple transactional databases. Omniscope interacts with these additional reporting or 'business intelligence' analytical databases in exactly the same way as it interacts with transactional database views/tables.

Suggestion: Relational SQL-compliant database tables are usually operational and transactional in nature. The contents are usually not intended to be shared directly with humans without some translation of the codes used in the tables. Rather than importing data directly from transactional tables, try to use reporting views that also translate obscure codes and other internal references to values meaningful to the humans receiving your reports. Also, it is often better to include every record in the view, rather than making pre-selections in the query assembling the reporting view. Errors and historic inconsistencies in fields can result in some records not being included if you are too selective when assembling the Omniscope reporting view. Try just dumping all related records (e.g. all people and all attributes linked to people, same for places, and the same for things, etc.) into a few large ('wholesale') Omniscope reporting views automatically extracted from the database(s). If the record counts are too high for your machine, divide them into smaller sets by time period, rather than internal attributes. Omniscope enables anyone to open these large-scale 'wholesale' reporting views, then correct/scrub and filter/hide/delete unwanted data visually. Use Omniscope to filter and sub-divide the 'wholesale' data dumps, then save as smaller, more-targeted ('retail') report templates and source files ( with automatic refresh).

For more information on how Omniscope compliments almost any configuration of databases and business intelligence solutions, consult the Knowledge Base, or contact us.

Using the Database Connection Wizard

Clicking on File > Connect to database launches the Database Connection wizard. The wizard guides you through a six-step process for defining a linked data source from a relational database view/table. You will need to know the type of database and its connection details. Using the Database Connection 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/view and the .IOK/.IOM file.

1. Connection type: In this step you define what type of connection you are creating from the options currently available.  Detailed examples of each type of connection are available in our Knowledge Base.

2. ODBC configuration: Assuming you chose the most common ODBC connection type , you will be asked for a name, or if you are rolling out auto-refresh from the database to a large number of desktops, a DNS-less connection string may be preferable.  For more details on using easy-administration connection strings, see the Knowledge Base.

3. Authentication: if access to the database view/table is authenticated, tick the box and supply the Username and Password combination to be used whenever the file is refreshed.

4. Choose Table: assuming the comprehensive database reporting view has already been defined as an ODBC-required single table, enter the name here:

5. Customise SQL: If there is a need to exclude some records in the linked database view/table from the Omniscope file, queries can be added  to the connection. These can be refined/edited at any time using File > Import to current file > Edit data source

6. Specify a title: Choose a name for this connection/linked relational database source that communicates something about the underlying queries (if any) being used.

Back to File Menu