Share this article:

Vertica Integration with Microsoft Power BI: Connection Guide

For Vertica 9.1.x

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.

About This Document

This document describes how to connect Microsoft Power BI Desktop to Vertica 9.1 using the Vertica ODBC driver and how to work with Vertica data.

The Vertica Partner Engineering team tested Vertica 9.1 and Power BI version 2.59 64-bit (June 2018 Release) on Windows Server 2012.

Power BI Overview

Power BI is a business intelligence solution by Microsoft Corporation that provides tools to analyze data and build interactive dashboards that can be made available in the cloud and on mobile devices. Power BI consists of the following:

  • A Windows desktop application for exploring your data and building reports. You can publish Power BI Desktop reports to the web and share them with others via Power BI server.

  • Power BI Server is a Software as a Service product for viewing and sharing data reports that you build using Power BI Desktop. To use the Power BI Server, you need a web browser and an email address. Power BI Server Pro has additional functionality, such as the ability to schedule data refreshes, for a monthly fee.

    Note Power BI Server is also known as Power BI Service and Power BI Site.

  • Power BI Mobile provides apps for Android, iOS, and Windows phones. These apps allow you to view reports published on Power BI Server on your mobile device.

To download these products, go to the Power BI downloads page.

Before You Start

Before you connect to Vertica using Power BI Desktop, you must:

  • Install and start Vertica. If you have not installed Vertica, go to the Vertica download page and download the Vertica Community Edition.
  • Deploy the VMart example database. The examples in this document use the VMart database.

    For information about the VMart database, see Introducing the VMart Example Database in the Vertica documentation.

Download and Install Power BI Desktop

To download Power BI Desktop, go to Microsoft Power BI Downloads.

Under Microsoft Power BI Desktop, if you click Download, you download the 64-bit version. To download and install the 32-bit version, click Advanced download options and follow the instructions.

After the download completes, execute the downloaded file and follow the directions.

Power BI connection modes - DirectQuery vs Import

Power BI connects to Vertica using Vertica’s ODBC driver and accesses data in one of two ways:

  • DirectQuery connection mode: This connection type enables push down of the queries generated by Power BI reports to your Vertica database, and only transfers/imports the result of your queries into Power BI . After connecting, the data remains on the Vertica server.
  • Import connection mode: When you use this type of connection, Power BI Desktop transfers the data from Vertica into the Power BI Desktop cache. When you create or interact with a visualization, Power BI Desktop uses the imported data to render the visualizations. Imported data needs to be refreshed on a regular basis to reflect the latest changes in the database.

Important DirectQuery connection to Vertica is still under development and very limited. For this reason, Vertica recommends using Import mode until the development on the DirectQuery functionality has been completed by Microsoft.

For information about how DirectQuery connection works see Use DirectQuery in Power BI Desktop in the Power BI documentation.

To learn about what Data sources are supported by DirectQuery in Power BI see Data sources supported by DirectQuery in Power BI in the Power BI documentation.

Connect to Vertica from Power BI Desktop

Currently Power BI provides two options to connect to your Vertica database:

Option 1: New Vertica named Connector (Beta)

To access the Vertica (Beta) connector from the Get Data dialog, select Other > Vertica (Beta).

  • Allows Import as well as DirectQuery connection to Vertica. See DirectQuery vs Import in this guide.
  • DirectQuery is not supported from Power BI Server.
  • DirectQuery doesn’t support DAX functions/transformations. DAX functions may be required to model the data and build the reports.
  • Vertica’s ODBC driver ships with the new Vertica named Connector. You don’t need to download and install Vertica’s ODBC driver on the client machine.

Option 2: Generic ODBC connection

To access the generic ODBC connector from the Get Data dialog, select Other > ODBC.

  • Power BI Desktop imports Vertica data into the Power BI Desktop cache for processing. Also known as Import connection mode, See DirectQuery vs Import in this guide.
  • You are required to download and install Vertica’s ODBC driver and create an ODBC DSN that Power BI uses to connect to Vertica.
  • You are required to schedule periodic refreshes in Power BI Server over a gateway.

