Data Refresh for a SQL Server-based and Excel-based Dashboard ?

[F_Reh]

Hello,

I have a Dashboard (with multiple reports) which uses datasets from a MS SQL Server Database (on a particular Server over the network) and also some MS Excel Spreadsheets. What is the best way to set up a system to automatically and regularly refresh the Datasets of the various Reports?  Regarding the SQL-based Reports, the SQL Scripts for those are actually embedded in the Tables (within Power BI). Currently I have to manually refresh these Tables within Power BI itself by clicking on the Refresh Button at the top every time. I will also flag up that there is currently no Gateway installed.

I would appreciate some guidelines on the best way in constructing the regular Data refreshes for each of the SQL and Excel Reports in this Dashboard.

Regards

[MAwwad]

For the SQL Server-based reports, you can use the DirectQuery or Live Connection feature in Power BI to connect to your SQL Server database and automatically refresh the data. To set up a schedule for data refresh, you can use the Power BI service and set up a schedule in the dataset settings. You will need to have a Power BI Premium or Power BI Report Server in place to take advantage of the DirectQuery or Live Connection feature.

For the Excel-based reports, you can use Power BI to import the data from the Excel spreadsheets. You can also set up a schedule for data refresh in the Power BI service by using the Get Data > SharePoint Folder > Power BI Service option in the Power BI Desktop. If your Excel spreadsheets are stored in SharePoint, you can use the Power BI Gateway to refresh the data automatically on a schedule.

You can also consider using Power BI Report Server to deploy your reports and schedule data refresh for both SQL Server-based and Excel-based reports.