Vertica Integration with Microsoft Power BI: 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 BI: Latest Versions Tested

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

Software Version
Partner Product

Power BI Desktop version 2.104.941.0 64-bit (May 2022)

Power BI Service Version 13.0.18400.37

Power BI Standard Mode On-premises Data Gateway version 3000.126.8 (May 2022)

Desktop Platform

Windows Server 2019

Vertica Client

Vertica 10.1 ODBC driver (Power BI Vertica connector)

Vertica 11.1.1 ODBC driver (generic ODBC connection)

Vertica Server Vertica 11.1.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:

  • Power BI Desktop is 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 the blog Comparing Power BI Report Server and the Power BI Service.

Note This guide addresses the connection of Vertica with Power BI Desktop and Power BI Service (SaaS) only.

Prerequisites

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

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

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

Connecting to Vertica from Power BI Desktop

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

Option 1: Vertica named Connector

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 is built into the Vertica named connector.
  • DirectQuery is supported from Power BI Service through 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

You can connect Power BI Desktop to Vertica using the Vertica ODBC driver that is built into the application. The native connector will not use or conflict with an existing Vertica ODBC driver installed previously on the system. The built-in ODBC driver version is 9.2.

Power BI allows Import as well as DirectQuery connections to Vertica. DirectQuery enables push down to Vertica. This lets you work with large volumes of data and leverage the speed of Vertica.

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 Database 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 username 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 Transform Data.

    The Power Query Editor opens.

    For information about the Power 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

  1. Click File > Options and Settings > Options.

    The Options window opens.

  2. On the left side of the screen under Global, select Data Load.

  3. On the right side of the screen click Clear Cache.

Installing the Vertica ODBC 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 Data Source Name (DSN).

Follow these steps to install the driver:

  1. Go to the Vertica Client Drivers page.
  2. Download the client driver package for Windows.

    Note  

    The Vertica ODBC driver is now both forward and backward compatible. For more information about client and server compatibility, see Client Driver and Server Version Compatibility in the Vertica documentation.

  3. Double-click the installer and follow the prompts to install the ODBC driver.

Creating an ODBC Data Source Name (DSN)

Power BI Desktop is available as a 64-bit and 32-bit application. If you download and install the 32-bit version of Power BI Desktop, create a 32-bit DSN. If you download and install the 64-bit version of Power BI Desktop, 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. For more information on how to configure the setting, see Troubleshooting.

Creating 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 your DSN from the Data source name (DSN) drop-down.

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

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

  • Selecting 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 Transform Data.

    The Power Query Editor opens.

    For information about the Power 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.

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

    Alternatively, click Select Related Tables. This action automatically selects the dimensions tables related to the fact table. Click Select Related Tables after you have selected the fact table: inventory_fact.

  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 Transform Data.

    The Power Query Editor opens.

    For information about the Power 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 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.

The Power BI On-Premises Data Gateway

Power BI Service connects to Vertica 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 the on-premises data gateway.

The on-premises data gateway does not involve Power BI Report Server. See Power BI Overview for information about Power BI Report Server.

The following configuration is required in order to access a DirectQuery‑based report from Power BI Service:

  1. Installing the On-Premises Data Gateway
  2. Configuring the On-Premises Data Gateway
  3. Configuring the Published Report

Each of these configuration steps are described below:

Installing the On-Premises Data Gateway

  1. Download the standard mode of 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.

Configuring 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 Installing the On-Premises Data Gateway in this document.

  4. Click On-premises data gateways to check and verify that your gateway is up and running and displays as Online.

  5. Now, add the Vertica connection that the published report uses to the gateway. On the same screen, click DATA SOURCE and click +NEW to create a connection.

  6. In the New data source screen, select the name of your gateway from the Gateway cluster name drop-down list.

  7. Type a name for your Vertica data source in the Data Source Name box, and select Vertica from the Data Source type drop-down list.

  8. Type your Vertica database connection information, including Server, Database, select Basic for Authentication method, and provide the Username and Password.

  9. Click Create.

    The connection should display as successful.

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

Configuring the Published Report

  1. In the gear menu on the top right-hand side of the Power BI Service screen, click Settings.

  2. In the new screen, select Datasets from the top menu.

    The published Vertica reports will be listed.

  3. Select the report you want to access and expand Gateway connection on the right side of the screen.

  4. Select the gateway you configured as described in the Configuring the On-Premises Data Gateway section and from the Maps to drop-down list select the Vertica connection used by your report.

  5. Click Apply.

  6. Repeat steps 1 to 5 for every published report 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 open the published report from Power BI Service (SaaS):

Troubleshooting

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

Standard Mode On-Premises Data Gateway cannot be configured in Power BI Service

Issue: The Standard Mode On-Premises Data Gateway is unable to connect to Vertica from Power BI Service and displays the following error message. This issue impacts all releases of the Standard Mode On-Premises Data Gateway beginning May 2020 version: 3000.40.15.

Solution: Download the “Visual C++ Redistributable Packages for Visual Studio 2015” from https://www.microsoft.com/en-in/download/confirmation.aspx?id=48145 and install it on the machine where the Standard Mode On-Premises Data Gateway is running. After installing this dependency, you should be able to configure the gateway in Power BI Service as described in the Connecting to Vertica from Power BI Service section.

Accessing a DirectQuery-based Report from Power BI Service

Issue #1: Power BI Service displays the following error message when a DirectQuery-based report has not been configured properly in Power BI Services:

Solution: Install the standard mode on-premises data gateway and configure the gateway, Vertica connection, and published report as described in the Connecting to Vertica from Power BI Service section.

Issue #2: Power BI Service displays the following error message when an on-premises data gateway has not been installed or configured properly:

Solution: Download and install a standard mode on-premises data gateway and configure it as described in the Connecting to Vertica from Power BI Service section.

DirectQuery does not support DAX functions/transformations

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

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.

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

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

  • CHAR, VARCHAR, and LONG VARCHAR data types are truncated to 32766 characters for import-based reports. For DirectQuery-based report, values are supported up to 32763 characters above which the “Memory Error” message is displayed.
  • INTEGER and NUMERIC data types are supported up to 15 digits of precision above which the value is rounded off.
  • INTERVAL, BINARY, VARBINARY, and LONG VARBINARY data types are not supported.
  • For DATE, TIMESTAMP, and TIMESTAMPTZ data types, minimum value supported is 01-01-0100.
  • For TIME, TIMESTAMP, TIMETZ, and TIMESTAMPTZ data types, milliseconds are not displayed .
  • For TIMETZ and TIMESTAMPTZ data types, time zone offset is not displayed.

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