Automate SQL Queries run and store output in one of the tables of Power BI report

[Anonymous]

Hi Community,

I have some different requirement from Customer in relation to reporting (Snap shot in time ) . As of now,There are some SQL queries we have been running every day (Weekdays) and store the results in an excel spreadsheet . This Spreadsheet is placed in Sharepoint and then connected to Power BI for reporting through Get Data --> Share Point folder option. To follow this process everyday, some times it is getting hectic for us to do this entire process manually. 

So, I would like to know if there is any automated solution that can be implemented to avoid manual effort . The available tools i have are as shown below : 

 - SQL Server Management studio 

- SSRS/SSIS/SSAS

- Power BI 

Please assist !!!!! 

Thanks,

G Venkatesh

[amitchandak]

@Anonymous , you should explore incremental load

https://radacad.com/all-you-need-to-know-about-the-incremental-refresh-in-power-bi-load-changes-only

Power Automate

[Anonymous]

Hi Amit, Thanks for your reply. I looked at this post when i implemented Data Flows in my org . I used Incremental refresh before . However, now the biggest challenge is Some part of data to Power BI report comes from Sharepoint ( Sharepoint has a spreadsheet . Every day SQL queries are run and results are stored in spreadsheet) Rest of the data is through SQL as a data source. The reason why I use Sharepoint is - Customer wants a snap shot in time based on the counts i store in spreadsheet . I am not able to achieve that through Power BI .As I mentioned earlier , my requirement is quite different and it may not require DAX usage . I am stuck at - Automating SQL Queries to run at designated time and store results in spreadsheet . That Spreadsheet will be used in Power BI report late for reporting purposes.

[V-lianl-msft]

Hi @Anonymous ,

Based on your description, you use SharePoint to export and save historical data every day. See if this blog can help you:https://visualbi.com/blogs/microsoft/powerbi/historical-data-preservation-using-power-bi-dataflow/ 

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.