Microsoft Power BI #

Starburst Galaxy and SEP can be accessed via DirectQuery with the following tools:

  • Microsoft Power BI Desktop
  • Microsoft Power BI Service

Power BI lets Starburst Galaxy and SEP perform all query processing. This combines their scalability and processing power with the reporting features of Power BI.

Requirements #

It is recommended to connect to Starburst clusters using the latest version of Power BI.

  • Power BI 2.122.1066.0 (October 2023) and higher. Older versions of Power BI may not support all functions.
  • Starburst ODBC driver version 2.0.15.1021 (Released May 2nd, 2023) or higher.

Data connectors #

You can use the Starburst Enterprise connector to connect to your clusters. Starting from release 2.122.1066.0 (October 2023), the Starburst Enterprise connector supports both DirectQuery and Import modes.

  • DirectQuery: Query data using the query engine without importing that data to the Power BI client. This mode is recommended for most use cases, as it has the least impact on network or client storage resources, regardless of the amount of data queried.
  • Import: Import queried data directly to the client machine for further local analysis with tools such as Quick Insights and calculated tables. Import mode is limited to 1GB of data per query.

Connect with Power BI Desktop #

The following sections describe how to connect to Starburst Galaxy or SEP from Power BI Desktop.

Connect with the Starburst Enterprise connector #

To connect from Power BI Desktop using the Starburst Enterprise connector, follow these steps:

  1. In Power BI Desktop, select Get Data > More.
  2. Select Other > Starburst Enterprise or type Starburst in the search box, and click Connect.

      Selecting Starburst Enterprise as a data source

  3. Configure the necessary connection and authentication details to access your cluster. You must include a port number, which your network administrator can provide.

    In the Host field, if you are connecting to Starburst Galaxy with OAuth authentication, you must add a forward slash (/) and append the OAuth client’s public reference to the host name:

    mydb.trino.galaxy-dev.io/examplestringzWjsoI9CtH.
    
  4. (Optional) Configure any of the following Advanced options:
    • Role: Specify a role used to connect to Starburst Galaxy or SEP.
    • Catalog: If you are using native query to execute a query upon connection, specify the catalog for the query to be executed against.
    • Use system proxy: Select an option if you want to use the HTTP proxy server of your operating system. Refer to Microsoft’s documentation regarding how to setup system proxy.
    • Connection string: The extra ODBC connection string applies to the connection. Connection strings are not supported if:
      • The string can be specified through other inputs, such as a catalog.
      • The string contains confidential information, such as a password ProxyPassword. For more information regarding Starburst ODBC connection parameters, refer to the Starburst ODBC driver documentation.
    • Native query: Enter a native query if you want to use a custom query as the data source input.
  5. Select either DirectQuery or Import as your Data Connectivity mode, then click OK.
  6. Select an authentication method, add credentials if needed, then click Connect to establish a connection.
  7. Use the Data Navigator to browse catalogs, query data sources, and more.

Authentication and Security #

Unless you are using an anonymous authentication type, the Starburst Enterprise connector connects to Starburst Galaxy and SEP servers using an HTTPS connection, by default. To switch TLS/SSL on or off, go to the connection string input. 0 switches TLS/SSL off. 1 switches TLS/SSL on. For example, SSL=1 or SSL=0.

The Starburst Enterprise connector uses your operating system truststore to verify a server certificate. For a TLS connection to work, your operating system must trust the certificate on your Starburst Galaxy or SEP server. Choose one of the following methods to ensure a TLS connection:

  • Add a server certificate to your operating system or personal trusted certificate store. We recommend installing and adding a root certificate to your operating system or personal trusted certificate store.
  • Add a server certificate and/or root certificate to a local certificate store PEM file, then add UseSystemTrustStore=0;TrustedCerts=<path_to_pem> to the connection string.
  • To use a self-signed certificate, add AllowSelfSignedServerCert=1 to the connection string input.

Authentication types #

The Starburst Enterprise connector for Power BI supports the following authentication types:

Anonymous #

The anonymous authentication type is available for testing or development in SEP clusters that support anonymous authentication and Starburst Galaxy service accounts

Kerberos #

To use Kerberos authentication, the Kerberos client must be installed for the user. Initialize with kinit before using the driver. This establishes your user credentials on the machine.

LDAP #

If your cluster is configured to use LDAP authentication, select LDAP in the authentication field and provide your username and password credentials. LDAP authentication is also known as basic authentication or username/password authentication.

Personal access token (JWT) #

