On this page

    Connecting from Microsoft Power BI

    You can use Microsoft Power BI Desktop to query data sources through Dremio 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. If they choose to view them through Power BI Service, the following information applies:

    • If they view reports that were published by Power BI Desktop June 2022 or later, Power BI Service can connect either through Power BI Gateway or directly to Dremio. The latter option is referred to as Direct Connect.
    • If they view reports that were published by Power BI Desktop May 2022 or earlier, Power BI Service must connect to Dremio through Power BI Gateway.

    Supported Versions

    In addition to supporting Power BI Service, the cloud version of Power BI, Dremio 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. However, if you use Power BI Gateway to connect from Power BI Service to Dremio, do not upgrade to Power BI Desktop October 2022 until your instance of Power BI Gateway is upgraded to the October 2022 release.

    • 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 from Power BI Desktop or from Power BI Service:

    • Use the username and password for a Microsoft account. As a prerequisite, ensure that your Dremio cluster is configured for authentication with Microsoft Azure Active Directory. For the requirements and steps, see Configuring Authorization for Microsoft Power BI.
    • Use a username and password for a Dremio account.

    Power BI for Report Creators

    Use Power BI Desktop to query data sources in Dremio, visualize the result sets, and publish reports to Power BI Service.

    Configuring Connections from Power BI Desktop to Dremio

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

    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”, select Dremio Software, and click Connect.

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

      a. Specify the hostname or IP address of your Dremio cluster.

      b. In the Encryption field, select Enabled if connections to your Dremio cluster must be encrypted.

      c. Select DirectQuery.

      d. Click OK.

    4. Sign in to Dremio by using one of these options:

      • If you want to sign in to your Dremio cluster 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.

      INFO

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

      • If you want to sign in to your Dremio cluster with 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.

    Creating a Live Connection to a Dataset from Dremio

    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.

      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 Creating a Connection from Power BI Service to Dremio.

    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.

    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, 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, 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 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 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:

    • Your Dremio cluster to be at version 20.1 or later.
    • Your Dremio cluster to accept only encrypted connections on its client port.
    • Connections from Power BI Desktop June 2022 or later to be encrypted.
    • The Dremio host and client port to be public.
    • The Dremio host to have a CA-signed certificate.

    Enabling Users of Power BI Service to Connect to Dremio 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 must go through Power BI Gateway.

    To enable Power BI users to connect to Dremio 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.

    8. Specify your authentication credentials by using one of these options:

      • If you want to sign in to your Dremio cluster by using the username and password for a Microsoft account, select Microsoft Account in the sidebar. Click Sign in, and then specify the username and password for the account.

      Note:

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

      • If you want to sign in to your Dremio cluster with a plain username and password, select Username/Password in the sidebar, specify them, and then click Connect. You have the option of using a personal access token (PAT), instead of your password. To generate a PAT, see Creating PATs.
    9. In the Advanced Settings section, set the option Connection Encryption setting for this data source to either of these values:

      • Not Encrypted

      • Encrypted
        Recommended
        Before selecting this value, ensure that these steps have been followed, if you are not using a load balancer for SSL offloading:

        1. Generate a certificate from a certificate authority or generate a self-signed certificate.
        2. Configure the dremio.conf file according to the “Web Server Encryption” section in “Configuring Wire Encryption”, so that Dremio can locate the certificate when you attempt a connection from Power BI Gateway.
    10. Click Apply.