|Share this article:|
Vertica Integration with BOARD: Connection Guide
Applies to Vertica 7.1.x and earlier
About Vertica Documentation
Vertica connection guides provide basic information about setting up connections to Vertica from software that our technology partners create. These documents provide guidance using one specific version of Vertica and one specific version of the third-party vendor’s software. Other versions of the third-party product may work with Vertica. However, other versions may not have been tested. This document provides guidance using the latest versions of Vertica and BOARD as of October, 2015.
The BOARD toolkit contains various business intelligence (BI) and corporate performance management (CPM) functionalities and combines them within a single software environment. BOARD’s BI capabilities include multi-dimensional analysis and reporting; BOARD’s CPM capabilities include budgeting, planning, and forecasting. The BOARD architecture is based on the Service Oriented Architecture (SOA) structure.
In this model, you can identify:
- A service provider – in this case the BOARD server
- A set of different service consumers, which are the client programs.
This document describes the connection mechanism to the Vertica database from BOARD. This document is based on the results of testing BOARD 9.0.1 with Vertica 7.1.x via the OLE DB for the ODBC driver (bridge) on a Windows platform.
BOARD Client Operating Systems
The BOARD client works with the following operating systems:
- Windows 2008 server and later
- Windows Vista and Windows 7, all professional editions
- Windows 8, all editions
Note BOARD client is not supported on Windows 2000, Windows XP Home Edition or other non-business editions of Windows OS.
You must have a minimum resolution of 1024x768.
Install BOARD Client
To download BOARD, follow these steps:
- Request a demo for the BOARD client by entering your information and clicking Download.
- Run the BOARD Client setup program and follow the onscreen instructions. The setup requires some applications are installed on your PC. If the applications are not installed, you are redirected for install.
- In the Installation Option dialog box:
- If you must connect to a centralized BOARD server, select the Auto-updating version. You must have enabled the Autoupdate service in your BOARD server.
- If you need to work in stand-alone architecture or if you need to work while not connected to a BOARD server, select the Standard version.
Install BOARD Server
To install the BOARD server, you must run the setup program and follow the on-screen instructions. If any application that the BOARD server requires is not installed, the installer redirects you for install. The demo version includes the software for the BOARD server.
- Open the BOARD folder and select the BOARD Server
- Run the setup program. Follow the onscreen installation steps.
- Select a destination to install the BOARD server.
- To complete the installation, click Finish.
- The BOARD server is installed as a Windows service. To view the service, open the Microsoft Management Console for services. Adjust the service settings as needed.
Configure the BOARD Server
You must run the BOARD Server Configuration program on the machine where the BOARD server is installed. To Configure the BOARD server follow the steps from the BOARD documentation.
Download and Install the Vertica Client Driver
Before you can connect to Vertica using BOARD, you must download and install the Vertica client package. This package includes the Vertica ODBC driver that BOARD uses to connect to Vertica.
Download Vertica Client Drivers
- Go to the Vertica Client Drivers page.
- Download the version (32-bit or 64-bit) of the Vertica client package that matches your operating system and Vertica server version.
Note Vertica drivers are forward compatible, so you can connect to the Vertica server using previous versions of the client. For more information, see Client Driver and Server Version Compatibility in the Vertica documentation.
Install Vertica Client Drivers
Based on the client package you downloaded, follow the steps for installation from the Vertica documentation: Installing ODBC/JDBC Client Drivers and vsql Client on Windows
Create an ODBC Data Source Name (DSN)
To connect to Vertica from BOARD using the ODBC driver, you must create a DSN. You can set up either a 64-bit DSN or a 32-bit DSN, depending on your operating system and BOARD architecture.
To create a DSN, follow these steps:
- Depending on which DSN you wish to install, navigate to the appropriate folder:
- 64-bit DSN: Navigate to Start > Control Panel > Administrative Tools > Data Sources (ODBC).
- 32-bit DSN: Navigate to C:\Windows\SysWOW64\ and locate the program file called exe.
- DSN Name: The name for the DSN.
- Server: The host name or the IP address of the Vertica server to which you want to connect.
- Database: The name of the Vertica database.
- User Name: The name of the user account to use when connecting to the database.
For more information, see Setting Up a DSN in the Vertica documentation.
Connect BOARD to Vertica
To connect BOARD to Vertica , you must:
- Create a New Database
- Create a Data Reader Protocol
Create a New Database
- Navigate to the Database tab of the Ribbon bar and click the New DB icon () in the Utilities section.
- Type the database name and click OK.
After the database is created, its folder is located in C:\Board\Database.
Create a Data Reader Protocol
The Data Reader imports data from an ODBC or OLE DB data source.
- In the Database tab of the Ribbon bar, click Data Reader in the Database Manager section. The Data Reader window opens.
- Navigate to the SQL tab in the Data Reader and click the Action icon.
- Select New Protocol.
- The SQL Reader window opens. In the Title field, type the protocol name.
- In the upper right hand corner, click New.
- Set the connection parameters.
- Under Connection Source, click OLE-DB Wizard.
- The Data Link Properties window appears. In the Provider tab, select the preferred OLE DB provider and follow the instructions for configuration. The configuration setup varies depending on the chosen provider.
- In the Connection tab, select the ODBC connection that was created earlier in the drop-down list.
- Enter the username and password. Click Test Connection.
- A dialog box appears stating the connection was successful. Click OK. A connection string appears in the Connection String field.
- Select the RDBMS type. If the relation database is not listed, set the type to General.
- Set the time-out parameter to set the maximum number of seconds for a query. If the query execution exceeds this time limit, the query is cancelled.
- If required, enter the connection user name and password. Click Save & Connect.
- In auto mode, in the right panel, select the tables and views you wish to import data from by selecting the check box. The selected tables are added to the main area.
- In manual mode, queries must be written manually. Click Browse and the SQL result window appears. For example, when you query the Char1_Table:
All tables are queried in a similar fashion.
Test Database Objects
You can test other database objects by following these steps:
- Create a connection to Vertica using the OLE-DB bridge for the ODBC driver.
- Use the manual query mode to write a query using the following naming schema:
Note Local temp views, global temp tables, and local temp tables cannot be loaded into BOARD.
You can view the stack trace through the BOARD Options window.
- Click the BOARD button and select Board Options.
- Choose to open either:
- Error Log File: Contains the history of errors of the client and server.
- SQL Log: Displays the history of SQL commands the user executed during the current session. This log is not saved to a file.
The Auto Mode in BOARD does not function properly.
For More Information
|For More Information About…||…See|
Vertica Community Edition
Big Data and Analytics Community