If your cluster is configured to use a personal JWT token for authentication, select Personal Access Token (JWT) in the authentication field. Follow the instructions provided by your administrator to retrieve your personal JWT token, then copy and paste your token into the Personal Access Token (JWT) field.

OAuth #

If your cluster is configured to use OAuth for authentication, select OAuth in the Authentication field. If you are not signed in, click the Sign in button and follow the login prompts.

Connect with Power BI service #

Using the web-based Power BI service requires you to have the on-premises data gateway with the appropriate permissions, and the ODBC driver installed in each Gateway environment.

To connect to Starburst Galaxy or SEP as a data source, follow these steps:

  1. Log in to the Power BI service.
  2. Navigate to Setting > Manage connections and gateways.
  3. Click + New.
  4. Select On-premises gateway, then select the desired gateway.
  5. Enter the connection name.
  6. In the Connection type list, select Starburst Enterprise.
  7. Configure the necessary connection and authentication details for your cluster.
  8. Click Add to create the data source.

Once you have added a connection, you can map the semantic models’ data source to it. For more information on configuring gateway data sources in Power BI, refer to the Microsoft documentation.

Specify a Starburst Enterprise connector version #

If you need to use a specific version of the Starburst Enterprise connector besides the one built-in to PowerBI desktop, download the .mez file, place it in the C:\User\user_name\Documents\Power BI Desktop\Connectors directory, and restart Power BI Desktop.

If you receive a warning in Power BI that states the connector is uncertified, causing the client to use the bundled Starburst Enterprise connector rather than the downloaded version, follow these steps:

  1. Select File > Options and settings > Options dialog.
  2. Navigate to the Data extensions settings.
  3. Select Allow any extension to load without validation or warning.

If you are using native query, go to the Options dialog, select the Security tab, and uncheck Require user approval for new native database queries.

Release notes #

The following sections describe each release of the SEP for Power BI extension, as well as a .mez download that can be installed in Power BI Desktop for that version.

Version 5.3 #

Shipped with Power BI version 2.124.1052.0

December 2023

  • Added support for canceling queries using CancelQueryExplicity.
  • Added Use default proxy checkbox.
  • Added support for Starburst Galaxy OAuth authentication.
  • Removed Safe metadata read (optional) option from connector dialog. It is enabled by default for all queries.

Download Starburst Enterprise .mez version 5.3.0 for Power BI

Version 5.2.1 #

Shipped with Power BI version 2.122.1066.0

October 2023

  • Added support for Native Queries (Custom SQL). Users can write their own SQL queries against Starburst clusters in both Import and Direct Query modes.
    • Requires Starburst ODBC Driver version 2.0.15.1021 or later.
  • Introduced Lazy Evaluation of Metadata for all connection modes, which improves performance when initially displaying the catalog tree.
  • Added optional Connection string (non-credential properties) in the Advanced section to allow using other ODBC connection parameters.
  • Changed data source display from Starburst Enterprise to a value based on the connection details (for example, {“Host”:”sep.example.com”, “Port”:”443”}) to allow distinction between multiple Starburst Galaxy or SEP clusters connected as separate data sources.
  • Fixed an issue with port 8443 for OAuth 2.0 authentication.
  • Fixed query folding with timestamp columns.

Download Starburst Enterprise .mez version 5.2.1 for Power BI

Version 5.0.0 #

Shipped with Power BI version 2.109.782.0

September 2022

  • Add support for new authentication methods:
  • Add support for advanced filtering with the built-in regular expression filtering feature in PowerBI Desktop.
  • Add an optional Catalog field in the connection dialog. Selecting a catalog with this field reduces the amount of metadata fetched from the cluster.
  • Add an optional Safe metadata read field in the connection dialog. When enabled, queries on defective catalogs do not fail, but result in lower performance. This setting is disabled by default.

Download Starburst Enterprise .mez version 5.0.0 for Power BI

Version 4.0.0 #

February 2022

  • Broken catalogs are ignored when connecting to Starburst Galaxy or SEP. Previously, if any catalogs were broken the whole connection failed.

Download Starburst Enterprise .mez version 4.0.0 for Power BI

Version 3.0.0 #

December 2021

  • Support for version 2.0.0+ of the Starburst ODBC driver.
  • Change query source name to PowerBI-Extension

Breaking changes:

  • Remove support for the Presto ODBC driver (prior to ODBC version 2.0.0).

Download Starburst Enterprise .mez version 3.0.0 for Power BI