Share this article:

Vertica Integration with PowerPivot: Connection Guide

Applies to Vertica 7.2.x and earlier 

About Vertica Connection Guides

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 PowerPivot as of April, 2016.

PowerPivot Overview

PowerPivot is an add-in for Microsoft Excel that enables users to import millions of rows of data from multiple data sources into a single Excel workbook.

This document is based on the results of testing Vertica 7.2.2 with PowerPivot for Excel 2010 on a Windows 64-bit machine. In addition, this document describes connection information for using both the OLE DB and ODBC drivers.  

Download and Install PowerPivot  

To download PowerPivot, follow these steps:  

  1. Navigate to the Install PowerPivot for Excel
  2. Verify that your system meets all hardware and software requirements.
  3. Follow the instructions for installation.

Vertica Client Drivers

Before you can connect to Vertica using PowerPivot for Excel, you must install the Vertica client driver package. This package includes the OLE DB and ODBC client drivers. PowerPivot can use either driver to connect to Vertica.

Download Vertica Client Drivers

  1. Go to the Vertica Client Drivers
  2. Download the version of the Vertica client package that is compatible with the architecture of 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 about client and server compatibility, see Client Driver and Server Version Compatibility in the Vertica documentation.

Install Vertica Client Drivers

Based on the client driver package you downloaded, follow the steps for installation from the Vertica documentation.

Create an ODBC Data Source Name (DSN)

If you are using the ODBC client driver, you must create a DSN to connect to Vertica from PowerPivot. Follow these steps to create a DSN:

  1. Create a 64-bit DSN with the executable located at: C:\Windows\System32\odbcad32.exe
  2. As shown in the following graphic, check these two DSN settings:
    • Report Unicode columns as char
    • Convert square bracket identifiers

     DSN.png

Create a Connection to Vertica

You can create a connection to Vertica using either of the following methods:

The steps that follow show a connection the Vertica VMart sample database.

For a short introduction on connecting using the ODBC driver, watch this connection video: 

Connect to Vertica using OLE DB

Follow these steps to create a connection to your database using OLE DB:

  1. Open Excel.
  2. Navigate to the PowerPivot tab.
  3. Click the PowerPivot window.
  4. In the new window, click the Get External Data from Other Sources The Table Import Wizard opens.

     DataSources.png

  1. Select Others (OLE DB/ODBC) and click Next.
  2. Name the connection and click Build to create a connection string.
  3. Click the Provider tab and select Vertica OLEDB Provider.
  4. Click Next and enter your connection information:
    • Data source: Server name or IP address
    • Vertica database, username, and password
  1. Check the Allow saving password box to avoid a connection error.
  2. Click Test Connection.
  3. Click OK. The connection string appears.
  4. To verify that the driver supports Microsoft query syntax, add the setting ConvertSquareBracketIdentifiers = True to the end of the connection string as shown in the graphic:

    ConvertSquareBrackets.png

13. Click Test Connection and click OK.

14. Click Next.

For more information about square bracket identifiers, see OLE DB Connection Properties and Data Source Name Connection Propertiesin the Vertica documentation.

Connect to Vertica using ODBC

Follow these steps to create a connection to your database using ODBC:

  1. Open Excel.
  2. Navigate to the PowerPivot tab.
  3. Click the PowerPivot window. 
  4. In the new window, click the Get External Data from Other Sources The Table Import Wizard opens.
  5.  DataSources.png

  1. Select Others (OLE DB/ODBC) and click Next.
  2. Name the connection and click Build to create a connection string.
  3. Click the Provider tab and select Microsoft OLEDB Provider for ODBC Drivers.
  4. Click Next and enter your connection information:
  • Data source: 64-bit DSN to Vertica
  • Vertica database, username, and password
  1. Click Test Connection.
  2. Click OK. The connection string appears.
  3. Click Next.

Import Tables

Follow these steps to add tables to your connection:

  1. From the Table Import Wizard, click Select from a list of tables and views.
  2. Click Next.
  3. Select a fact table.
  4. Click Select Related Tables to select all the dimension tables related to your fact table.
    If you are using the ODBC driver, manually select the tables.
  5.  ImportTables.png

  1. Click Preview & Filter. You can look at the data and apply any necessary filters.
  2. Click Finish to import the data.

Troubleshooting

You may encounter some issues when connecting to Vertica using PowerPivot. The following section details known issues and workarounds.

Select Related Tables Button Bug

OLE DB Driver

Each time you click the Select Related Tables button, additional related tables are added to the import. However, the interface does not update to reflect the number of tables selected. Instead, the interface only shows the number of related tables selected during the first time you clicked the button. Currently, there is no workaround for this issue.

ODBC Driver

The Select Related Tables button does not identify the dimension tables associated with the fact tables. You must manually select the related tables.

Preview & Filter Button Bug

OLE DB Driver

If you click the Preview & Filter button, you may see the following error:

Failed to retrieve data from warehouse_dimension. Reason: ERROR [42601] ERROR 4856:  Syntax error at or near "[" at character 8

To resolve this error, manually edit the connection string to add ConvertSquareBracketIdentifiers = True.

ODBC Driver

If you click the Preview & Filter button, you may see the following error:

Failed to retrieve data from warehouse_dimension. Reason: Cannot set column 'warehouse_name'. The value violates the MaxLength limit of this column.

If you see this error, check your DSN settings and verify that Report Unicode columns as char is selected.

If you click the Preview & Filter button, you may also see the following error:

Failed to retrieve data from warehouse_dimension. Reason: ERROR [42601] ERROR 4856:  Syntax error at or near "[" at character 8.

If you see this error, check your DSN settings and verify that Convert square bracket identifiers is selected.

Import Failure

OLE DB and ODBC Driver


Your import could fail and show the following error:

ImportFailure.png

If you see this error, manually edit the connection string to add ConvertSquareBracketIdentifiers = True.

For More Information

For More Information About… See…

PowerPivot

https://support.office.com/en-gb/article/Power-Pivot-Add-in-a9c2c6e2-cc49-4976-a7d7-40896795d045

Vertica Community Edition

https://my.vertica.com/community/

Vertica Documentation

http://my.vertica.com/docs/latest/HTML/index.htm

Big Data and Analytics Community

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

Share this article: