Connecting to a database

Working with text files and spreadsheets can be very convenient but, sooner or later you are going to want to work with databases, which offer many advantages such as scale and centralization. If you want to build scalable, enterprise analytical solutions, you will have to maintain your source data in a database or even distributed across multiple databases. Spotfire provides many ways to connect to databases, and you can retrieve data simultaneously from multiple databases and files.

Up to Version 3 of Spotfire, we had the option to connect to databases such as Oracle, Microsoft SQL Server, and Microsoft Access using standard data connectors such as ODBC, OleDB, Oracle client, and SqlClient. Explaining how these types of connectors work is beyond the scope of this book. You will find detailed explanations on the Internet.

They all require database-specific drivers, which you can think of as like an operating system plugin. Many of these drivers come bundled with operating systems; some you will have to download and install. Again, you will find lots of help on the Internet. The best place to look is on the websites of the various database vendors.

They all require some form of connection string, which is just a way to tell the driver the address of your database and the credentials you are using to connect. Most databases will require authentication before you can connect and access their data. The website http://www.connectionstrings.com is a good starting place. Sometimes the entire connection string is pasted as a literal string into a single configuration box; sometimes the individual elements of the connection string are entered into fields in configuration form; it just depends on the connector.

Using ODBC

Open Database Connectivity (ODBC) is one of the easiest drivers to use. It might not be perfect for all applications, but it's usually a reliable way to get started. As you gain experience, you will use other options. It might also depend on the database(s) you wish to connect to.

There are three important prerequisites before you start:

• The relevant ODBC driver must be installed on your machine.
• You must have local administrator rights to set up the ODBC connection.
• You must have connection details for the target database, including a username and password to authenticate.

How to set up an ODBC connection in Microsoft Windows 7

1. Open Control Panel and then Administrative Tools. If your control panel is organized by theme, you'll have to go into System and Security and then into Administrative Tools.
2. In Administrative Tools, open Data Sources (ODBC) and click on the System DSN tab.
   
3. Click on the Add button to configure the connector, scrolling down the list of drivers until you find the one you need. For this example, we're interested in the Oracle driver.
   
4. Click on Finish to get to the business end of the configuration.
   
5. Data Source Name is entirely arbitrary; it is the reference you will use in Spotfire. The Server configuration includes a hostname, which is simply the name of the server hosting the database; a port, usually 1521; and a DB service name, which you can get from the database administrator. Click on OK to finish and return to the System DSN tab, where you will see your entry. Make sure you click on OK to exit the ODBC administration tool.

Now you are ready to use this ODBC connection to retrieve data into Spotfire.

Using an ODBC connection in Spotfire

Use the File menu or the Add Data Table icon to navigate to the add data table dialog just as you did to add data from a file, except this time you are going to select Database rather than File.

1. Click on Database to get the Open Database dialog. We're interested in Odbc Data Provider, so select it and click on the Configure button.
   
2. Select the connection name you configured in the ODBC administrator. You'll notice that a number of connectors are defined in the next screenshot, including one called SpotfireDev. You will need to create one of your own for a database to which you have access.
   
3. Click on OK to get back to the Open Database window; notice that Spotfire has created a connection string. Click on OK to open the database. The database will eventually open; it may take a little time if your network connection is slow or the database is complex. You will see a hierarchical organization of high-level containers, in each of which you should see data tables and their constituent columns. It's not unlike navigating a folder system on your computer.
   
4. You will need to have some knowledge of the database and some idea of the data you are looking for, but Spotfire will allow you to navigate the database objects and select whole or partial tables to import. Spotfire will also generate the SQL automatically, but you can customize and fine-tune the SQL, adding a where clause, for example.
5. If you click on + next to one of the main objects, such as SPOTFIRE in the example, the entry will expand to list all the tables in that schema. You can then scroll down to a table of interest and expand further to view the individual columns in the table.
   
6. When you are happy with the selections, give the import a name. This will be the name of the table in Spotfire. The data will load just as the files did, except the structure of the data (column headers and data types) will be determined by the source database.

Tip

Downloading the example code

You can download the example code files for all Packt books you have purchased from your account at http://www.packtpub.com. If you purchased this book elsewhere, you can visit http://www.packtpub.com/support and register to have the files e-mailed directly to you. You can also download code from http://www.insidespotfire.com.