Share this article:

Vertica Integration with Microsoft Power BI: Connection Guide

To read this document in PDF format, click here.

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 BI: Latest Versions Tested

This document is based on our testing using the following versions:

Software Version
Partner Product

Power BI Desktop version 2.63 64-bit (October 2018 release)

Power BI On-premises Data Gateway (October release build number 3000.0.144.3)

Desktop Platform

Windows Server 2012

Vertica Client

Vertica 7.2 ODBC driver (Power BI Vertica connector)

Vertica 9.1 ODBC driver (generic ODBC connection)

Vertica Server Vertica 9.1

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 components:

  • 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 Service.

  • Power BI Service 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 Service, you need a web browser and an email address.

    Power BI Service is also known as Power BI Server 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 Service on your mobile device.

  • Power BI Report Server is the on-premises version of Power BI Service. It is used in cases when the customer wants to host the Server on their own infrastructure. It requires a SQL Server Report Server instance and it has a different release cycle than Power BI Service. Also, it uses a separate install of Power BI Desktop that is optimized for Report Server.

For a comparison between Power BI Service and Power BI Report Server, read this blog: https://docs.microsoft.com/en-us/power-bi/report-server/compare-report-server-service

To download Power BI, go to https://powerbi.microsoft.com/en-us/downloads/.

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

    For information about VMart, 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 instructions.

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.

    For details about DirectQuery, see the following topics in the Power BI documentation:

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

Connect to Vertica from Power BI Desktop

Currently Power BI provides two options for connecting to your Vertica database:

  • Option 1: New Vertica named connector

    Previously in beta, the Vertica named connector is now generally available.

    • Allows you to build DirectQuery-based and import-based reports against your Vertica database. For information about DirectQuery and import, see Power BI Connection Modes - DirectQuery vs Import in this guide.
    • 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.
    • DirectQuery does not support DAX functions/transformations. DAX functions may be required to model the data and build the reports.
    • DirectQuery is supported from Power BI Service through the on-premises data gateway.

    Important

    To refresh datasets based on Vertica data in the Power BI Service, you need to upgrade to the October 2018 release of the on-premises data gateway.

  • Option 2: Generic ODBC connection

    • Allows you to build import-based reports against your Vertica database. For information about import connection mode, see Power BI Connection Modes - DirectQuery vs Import in this guide.
    • You must download and install Vertica’s ODBC driver and create an ODBC DSN that Power BI uses to connect to Vertica.
    • You must schedule periodic refreshes in Power BI Service over a gateway in order to see the latest changes in the database.

Connecting to Vertica via the Vertica Named Connector

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 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 enables push down to Vertica. This lets you work with large volumes of data and leverage the speed of Vertica. For more information about DirectQuery connectivity, see Power BI Connection Modes - DirectQuery vs Import.

Note  

By design, the Vertica named connector does not allow writing SQL statements. This option is only available with Import mode using a generic ODBC connection. Writing SQL statements is highly recommended when using a generic ODBC connection to narrow down the data transfer into Power BI and to improve performance when working with large datasets.

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

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

  1. Click Connect.
  2. In the connection window, enter the connection information for your Vertica database:

  3. Select the Data Connectivity mode. Import is selected by default.

  4. Click OK.

  5. If prompted, enter your database user name and password:

  6. Click Connect.
  7. In the Navigator window, expand the database name to list the schemas.
  8. 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 database. From the Public schema, select the inventory_fact table and its related dimension tables:

  9. Click Load.

    For DirectQuery mode, the status of the load operation displays as follows:

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

    For Import mode, the status of the load displays as follows:

    Evaluating...
    Waiting for other queries...
    Creating connection in model...
    Loading data to model...
    xxx rows from <vertica_server>;<db_name>.
    Detecting relationships...
  10. Alternatively, to filter, transform, and shape your data before Load, click Edit.

    The Query Editor opens.

    For information about the Query Editor, see Shape and Combine Data on 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.

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

Install the Vertica ODBC Driver

Important  

If you are using the new Vertica named connector you don’t need to 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).

Follow these steps to install the 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.

  3. Based on the client driver package you downloaded, follow the installation instructions in 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 an ODBC DSN in the Vertica documentation.

To configure the Report Unicode columns as char setting, follow these steps:

  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

