Microsoft Power BI Gateway
If you’re using on-premises data sources, Microsoft Power BI Gateway enables you to keep dashboards and reports up-to-date without the need for moving data between services. This application connects your Dremio datasets with Power BI Desktop and Cloud so that you can easily query data wherever it is housed.
Install this application on a machine that is always online (24/7). Otherwise, queries may fail when users interact with a Power BI dashboard.
- If you want to sign in to your Dremio cluster by using the username and password for a Microsoft account:
- Ensure that you are using a version of Power BI Gateway released in December 2021 or later.
- If you are using a version of Power BI Gateway earlier than the April 2022 release: Ensure that you are using version 22.214.171.1241 or later of Dremio’s ODBC driver. To get the latest version, go to the ODBC driver download page. To install this driver, see ODBC.
- 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.
- Ensure that you have set up and configured a Dremio cluster.
- Ensure that you have administrator access to Microsoft Power BI Service.
Configuring Versions Power BI Gateway Earlier than the April 2022 Release
The following section outlines the steps required for installing and configuring Microsoft Power BI Gateway.
In the On-premises data gateway dialog, select Connectors tab.
Turn on the Custom data connectors toggle switch.
Click … next to the Load custom data connectors from folder field.
Specify a folder to store connector files. Make sure that the last folder is followed by a \ to ensure the gateway application locates the folder.
The service account (
NT SERVICE\PBIEgwService) must have access to the directory specified and its contents. For instructions on giving access, see the walkthrough below.
Navigate to where the Dremio ODBC driver is installed. Typically, the location is
C:\Program Files\Dremio Connector\Microsoft Power BI).
Dremio.mezfile and paste it in the folder specified in the previous step.
Restart the Power BI Gateway application by navigating to the Service Settings tab and selecting Restart now. From the Connectors tab, Dremio should now appear under Custom data connectors.
Give the service account access to your connector folder. The Gateway service account must have access to the folder where you’ll be storing your connectors. Complete this section to give Power BI Gateway’s service account access to the folder.
Navigate to the folder you specified in Power BI Gateway for custom connector files.
Right-click on the folder and select Give access to > Advanced sharing.
Click the Advanced Sharing… button.
Click the Permissions button.
Click the Add… button. This launches the Select Users or Groups window.
Under the Enter the objet names to select field, enter
NT SERVICE\PBIEgwServiceand then click the Check Names button.
From the Group or user names window, select the service account. Then select the Allow checkbox for Full Control.
Click OK and Close to close all advanced sharing windows.
The Gateway service account now has access to this folder.
Configuring Power BI Service to Query Dremio
This section outlines the steps necessary to configure Power BI Service to query Dremio.
Log in to your account at https://app.powerbi.com/.
Click … next to your profile picture at the top-right corner of the browser and navigate to Settings > Manage gateways.
Under GATEWAY CLUSTERS, select the gateway you created previously.
Select the checkbox Allow user’s cloud data sources to refresh through this gateway cluster.
At the top of the page, click Add data sources to use the gateway. This launches the Data Source Settings page.
Enter a Data Source Name.
Select the Data Source Type drop-down menu and click on Dremio.
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.
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.
In the Advanced Settings section, set the option Connection Encryption setting for this data source to either of these values:
Before selecting this value, ensure that these steps have been followed, if you are not using a load balancer for SSL offloading:
- Generate a certificate from a certificate authority or generate a self-signed certificate.
- Configure the
dremio.conffile 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.
Power BI Gateway setup is now fully complete and you can use Power BI Service to run queries against your Dremio datasets.
Setting up and publishing your Dashboard to Power BI Service
If you have not yet set up a dashboard through Power BI Desktop for querying your Dremio data source, complete the following section.
From Dremio, create a virtual dataset.
Launch Power BI Desktop and sign in 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.
Create a dashboard for Dremio via the Direct Query method.
The Direct Query method is the only method that Dremio supports.
Navigate to Files > Publish > Publish to Power BI.
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.
Once the publishing has completed, you will receive a link to your report. Click on the link to open the report on Power BI Service. This dashboard should now be found under the Reports section of the pre-defined workspace. This dashboard has a live connection to Dremio and can perform queries from your VDS.