Skip to main content
Version: current [24.2.x]

Connecting to Dremio from Microsoft Power BI Desktop

Before you can create reports, you must either configure a connection to Dremio, or connectly directly to a dataset that is in Dremio.

After you create a report, you can publish it to Power BI Service.

Supported Versions​

  • It is recommended that you upgrade to the latest version of Power BI Desktop for the best experience with Dremio.

  • Use the November 2022 version or later if you plan to enable single sign-on for viewers of your published reports. You can enable viewers of your reports in Power BI Service to run them under their own Power BI Service usernames. When you do so, they no longer run reports under your Power BI Service username or the username of the person who set up Power BI Gateway (if you are using a gateway between Power BI Service and Dremio).

    For example, suppose your Power BI Service username is user1 and that you publish report Widgets-2022-Q1. Another person, who has the Power BI Service name user2, runs your report.

    • Without SSO enabled: In Dremio, the username associated with the job that runs the query from your report is your usernam: user1.

    • With SSO enabled: In Dremio, the username associated with the job that runs the query from your report is the username of the other person: user2.

      By enabling SSO, you make it easier for administrators in Dremio to know who exactly is running your reports.

      note

      Ensure that your Dremio administrator has enabled Azure Active Directory for authenticating from Power BI to Dremio. The steps are detailed in Azure Active Directory Authentication.

Connecting by Configuring a Connection to Dremio​

You can browse and analyze one or more datasets in Dremio by configuring a connection with these steps.

  1. In Power BI Desktop, click Get data. The pins in this image show two locations for this option.

  2. In the Get Data dialog, search for "Dremio"; then, follow either of these steps:

    • If you want to enable SSO for viewers of the reports that you create with this connection, select Dremio Cloud (Beta). The connection dialog for Dremio Cloud is modified to support connections to Dremio Software for the purpose of enabling SSO.
    • If you do not want to enable SSO, select Dremio Software.

  3. Click Connect.

  4. Follow these steps:

    a. In the Server field, specify the hostname or IP address of the coordinator node of your Dremio cluster. If you are using a port number other than the default of 31010, specify the port number, too, using this format: hostname:port_number.

    note

    If you are using TLS for connections to your Dremio cluster, ensure that you specify a hostname that matches one of the following fields in the certificate associated with the hostname and client port that Power BI connects to:

    • Subject - Common Name (CN)
    • Subject Alternative Names (SAN)

    b. (If you are using the Dremio Software connector) In the Encryption field, select Enabled if connections to your Dremio cluster must be encrypted.

    c. (Optional) In the Engine field, specify the name of the engine on which to run queries that use this connection. If you do not specify an engine, one is selected automatically according to engine routing rules set up for the cluster.

    d. (Optional) In the Tag field, specify a tag that you can use together with rules for routing queries. See Workload Management for more information.

    e. (Optional) In the Queue field, specify the name of a queue that you want to route queries to while this connection is open. See Workload Management for more information.

    f. Under Data Connectivity mode, select either Import or DirectQuery.

    • Import: The selected tables and columns are imported into Power BI Desktop. As you create or interact with a visualization, Power BI Desktop uses the imported data.

    • DirectQuery: No data is imported or copied into Power BI Desktop. As you create or interact with a visualization, Power BI Desktop queries the underlying data source, so you’re always viewing current data.

      note
      • You must select DirectQuery if you want to allow Power BI users viewing your published reports to use Single Sign-on. See Enabling SSO If You Are a Power BI Report Creator for details.
      • Using DirectQuery might prevent the use of aggregation reflections for accelerating queries on columns that use the FLOAT datatype. DirectQuery translates FLOAT columns to DECIMAL columns when loading tables into Power BI. Then, in queries on the transformed columns, Power BI casts the DECIMAL to DOUBLE, which prevents such queries from matching aggregation reflections that use FLOAT for those columns.

  5. Sign in to your Dremio cluster by using one of these options:

    • (If you selected either the Dremio Cloud or the Dremio Software connection type) If you want to use the username and password for a Microsoft account, select Microsoft Account in the sidebar. If you are already signed into a Microsoft account, the username for it appears in the dialog. If that is the account you want to use, click Connect. Otherwise, click Sign in, specify your username and password, and then click Connect.

      note

      You can use Microsoft authentication only if an administrator for your Dremio cluster has enabled the use of Microsoft credentials. You must use your Microsoft credentials if you want to enable Single Sign-on for viewers of reports created with this connection.

    • (If you selected the Dremio Cloud connection type) If you want to use a personal access token (PAT), select Personal Access Token in the sidebar, paste in a PAT, and then click Connect. For the steps to create a PAT, see Personal Access Tokens.

      note

      You can authenticate with a PAT only if an administrator for your Dremio cluster has enabled the use of PATs.

    • (If you selected the Dremio Software connection type) If you want to use a local username and password, select Username/Password in the sidebar, specify your credentials, and then click Connect.

The Navigator window is opened, listing the datasets in your project.

Connecting Directly to a Dataset that is in Dremio​

You can quickly create a connection to a single dataset by downloading a Microsoft Power BI Data Source (.pbids) file from Dremio and opening it in Power BI Desktop.

Before you follow these steps, ensure that the Microsoft Power BI Desktop option is enabled on the BI Applications page. To enable this option, ask an organization administrator to follow these steps:

  1. Click the Settings icon in the left sidebar.
  2. Select Project Settings.
  3. Select BI Applications.
  4. Toggle the switch next to Microsoft Power BI Desktop.

After the organization administrator completes these steps, refresh your browser window if you are viewing the SQL Runner.

To open a dataset in Power BI from within Dremio, you can use the PowerBI button in the top right-hand corner of a dataset view. Perform the following steps:

  1. On the Datasets page, select the dataset to open it in the SQL Runner.

  2. Click the Power BI button in the top-right corner of the SQL Runner. The location of the button is indicated by the pin in this image:

    A sample file with the complete dataset location in its filename and Microsoft Power BI Desktop Data Source as the file type, is downloaded. For example, Samples.samples.dremio.com.SF_incidents2016.json.

  3. Click the file to open it in Power BI.

  4. Click Load.

  5. Save the file.

You can now browse and analyze the data in the dataset.