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 changes in functionality or terms of use for third-party tools. For more details about the compatibility and use of these tools with Exasol, refer to the respective software vendor’s website.

Prerequisites

  • Your Exasol SaaS database must be running. For more information, see Database Management.

  • The IP address of the host where the tool is running must be in the allowed IP address list. For more information, see Network Security.

  • Exasol ODBC driver is installed
  • Microsoft Power BI Desktop installed

Get ODBC Driver and Connection Details

To connect to your Exasol SaaS database from a tool you need a valid connection string. You must also allow network access to the database from the tool, and make sure that the latest Exasol ODBC driver is installed.

  1. On the Databases page in the web console, click on Connect via tools on the cluster that you want to connect to.

    connect via tools

  2. In the wizard, enter the IP address of the client or server that will connect to the database and click Add IP, then click Next. This will add the IP address to the list of IP addresses that are allowed network access to the cluster. The allow list is found on the Security page in the web console. For more information, see Network Security.

    If the IP address is already present in the allow list, just click Next.

    enter ip address

  3. If the latest ODBC driver for your operating system is not already installed, select the driver type and operating system and click on Download to download the latest driver to your local system. For more information about how to install the driver, see ODBC Driver.

    download driver

  4. Click on Next to view your connection details:

    connection details

  5. Copy the Connection string, Port, and User name values from the Connect details dialog. You will need to enter this information in the tool that should connect to the Exasol SaaS database.

  6. To generate a new Personal Access Token (PAT) to use for authentication in the client, click on the Password row, then click on Copy to copy the PAT to your clipboard. For more information, see Personal Access Token.

    generated pat

    The generated PAT is only shown once and cannot be retrieved after you have closed the wizard.

  7. Click on Done to close the wizard.

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:

  4. Enter the following details from the Get ODBC Driver and Connection Details procedure and click Connect:
    • User name: Enter the user name you copied from the web console.
    • Password: Enter your Personal Access Token that you copied from the web console.

  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 from the Get ODBC Driver and Connection Details procedure 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 Host string you copied from the web consolefollowed by the port 8563.
    • Encrypted: Yes.
    • Username: Enter the user name you copied from the web console.
    • Password: Enter your Personal Access Token you copied from the web console.

  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.