One of my favorite features included in the new Power BI ReportServer (PBIRS) October 2017 release is the ability to define scheduled data refresh for Import-mode datasets.
I would like to go through a walkthrough example to show you the steps that are required in order to create, publish and configure scheduled data refresh for a Power BI report published to PBIRS (minimum October 2017 release).
Power BI Desktop (optimized for PBIRS)
I assume, you already have downloaded (https://aka.ms/pbireportserver) and installed the PBIRS October Release. Besides the Report Server you’ll need a specialised Power BI Desktop release (also downloadable from the link above). The PBI Desktop release optimized for PBIRS can be distinguished from the “normal” PBI Desktop release based on addition to the program link in the start menu (“Power BI Desktop (October 2017)”). In addition, the title bar of the application also contains the October 2017 hint which makes it easier to find the right PBI Desktop.
Power BI Desktop optimized for PBIRS (start menu entry)
PBI Desktop optimized for PBIRS
Create the Report
In my sample I create a sample report based on a simple table stored in SQL Server. During the Get Data steps I chose Import mode and no data transformation was applied.
Use Import Mode
In addition to the sales data I imported a system view that contains one field with a timestamp (this view is used to show the latest timestamp the data was imported into the dataset / Power BI ).
Based on those imported tables I created a sample report:
Publish the Report
After finishing the report, the next step was to publish it to Power BI Report Server (PBIRS). I think I already blogged about it but again: Publish to PBIRS is not possible with the publish action in the PBI Desktop menu. PBI Reports for PBIRS need to be saved to the report server.
You only need to enter the URL of your PBIRS and choose the folder where you would like to save the report to.
After that we are ready to test the report. Either you use the direct link in the dialog shown above or you navigate manually to your PBIRS URL.
Let’s open the report et voila! Here it is! What you eventually can spot in the picture below (it’s marked in yellow) is the indicator for the last import timestamp plus the current time in my test-server. The last data import happend ~5 minutes before the report rendering. If you try to hit the refresh button – nothing will change because that report is based on an import dataset which means that data is only updated during a data refresh cycle!
Configure Data Refresh
Navigating back in your browser brings you to the folder content. If you click on the three “…” next to your published report the context menu opens. We need to navigate to the Manage command in order to configure data refresh.
In the Manage section you can either edit the properties of your report, define security definitions or configure Scheduled Refresh and your Data Sources.
My first idea was to “just configure scheduled refresh” was stopped by the following screen. Scheduled Refresh in PBIRS needs stored credentials (either SQL user or Windows user)!!
Configuration of stored credentials is done in the Data sources menu. Enter the credentials for all the data sources in your report (in my case it is only one data source but there can be multiple data sources!).
Back to the Scheduled Refresh menu I am now able to configure a New scheduled refresh plan. Within this dialog you can also use existing plan definitions to build new ones plus start an immediate data refresh. In the screenshot below those options are greyed out because no refresh plan existed!
The definition of a refresh plan is simple: Enter an (optional) description and define your refresh schedule. You can either use a shared schedule or create a report-specific one.
If you are familiar with defining SQL Server Agent Job Schedules the definition dialog looks definitely familiar for you. You can define the schedules based on a repeating time interval or just a one-time schedule.
What is also important to add here: SQL Server Agent is needed for the Scheduled Data Refresh functionality (see next chapter for more details).
What needs to be added here is the fact that data refresh can be speeded up to 1 minute refresh intervals. (Which is much more often compared to 8 times for powerbi.com PRO users).
When you are done with the schedule configuration, save it and the definition dialog is updated with the plan information. The last data refresh timestamp plus the last status is also displayed.
Going back to the report we are informed about the latest data refresh timestamp that is almost up-to-date with the system time in my test system.
Behind the scenes – SQL Server Agent
As I’ve already mentioned SQL Server Agent is needed for the Scheduled Data Refresh functionality. Behind the scenes PBIRS creates a SQL Server Agent job for every refresh plan. In my example the following Agent job was created:
Looking at the properties of that job we’ll see that it’s a “normal” Agent job that does the magic of data refresh (stored procedure [ReportServer].dbo.AddEvent with parameter @EventType=’DataModelRefresh’).