Skip to main content

Working from Power BI Dataflows

In Power BI Service, you can create Power BI dataflows that are derived from data that is in Dremio Cloud, and make them available to report creators who are using Power BI Desktop.

note

Report creators who are using Power BI Service cannot consume dataflows.

In Power BI Desktop, you can consume dataflows to create datasets of Dremio Cloud data that you can base reports on. Then, you can publish such reports to Power BI Service.

note

SSO is supported by Power BI datasets. It is also supported indirectly by Power BI dataflows; reports created from dataflows can be configured for SSO.

Creating Dataflows from Data that is in Dremio​

  1. In a workspace in Power BI Service, click New > Dataflow.

    note

    Ensure that you are not using "My workspace". You can cannot create dataflows in your personal workspace.

  2. Under Define new tables, click Add new tables.

  3. On the Choose data source page, search on "Dremio" and select "Dremio Cloud".

  4. Under Connection settings, follow these steps:

    1. 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. After you specify the control plane, the list of connections in the Connection field under Connection credentials (further down the page) is filtered to show only those connections that use the same control plane.

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

  5. Under Connection credentials, follow these steps:

    1. In the Connection field, select the connection that you want the dataflow to use when connecting to Dremio.

    2. In the Data gateway field, follow either of these steps:

      • If you want the dataflow to connect to Dremio Cloud through a Power BI gateway, select the gateway that is already configured to support this.

      • If you want the dataflow to connect to Dremio Cloud directly, select (none).

    3. In the Authentication kind field, select Account key.

    4. In the Account key field, paste in a personal access token that you have obtained from Dremio Cloud. See Creating a Token for the steps to obtain one.

  6. Click Next.

  7. Select the data from Dremio Cloud that you want to use for the dataflow, and then and click Transform data.

  8. Apply any necessary transformations to the data, and then click Save & close.

  9. Name the dataflow and save it, and optionally set a refresh schedule for it. The tables in the dataflow are listed.

  10. Return to the main page of your workspace.

  11. If you are not using a gateway to connect to Dremio Cloud through the dataflow, follow these steps:

    1. Open the settings for the dataflow.

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

    3. In the configure dialog, follow these steps:

      1. In the Authentication key field, paste your personal access token for authenticating to Dremio Cloud.

      2. If the dataflow will be used for the creation of reports that, when published, will allow single sign-on (SSO), select the check box Report viewers can only access this data source with their own Power BI identities using DirectQuery. For information about SSO, see Enabling Single Sign-On.

      3. Click Sign in.

  12. Ensure that your workspace is shared with all of the people who you want to be able to consume the dataflow.

The dataflow is now created and available to any Power BI users who have access to the workspace.

Consuming Dataflows from Power BI Desktop​

  1. In Power BI Desktop, click Get data in the Home ribbon bar.

  2. Under Common data sources, click Dataflows.

  3. Locate the dataflows and select the tables in them that you want to use.

  4. Click either Load to load the data into Power BI as it is, or click Transform Data to apply transformations before loading it.

Publishing Reports Based on Datasets Derived from Dataflows​

After you create a report that uses data in Dremio Cloud, you can publish the report to Power BI Service.

  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. Navigate to Files > Publish > Publish to Power BI.

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

  5. Log into Power BI Service.

  6. Go to the workspace to which you published the report.

  7. In the list of the contents of the workspace, hover your cursor over the entry for the dataset on which the report is based.

  8. Click the ellipsis to the right of the name of the dataset, and click Settings.

  9. If the dataset with which you created your report connects directly to Dremio Cloud, follow these steps to provide your credentials for authenticating to Dremio Cloud:

    1. In the settings for the dataset, expand the Data source credentials section.

    2. Click Edit credentials.

    3. Select the account that you want to use for authentication.

    4. Click Sign In.

  10. If the dataset with which you created your report uses a Power BI gateway to connect to Dremio Cloud, follow these steps to map your dataset to a data source:

    1. In the settings for the dataset, expand the Gateway connection section.

    2. Toggle to On the switch Use an On-premises or VNet data gateway.

    3. On the right end of the entry for the gateway that you want to use, click the arrow. The tooltip on this arrow is View Datasources.

    4. Click Manually add to gateway.

    5. In the New data source dialog, name the new data source.

    6. In the Data source type field, leave the value as Dataflows.

      note

      In the Authentication method field, OAuth2 should be the only choice.

    7. Below the Authentication method field, click Edit credentials and sign in again to Power BI.

    8. Click Create.

    9. Return to the settings for your dataset.

    10. Under Gateway connection, toggle to On the switch Use an On-premises or VNet data gateway.

    11. Select the gateway for which you created the new data source.

    12. In the Maps to field, select the name of the new data source.

    13. Click Apply.