Microsoft Power BI #

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.

Requirements #

  • Power BI 2.87.720.0 and higher. Older versions of Power BI only support import mode.
  • The OBDC driver for SEP.
  • A .lic license file for the SEP ODBC driver. Install this file in the \lib\ directory of the driver’s program files.

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. Note that import mode is limited to 1GB of data per query.
  • Standard ODBC: use a generic ODBC connection. Allow for custom SQL which is unsupported 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:

  1. In Power BI Desktop, select Get Data > More.
  2. Select All > Starburst Enterprise 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.
  4. Select either DirectQuery or Import as your Data Connectivity mode.
  5. Click OK.
  6. Select an authentication method and click Connect to establish a connection.
  7. 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:

  1. Open the ODBC Data Source Administrator utility.
  2. Add the SEP ODBC driver as a User or System Data Source.
  3. Click Apply.

To connect to SEP from Power BI Desktop using a standard ODBC connection, follow these steps:

  1. In Power BI Desktop, select Get Data > More.
  2. Select All > ODBC and click Connect.   Selecting ODBC as a data source

  3. Under Data source name (DNS), select the SEP data source.
  4. Under Advanced options > Connection string add host="hostname:port", replacing “hostname:port” with the connection details for your cluster.
  5. Configure the necessary authentication details for your cluster.
  6. 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 window when connecting 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 initialized using kinit, before using the driver. This establishes your user credentials on the machine.

Select Kerberos in the authentication window, when connecting, and provide the Kerberos Service name.

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:

  1. Copy the ODBC driver file into %USER%\Documents\Power BI Desktop\Custom Connectors for each user. Create the directory, if it does not exist.
  2. Grant the gateway service account permissions to access the Custom Connectors directory from the previous step.
  3. 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.
  4. In the Connectors configuration of the gateway, update the value for Load custom data connectors from folders to point at the Custom Connectors directory.
  5. 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:

  1. Log in to the Power BI service.
  2. Navigate to Setting > Manage gateways.
  3. Select the gateway, and Add data source.
  4. Set the Data Source Type to Starburst Enterprise.
  5. Configure the necessary connection and authentication details for your cluster. You must include a port number, which your network administrator can provide.
  6. Click Add to create the SEP data source.

Limitations #

  • 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 2.0.0 #

April 2021:

  • Remove beta flag.
  • Change name to Starburst Enterprise as breaking change. All existing reports break, and all users need to recreate their queries.