416.467.9100 | Dundas Data Visualization | Login
Welcome Guest
This is the support page for the legacy Dundas Dashboard application. For assistance with the current Dundas BI application please click here.
Dashboard v5.0

This site makes extensive use of JavaScript.

Please enable JavaScript in your web browser and reload the page before proceeding.

Data Provider - ODBC

Hide navigation
RSS
Modified on Thu, 21 Aug 2014 04:05 PM Categorized as Data Connectors
Click to return to: Documentation | Connecting to Data | Creating a Data Connector


Overview

Dundas Dashboard's ODBC Generic data provider option lets you connect to data sources that have an ODBC driver. Since there are over 100 third-party ODBC drivers available for a wide range of database systems, this means that Dundas Dashboard has the ability to connect to numerous types of data sources out-of-the-box. Using ODBC is a viable alternative to writing a custom data provider add-on, which requires .NET programming expertise.

Here are some examples of data sources that have an ODBC driver:

  • Adaptive Server (Sybase)
  • Advantage Server (Sybase)
  • DB2 (IBM)
  • Informix (IBM)
  • Ingres
  • Pervasive PSQL (Pervasive)
  • PostgreSQL
  • Proficy Historian (GE)
  • Quickbooks
  • SAS
  • SPSS
  • SQL Anywhere (Sybase)
  • Vertica

ODBC DSN

A Data Source Name (DSN) contains the information necessary to make a connection to a data source via ODBC. This information is stored in Windows and may include the database name, driver, and user credentials. Once a DSN is created for a data source, the DSN can be used by client applications to access data.

A System DSN is a DSN that can be used by anyone who has access to the computer. Use the ODBC Data Source Administrator program in Windows to create and manage System DSNs on your computer. For example, on Windows XP / 2003, go to your Control Panel and open Administrative Tools to find the Data Sources (ODBC) shortcut. Double-click on this shortcut to launch the ODBC Data Source Administrator.

Managing System DSN entries in Windows.

Managing System DSN entries in Windows.


DSN-less connections

Dundas Dashboard also lets you connect to ODBC data sources using a DSN-less connection. In this case, you must specify the connection information directly in the General tab. Note that this connection information is specific to each type of data source and driver.

The advantage of DSN-less connections is that they are not machine-specific and will still work if you move the application to another server. On the other hand, to set up a DSN-less connection requires more knowledge about the ODBC driver.

Connecting to ODBC Data Sources

To connect using a System DSN (Refer to this article on general information for data connector creation process):

  1. From the General tab of the Data Connector Designer screen, choose the ODBC Generic provider option.
  2. Select the Specify ODBC System DSN option. This will connect using Data Source Name information that is stored in your system's registry. An ODBC DSN contains information such as the database name, driver and user credentials.
  3. Choose the desired DSN name via the dropdown list.
  4. Test the data connection, then save your data connector.

Connect using ODBC System DSN.

Connect using ODBC System DSN.


To connect using a DSN-less connection:

  1. From the General tab of the Data Connector Designer screen, choose the ODBC Generic provider option.
  2. Select the Specify ODBC Driver option.
  3. Choose the specific driver type via the dropdown list (which shows all of the ODBC drivers installed on your computer).
  4. Enter additional connection parameters in the box provided, such as the path to a database file. This additional information is specific to each type of data source and driver. You specify each connection parameter in the form of name=value strings which are separated using semi-colons.
  5. Type a password in the Password box (if applicable).
  6. Test the data connection, then save your data connector.

DSN-less connection option.

DSN-less connection option.


Note: Use the IsRestricted option to indicate that the data source may not support standard database functionality such as joins. In this case, Dundas Dashboard will load the data into its own internal database first so that operations such as joins can be performed.

Data source notes

Notes on connecting to specific ODBC data sources:

  • Informix (IBM)
    • Because Informix doesn't have quoted identifiers by default, it is necessary to supply the “DELIMIDENT=y” option as an additional connection parameter in the Data Connector screen.
  • Progress database
    • Progress does not support certain SQL syntax required by Dundas Dashboard. To avoid resulting issues, it is recommend to use the Is Restricted option which will cause data to be loaded to temporary tables and queried from there. Another workaround is to uncheck the (All) and open ranges allowed option in your filters.
  • SAS

Limitations

Current limitations with using the ODBC provider:

  • Stored procedures and functions are not supported.
  • Date-trimming functions are not supported (workaround is to use manual queries instead).
  • Original data type names are unavailable as only ODBC data types are used.
  • Some ODBC drivers are available in 32-bit versions only. To use such drivers on a 64-bit server, you need to run IIS or your application pool in 32-bit mode.
  • In order to use time dimensions based on your ODBC data source (and assuming you have not selected the IsRestricted option), a permanent table must be created in the data source (i.e. use of temporary tables is not supported).
  • Discovery of table and view definitions may not work for some ODBC data sources.
  • Microsoft Access and Visual FoxPro do not support the COUNT_BIG and COUNT_BIG DISTINCT aggregator options at the virtual table, KPI, and dataset stages.

Related topics




Click to return to: Documentation | Connecting to Data | Creating a Data Connector

About Dundas | Contact Us Follow us on Twitter! | Privacy Statement | Report Site Issues

Copyright © 2009-2014 Dundas Data Visualization, Inc.