Vertica Integration with Dundas BI: Connection Guide

About Vertica Connection Guides

Vertica connection guides provide basic instructions for connecting a third-party partner product to Vertica. Connection guides are based on our testing with specific versions of Vertica and the partner product.

Dundas BI Overview

Dundas BI provides a set of tools that allows you to view, analyze, and transform your data using comprehensive business intelligence capabilities.

You can connect and integrate Dundas BI with Vertica in real-time and on any device from desktop to mobile. You can run Dundas BI on-premises or in a cloud environment.

Dundas allows you to:

• Create interactive and customizable dashboards and reports.
• Run ad-hoc queries.
• Analyze your data and performance.

Vertica and Dundas BI Software Versions

This document provides guidance using the software listed as follows:

Installing Dundas BI

Before downloading and installing Dundas BI, review the Dundas BI System Requirements page for hardware and software requirements.

1. Go to the Dundas BI download page.
2. Click DOWNLOAD NOW

3. Complete the registration criteria, and click Submit.

   You will receive a verification email.

4. Click the download link from the email to access the download page.

5. Run the Dundas BI executable (for example, Dundas.BI.Setup.x.x.x.xxxx.exe) to install the product.

   For detailed installation and configuration instructions, see the Dundas installation documentation.

Installing the Vertica Client Driver

Dundas BI uses the Vertica native connector to connect to your Vertica database. Download and install the Windows ADO.NET driver on your Dundas BI client machine as follows:

1. Go to the Vertica Client Drivers page on the Vertica website.

2. Download the required client driver that is compatible with your Vertica server version.

   Note  

   For details about client and server compatibility, see Client Driver and Server Version Compatibility in the Vertica documentation.

3. Based on the client driver package you downloaded, follow the installation instructions in the Vertica documentation.
4. For ADO.NET, double-click the installer and follow the prompts.

Connecting Dundas BI to Vertica

Follow these steps to connect Dundas BI to Vertica:

1. Use the Dundas administrator account you set up during installation to log in to your Dundas BI website using a browser. The URL is provided to you at the end of the installation process, for example: http://<myDundasServer>:8000/.

2. After you log in, the Getting Started window appears:

   

3. In the Getting Started window, click the Home icon.

   The Welcome Home screen appears.
   

   
   
   

4. Select Explore Data.

   

5. Select Data Connectors from the right pane. The following toolbar appears:
   
   
   

6. Select the New Data Connector icon.

   The New Data Connector window appears on the left pane of the screen.

   

   

7. From the Data Provider drop-down list, select Vertica Database.

8. Enter the following information:

   • Authentication
   • Login credentials
   • Host IP address
   • Database name
   • Connection pooling parameters
   • Connection settings parameters
   • Session settings parameters
9. Click Test connection to verify the connection.
10. After you verify the connection, click the check icon at the bottom-right of the screen.

Working with Vertica Data

Follow these steps to select the data you want to work with:

1. Click the Home icon on the upper-left side of the screen to return to the Welcome Home page.

2. Click Explore Data.

   The Metric Set screen appears.

   

   A metric set is an object that allows you to work with your Vertica data. You can define measures, dimensions, filters, sorting, hierarchy, and sort and transform your data. You create a visualization of that data using the characteristics specified in your metric set.

3. Click Use an existing data structure.

   A list of objects in the schema is displayed.

   

4. Select the table you want to work with and click the check icon to view the details of that table:

   

   Now you can view, transform, and make visualizations of the data in your Vertica database.

Troubleshooting

You may encounter some issues when connecting Vertica to Dundas BI on Windows Server 2016.

Issue

If you are displaying data from Vertica using the ADO.net driver-nativeConnector, you may receive the following error:

Could not load file or assembly 'MicrosoftSqlServer.TransactSql.ScriptDom, Version=12.0.0.0, Culture=neutral,PublicKeyToken=89845dcd8080cc91' or

one of its dependencies. The system cannot find the file specified.

Solution

Download and install the package ENU\x64\SqlDom.msi from Microsoft Download Center. Restart Dundas BI and try again.

Known Limitations

Before using Dundas BI with Vertica, be aware of the following known limitations:

• BINARY, VARBINARY, LONGVARBINARY, TIME, TIMETZ, INTERVALSECONDS, INTERVALMONTH, and UUID data types are not supported.

• For DECIMAL and FLOAT data types, values are rounded to 2 digits after the decimal.

• NUMERIC data type supports up to 28 digits of precision and 2 digits of scale above which the following error is displayed:

  Data Retrieval failed, as it was not possible to generate a valid query for the specified metric set. Specified argument was out of range of valid values. 

• For TIMESTAMP and TIMESTAMPTZ data types, milliseconds are truncated.

For More Information

• Dundas BI Website
• Dundas Tutorials and User Guide
• Dundas Documentation
• Vertica User Community
• Vertica Documentation