On this page

    Connecting from Microsoft Power BI

    You can use Microsoft Power BI Desktop to query data sources through Dremio Cloud and to publish reports to Power BI Service. You can use Power BI Service to view those reports.

    Report creators use Power BI Desktop to query data sources and then publish reports.

    important:

    Reports must be created in and published from Power BI Desktop. It is not possible to create reports directly in Power BI Service.

    People who want to view published reports view them in Power BI Desktop or in Power BI Service.

    Supported Versions

    In addition to supporting Power BI Service, the cloud version of Power BI, Dremio Cloud supports these versions of Power BI Desktop and Power BI Gateway:

    important:

    • Upgrade to the latest version of Power BI Desktop for the best experience with Dremio Cloud.

    • If people need to view reports published from Power BI Desktop May 2022 or earlier, upgrade to the latest version of Power BI Gateway.

    ToolSupported Versions
    Power BI DesktopThe current version and all previous versions within a 12-month window before that
    Power BI GatewayJuly 2021 or later

    Supported Authentication Methods

    There are two methods of authenticating that you can choose from when you connect to Dremio Cloud from Power BI Desktop or from Power BI Service:

    • Use Microsoft Azure Active Directory as an enterprise identity provider
      To use this method with Power BI Desktop, ensure that version November 2021 or later is installed on your system. To configure Microsoft Azure Active Directory, see Microsoft Azure Active Directory and the section "Configuring Power BI Authentication" in BI Applications Authentication Configuration.

      note:

      You can use Microsoft authentication only if the admin for your Dremio Cloud organization has enabled it.

    • Use a personal access token (PAT) obtained from Dremio Cloud
      To create a PAT, follow the steps in the section Creating a Token.

    Power BI for Report Creators

    Use Power BI Desktop to query data sources in Dremio Cloud and visualize the result sets. You can also publish reports to Power BI Service.

    Configuring Connections from Power BI Desktop to Dremio Cloud

    Before you can run queries, you must set up a connection to your Dremio Cloud project.

    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 on “Dremio”, select Dremio Cloud (Beta), and click Connect.

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

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

      a. In the Server field, select whether to connect to Dremio Cloud’s control plane in the US or the one in the EU.

      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 project that you want the ID for, click the settings icon, select Project Settings, and then copy the content of the Project 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. For Data Connectivity mode, select DirectQuery.

      e. Click OK.

    5. Sign in to Dremio Cloud.

    • (Supported for Power BI Desktop version November 2021 or later) 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.

    Creating a Live Connection to a Dataset from Dremio Cloud

    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 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.

    2. 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.

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

    4. Click Load.

    5. Save the file.

    Publishing a Report to Power BI Service

    note:

    Ensure that Power BI Service is already configured to connect to Dremio Cloud. See Power BI for Administrators.

    If you have not yet set up a dashboard through Power BI Desktop for querying your Dremio data source, complete the following section.

    1. Launch Power BI Desktop.

    2. Sign into Power BI Service, if you are not already signed in. To sign in, select File in the top-left corner of the screen and then select Sign in at the bottom-left corner of the menu that appears.

    3. Create a report for Dremio Cloud.

    4. Navigate to Files > Publish > Publish to Power BI.

    5. Select the destination. You may pick from a list of available workspaces to choose the desired one on which to publish. Select the workspace, and then click the Select button to publish.

    After the report is published, you receive a link it. Click the link to open the report in Power BI Service. This report has a live connection to Dremio Cloud, so you can run queries against it.

    note:

    If you upgraded to Power BI Desktop June 2022 and want to open a report that you published to Power BI Service from an earlier version of Power BI Desktop, follow these steps in Power BI Service. After you follow them once, you do not have to follow them again.

    1. Locate your report on the Data hub page.
    2. Click the three dots to the right of your report and select Settings.
    3. Expand the section Data source credentials and click Edit credentials.
    4. Select OAuth2 as the authentication method.
    5. Set Privacy level setting for this data source to Private.
    6. Click Sign In.

    Power BI for Administrators

    As an administrator, you configure connectivity from Power BI Service to Dremio Cloud, so that Power BI business users and report creators can run reports published to Power BI Service.

    Direct Connect: Enabling Users of Power BI Service to Connect to Dremio Cloud Without Going Through Power BI Gateway

    If users of Power BI Service want to run reports that were published from Power BI Desktop June 2022 or later, then connections from Power BI Service to Dremio Cloud do not have to go through Power BI Gateway. The ability to connect without using Power BI Gateway is called Direct Connect.

    Direct Connect requires connections from Power BI Desktop June 2022 or later to be encrypted.

    Creating a Connection from Power BI Service to Dremio Cloud via Power BI Gateway

    If users of Power BI Service want to run reports that were published from Power BI Desktop May 2022 or earlier, then connections from Power BI Service to Dremio Cloud must go through Power BI Gateway.

    To enable Power BI users to connect to Dremio Cloud via Power BI Gateway:

    1. Log in to your Power BI Service account at https://app.powerbi.com/.

    2. Click next to your profile picture at the top-right corner of the browser and navigate to Settings > Manage gateways.

    3. Under GATEWAY CLUSTERS, select the gateway you created previously.

    4. Select the checkbox Allow user’s cloud data sources to refresh through this gateway cluster.

    5. At the top of the page, click Add data sources to use the gateway. This launches the Data Source Settings page.

    6. Enter a Data Source Name.

    7. Select the Data Source Type drop-down menu and select Dremio Cloud.

    8. In the Server field, select whether to connect to Dremio Cloud’s control plane in the US or the one in the EU.

    9. 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 project that you want the ID for, click the settings icon, select Project Settings, and then copy the content of the Project field.
      If you want to use the default project for your Dremio Cloud organization, proceed to the next step.

    10. For Authentication Method, select Key or Microsoft Account from the drop-down list.

      • If you want to sign in with a personal access token (PAT) for Dremio Cloud, select Key, and then paste in the personal access token you obtained from Dremio Cloud. To create a PAT, follow the steps in the section Creating a Token.
      • If you want to sign into Dremio Cloud by using the username and password for a Microsoft account, select Microsoft Account. Click Sign in, and then specify your credentials.

        note:

        You can use Microsoft authentication only if the organization admin has enabled it in Dremio Cloud.

    11. Check the Skip Test Connection box if you are using a release of Power BI earlier than the April 2002 release.

    12. (Optional) For Engine, enter the name of the query execution engine in your project.

    13. Under Advanced Settings, set the Connection Encryption setting for this data source to Encrypted.

    14. For Privacy Level setting for this data source, select Organizational from the drop-down list.

    15. Click Add.

    A Connection Successful message is shown on top of the Data Source Settings page.