On this page

    Connecting from Microsoft Power BI

    You can use Microsoft Power BI to query data sources with Dremio.

    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:

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

    note:

    If you are using Power BI Gateway for connections to Dremio from Power BI Service, ensure that your version of Power BI Desktop matches your version of Power BI Gateway.

    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.
      • Ensure that you are using a version of Power BI Desktop released in December 2021 or later.
      • If you are using a version of Power BI Desktop earlier than the April 2022 release: Ensure that you are using version 1.5.4.1001 or later of Dremio’s ODBC driver. To get the latest version, go to the ODBC driver download page. Instructions for installing the driver are here.
      • 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.

    Direct Connect

    Users can connect directly from Power BI Service to Dremio, without going through Power BI Gateway, to view published reports that make use of data in Dremio.

    Therefore, to use Direct Connect, you do not need to use Power BI Gateway.

    Prerequisites for Direct Connect

    • Ensure that your Dremio cluster is at version 20.1 or later.
    • Ensure that your Dremio cluster accepts only encrypted connections on its client port.
    • Ensure that connections from Power BI Desktop June 2022 or later are encrypted.
    • Ensure that the Dremio host and client port are public.
    • Ensure that the Dremio host has a CA-signed certificate.

    Authenticating Published Reports

    Follow these steps in Power BI Service for each published report:

    1. Locate your report on the Data hub page.

    2. Click the three dots to the right of your report and select Settings.

    3. If the report was published from a version of Power BI Desktop earlier than June 2022, follow these steps:

      a. Expand the Gateway connection section.

      b. Toggle On-premises or VNet data gateway off.

    4. Expand the section Data source credentials and click Edit credentials.

    5. Select OAuth2 as the authentication method.

    6. Set Privacy level setting for this data source to Private.

    7. Click Sign In.

    Microsoft Power BI Desktop

    Use Power BI Desktop to query data sources in Dremio and visualize the result sets. You can also 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.

    Prerequisites

    • If you are using a version of Power BI Desktop earlier than the April 2022 release, download and install Dremio’s ODBC driver. You do not need to configure a system DSN after installing this driver.
    • If you want to use a username and password for a Dremio account when connecting to Dremio, ensure that Microsoft Power BI Desktop release August 2021 or later is installed on your system.

    Configuring

    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. (For Power BI Desktop June 2022 or later) 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 via the DirectQuery method.

      Note:

      The DirectQuery method is the only method that Dremio supports.

    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.

    Microsoft Power BI Gateway

    Use Power BI Gateway for connecting from Power BI Service to Dremio only if users publishing reports to Power BI Service are using a version Power BI Desktop and Power BI Gateway that are earlier than the June 2022 version.

    If users publishing reports to Power BI Service are using Power BI Desktop June 2022 or later, users can connect to Dremio directly from Power BI Service without going through Power BI Gateway. See Direct Connect.

    For the April 2022 and May 2022 releases of Power BI Gateway, no configuration is required.

    For releases of Power BI Gateway earlier than April 2022, follow these steps:

    note:

    Before following these steps, ensure that you have administrator access to Microsoft Power BI Service.

    1. Configure the Dremio connector.
    2. Grant the Gateway service account access to your connector folder.

    Configuring the Dremio Connector for Power BI Gateway

    Prerequisites

    • Ensure that you are using a version of Power BI Gateway later than June 2021 but earlier than April 2022.
    • Download and install Dremio’s ODBC driver. Ensure that you select the option Microsoft Power BI Extension in the installer. You do not need to configure a system DSN after installing this driver.

    Steps

    1. In Power BI Gateway, navigate to the Connectors tab and click the ellipsis button next to the Load custom data connectors from folder field. Specify the C:\Users\{Your Username}\Documents\Power BI Desktop\Custom Connectors folder that contains the custom Dremio connector. Add a back slash (\) at the end of the location path.

    warning:

    The custom connector is required for connecting to Dremio.

    note:

    By default, the gateway runs under the (NT SERVICE\PBIEgwService) Windows service account that must have access to the directory specified and its contents. For instructions on giving access, see granting access to the service account.

    1. Restart the Power BI Gateway application by navigating to the Service Settings tab and selecting Restart now.

    From the Connectors tab, Dremio now appears under Custom data connectors.

    Granting the Gateway Service Account Access to your Connector Folder

    The gateway service account must have access to the folder where you will store your connectors. Perform the following steps to grant the Power BI gateway’s service account access to the folder:

    1. Navigate to the folder you specified in the Power BI gateway for custom data connector files.
    2. Select that folder and from the menu bar, click Share, and click Advanced Security.
    3. In the Advanced Security Settings for Custom Connectors dialog, click the Add button.
    4. In the Permission Entry for Custom Connectors dialog, for Principal, click Select a Principal.
    5. In the Select Users or Groups dialog, under Enter the object names to select, enter NT SERVICE\PBIEgwService and then click the Check Names button.
    6. Click OK.
    7. It takes you back to the the Permissions for Microsoft Power BI dialog, for Principal, PBIEgwService is the principal.
    8. Under Basic Permissions, check the Full Control box.
    9. Click OK and close all advanced sharing windows.

    The Power BI Gateway service account now has access to this folder.

    Microsoft Power BI Service

    You can publish reports to Power BI Service from Power BI Desktop, and log into Power BI Service to run queries from your reports and reports published by other Power BI users.

    note:

    If you are using encryption for connections to Dremio, and connecting directly to Dremio without going through Power BI Gateway, the Dremio server that you connect to must have a CA-signed certificate.

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

    Follow these steps only if users publishing reports to Power BI Service are using a version Power BI Desktop that is earlier than the April 2022 version.

    If this condition is true, ensure that Power BI Gateway is configured before you follow these steps. See Microsoft Power BI Gateway for the configuration steps.

    If this condition is not true, you do not have to follow these steps. Users can connect to Dremio directly from Power BI Service, and there is no need for Power BI Gateway. See Direct Connect

    1. Log in to your 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.