Important Vertica recommends using Import connection mode until the implementation of DirectQuery is complete and the connector is no longer in Beta. Connect to Vertica from Power BI using one of the following options:

  • Using the Power BI Vertica Connector (BETA) and selecting “Import” connection mode instead of “DirectQuery”.
  • Generic ODBC connection

Connecting to Vertica via the Vertica named Connector (Beta)

Power BI Desktop now bundles the Vertica ODBC driver with the application. You no longer need to download and install the Vertica ODBC driver on your Power BI machine unless you are using the generic ODBC connector to connect to Vertica. The new native connector (beta) will not use or conflict with an existing Vertica ODBC driver installed previously on the system.

The ODBC driver that is shipped with Power BI is version 7.2.x.

Power BI allows “Import” as well as “DirectQuery” connection to Vertica. DirectQuery connection through this connector is still under development and currently very limited. DirectQuery enables push down to Vertica, which means the queries triggered by the visualizations are executed in the database leveraging the speed of Vertica and working with large volumes of data. For more information about DirectQuery connectivity see DirectQuery vs Import connection mode in this guide. Also see Step 7 below for recommendations from Vertica.

Note By design the Vertica named connector (beta) doesn’t allow writing SQL statements, this option is available with Import mode using a generic ODBC connection. Writing SQL statements in generic ODBC connection is highly recommended to narrow down the data transfer into Power BI and improving performance when working with large datasets.

Connect to Vertica from Power BI Desktop using the Beta connector as follows:

  1. Open Power BI Desktop.
  2. Click on the Get Data icon in the home menu.
  3. Enter Vertica in the search input box or select it from the list of connectors Other:


  1. Click Connect. THe following warning appears indicating that the connector is in beta mode and bugs may appear:

  2. Click Continue.
  3. In the connection window enter the connection information to your Vertica database:

  4. Select the Data Connectivity mode, Import is selected by default.

    Important DirectQuery is currently limited. Vertica recommends using Import until the connector is no longer a beta connector.

  5. Click OK.

  6. If prompted, enter your database username and password:


  7. Click Connect.
  8. In the Navigator window, expand the database name to list the schemas.
  9. Expand the schemas you are interested in and select the tables to use for your analysis.

    In this example, you are connected to the VMart example database and, from the Public schema, select the product_dimension table and its related tables:


  10. Click Load. The status of the load operation appears as the following steps for Import mode:

    Evaluating...
    Waiting for other queries...
    Creating connection in model...
    Loading data to model...
    xxx rows from <vertica_server>;<db_name>.
    Detecting relationships...


    Or the following steps for the DirectQuery mode:

    Evaluating...
    Waiting for other queries...
    Creating connection in model...

  11. Alternatively, to filter, transform, and shape your data before Load, click Edit. The Query Editor opens. For information on using the Query Editor, see Shape and Combine Data at the Power BI Desktop website.

    When the load operation completes, the selected tables and columns appear on the right-hand side of the window, under Fields.

Connect to Vertica via a Generic ODBC connection

You can connect from Power BI Desktop to Vertica using a generic ODBC connection. Power BI Desktop imports data from Vertica into its cache to use in reports. When you work with your visualizations, you may need to re-import the Vertica data to make sure you have an up-to-date dataset. Before you re-import the data, you need to clear the Power BI Desktop cache. To do so, select File > Options and Settings > Options > Global Data Load.

Download and Install the Vertica Driver

Important If you are using the new Vertica named connector you don’t need to download and install Vertica’s ODBC driver. The new Vertica connector bundles the appropriate driver.

Before you can connect Power BI Desktop to Vertica using the generic ODBC connection, you must download and install the Vertica ODBC driver and create a 64-bit Data Source Name (DSN).

Download the Vertica ODBC Driver

  1. Go to the Vertica Client Drivers page.
  2. Download the version of the Vertica ODBC client package that is compatible with the architecture of your operating system.

Note The Vertica ODBC driver is now 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.

Install the Vertica ODBC Driver

Based on the client driver package you downloaded, follow the steps in Installing the Client Drivers from the Vertica documentation.

