In this article, we will explore how to auto refresh the data in a Power BI report that is created using the Yeeflow connector. By automating the data refresh process, you can ensure that your Power BI reports always display the latest and most accurate information from your Yeeflow data sources.
We will walk through the steps of creating parameters, modifying queries, setting incremental refresh, publishing the report to Power BI Online, and configuring the data refresh settings using an On-premises gateway.
Step 1: Create Parameters
Open your Power BI Desktop application, and open one of your Power BI report created before. Go to the Home tab and click on "Transform data" to open the Power Query Editor:
Click the "Manage parameters" from the home tab.
From the pop up window, Click "New" button to create a new parameter:
Create two parameters: "RangeStart" and "RangeEnd" with the data type set to Date/Time. Set default values for the parameters based on your desired date range.
Step 2: Modify Queries
From the Power Query Editor window, Select each table (e.g., tb_Accounts, tb_Opportunities, tb_Users) one by one.
In the Power Query Editor window, locate the existing queries that contain the date parameters (e.g., #datetime) for each table.
Replace the existing #datetime query parameters with the newly created "RangeStart" and "RangeEnd" parameters.
Verify and ensure that the queries are retrieving data based on the selected date range.
Once done, click on "Close & Apply" to apply the changes and close the Power Query Editor window.
Step 3: Set Incremental Refresh
In Power BI Desktop, go to the "Data" pane on the right-hand side.
Click on each data table (e.g., tb_Accounts, tb_Opportunities, tb_Users). In the pop-up operation menu, click the "Incremental Refresh" option.
From the Incremental refresh settings window, enable the "Set import and refresh ranges" option.
Configure the incremental refresh settings based on your requirements, specifying the ranges to refresh. Follow up the same steps to set other data tables which you want to auto refresh.
Save the Power BI report.
Step 4: Publish the Report to Power BI Online
Click on "Publish" in the Home tab to publish the report to Power BI Online.
Select the destination workspace and click "Select" button.
If you've published this Power BI report before, you can click "Replace" button from the confirmation form to update the new changes.
Step 5: Configure On-premises Gateway
Before enabling auto refresh for your Power BI report created with the Yeeflow connector, you need to set up and configure the On-premises Gateway. The On-premises Gateway is a critical component that enables secure communication between Power BI Online and your on-premises data sources.
Download and Install On-premises Gateway: Begin by downloading the On-premises Gateway application from the official Microsoft website. Click the link of "Download the standard gateway." to download the app:
Following the steps in this article to Install the Gateway on a server within your organization's network.
Once the Gateway is installed, download the Yeeflow Connector and place this "YeeflowConnector.mez" file to one folder on the same server.
Open the On-premises data gateway app. Click the "Connectors" tab from left, and select the folder that contains the Yeeflow connector file.
Click the Service Settings tab, and click the "Restart now" to restart this gateway service..
Open the Power BI Online portal. Click the "Settings" button from the top-right, and click the "Manage connections and gateways" from the pop-up window.
Navigate to the On-premises data gateway section. You will be able to find the Gateway that was created with the On-premises data gateway application. Click "More" operations. Select "Settings":.
From the pop-up settings window, find the Power BI section and check to check the "Allow user's cloud data sources to refresh through this gateway." and "Allow user's custom data connectors to refresh through this gateway" options..
Click "Save" button to close this window.
Navigate to the Connections tab. Click the "+ New" button from the top-left.
Select the connection type as "On-premises". Choose the "Yeeflow Gateway" from Gateway cluster name drop down list. Set the Connection name. Then from the Connection type drop down list, select YeeflowConnector.
From the Authentication section, choose "OAuth 2.0" from the drop down list. Then, click the "Edit credentials" link blow to login with your Yeeflow account.
Click "Create" button to create this new connection.
Step 6: Configure Auto Refresh Settings
In the Power BI Online portal, navigate to the desired workspace. Find the Dataset of your report. Click to open the Dataset detail page.
Select the Refresh button form the top navigation menu, then click "Schedule refresh" from the drop down list.
Find the Gateway connections form the settings page and expand this section.
Choose to user the YeeflowGateway by selecting the "Yeeflow connections" from the "map to" drop down list.
Click the "Apply" button to save the Gateway connections setting.
Scroll down this page to find and expand the Refresh section. Toggle on the schedule refresh option, and configure the refresh frequency andtime slots according to your requirements.
Click the "Apply" button to save your settings.
Go back to the Dataset detail page. You can manually trigger the data refresh to verify the previous configurations.
Click the Refresh button, and select "Refresh now" to trigger data refresh:
By following these steps, you can auto refresh the data in your Power BI report created using the Yeeflow connector. The parameters and modified queries ensure that the report retrieves data within the specified date range. Enabling incremental refresh optimizes the refresh process, and configuring the On-premises gateway and refresh settings ensures that your report is regularly updated with the latest data from Yeeflow.