On this page

    Connecting from Power BI

    Before you can use Power BI to derive powerful insights from your data and to create real-time dashboards, you must configure connections to Dremio Cloud from your Power BI application.

    Supported Versions

    Dremio Cloud is supported with the following Power BI tools:

    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 Power BI Cloud:

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

    Configuring Connections from Power BI Desktop to Dremio Cloud

    Prerequisite

    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.

    Configuring Connections

    1. Launch Power BI Desktop.

    2. Click the Get data option in the Home, or Get data from another source on the homescreen.

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

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

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

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


    Configuring Power BI Gateway and Power BI Cloud

    1. Configure Power BI Gateway:

      a. Configure the Dremio Cloud connector.

      b. Grant the Gateway service account access to your connector folder.

    2. Configure Power BI Cloud by creating a data source for your custom connector.

    Configuring the Dremio Connector for Power BI Gateway

    Prerequisites

    • Ensure that you are using a version of Power BI Gateway released in July 2021 or later. If you have an older version Power BI Gateway installed, upgrade it to the latest version.
    • If you are using a version of Power BI Desktop earlier than the April 2022 release: 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.

    Configuring

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

    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.

    Creating a Connection from Power BI Cloud to Dremio Cloud

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

    2. Click the Settings (gear) icon close to the search field at the top of the browser and navigate to Manage gateways.

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

    4. Hover around the gateway name towards the end to see a menu. Click ADD DATA SOURCE from the menu.

    5. In the Data Source Settings page, for Data Source Name, a default name is added. Change the name as required.

    6. From the Data Source Type drop-down list, click Dremio Cloud. After you pick the Dremio Cloud, the following parameters below are displayed.

    warning:

    If you see only the Dremio connector, then you do not have the correct version of the custom Dremio connector. Copy the custom Dremio connector to the C:\Users\{Your Username}\Documents\Power BI Desktop\Custom Connectors folder.

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

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

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

    4. Check the Skip Test Connection box.

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

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

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

    8. Click Add.

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

    The Power BI gateway setup is now fully complete and you can use Power BI cloud to run queries with your datasets.


    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. Try creating a visual of a bar chart to see the data.

    6. Save this file. Pick a location to save this file by providing a name.

    7. Under the Home menu, click Publish.

    8. In the Publish to Power BI dialog, choose a workspace to publish this dataset and click Select.

    9. After the publishing is done, you are notified about the success. Click Got it or close the notification.

    10. Go to your account on https://app.powerbi.com/ and click Datasets in the side menu to see the published dataset.