Home Download Forums Support  

Database Wizard 2.5

File > Connect to Database...Using the Database Connection wizard

Connecting to relational databases using SQL and ODBC or JDBC

Omniscope can connect to and import data from any relational database that supports common protocols like ODBC and JDBC. The Database Connection wizard guides you through the process of connecting and importing your data, and saving it as a new Omniscope file. Your file will remember its source(s), and on refresh re-execute the embedded SQL statement to re-generate the reporting view/table and repeat any subsequent merges with other files either on opening, or on demand. 

The Omniscope Database Connection wizard lets you define a connection and pass any standard SQL expression that results in a single 'flat' table. 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 connection in the wizard and/or create the appropriate reporting view(s).

Some organisations also have additional high-performance analytical and reporting databases that pre-prepare data for reports by performing joins, aggregations, sorts and scans on data assembled from multiple transactional databases. In general, Omniscope interacts with 'business intelligence' analytical databases in exactly the same way as transactional database views/tables. For more technical information, see the Database Connections section of the KnowledgeBase.

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 Omniscope file.

 

 

1. Connection type: Select the type of connection you are creating from the options currently available. 

 

ODBC - use this option to connect to defined data sources, like MS Access.  Can also be used to connect to spreadsheets for testing.

 

JDBC - use with databases other than MS SQL Server & Oracle if you have the JDBC driver installed.

 

MS SQL Server - use this option with MS SQL Server 2000. If you have 2005 or 2008 use the generic JDBC option.

 

Oracle - use this option with all versions of Oracle databases

 

Detailed examples of each type of connection are available in our KnowledgeBase.

 

2. Database Server (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 examples of various connection types and an exlanation of easy-administration connection strings, see the KnowledgeBase.

 

 

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 data set (database reporting view) has already been created as an ODBC-required single table, enter the name here:

 

 

5. Customise SQL:
Omniscope can import the results of any SQL statement that returns a table. The default SQL statement imports everything from the selected data table or reporting view.

If you know SQL and your database, you can modify the default statement and your statement will be saved and re-evaluated each time the file is refreshed.

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 Data > Edit source.

If you have several related queries, you may want to save each SQL statement in a text file and paste them in with minor variations.

Note: If you want o modify the SQL statement programatically using Enterprise XML actions for automated refresh and distribution using the Scheduler watch folder, please contact us.

 

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

 

Home Knowledge Base Reference Legacy User Guide Commands Reference Database Wizard 2.5