Microsoft Power BI Desktop

This article explains how to connect Microsoft Power BI Desktop to your Exasol database.

Exasol Microsoft Power BI Connector enables you to connect from Power BI Desktop to Exasol in Direct Query mode. The classical import mode is supported as well. Exasol Microsoft Power BI Connector is a Certified Custom Connector and is now shipped with Power BI Desktop and the On-premises data gateway.

Exasol takes no responsibility for any changes in functionality or terms of use for the third-party software described in this section. For more information, refer to the respective software vendor’s website.

Prerequisites

  • A running Exasol database
  • Exasol ODBC driver must be installed. You can download drivers from the Exasol Downloads portal. For more information, see Drivers.
  • Microsoft Power BI Desktop installed

Connect Power BI Desktop to Exasol

Follow these steps to connect Power BI Desktop to an Exasol database.

  1. Launch the Power BI Desktop application, and from the Home ribbon, select Get Data.

  2. The Get Data screen appears. From list of data sources under Database, select Exasol, then click Connect.
  3. In the connection window, enter the following details and click OK:
    • Connection String: Enter the Connection string.
    • Encrypted: Yes
    • Data Connectivity Mode: DirectQuery

  4. Enter the following details and click Connect:
    • User name: Enter the user name.
    • Password: Enter your password.

  5. In the Navigator screen, expand the database name to view the schemata. Expand the schema you want to use and select the desired tables for your analysis. Click Load.

  6. Review the relational model. It is recommended to have foreign keys set in the database so Power BI can auto-detect the relations.

  7. Build interactive dashboards on billions of rows in Exasol.

Power BI On-Premises Data Gateway

The Exasol connector is tested successfully with the On-premises data gateway version 3000.8.452 (August 2019) and also supports Direct Query.

Use the following steps to create an Exasol Power BI connection with the on-premises data gateway.

  1. Install and configure the Power BI On-premises data gateway (standard mode). For more information, see Power BI Gateway official download page.

    The Exasol connector is shipped with Power BI Gateway. If you have updated an existing setup, make sure you delete the Exasol.mez file from the \Power BI Desktop\Custom Connectors directory.

  2. To create a new Exasol connection for this gateway, log in to your Power BI account (powerbi.com) . Click the gear icon on the Power BI window and click Manage gateways.
  3. Enter the following connection information and click Add:
    • Data Source Name: Enter a name for the data source. For example, My Exasol.
    • Data Source Type: Select the source type as Exasol.
    • Connection String: Enter the Connection string followed by the port.
    • Encrypted: Yes.
    • Username: Enter the user name.
    • Password: Enter your password.

  4. Once you have configured the gateway, you are ready to publish your workbooks to powerbi.com from the Power BI Desktop. When connecting to Exasol from Power BI Desktop use the exact same settings (Connection-String / Encryption Setting / User Credentials). When the workbook is published the connection of the gateway is then associated with this workbook.