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, QuasarDB, Amazon Athena, Amazon Redshift, Snowflake and Google BigQuery. 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

Select your previously configured DSN in the welcome dialog to import data from a table.

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. You can check the preview to peek into the data and change column types if needed.

3. If you want to import all data, click OK. Otherwise, you can add import filters to reduce the amount of data you wish to import. To add filters click the filters panel on the right-hand side.

Database query definition and preview

1. In the filters panel, you can see all filters you defined on your import. Clicking the underlined text lets you change the filter, the X button removes a filter.

2. Use the + Add filter button to add more filters. Depending on the data type of the data attribute you select, an appropriate filter comes up. Currently, time and categorical filters are available.

3. You can also limit the data attributes (columns) and data records (rows) to reduce the size of your import.

4. If a more specialized import option is needed, the edit query manually button lets you define your own custom query in the query language of your database.

5. Use the update preview button to show your filters’ changes on the loaded data in the preview panel.

6. You can still change the data attribute types if needed.

After clicking OK, the data gets imported, visualized, and you can start working like you are used to in Visplore!

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

Specify Filters

Pre-defined filtering options 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>
</odbc_access_mask>

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