Share this article:

Vertica Integration with Microsoft Power BI: Connection Guide

For Vertica 7.2.x

About Vertica Connection Guides

Vertica connection guides provide basic information about setting up connections to Vertica from software that our technology partners create.

These documents provide guidance using one specific version of Vertica and one specific version of the third-party vendor’s software. Other versions of the third-party product may work with Vertica. However, these other versions may not have been tested. This document provides guidance using the latest versions of Vertica and Microsoft Power BI as of May, 2016.

About This Document

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

You will learn:

  • How to install Power BI Desktop and how to connect Power BI Desktop to Vertica and work with data
  • How to import Vertica data into Power Bi Desktop and model and create reports with that data
  • How to publish reports to Power BI Server
  • How to create a secure gateway and refresh Vertica data in Power BI Server over that gateway

The Vertica Partner Engineering team tested Vertica 7.2.x and Power BI version 2.35 (last released in May 2016) on Windows Server 2008. The team tested the 64-bit version of Power BI in May, 2016.

Power BI Overview

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

Power BI has three products:

  • Power BI Desktop is a Windows desktop application for exploring your Vertica data and building reports.

    Power BI Desktop reports are published to the web and shared with others via Power BI server.

  • Power BI Server is a Software as a Service product for viewing and sharing data visualizations (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.

For all downloads, go to the Power BI downloads page.

Before You Start

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

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 optionsand follow the instructions.

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

Download and Install the Vertica Driver

Before you can connect Power BI Desktop to Vertica, 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 Vertica drivers are forward compatible, so you can connect to the Vertica server using previous versions of the client. 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 Power BI Desktop to Vertica

You can connect from Power BI Desktop to Vertica using ODBC. Power BI Desktop imports data from Vertica into its cache to use in reports.

  1. To open Power BI Desktop, select Start menu > 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. Continue to the next section to learn how to import data from Vertica into Power BI Desktop.

How Power BI Desktop Accesses Vertica Data

Power BI Desktop imports Vertica data into the Power BI Desktop cache for processing. When you create or interact with a visualization, Power BI Desktop uses the imported data to render the visualizations.

When you work with your visualizations, you may need to reimport the Vertica data to make sure you have an up-to-date dataset. Before you reimport the data, you need to clear the Power BI Desktop cache. To do so, select File > Options and Settings > Options > Global Data Load.

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.

Model Vertica Data in Power BI Desktop

After you import your data, Power BI Desktop automatically builds a data model. You can use the Data Access Extensions (DAX) library to add the calculations and measures that your analysis and reports require.

Review the Data Model

Power BI Desktop guesses the relationships among the fact tables and the dimension tables based on column names.

Carefully review the model that Power BI Desktop creates, as described in the following sections:

  • Verify Relationships Among Tables
  • Check the Cardinality
  • Review the Cross Filter Direction
  • Hide Unnecessary Columns

Verify Relationships Among Tables

  1. To view the relationships, click the Relationships icon:

    relationship_view_button.png

  2. Verify that the relationships that Power BI Desktop guessed are correct. For this example, the following diagram shows that the model is missing the join between the inventory_fact table and the product_dimension table:

    relationship_view_details.png

    The primary key in the product_dimension table is a composite key between the product_key column and the product_version column. Power BI Desktop doesn’t support relationships based on composite keys, so it ignores the join between the inventory_fact and product_dimension tables.

    To configure this relationship in Power BI Desktop, you create a new column on both tables that form the relationship and then create a relationship based on these new columns.

  3. To create the composite key in both tables (inventory_fact and product_dimension), first you need to create a new column within the Power BI Desktop model that concatenates the product_key and product_version fields into a single field. Click the Data icon:

    data_icon_with_arrow.png

  4. Right-click the inventory_fact table and select New Column:

    new_column_option.png

  5. In the Edit window, enter the following text and click the checkmark:

    composite_column_text.png

    The new composite key appears as a new column in the inventory_fact table under Fields.

  6. Repeat step 6 for the product_dimension table.
    product_version_key = [product_key] & [product_version]

    The following graphics show the two new composite keys in those two tables:

    inventory_fact_composite_key.pngproduct_dimension_composite_key.png

  7. To create the relationship, draw a line between the two composite keys:

    draw_new_relationship.png

    The model now shows the join between the inventory_fact and product_dimension tables using the composite key:

    relationship_in_model.png

  8. To view the relationship details, double-click the newly created link.

    edit_relationship.png

Check the Cardinality

 Verify that the cardinality is correct in all tables. Avoid many-to-many cardinality relationships. The default cardinality is many-to-one.

Review the Cross Filter Direction

Carefully review the cross filter direction between tables in your model.The bidirectional cross filter works well when you have a single fact table and as many dimensions as you want. When your model has two or more fact tables sharing dimension tables, change your model so that all the relationships use a single cross filter direction.

The default behavior in Power BI Desktop is Both, or bidirectional.

For more information, see the following topics in the Power BI Desktop documentation:

Hide Unnecessary Columns

To save time when importing data from Vertica to Power BI Desktop over the network, consider hiding columns that the visualizations do not use:

  1. Select the Data icon:

    data_icon_with_arrow.png

    For each column that you want to hide from the report, right-click the column name and select Hide in Report View.

Build Reports in Power BI Desktop

After you model your data, use Power BI Desktop to create reports on your Vertica data. The following image shows an example of such a report.

sample_report.png

For information on creating reports from Power BI Desktop, see Report View in Power BI Desktop.

Refresh Data in Power BI Desktop

As you create visualizations and interact with them, Power BI Desktop uses the imported data to render the visualizations. To make sure you see the most up-to-date dataset, you need to refresh the data.

After you refresh, you see all changes to the underlying database that occurred since the initial import or since the most recent refresh.

To refresh data in the desktop application, click the Refresh icon on the Home ribbon.

refresh_icon.png

Power BI Desktop displays the number of rows refreshed from the Vertica database.

Publish Power BI Desktop Reports to Power BI Server

There are two ways to publish Power BI Desktop reports to the browser-based Power BI Server:

  • Publish Reports from Power BI Desktop
  • Use Power BI Server to Upload a Power BI Desktop File

Publish Reports from Power BI Desktop

To publish your reports (*.pbix files) from Power BI Desktop, take these steps:

  1. In Power BI Desktop, select File > Publish.
  2. Click Publish to Power BI or click the Publish icon.

    publish_from_power_bi.png

  3. Sign in to Power BI Server.
  4. Log in to Power BI Server using your browser: https://login.microsoftonline.com.
  5. Enter your email address, for example, sally.smith@microfocus.com.

    Your reports automatically upload to Power BI Server.

  6. When complete, open your report in your Power BI Server site.

    published_to_server.png

Use Power BI Server to Upload a Power BI Desktop File

To upload a Power BI Desktop file (*.pbix) from Power BI Server, use the Get Data option:

  1. Sign in to Power BI Server.
  2. Click Get Data > Files > Local File.

    get_data.png local_files.png

  3. Select your Power BI Desktop file (*.pbix) and click Open to import data.

    importing_files.png

After import the Power BI Desktop file, Power BI Server creates a new dataset and imports data and reports. To see the new dataset and the reports, navigate to My Workspace in Power BI Server.

If you modify the reports in Power BI Server, you cannot update the data in Power BI Desktop with those changes.

To refresh your data in Power BI Server with updated Vertica data, install Power BI Server Pro, and then set up a secure gateway to connect to Vertica. For details, in this document, see:

  • Set Up Power BI Gateway
  • Refresh Data in Power BI Server Pro

Set Up Power BI Gateway

Power BI Gateway (Personal and Enterprise) allows you to connect securely to Vertica and transfer data to Power BI Server on a secure channel. You need to install Power BI Gateway before you can refresh data in Power BI Server.

Important If you do not have Power BI Server Pro, you cannot refresh data in Power BI Server.

When it is time to run a scheduled refresh, Power BI Server creates a secure connection to the gateway. The gateway then securely connects to your Vertica data source and refreshes the Vertica data in Power BI Server.

Download and Install the Gateway

Take these steps to download and configure the gateway:

  1. Download a gateway from: https://powerbi.microsoft.com/en-us/downloads/

  2. Run the executable file that you downloaded and follow the instructions for installing the gateway.

    Install the Power BI Gateway on a computer that is always up and running. This does not have to be the computer where Power BI Desktop is installed, nor does it have to be the computer you upload your Power BI Desktop files from.

By default, Power BI Gateway installs in this location:

C:\Users\<windows_user>\AppData\Local\Power BI Gateway – Personal

Configure the Gateway

After the gateway completes, click Launch to start the Power BI Gateway – Personal Configuration Wizard. After the configuration completes, the gateway is up and running.

On the Power BI website, specify an authentication type and re-enter the credentials to your Vertica data sources.

Troubleshoot the Gateway

If, when you try to refresh, you see the following error, make sure the Power BI Gateway is on. If not, install and configure the gateway:

gateway_error.png

Refresh Data in Power BI Server Pro

After you have created a secure gateway from Power BI Server Pro to Vertica, you can refresh the Vertica data in Power BI Server Pro. In Power BI Server Pro, this operation is always a full refresh, not an incremental refresh.

You can refresh your data in one of two ways:

  • Click Refresh Now.

    refresh.png

  • Schedule periodic refreshes.

In either case, this process requires that you install a gateway. For instructions, see Set Up Power BI Gateway in this document.

Schedule Periodic Refreshes

To schedule a refresh of your Vertica data:

  1. On the gear icon menu, select Settings.

  2.  Under Gateway connection, select the gateway you installed using the instructions in Set Up Power BI Gateway in this document.
  3. Select the Datasets tab and choose the dataset that contains the data you want to refresh.

    dataset_to_refresh.png

  4. Under Data source credentials, enter your database username, authentication method, and password.

  5. Under Schedule Refresh, click Keep your data up to date to select Yes. With Power BI Server Pro (paid or trial), you can schedule updates up to 8 times a day:

    schedule_refresh1.png

  6. To schedule a refresh for another hour in the day, click Add another time for each hour you want Power BI Server Pro to perform a refresh. The following example shows 8 refreshes/day scheduled:

    schedule_refresh.png

  7. To see a summary of executed refreshes, at the top of the window, click Refresh History. The results look like the following:

    refresh_history.png

For more information about refreshing data in Power BI Server Pro, see:

Use Power BI Mobile to Access Reports

You can use the Power BI Mobile app to connect to Power BI Server and look at reports that have been published on the server on your mobile device. Take these steps:

  1. Download and install Power BI on your mobile device.

    mobile1.png

  2. Click Sign in to Power BI, enter your credentials, and click Sign In.

    mobile2.png

  3. Click Start Exploring.
  4. In My Workspace > Reports, select the report you want to review. The following image shows a report in Power BI Mobile:

    mobile5.png

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. 

For More Information

For More Information About… … See
Power BI https://powerbi.microsoft.com/en-us/
Vertica Community https://my.vertica.com/community/
Vertica Documentation http://my.vertica.com/docs/latest/HTML/index.htm
Big Data and Analytics Community https://my.vertica.com/big-data-analytics-community-content/

Share this article: