Share this article:

Vertica Integration with Power Pivot: 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.

Power Pivot Overview

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

See the Power Pivot page on the Microsoft website for details.

Vertica and Power Pivot: Latest Versions Tested

This document is based on testing using the following versions:

Software Version
Partner Product

Power Pivot for Excel 2016

Partner Product Platform

Windows Server 2016 R2

Vertica Client

Vertica ODBC Driver 10.0.1

Vertica Server

Vertica Server 10.0.1

Installing Power Pivot  

To download and install Power Pivot, 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 Power Pivot.

Installing the Vertica Client Driver

Power Pivot for Excel uses the ODBC driver to connect to your Vertica database. To download and install the client driver

  1. Go to the Vertica Client Drivers page.
  2. Download the client driver that is compatible with your Vertica server version.

    Note For more information about client and server compatibility, see Client Driver and Server Version Compatibility in the Vertica documentation.

  1. Follow the installation instructions in the Vertica documentation.

  2. Create a DSN as described in Creating an ODBC Data Source Name (DSN).

Creating an ODBC Data Source Name (DSN)

If you are using the ODBC client driver, you must create a DSN to connect to Vertica from Power Pivot.

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 Power Pivot, because Power Pivot 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 in the Client Settings tab:

    - Report Unicode columns as char
    - Convert square bracket identifiers

    Note Three part naming is selected by default.

  3. Click Test Connection, and then click OK
  4. Click OK to close the Vertica ODBC DSN Configuration window.

Connecting Power Pivot to Vertica

The steps that follow create a connection to the Vertica VMart example database using ODBC:

  1. Open Excel.
  2. Navigate to the PowerPivot tab.
  3. Click the Manage option. The PowerPivot Editor opens.
  4. In the PowerPivot Editor 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.

Importing 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 and other related tables.
The Select Related Tables button does not identify the dimension tables associated with a fact table. Manually select the related tables.

     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 Power Pivot. This topic describes known issues and workarounds.

Select Related Tables Button Bug

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

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

Failed to retrieve data from <my table>. 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

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 Power Pivot with Vertica, be aware of the following known limitations:

  • CHAR, VARCHAR, and LONGVARCHAR support up to 32766 characters beyond which the value is truncated without an error.
  • INT data type supports up to 18 digits beyond which the following error is displayed:
    The <ColumnName> column of the <QueryName> table contains a value <Value>,  which is not supported
  • NUMERIC data type supports up to 18 digits of precision beyond which 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 and no error is displayed.
  • 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>
  • For TIME, TIMESTAMP, and TIMETZ data types, milliseconds are not supported.
  • For TIME data type, current date is appended before the time value.

  • TIMETZ and TIMESTAMPTZ do not support Timezone.
  • For TIMESTAMP and DATE data type, the minimum supported value is 01/01/0100 below which 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: