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.
This tutorial assumes that you have already set up and configured the following resources:
The following section outlines the steps required for installing and configuring Microsoft Power BI Gateway.
Download and install the Power BI Gateway application.
Once the install is completed, leave the On-premises data gateway window open. For servers using Windows, the Gateway application must be left open for it to continue running.
Navigate to the Connectors tab and 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
</code> 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
C:\Program Files\Dremio Connector\Microsoft Power BI).
Dremio.mez file 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.
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\PBIEgwService and 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.
As part of the configuration process for the Dremio connector, you’ll need to complete the following steps with Power BI Cloud. This section outlines the steps necessary to configure the Cloud application to query an on-premises data source like 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 checkboxes for Allow user’s cloud data sources to refresh through this gateway cluster and Allow user’s custom data connectors to refresh through this gateway cluster (preview).
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.
Fill out your connection information as needed, such as Username and Password.
Set the Connection Encryption setting for this data source to Not Encrypted.
Power BI Gateway setup is now fully complete and you can use Power BI Cloud to run queries with your Dremio dataset(s).
If you’ve already created dashboards through Power BI Desktop and Cloud to query Dremio virtual datasets (VDS), then you do not need to proceed further with this tutorial.
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.
Create a dashboard for Dremio via the Direct Query method.
Ensure that you are signed in to Power BI, if you have not done so already. This can be done by clicking File at the top-left corner of the screen and then Sign in at the bottom-left corner of the menu that appears.
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 Cloud. 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.