Skip to main content

Connecting to Dremio from Microsoft Power BI Desktop

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

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 October 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 username: 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 Microsoft Entra ID for authenticating from Power BI to Dremio. The steps are detailed in Microsoft Entra ID and the section "Configuring Power BI Authentication" in BI Applications.

Connecting by Configuring a Connection to Dremio Cloud​

You can browse and analyze one or more datasets in Dremio Cloud 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.

  1. In the Get Data dialog, search on "Dremio", select Dremio Cloud (Beta), and click Connect.

  2. Click Continue in the message that the Dremio Cloud (Beta) connector is still under development.

  3. In the Dremio Cloud dialog, follow these steps:

    a. In the Server field, specify which of Dremio Cloud's control planes to connect to:

    • To connect to the US control plane, type sql.dremio.cloud.

    • To connect to the European control plane, type sql.eu.dremio.cloud.

    b. In the Project field, if your datasets are in a non-default project of your Dremio Cloud organization or you do not have access to the default project, paste the ID of the project that you want to connect to.
    To obtain the project ID, log into Dremio Cloud, switch to the Dremio Cloud project that you want the ID for, click the Project Settings This is the Project Settings icon. icon in the side navigation bar, and then copy the content of the Project ID field.
    If you want to use the default project for your Dremio Cloud organization, proceed to the next step.

    c. (Optional) In the Engine field, specify the name of the query-execution engine for your project. For information about query-execution engines, see Managing Engines.

    d. (Optional) In the Native Query field, specify a SQL query as the data input source.

    note
    • The February 2024 release for Power BI contains the native query option.
    • After you specify a new native query, Power BI Desktop prevents the first execution and states the query is not authorized. When this issue occurs, click Retry to navigate to the authorization menu. Power BI Desktop reminds you of the potential risks of using a native query, which is an assertion by Power BI Desktop that is unrelated to Dremio. Click Run to authorize and execute the query. The results are then presented in Power BI Desktop.

    e. 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. To see underlying data changes since the initial import or the most recent refresh, you must refresh the data, which imports the full dataset again.

    • DirectQuery: No data is imported or copied into Power BI Desktop. For relational sources, the selected tables and columns appear in the Fields list. For multi-dimensional sources like SAP Business Warehouse, the dimensions and measures of the selected cube appear in the Fields list. 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.

  4. Sign in to Dremio Cloud.

  • If you want to sign into Dremio Cloud by using 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 the organization admin has enabled it in Dremio Cloud.

  • (Supported for all supported versions of Power BI Desktop) If you want to sign in with a personal access token (PAT) for Dremio Cloud, select Personal Access Token in the sidebar, paste in your token, and then click Connect. To create a PAT, follow the steps in the section Creating a Token.

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

Connecting Directly to a Dataset that is in Dremio Cloud​

You can quickly create a connection to a single dataset by downloading a Microsoft Power BI Data Source (.pbids) file from Dremio Cloud 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 Project Settings This is the icon that represents the Project Settings. icon in the side navigation bar.

  2. Select BI Applications in the project settings sidebar.

  3. 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 Cloud, 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.
note

For Dremio Arctic catalogs that are available in your Sonar project, you can access only tables and views that are on the main branch. For more information about Arctic catalogs, see Arctic Catalogs.

  1. Click the Power BI button in the top-right corner of the SQL Runner.
    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.

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

  3. Click Load.

  4. Save the file.

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