Connecting Power BI to Presto in watsonx.data

This topic provides you with the procedure to connect Power BI to Presto using the Simba ODBC or CData ODBC drivers.

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.

Before you begin

  • 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.
    Note: Power BI Desktop primarily supports Windows only.
  • Based on the ODBC driver that you select, download, and install either of the following:
    • Simba Presto ODBC driver: Download and Install latest desktop version of Insightsoftware Simba ODBC driver for Presto.
    • CData ODBC driver for Presto: Download and install latest desktop version of CData ODBC driver for Presto.
      Note: If you are having trouble installing the CData ODBC driver for Presto, contact support@cdata.com.
  • Install watsonx.data.
  • Provision watsonx.data instance with Presto engine.

Authentication

Power BI uses Lightweight Directory Access Protocol (LDAP) authentication mechanism to connect to Presto. You can use the username and password of the watsonx.data console login, or use the ibmlhapikey_<watsonx.data_login_name> and API key.

Configuring Driver

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

Click the respective links to know the configuration.

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: Username is ibmlhapikey_<watsonx.data_login_name>.
    • Password : The API key of the watosnx.data user. To generate API key, log in into the watsonx.data console and navigate to Profile > Profile and Settings > API Keys and generate a new API key.
      Important: Generating new API key will revoke the existing one, and will impact the services that uses the old key.
      Note: You can also provide the watsonx.data console credentials as the Username and Password.
    • Retrieve the Host, Port, and Catalog from the watsonx.data Connection information page. For more information, see Getting connection information.
    • Click SSL Options. The SSL Options page opens. Configure the following:
      • Select the Allow Self-signed Server Certificate check box.
      • From the Trusted Certificates field, browse and select the Presto.cert file from your computer.
        Note: Get the SSL Certificate from watsonx.data > Connection information > IBM products > Instance Details > SSL Certificate. For more information, see Getting connection information.
    • 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.
    • 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. In the Basic tab, provide the following details:
    • Data source name : Enter a name for your data source connection.
    • Retrieve the Server, and Port from the watsonx.data Connection information page. For more information, see Getting connection information.
    • Auth Scheme : Select LDAP from the list.
    • Username : The username is ibmlhapikey_<watsonx.data_login_name>.
    • Password : The API key of the watosnx.data user. To generate API key, log in into the watsonx.data console and navigate to Profile > Profile and Settings > API Keys and generate a new API key.
      Important: Generating new API key will revoke the existing one, and will impact the services that uses the old key.
    • Use SSL : Select True from the list.
    • Click the Advanced tab, provide the following details:
      • In the SSL Server Cert field, enter the path to the Presto.cert file.
        Note: Get the SSL Certificate from watsonx.data > Connection information > IBM products > Instance Details > SSL Certificate. For more information, see Getting connection information.
    • 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.
    • 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. You can use the username and password of the watsonx.data console login, or use the ibmlhapikey_<watsonx.data_login_name> and API key.
  9. The Navigator page opens with the list of schemas in the Presto.
  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.