Create the Connection

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

  • Supply a Query to Import Data

    Choose this option if you are working with very large tables. By providing a query, you can reduce the number of rows and columns that you are importing.

    To enter a query, select Advanced options.

  • Select Tables to Import

    To select the tables from which you want to import data, click OK.

Supply 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:

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

  3. To continue with the import, click OK.

    Power BI Desktop displays the query results:

    data_from_query_to_import.png

  4. To import the data from the selected tables, click Load.

    The status of the load displays as follows:

    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...
  5. Alternatively, to filter, transform, and shape your data before importing, click Edit.

    The Query Editor opens.

    For information about the Query Editor, see Shape and Combine Data on 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, type your database user name and password if required.
  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 load status displays as follows:

    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 the Query Editor, see Shape and Combine Data on 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 from Power BI Service

After you design your reports in Power BI Desktop, you can choose to publish those reports to Power BI Service in order to share them with other users. You access Vertica from Power BI Service when you open a published report. Power BI Service connects using a DirectQuery connection via the on-premises data gateway.

Important

In order to refresh datasets based on Vertica data in the Power BI Service, you need to upgrade to the October 2018 release of the on-premises data gateway.

The October release enhancements do not involve the Power BI Report Server. See Vertica Integration with Microsoft Power BI: Connection Guide for information about the Power BI Report Server.

The Power BI On-Premises Data Gateway

You must install and configure the October 2018 release of the on-premises data gateway in order to:

  • Publish a DirectQuery-based report from Power BI Desktop to Power BI Service.
  • Connect live to your Vertica database from Power BI Service.

Download and Install the On-Premises Data Gateway

  1. Download the on-premises data gateway from the Power BI downloads website.
  2. Double click the downloaded file and follow the prompts for installation.

Note  

You should install the gateway on a machine that is always up and running and that Power BI users can access at all times.

Configure the On-Premises Data Gateway

After installation, you must configure the gateway from Power BI Service so that it works with your Vertica connection:

  1. Log in to your Power BI Service account.
  2. In the gear menu on the top right-hand side of the Power BI Service screen, click Manage gateways.

  3. In the GATEWAY CLUSTERS screen, select from the list of gateways the name of the gateway you installed as described in Download and Install the On-Premises Data Gateway in this document.

  4. Verify that your gateway is up and running and displays as Online.

  5. On the same screen, click ADD DATA SOURCE.

  6. In the new screen, type a name for your Vertica data source in the Data Source Name box, and select Vertica from the Data Source type list.

  7. Type your Vertica database connection information, including Server, Database, Username and Password.

  8. Click Add.

    The connection should display as successful.

  9. Repeat steps 5 to 8 for every unique Vertica connection you want to access from Power BI Service.

Once you have configured your gateway as well as the Vertica connection that the DirectQuery-based report is using, you should be able to publish your report. You will see the following message that confirms that publishing to Power BI Service was successful:

Lastly, you are now able to open the published report from Power BI Service (SaaS):

Known Issues and Work-Arounds

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

Publishing to Power BI Service fails using DirectQuery

Issue #1: You may see the following error when publishing a DirectQuery-based report from Power BI Desktop to Power BI Service if the on-premises data gateway is not installed:

Solution: Power BI Server requires the October 2018 release of the on-premises data gateway in order to connect LIVE to Vertica. Install and configure the October 2018 release of the on-premises data gateway from: https://powerbi.microsoft.com/en-us/downloads/

Issue #2: You may see the following error when publishing to Power BI Service if the on-premises data gateway hasn’t been configured correctly inside Power BI Service:

Solution: After installing the on-premises data gateway, make sure to configure the gateway in order to connect to Vertica using DirectQuery. The configuration should be performed in Power BI service > Manage gateways. To configure the on-premises data gateway in Power BI Service, follow the instructions in Configure the On-Premises Data Gateway in this document.

DirectQuery doesn’t support DAX functions/transformations

Issue: DAX functions are not supported when working in DirectQuery mode. You may see an error like the following:

Solution: If you require DAX functions to model the data and build the reports, try performing the calculations and transformations in Vertica, for example, by using live aggregate projections, as described in Use Live Aggregate Projections. Push down the execution of these calculations to the database, and use Power BI to create the visualizations only.

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. 

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.

For More Information

Share this article: