Share this article:

Vertica Integration with PowerPivot: Connection Guide

To view this article in PDF format, click here.

About Vertica Connection Guides

Vertica connection guides provide basic information about setting up connections to Vertica from software that our technology partners create. This document provides guidance using the latest versions described in the next section.

PowerPivot and Vertica Versions Tested

This document is based on testing using the following versions:

Software Version
Partner Client

PowerPivot for Excel 2016

Desktop Platform

Windows Server 2012 R2

Vertica Client

Vertica Client 9.1.0, ODBC and OLE DB drivers

Vertica Server

Vertica Server 9.1.0

PowerPivot Overview

PowerPivot is an add-in for Microsoft Excel. With PowerPivot, you can import millions of rows of data from multiple data sources into a single Excel workbook.

See the PowerPivot page on the Microsoft website for details.

Download and Install PowerPivot  

To download and install PowerPivot, follow these steps:  

  1. Go to the Install PowerPivot for Excel page.
  2. Verify that your system meets all hardware and software requirements.
  3. Follow the instructions for downloading and installing PowerPivot.

Download and Install the 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 and install the client drivers as follows:

  1. Go to the Vertica Client Drivers page.
  2. Download the version of the Vertica client package that is compatible with the architecture of your operating system and Vertica server version.
  1. Based on the client driver package you downloaded, follow the installation instructions in the Vertica documentation.

Note Vertica drivers are forward compatible, so you can connect to the Vertica server using earlier versions of the client.

In Vertica 9.1, ODBC is both forward and backward compatible. The ODBC driver was made backwards compatible starting in Vertica 8.1.1 to enable connection to most Vertica server versions.

For more information about client and server compatibility, see Client Driver and Server Version Compatibility in 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.

Important The Vertica Connectivity Pack installer provides both the 32- and the 64-bit ODBC drivers. However, only the 32-bit ODBC driver is visible within PowerPivot, because PowerPivot is a 32-bit application.

For ODBC connections, we recommend that you create a User DSN (not a System DSN), because a User DSN automatically uses the appropriate bitness for the connection. If you create a System DSN, then you must create both a 32- and a 64-bit DSN with the same name.

Follow these steps to create the DSN:

  1. Create a User DSN based on this executable:
    C:\Windows\System32\odbcad32.exe

    A User DSN is both a 32- and 64-bit DSN.

  2. As shown in the following graphic, check the following DSN settings:

    - Report Unicode columns as char
    - Convert square bracket identifiers

    Note Three part naming is selected by default.

  3. Click OK.

Create a Connection to Vertica

Create a connection to Vertica using either of the following methods:

The steps that follow create a connection to the Vertica VMart example database.

Connect to Vertica using OLE DB

Create a connection to your database using OLE DB, as follows:

  1. Open Excel.
  2. Navigate to the PowerPivot tab.
  3. Click the Manage option.
  4. In the new window, click From Other Sources.

     DataSources.png

    The Table Import wizard opens.

  5. Select Others (OLE DB/ODBC) and click Next.
  6. Name the connection and click Build to create a connection string.
  7. Click the Provider tab and select Vertica OLEDB Provider.
  8. Click Next and enter your connection information:
    • Data source: Server name or IP address
    • Vertica database, username, and password
  9. Check the Allow saving password box to avoid a connection error.
  10. Click the All tab.
  11. Type the port number in the Port field.
  12. Return to the Connection tab.
  13. Click Test Connection.
  14. Click OK. The connection string appears.
  15. 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 screenshot:

    ConvertSquareBrackets.png

  16. Click Test Connection and click OK.
  17. Click Next.

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

Connect to Vertica using ODBC

Create a connection to your database using ODBC as follows:

  1. Open Excel.
  2. Navigate to the PowerPivot tab.
  3. Click the Manage option.
  4. In the new window, click From Other Sources.

    DataSources.png

    The Table Import wizard opens.

  5. Select Others (OLE DB/ODBC) and click Next.
  6. Name the connection and click Build to create a connection string.
  7. Click the Provider tab and select Microsoft OLEDB Provider for ODBC Drivers.
  8. Click Next and enter your connection information.
  9. Click Test Connection.
  10. Click OK. The connection string appears.
  11. Click Next.

Import Tables

Add tables to your connection as follows:

  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 view 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. This topic describes 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 is not updated 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: 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.

Known Limitations

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

Driver Compatibility

  • PowerPivot connections to Vertica work only with ODBC & OLEDB. ADO.NET is not supported.

Data Type Incompatibilities with OLEDB

  • CHAR, VARCHAR, and LONGVARCHAR support up to 32766 characters. Anything higher gets truncated without an error.
  • INT data type is supported up to 18 precision. Anything higher returns the following error:
    The <ColumnName> column of the <QueryName> table contains a value <Value>,  which is not supported
  • NUMERIC data type is supported with 15 precision. For values higher, the value is rounded.
  • TIME, TIMETZ, BINARY, VARBINARY, and LONGVARBINARY data types are not supported. No error appears if you use these data types.
  • TIMESTAMP data type does not support milliseconds.

• TIMESTAMPTZ data type does not support milliseconds and Timezone.

• DATE, TIMESTAMP, and TIMESTAMPTZ data types, the minimum value 01/01/0001 is not supported.

Data Type Incompatibilities with ODBC

  • CHAR, VARCHAR, and LONGVARCHAR support up to 32766 characters. Anything higher gets truncated without an error.
  • INT data type is supported up to 18 precision. Anything higher returns the following error:
    The <ColumnName> column of the <QueryName> table contains a value <Value>,  which is not supported
  • NUMERIC data type is supported with 15 precision. For values higher, the value is rounded.
  • BINARY, VARBINARY, LONGVARBINARY, INTERVAL HOUR TO SECOND, INTERVAL HOUR TO MINUTE, INTERVAL, and INTERVAL YEAR TO MONTH data types are not supported. No error appears to the user.
  • For Date data type, the minimum date is not supported. The following error appears if you use a minimum date:
Data overflow converting to the data type for table <Table Name> column <Column Name>
  • Milliseconds are not supported in TIME, TIMESTAMP, and TIMETZ data types.
  • TIMETZ and TIMESTAMPTZ do not support Timezone.
  • For TIMESTAMP data type, the minimum value 01/01/0001 is not supported. The following error appears:
Data overflow converting to the data type for table <Table Name> column <Column Name>

For More Information

For more information, see the following resources:

Share this article: