Vertica Integration with Bold 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.
Vertica and Bold BI: Latest Versions Tested
Software | Version |
---|---|
Partner Product |
Bold BI Enterprise Edition 4.2.68 Embedded BI (on-premises) |
Partner Product Platform | Windows Server 2019 Standard |
Vertica Client |
Vertica ODBC Driver 11.0.1-0 |
Vertica Server | Vertica Analytic Database 11.0.1-0 |
Vertica Server platform | Red Hat Enterprise Linux 7.3 |
Bold BI Overview
Bold BI is a business intelligence platform to explore data and build dashboards of your data in Vertica. Bold BI is a web-based application available for Windows and Linux and can be deployed on-premises or hosted in the cloud as a service.
Bold BI uses ODBC and allows live connections to your Vertica database.
Installing Bold BI
Follow these steps to install Bold BI on-premises:
Note Follow the Bold BI documentation for prerequisites for your environment.
-
Go to https://www.boldbi.com/.
-
Click Schedule Free Demo.
-
Fill out the form and click Schedule Free Demo to request a trial of the software.
-
Download the software provided by Bold BI.
-
Run the Bold BI Enterprise Installer and follow the prompts for installation.
-
After the installation has completed, follow the Bold BI documentation to configure and startup the application.
Installing the Vertica Client Driver
Before you can connect Bold BI to Vertica, you must install the Vertica ODBC client driver. Follow these steps:
- Navigate to the Client Drivers page on the Vertica website.
-
Download the Vertica client package 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.
-
Double-click the installer and follow the prompts. When prompted to select one or more drivers, select ODBC only.
-
Create an ODBC DSN as described in the Creating an ODBC Data Source Name (DSN) section.
Creating an ODBC Data Source Name (DSN)
After you have installed the Vertica ODBC driver, you must create a DSN to connect to Vertica from Bold BI.
Important
Bold BI supports 32-bit system DSN only.
The Vertica client package provides both the 32-bit and the 64-bit ODBC drivers. However, Bold BI requires a DSN using the 32bit driver architecture. In addition, only system DSNs are visible within Bold BI, this is because all applications hosted in IIS are considered and running on the system user privilege.
Follow these steps to create a DSN:
- Open the 32-bit version of the Odbcad32.exe file located in the
%systemdrive%\Windows\SysWoW64
folder. - In the ODBC Data Source Administrator (32-bit) window, click the System DSN tab.
- Click Add…
- Select Vertica from the list of drivers and click Finish.
- In the Vertica ODBC DSN configuration window, enter your connection information.
- Click Test Connection, and then click OK.
- Click OK to close the Vertica ODBC DSN Configuration window.
For more information about ODBC DSN, see Setting Up an ODBC DSN in the Vertica documentation.
Connecting Bold BI to Vertica
Follow these steps to create a connection to Vertica:
- In a browser, open the Bold BI portal http://<my_boldbi_server>:<my_port>/.
- In the Login screen, enter your Bold BI administrator credentials and click Log In. The home page opens.
- In the home page, on the left-hand side menu, click Data Sources.
- Click Create Data Source.
- In the Add Data Source screen, search for ODBC in the list of connectors.
- In the Connect to ODBC screen, enter the following connection information:
- Name: Name of your Vertica connection.
- Description (Optional): Description of your Vertica connection.
- ODBC Connection Type: From the drop down list, select DSN.
- Dsn Name: From the drop down list, select a DSN to connect to Vertica. For instructions on how to create a DSN see the section Creating an ODBC Data Source Name (DSN) in this document.
Note Only 32-bit System DSNs are listed out.
- User name: Database user.
- Password: Database user password.
- Database (Optional): Name of your Vertica database.
- Command Timeout (in seconds): The maximum query execution response time in seconds.
- Additional connection parameters (Optional): You can use this option to customize your ODBC connection properties. For information on Vertica’s ODBC properties see, Data Source Name (DSN) Connection.
- Click Connect.
The Data Source Designer page appears. You can now begin exploring your data in Vertica.
Troubleshooting
Review these known issues and their workarounds when connecting to Vertica using Bold BI.
Query Joiner in Data Source Designer generates syntactically wrong queries.
Issue: Bold BI generates a query that is syntactically incorrect when joining multiple tables using Query Joiner in Data Source Designer. This issue happens when specifying the relationship between three or more tables. For example, joining one fact-table and two dimension-tables.
The following error message is displayed when you click Data Preview to preview the data:
Data Source failed to preview the selected table: There was an error executing the query <query1>. Reason: ERROR [42601] ERROR 4856: Syntax error at or near ""<>"" at character <#>
Solution: After you have joined three or more tables using Query Joiner in the Data Source Designer:
- Click Code to switch from Design mode to query mode.
- Examine the query and manually comment out or remove the lines in the query that are incorrect.
- Click Run to execute the query and preview the data.
Known Limitations
The following is a list of known limitations with data types when connecting Bold BI to Vertica using ODBC:
- For INTERVAL data type, data is not displayed and the error Unknown SQL type - 107 or 112 is displayed.
- For BINARY, VARBINARY, LONG VARBINARY, LONG VARCHAR, TIMETZ, TIMESTAMPTZ, and UUID data types, data is not displayed.
- For DATE data type, values are displayed in TIMESTAMP format.
- For NUMERIC data type, values up to 29 digits are displayed beyond which the error "There was an error executing the query <my_query> Reason: Value was either too large or too small for a Decimal" is displayed.
- For INTEGER data type, values are rounded off after 16 digits.
- For TIME data type, milliseconds are not displayed.
- For TIMESTAMP data type, seconds and milliseconds are not displayed.
For information about Vertica’s specific data types, see SQL Data Types in the Vertica documentation.