The client driver package provides several drivers, but you should install only the ODBC driver.

Create a DSN

Power BI is available as a 64-bit and 32-bit application. If you download and install the 32-bit driver, create a 32-bit DSN, If you download and install the 64-bit driver, create a 64-bit DSN.

After you have installed the ODBC driver, you need to set up a DSN and configure the Report Unicode columns as char setting.

To set up the DSN, follow the instructions in Setting Up a DSN in the Vertica documentation.

To configure the Report Unicode columns as char setting, do the following:

  1. Select Start > Control Panel > Administrative Tools > Data Sources (ODBC).
  2. Click System DSN.
  3. Select your DSN and click Configure.
  4. Click the Client Settings tab.
  5. Select Report Unicode columns as char. This setting tells the ODBC driver to tell Power BI Desktop that Vertica uses the ODBC CHAR data type.

    report_unicode_as_char_setting.png

Connect using a generic ODBC connection as follows:

  1. To open Power BI Desktop, select Start > Microsoft Power BI Desktop > Power BI Desktop.
  2. In the initial window, on the Home tab, click Get Data.
  3. In the Get Data window, click Other.
  4. In the list of available connections, select ODBC and click Connect.
  5. In the From ODBC window, select the Data Source Name (DSN) you created as described earlier in this document.

    The database you designated in your DSN appears in the left-hand column of the Navigator window.

  6. Next specify the data to import by writing a query or selecting tables.

Import Data from Vertica into Power BI Desktop

In the From ODBC window, after you have entered your DSN, you have two options for importing data:

  • If you are working with very large tables, enter a query. Doing so allows you to reduce the number of rows and columns that you are importing. To enter a query, select Advanced options.
  • To select the tables from which you want to import data, click OK.

Enter a Query to Import Data

  1. In the From ODBC window, click Advanced options.
  2. Under SQL statement (optional), enter a query. The example in this section uses the following query to retrieve data from the inventory_fact table in VMart:

  3. --Inventory fact table - analysis
    SELECT
        date,
        full_date_description,
        day_of_week,
        calendar_month_name,
        calendar_month_number_in_year,
        calendar_year_month,
        calendar_quarter,
        calendar_year_quarter,
        calendar_year,
    public.product_dimension.product_key || public.product_dimension.product_version as product_version_key,
        product_description,
        sku_number,
        category_description,
        department_description,
        package_type_description,
        package_size,
        fat_content,
        diet_type,
        warehouse_name,
        warehouse_city,
        warehouse_state,
        warehouse_region,
        qty_in_stock
    FROM
        public.inventory_fact INNER JOIN public.date_dimension
        ON inventory_fact.date_key = date_dimension.date_key
        INNER JOIN public.warehouse_dimension 
        ON inventory_fact.warehouse_key = warehouse_dimension.warehouse_key
        INNER JOIN public.product_dimension
        ON inventory_fact.product_key = product_dimension.product_key 
        AND inventory_fact.product_version = product_dimension.product_version
    WHERE
        public.date_dimension.date >= (DATE '2003-01-01') AND 
        public.date_dimension.date <= (DATE '2017-12-31') AND
        discontinued_flag = 0;

    The From ODBC window looks like this:

    from_odbc_window_with_query.png

  4. To continue with the import, click OK.

    Power BI Desktop displays the query results:

    data_from_query_to_import.png

  5. To import the data from the selected tables, click Load. The status of the load appears as the following steps:

    Evaluating...
    Waiting for other queries...
    Creating connection in model...
    Loading data to model...
    Waiting for dsn=<my_dsn>.
    xxx rows from dsn=<my_dsn>.
    Detecting relationships...
  6. Alternatively, to filter, transform, and shape your data before importing, click Edit. The Query Editor opens.

    For information about how to use the Query Editor, see Shape and Combine Data at the Power BI Desktop website.

When the load operation completes, the selected tables and columns appear on the right-hand side of your window, under Fields.

