This is the fourth part of a series on Spotfire Properties. In previous posts, I discussed Document Properties, Data Table Properties, and Column Properties. This week we’ll take a look at Data Connection Properties.
When I first started working with Spotfire, I didn’t know what a data connection was. Thus, I didn’t understand the dialog at all or what it did. So, let’s first talk about what a data connection is.
When the application opens, there are four options for adding data tables, as shown below. If you added a data table using the last option, the Data Connection Properties dialog will be relevant. If you used one of the first two options, you do not have any data connections in the DXP, and you don’t need to worry at all about the Data Connections dialog.
When you add a data table with a data connection, you are making a direct connection to the data source (such as SQL databases). This type of connection will usually require Windows authentication or some type of username and password. Many companies do not allow Spotfire users to make this type of connection because it hits the database directly, which can impact resources and other processes. This is a meaty subject, but for now, just understand that connecting with a data connection to a database is different from connecting to a database with an information link.
Data Connection Properties
The Data Connection Properties dialog opens up to the screen shown below where you have the option to Rename the connection, go to Settings, or Refresh the data. I highly recommend coming into this screen after adding a data connection to rename the connection. Why? Well, when you import data from a data connection, most users name the table, but they don’t know they can also name the connection. When the DXP has many connections, they can get confusing quickly, and so it’s a best practice to rename them. In this case, the delete button is grayed out because the DXP only has one table from one connection, so deleting it isn’t an option.
If you click on the Settings button in Data Connection Properties, a new dialog box appears with three tabs. By default, the data connection is embedded in the analysis, but you can also choose to save the connection in the library for other users. This dialog also has an option to replace the data connection.
Settings – General
From within the General tab, you can see the views and tables associated with the data connection. There are two actions you can take — Edit the data connection and Refresh the Schema. Prior to version 7.11, clicking on the Edit button was the only way to edit the data coming in. However, this type of editing is now available in the Data Panel as well. Refreshing the Schema will simply ping the data connection to show you any changes to the schema. This could be new tables, new views, or other changes to the database.
Settings – Data Source
The data source tab shows all of the information about the data connection itself.
Settings – Login
Clicking on the Settings button will bring up another dialog with the details of the connection and two more tabs. You can edit the data connection in the Login tab by clicking the Edit button. For example, if you were working with a data table in a “dev” or “uat” instance, you can change the connection to the “prod” instance. As long as everything is the same, the tables are easily swapped out without any loss of inserted columns, transformations, or calculations.
Settings – Credentials
The Credentials tab is very important, perhaps the most important dialog for Data Connections. There are three ways credentials may be retained in Spotfire.
- No, do not save any credentials — This means that each time a new user attempts to open the DXP, they will need to enter their own database credentials in order to update the table. This can be problematic depending on how free your company is about handing out database credentials.
- No, but save credentials profile — This will save a credentials profile, which consists of a profile name, a username, and a password (only the profile name is saved with the connection data source).
- Yes, save credentials… — This can be risky, but I have used this option before when had to use a data connection and had many users without direct database access. In this case, we setmup a generic account (rather than an account for each user). As the administrator, I set up the DXP, entered the username and password. Spotfire saved it, and the users didn’t need to know either username or password, and the file worked.
The third option does not appear in the screenshot below because I opened the data connection with Windows authentication, rather than SQL Authentication. You would see this option when it is relevant.
Settings – Cache Settings
Finally, the Cache Settings tab specifies when data should refresh. How you configure these settings will vary depending on…
- The frequency of data updates
- The number of users using the direct connection
- The volume of data being loaded
Spotfire provides three options.
- No, always get fresh data from the external source. Use this option if you do not want to cache data from the data connection. This may put a very high load on the database.
- Yes, but let the cached data expire. Use this option to cache data but refresh if the cached data is older than a specified limit.
- Yes, but let the cached data expire every (specify interval). Use this option to cache data but refresh at a specified time or interval.
Hopefully, you have a better understanding of what Data Connections are and what options you have to configure them. The next post will look at Data Function Properties.