Microsoft Power BI Desktop

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

The Exasol Microsoft Power BI Connector is a Certified Custom Connector and is now shipped with Power BI Desktop and the On-premises data gateway.

Prerequisite

You need to have the following:

  • Microsoft Power BI Desktop Installation.
  • Download the latest Exasol ODBC driver.
  • A running Exasol SaaS database. For more information, see Manage Databases and Clusters.
  • The IP address of the client or server where the tool is running must be listed in the allowed IP address list. For more information, see Network Security.

Get Drivers and Connection Details

Before you can proceed with connecting to your database from a tool, you need to download the latest Exasol ODBC driver and get the connection string. Follow these steps to get your connection details: 

  1. Click Databases Databases in the web console to open the Databases page.
  2. Click More on the cluster you want to connect to and then click Connect via tools.
  3. In the Connect to cluster wizard, enter the IP address of the client or server which will connect to the database and click Add IP. Then click Next. If the IP address was already added, just click Next.
  4. If not already installed, download the latest ODBC driver for your operating system and install it.

    5. Installing the latest ODBC driver requires you to uninstall a previous version of the Exasol ODBC Driver. After installation, the ODBC driver will be used for all Exasol ODBC connections from your local machine. The ODBC driver is backwards compatible and will work with any existing database connections.

  5. Click Next and proceed through the wizard until you see your connection details:

  6. Copy the HOST, PORT, USERNAME, and PASSWORD. You can generate a new Personal Access Token by clicking Generate. You will need this information to connect to your database.

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, 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 Drivers 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 Drivers 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.