Microsoft Power BI #
You can use the popular analytics platform Microsoft Power BI with your Starburst cluster, using Power BI DirectQuery.
SEP can be accessed via DirectQuery with the following tools:
- Microsoft Power BI Desktop
- Microsoft Power BI Service
Power BI lets SEP perform all query processing. This combines the scalability and power of SEP with the reporting features of Power BI.
- Power BI 2.87.720.0 and higher. Older versions of Power BI only support import mode.
- The Starburst OBDC driver version 188.8.131.521 or later.
Data connectivity modes #
Power BI can access data from SEP in the following connection modes:
- DirectQuery: query data using the SEP 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 like quick insights and calculated tables. Allows for custom SQL which is not supported by DirectQuery. Note that import mode is limited to 1GB of data per query.
- Standard ODBC: use a generic ODBC connection. Allows for custom SQL which is not supported by DirectQuery. Note that a standard ODBC connection is limited to 1GB of data per query.
Connect with Power BI Desktop #
The following sections describe how to connect to SEP from Power BI Desktop.
DirectQuery and import mode #
To connect to SEP from Power BI Desktop using either DirectQuery or import mode, follow these steps:
- In Power BI Desktop, select Get Data > More.
Select All > Starburst Enterprise and click Connect.
- Configure the necessary connection and authentication details to access your cluster. You must include a port number, which your network administrator can provide.
- Select either DirectQuery or Import as your Data Connectivity mode.
Select the authentication method.Note: If you use OIDC as the authentication method, note that the auth token is not cached and Power BI prompts to re-authenticate on each connection.
- Click OK.
- Select the same authentication method and add credentials if needed, then click Connect to establish a connection.
- After the connection is established, use the Data Navigator to browse catalogs, query data sources, and more.
Standard ODBC #
The standard ODBC connection mode requires you to add the SEP ODBC driver to Windows as an ODBC data source. To set up the data source, follow these steps:
- Open the ODBC Data Source Administrator utility.
- Add the SEP ODBC driver as a User or System Data Source.
- Click Apply.
To connect to SEP from Power BI Desktop using a standard ODBC connection, follow these steps:
- In Power BI Desktop, select Get Data > More.
Select All > ODBC and click Connect.
- Under Data source name (DNS), select the SEP data source.
- Under Advanced options > Connection string add
host="hostname:port", replacing “hostname:port” with the connection details for your cluster.
- Configure the necessary authentication details for your cluster.
- After the connection is established, use the Data Navigator to browse catalogs, query data sources and more.
Authentication and Security #
Power BI Desktop always tries to connect with an encrypted connection first. If you are connecting without TLS/SSL, the connector offers the option to connect using an unencrypted connection afterwards.
To use a TLS-encrypted connection with your cluster, make sure the server uses a globally trusted certificate.
If this is not the case, add the server’s certificate to the system trust store (Certificates > Trusted Root Certification Authorities) before connecting. The certificate can be added for the machine, or for each user running the Power BI connector. In many organizations this is handled automatically as part of the operating system and browser configuration.
LDAP authentication with Power BI Desktop #
If your cluster is configured to use LDAP authentication, select LDAP in the authentication field and provide your username and password credentials.
Kerberos authentication with Power BI Desktop #
To use Kerberos authentication, Kerberos must be installed for the user and
kinit, before using the driver. This establishes your user
credentials on the machine.
Select Kerberos in the authentication field and provide the Kerberos Service name.
OIDC authentication with Power BI Desktop #
If your cluster is configured to use OIDC authentication, select OIDC in the authentication field.
Connect with Power BI service #
Using the web-based Power BI service requires you to have the on-premises data gateway, and the SEP ODBC driver added to that installation with the appropriate permissions.
To add the ODBC driver, follow these steps:
- Copy the ODBC driver file into
%USER%\Documents\Power BI Desktop\Custom Connectorsfor each user. Create the directory, if it does not exist.
- Grant the gateway service account permissions to access the
Custom Connectorsdirectory from the previous step.
- Update the directory properties in the Security - Advanced tab. Add access that grants Basic Permissions - Full Control to Everyone/Authenticated Users. Alternatively, you can also create a new group and add the gateway service account to a new group.
- In the Connectors configuration of the gateway, update the value for
Load custom data connectors from folders to point at the
- Log in to Power BI Online and update the Gateway Cluster Settings. Enable Allow user’s custom data connector to refresh through this gateway cluster.
To connect to SEP as a data source, follow these steps:
- Log in to the Power BI service.
- Navigate to Setting > Manage gateways.
- Select the gateway, and Add data source.
- Set the Data Source Type to Starburst Enterprise.
- Configure the necessary connection and authentication details for your cluster. You must include a port number, which your network administrator can provide.
- Click Add to create the SEP data source.
Self-signed certificate usage for TLS/SSL connections is not supported.
Writing and using custom SQL statements is not supported with DirectQuery.
The ODBC standard connector can be used for custom SQL but does not support direct querying.
Authentication type and field name customization does not apply on the Power BI service. The following is the mapping of the service field names to their Desktop counterparts:
Service name Desktop name Basic LDAP Key Kerberos Key: Account Key Kerberos: Service Name
Release notes #
Version 3.0.0 #
- Support for version 2.0.0+ of the Starburst ODBC driver.
- Support for custom SQL in import mode.
- Support for OIDC-based authentication.
- Remove support for the Presto ODBC driver (prior to ODBC version 2.0.0).
Version 2.0.0 #
- Remove beta flag.
- Change name to Starburst Enterprise.
Is the information on this page helpful?
- Microsoft Power BI
- Data connectivity modes
- Connect with Power BI Desktop
- Connect with Power BI service
- Release notes