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.

Vertica and Power Pivot: Latest Versions Tested

This document is based on testing using the following versions:

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.

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.

3. Follow the installation instructions in the Vertica documentation.

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

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.
   

   

   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.
   • Data source: select the DSN you created in Create an ODBC Data Source Name (DSN).
   • Enter your database name, username and password if prompted.
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.

5. Click Preview & Filter. You can view the data and apply any necessary filters.
6. 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:

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 LONG VARCHAR data types display up to 32766 characters beyond which the value is truncated without an error.
• INTEGER data type display 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 display up to 18 digits of precision beyond which the value is rounded.
• BINARY, VARBINARY, LONG VARBINARY, INTERVAL HOUR TO SECOND, INTERVAL HOUR TO MINUTE, INTERVAL, and INTERVAL YEAR TO MONTH data types are not displayed and there is no error message.
• For DATE data type, the minimum date is not displayed. 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 displayed.

• For TIME data type, current date is appended before the time value.

• TIMETZ and TIMESTAMPTZ do not display timezone.
• For TIMESTAMP and DATE data type, the minimum displayed 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:

• Power Pivot website
• Vertica Community Edition
• Vertica User Community
• Vertica Documentation