Select Tables to Import

  1. In the From ODBC window, do not enter a query. Just click OK.
  2. In the Access a Data Source using an ODBC driver window, if required, enter your database user and password.
  3. Click Connect.
  4. In the Navigator window, expand the database name to list the schemas.
  5. Expand the schemas you are interested in and check the tables you want to import for your analysis. In this example, you connect to the VMart example database and, from the Public schema, import the inventory_fact table and its related dimension tables.

    • date_dimension on date_key
    • product_dimension on (product_key and product_version)
    • warehouse_dimension on warehouse_key

    The following image shows how to select the tables:

    navigator_window.png

  6. To import the data from the selected tables, click Load. The status of the load appears as the following steps:

    Evaluating...
    Waiting for other queries...
    Creating connection in model...
    Loading data to model...
    Waiting for dsn=<my_dsn>.
    xxx rows from dsn=<my_dsn>.
    Detecting relationships...
  7. Alternatively, to filter, transform, and shape your data before importing, click Edit. The Query Editor opens. For information about how to use the Query Editor, see Shape and Combine Data at the Power BI Desktop website.

When the load operation completes, the selected tables and columns appear on the right-hand side of the window, under Fields.

Data Type Limitations

Power BI Desktop does not support all data types supported by Vertica. Power BI Desktop does not load unsupported data types into the cache or display the data in dashboards. The following is a list of known limitations for data types:

  • BINARY, VARBINARY, LONG VARBINARY data types are not supported.
  • Long string values are truncated to 32766 characters.
  • Milliseconds and time zone offset are not displayed for TIMETZ and TIMESTAMPTZ data types.
  • INTERVAL YEAR TO MONTH, INTERVAL HOUR TO SECOND, INTERVAL HOUR TO MINUTE, and INTERVAL DAY TO SECONDS data types are not supported.
  • Maximum number of digits in numeric values is 15.
  • Some numeric values are displayed in scientific notation, for example:

    -79228162514264337593543950335 is displayed as
    -7.92281625142643E+28

    7922816251426433759354395.0335 is displayed as 7.92281625142643E+24

  • Digits to the right of the decimal point are truncated, for example:

    7.9228162514264337593543950335 is displayed as 7.92281625142643

    79228162514264.337593543950335 is displayed as 79228162514264.3

  • Some decimal values are rounded, for example:

    –99999999999999.9999 to –100000000000000

  • Large integer values are not supported. Maximum number of digits is 15. This error message is presented when loading very large integer values:

    integer_table_error.png

For information about Vertica data types, see SQL Data Types in the Vertica documentation.

For information about data types in Power BI Desktop, see Data types in Power BI Desktop.

Known Issues

Review these known issues and their workarounds when connecting to Vertica using Power BI Desktop.

String Data Too Big Error

Issue: You may see the following error when trying to load data from some Vertica data types:

Details: "ODBC: SUCCESS_WITH_INFO [01004] [Vertica][ODBC]
(10160) String data right truncation: String data is too big for the
output data buffer and has been truncated.

Solution 1: Configure the Report Unicode columns as char setting as follows:

  1. Select Start > Control Panel > Administrative Tools > Data Sources (ODBC).
  2. Click System DSN.
  3. Select your DSN and click Configure.
  4. Click the Client Settings tab.
  5. Select Report Unicode columns as char. This setting tells the ODBC driver to tell Power BI Desktop that Vertica uses the ODBC CHAR data type.

report_unicode_as_char_setting.png

Solution 2: Upgrade to Power BI Desktop 2.35 or later. 

Vertica named connector is a beta connector

Issue: Some of the limitations of the new beta connector include:

  • DirectQuery from Power BI Desktop doesn’t support DAX functions/transformations. DAX functions may be required to model the data and build the reports.
  • DirectQuery from Power BI Server is NOT supported. Because DirectQuery is not available from Power BI Server, any reports that you create in Power BI Desktop using a DirectQuery connection, won’t be able to be published to Power BI Server.

Solution: Use Import connection mode. Select an import connection mode when using the new Vertica named connector or a generic ODBC connection.

Report fails using DirectQuery

Publishing a report created in Power BI Desktop using DirectQuery fails with the following error:

For More Information

Share this article: