You can set up scheduled refreshes in Power BI to fetch fresh data to your report regularly.
While the Supermetrics connector for Power BI is undergoing certification with Microsoft, scheduled refreshes work with the help of an on-premises data gateway. The on-premises data gateway acts as a bridge. By using a gateway, organizations can keep databases and other data sources on their on-premises networks while securely using that on-premises data in cloud services.
Follow the steps below to install the Microsoft Power BI On-Premises Gateway on Windows 10 to let the Supermetrics Power BI custom connector support scheduled refreshes in Power BI.
Before you begin
You need to be logged in with your work Office 365 account in Power BI Desktop. Also, make sure that you have the latest version of the Supermetrics connector: download the latest version of the connector file from the Supermetrics Hub, and replace the one you have in the \Documents\Power BI Desktop\Custom Connectors folder.
Note that the computer where the gateway is installed must always be turned on.
Step 1: Set up the data connector folder
- Go to Power BI Service, click the Download icon in the top-right corner, and select Data Gateway to download the Microsoft on-premises gateway. Follow the installation steps with the default values. You can also download the data gateway from the Microsoft Download Center.
Make sure to use the standard mode. Don't use the Personal mode as it no longer supports Power BI Service.
- After the installation is finished, open the Connectors tab and select the folder with the Supermetrics connector. You can use the %UserProfile%\Documents\Power BI Desktop\Custom Connectors folder you have, but if you want to distinguish between connectors used in the gateway service and desktop app, navigate to the Documents\Power BI Desktop folder and create a new folder called Gateway Connectors. Put the Supermetrics.mez file you have there.
- To make sure the system has access to the file, go to the folder you used in step 2 (\Documents\Power BI Desktop) in Windows Explorer, right-click the Custom Connectors folder, and click Properties.
- Open the Security tab and click Edit → Add.
- Enter NT SERVICE\PBIEgwService.
- Click OK to confirm.
- Make sure that the Read & execute and List folder contents permissions are selected for this user and confirm by clicking OK in both windows you had open.
- Go back to the On-premises data gateway settings window, go to the Connectors tab, and make sure you see Supermetrics in the list. You may need to click Apply and restart the On-premises Gateway for the change to take effect.
If everything is done correctly, you'll see the Supermetrics connector listed. Now you're ready to set up a scheduled refresh in Power BI Service.
Step 2: Add data source connection in Power BI Service
- Go to Power BI Service, click the settings icon in the top-right corner, and select Manage connections and gateways.
- Go to the On-premises data gateways tab.
- Select your gateway, click the three-dot icon and open Settings.
- Select the 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 checkboxes, and click Save.
- Switch to the Connections tab and click + New in the top-left corner of the page.
- Set the following settings:
- Under Gateway cluster name, select the gateway you created in step 1.
- Enter a unique connection name, such as "Supermetrics".
- Under Connection type, select "Supermetrics" from the list.
- Keep OAuth Credentials as the authentication method and click Edit credentials.
- Sign in to your Supermetrics account using Microsoft or Google log-in.
- Keep the Organizational privacy level.
- Click Create.
Now, you're ready to set up a scheduled refresh in Power BI Service.
Step 3: Set up scheduled refresh
- Go to the Workspace with the report you’d like to have refreshed.
- Select the dataset used in the report and click Refresh.
- Select Schedule refresh at the top of the page.
- Open the Gateway and cloud connections section and map the data source to the Supermetrics connection you created in step 2.
- Go to the Refresh section and adjust the time for the refresh.
- Click Apply.