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.

About BOARD

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:

  1. Request a demo for the BOARD client by entering your information and clicking Download.
  2. 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.
  3. 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.
  4. Select a destination to install the BOARD client.
  5. To complete the installation process, click Finish.
  6. Once setup is complete, launch the BOARD client program from the Windows programs menu. The BOARD application opens.Board1.png

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.

  1. Open the BOARD folder and select the BOARD Server
  2. Run the setup program. Follow the onscreen installation steps.
  3. Select a destination to install the BOARD server.
  4. To complete the installation, click Finish.
  5. 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

  1. Go to the Vertica Client Drivers page. 
  2. 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:

  1. 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.
  2. The ODBC Data Source Administrator displays. In the System DSN tab, click Add.Board2.png
  3. In the Create a New Data Source dialog box, select Vertica driver and click Finish. The Vertica ODBC DSN configuration dialog box appears.
  4. In the Basic Settings tab, fill in the following required fields:
    • 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.
  5. Click OK. The Vertica ODBC DSN configuration window closes and your new DSN is listed in the ODBC Data Source Administrator window.
  6. To close the ODBC Data Source Administrator window, click OK.

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

  1. Navigate to the Database tab of the Ribbon bar and click the New DB icon () in the Utilities section.
  2. Type the database name and click OK.Board4.png

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.

  1. In the Database tab of the Ribbon bar, click Data Reader in the Database Manager section. The Data Reader window opens.

    Board5.png

  2. Navigate to the SQL tab in the Data Reader and click the Action icon. 
  3. Select New Protocol.
  4. The SQL Reader window opens. In the Title field, type the protocol name. 

    Board7.png

  5. In the upper right hand corner, click New
  6. Set the connection parameters. 
  7. Under Connection Source, click OLE-DB Wizard.

    Board8.png

  8. 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.
  9. In the Connection tab, select the ODBC connection that was created earlier in the drop-down list. 
  10. Enter the username and password. Click Test Connection.

    Board9.png

  11. A dialog box appears stating the connection was successful. Click OK. A connection string appears in the Connection String field. 
  12. Select the RDBMS type. If the relation database is not listed, set the type to General
  13. 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.
  14. If required, enter the connection user name and password. Click Save & Connect.

    Board10.png

  15. 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. 

    Board11.png

  16. In manual mode, queries must be written manually. Click Browse and the SQL result window appears. For example, when you query the Char1_Table: 

    Board12.png

    All tables are queried in a similar fashion.

Test Database Objects

You can test other database objects by following these steps:

  1. Create a connection to Vertica using the OLE-DB bridge for the ODBC driver.
  2. Use the manual query mode to write a query using the following naming schema:

    <schema_name>.<table_name>

Note Local temp views, global temp tables, and local temp tables cannot be loaded into BOARD.

Troubleshooting

You can view the stack trace through the BOARD Options window.

  1. Click the BOARD button and select Board Options.
  2. Board13.png

  3. 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.

Known Limitations

The Auto Mode in BOARD does not function properly.

For More Information

For More Information About… …See

BOARD  

http://www.board.com/us/

BOARD Documentation

http://help.board.com/

BOARD Support

http://support.board.com/

Vertica Community Edition

https://vertica.com/community/

Big Data and Analytics Community

https://vertica.com/big-data-analytics-community-content/