ODBC Data Import

Pro This data connector is only available in Visplore Professional.

Visplore can connect to databases via ODBC. All you need is an ODBC driver and, ideally, an ODBC data source ("DSN") configured in Windows. ODBC allows Visplore to load data from individual tables or views (that combine or transform multiple tables).

To further simplify the interaction with the database, one can configure so-called access masks for Visplore. These access masks result in a user-friendly dialog in Visplore, where the user can pick time ranges and choose to only load data from certain assets/categories.

ODBC data import Intro

Requirements and Setup

The following databases have been tested and are supported: PostgreSQL, Microsoft SQL Server, MySQL, Oracle, SAP Hana, IBM DB2, MongoDB, TimescaleDB, Vertica, Crate.IO, and QuasarDB. Other databases supporting ODBC may work as well.

To avoid having to enter database connection information more than once, we recommend registering an ODBC source.

Start the "ODBC Data Source Administrator (64-bit)" in the Windows start menu by typing "odbcad".

Configure a user DSN ("data source name") by clicking "Add...". Some databases (e.g. PostgreSQL) allow storing the password within a DSN to avoid having to enter the password more than once.

Once you configured your DSN, it appears as an option in the Welcome Dialog. Should you not see your DSN here, ensure that you used the 64-bit ODBC Data Source Administrator, as the 32-bit one does not show up in Visplore. Also, close (or restart) Visplore before the configuration of your DSN.

Welcome dialog database selection

Should you choose not to configure an ODBC source in windows, you can specify a connection manually with the "Custom..." option. This custom option is useful for initial development/testing.

Welcome dialog database custom

Note: When using the manual "Custom ..." option, settings do not persist for future connections and also cannot be saved as reference in Visplore sessions.

Import a Table or a View

To import data from a table, select your previously configured DSN in the welcome dialog.

Welcome dialog database selection

Clicking ok will open a dialog with options to further define your database query and a preview of your resulting data.

1. Navigate the database schema and select the desired table or view.

2. Check the preview and adjust the data types if necessary. Use the drop down to adjust or remove a column. These settings are memorized for the next time you import this table.

3. Alternatively, you can manually enter or adjust the SQL statement.

Database query definition and preview

After clicking OK, the data is imported. In the following role assignment dialog, you can mostly just press OK or make adaptations regarding which timestamp column to use, etc. Then, the data is visualized, and you can start working like you are used to in Visplore!

Note: In this example workflow the whole table is loaded. The following section describes how to filter in the case of large tables, for example, on desired time ranges or only rows of a particular category using access masks.

Note: If you're wondering if your table format can be analyzed in Visplore, please look at this summary of supported table formats.

To perform a new data import, please click "Import Data" again. When you save your analysis as .visplore file, and use "keep link to data source", you get a dashboard using a live database connection (see section "Setting up operational dashboards on live data" below).

Easy filtering with Access Masks

Visplore offers the possibility to set up a filter dialog for end users for a table/view (a so-called Access Mask). This way, the end user does not have to navigate the whole database structure but instead can use selected tables as an entry point. In addition, the end user can be provided with user-friendly filters to import only selected data.

Access dialog from manufacturing

An Access Mask is defined through an XML file and offers the following possibilities:

The user finds the Access Masks as a new option in the Welcome dialog once the corresponding XML definition has been created (see below). After choosing the access mask, credentials may have to be entered. After that, the mask dialog appears, where you can apply filters depending on your application. Finally, click OK to load the corresponding table, and the Visplore analysis can begin!

How to setup the XML configuration of access masks

An access mask is defined in an XML file. Use the following steps to create your custom access mask.

As you can see in the picture above, the access mask XML reference contains a filter for the time-period, a categorical filter for the product type, and no restriction on the columns (imports all columns). The reference for the access mask configuration looks as follows:

<odbc_access_mask id="ManufacturedItems" name="ManufacturedItems" odbcsource="PostgreSQL35W" database="visplore" schema="public" viewname="ManufacturedItems" order_by="Time">
	<timespanrowfilter id="Time" caption="Time" mandatory="no" dbcolumnname="Time" dbcolumntype="datetime" specification="absolute_or_relative"/>
	<categoricalrowfilter id="Product Type" caption="Product Type" mandatory="no" multiselection="yes" dbcolumnname="Product Type" dbcolumntype="string"/>
    <columns use="all"></columns>

Important: Visplore must be restarted in order for it to detect the new access mask. Upon restarting, you see the new access mask in the welcome dialog. Depending on your DSN configuration and ODBC database you might be prompted for a username and password.

Access dialog with password and filters

See the troubleshooting section below for common issues. If you have questions about this, please get in touch with us via Questions and Feedback. We are happy to provide you with details!

Setting up operational dashboards on live data

Databases usually contain fresh and up-to-date live data. Visplore allows to create analysis dashboards that work with this latest data. These dashboards can be used easily to monitor the latest production or quality data without any further setup effort. Furthermore, these dashboards can be shared among multiple users. Setup an operational dashboard on live data as follows:

Import your data using a configured access mask as described above. To see the latest data, you may want to use the relative time filter (e.g. "last 24 hours" or "last 7 days").

Configure your visualizations as needed.

Save your analysis as a .visplore file by clicking Save icon. Use the checkbox "Keep link to data source" when saving your analysis, as seen below. See the chapter Saving and loading the analysis for details.

Use checkbox Keep link to data source

Optionally, share this file with other users. Loading such an analysis (either by double-clicking on the .visplore file or through the Welcome dialog) allows you to adjust your query and filters in a dialog.

Visplore file in Windows explorer

Note: When sharing a .visplore file with other users, they need to have the Access Mask configured, too.

When you change your Access Mask or your database schema changes, this usually results in a different data table. In this case, it might happen that out-of-date .visplore files throw a warning or not load at all. In this case, you can try to import your data with the new access mask first, and then apply the old .visplore file and select "apply to current data" when asked. Now save your analysis with a new file name, again with the "Keep link to data source" option.

Now you can perform routine analysis on your live data on a regular basis, thus operationalizing your analysis.

If you have questions about this, please get in touch with us via Questions and Feedback. We are happy to provide you with details!

Troubleshooting Access Masks

Troubleshooting the Setup of Access Masks

Troubleshooting the Using of Access Masks