IBM Cloud Docs
Connecting Power BI to Presto in watsonx.data

Connecting Power BI to Presto in watsonx.data

This topic provides you with the procedure to connect Power BI to Presto using one of the following ODBC drivers:

  • Simba ODBC driver
  • CData ODBC driver

When you connect to the Presto engine in watsonx.data, you can access the various connected data sources and build compelling and interactive data visualizations.

Pre-requisites

  • Power BI Desktop : Download and install the latest desktop version of Power BI using the Advanced download link. Create a Power BI account by using your email-ID and log in.
  • Based on the ODBC driver that you select, download and install one of the following:
    • Simba ODBC driver for Presto: Download and install latest desktop version of Insight software Simba ODBC driver from the Presto ODBC Driver. Based on your computer platform (Mac or Desktop), log in to the account. After you download, you will receive a license file in registered email . Copy the file and paste it under ~\Simba Presto ODBC Driver\lib folder.
    • CData ODBC driver for Presto: Download and install latest desktop version of CData ODBC driver for Presto from the Presto ODBC Driver.
  • Subscription of watsonx.data on IBM Cloud.
  • Provision watsonx.data instance with Presto engine.

Authentication

Power BI uses Lightweight Directory Access Protocol (LDAP) authentication mechanism to connect to Presto. You need the following sign-in credentials:

  • Username: Username is ibmlhapikey or ibmlhapikey_<watsonx.datauser_id>.
  • Password: The API key of the watsonx.data user.

Configuring the Driver

You can select one of the following ODBC drivers to connect to Presto from Power BI.

Simba

  1. Open ODBC Data Source > Run as administrator from your computer. The ODBC Data Source Administrator page opens.

  2. Click System DSN.

  3. Select Simba Presto.

  4. Click Add. The Create New Data Source page opens.

  5. Select the Simba Presto ODBC driver for which you want to set up the data source and click Finish. The Simba Presto ODBC Driver DSN Setup page opens.

  6. Provide the following details:

    • Data source name : Enter a name for your data source connection.
    • Description : Provide a description for the Presto driver setup.
    • Authentication Type : Select LDAP from the list.
    • Username : The username is ibmlhapikey or ibmlhapikey_<watsonx.datauser_id>.
    • Password : The API key of the watsonx.data user. For more information about retrieving the API key, see Generating the API key.
    • Host : Hostname of the Presto engine in watsonx.data that you want to connect to. For more information about retrieving the hostname, see Getting connection information.
    • Port : For information about retrieving the port number, see Getting connection information.
    • Catalog : Select the Iceberg catalog (Iceberg_data) that is associated with the Presto engine in watsonx.data.
    • Schema : Select the schema that is associated with your data.

  7. Click Test to verify that the connection is successful. The Test results window opens to display the success message. Click Ok. The Simba Presto ODBC Driver DSN Setup page opens.

  8. Click Ok. The ODBC Data Source Administrator page opens. Click Ok.

CData

  1. Open ODBC Data Source > Run as administrator from your computer. The ODBC Data Source Administrator page opens.

  2. Click System DSN.

  3. Select CData Presto.

  4. Click Add. The CData ODBC Driver for Presto - DSN Configuration page opens.

  5. Provide the following details:

    • Data source name : Enter a name for your data source connection.
    • Server : Hostname of the Presto engine in watsonx.data that you want to connect to. For more information about retrieving the hostname, see Getting connection information.
    • Port : For more information about retrieving the port number, see Getting connection information.
    • Auth Scheme : Select LDAP from the list.
    • Username : The username is ibmlhapikey or ibmlhapikey_<watsonx.datauser_id>.
    • Password : The API key of the watsonx.data user. For more information about retrieving the API key, see Generating the API key.
    • Use SSL : Select True from the list.

  6. Click Test Connection to verify that the connection is successful. The ODBC – DSN Configuration window opens to display the success message. Click Ok. The CData ODBC Driver for Presto - DSN Configuration page opens.

  7. Click Ok. The ODBC Data Source Administrator page opens. Click Ok.

Setting up Power BI and viewing tables

  1. Open Power BI Desktop.

  2. Click Get data from other sources. The Add data to your report page opens.

  3. Click Get data from other sources link. The Get Data page opens.

  4. Search and select ODBC.

  5. Click Connect. The From ODBC page opens.

  6. From the Data source name list, select the data source that you created and click Ok.

  7. The ODBC driver page opens.

  8. Provide username and password and click Connect.

    Username : Username is ibmlhapikey or ibmlhapikey_<watsonx.datauser_id> and password is the API key of the watsonx.data user. For more information about retrieving the API key, see Generating the API key.

  9. The Navigator page opens with the list of schemas in the Presto engine.

  10. Select a required table to view the preview of the table and click Load or Transform Data based on your requirement. You can build visuals by using the data.

Connecting to Presto by using the Config files

In this method, you can select the catalog that you want to analyze in Power BI from the watsonx.data console, and watsonx.data generates a Config file for the selected driver (Simba or CData). This file can be used to access watsonx.data catalog directly in Power BI without any Presto configurations.

Config files are shortcuts for quickly connecting to the original data that you use often. Data source files do not contain the actual data but rather the information necessary to connect to the actual data.

  1. Log in to the watsonx.data instance.
  2. Go to Configurations.
  3. Click on the Connection information page.
  4. Expand BI Tools and select PowerBI.
  5. From the Connection Details section, expand the Presto engine.
  6. Use the Edit catalog icon in the Catalog field to select the required catalog for data analysis.
  7. Click the Export config files list. Select to download the config zip file (Simba config.zip or CData config.zip) based on the driver used. Click the downloaded file. It includes two files, .reg file and .pbids file.
  8. Run the .reg file to configure the database connection on Windows. Now run the .pbids file to connect to the specified data source. The PowerBI desktop opens. Provide the username and password to log in.
  9. The SSL cert info is available from watsonx.data Connection Information > IBM Products > Instance details > SSL Certificate. For more information about retrieving the SSL certificate, see Getting connection information.
  10. The Catalog page is displayed. You can select the schemas and tables for analysis.