Solved: Re: Power BI Report Server - SharePoint Data Sourc...

niyati_61 · ‎04-28-2022

I’m currently working on a Department’s Dashboard where their data sources are on SharePoint Online Folders/ Files.

Once the data is connected to Power BI – I need to save it on Power BI Report Server, ensure connectivity, and schedule refresh the data. The objective is that the data is connected live, and a schedule refresh is available on the Report Server Web Portal.

Power BI Report Server gives an error that SharePoint Online files are not supported for Scheduled refresh and Live/ Direct Query.

-Our client has a strict requirement for On-premises only, so I cannot use Power BI Service.

-What can be done in the case where the data is SharePoint Online Files to be connected to the Report Server, or Alternatives on how to connect the data and ensure live data on a refresh schedule?

P.S: I've created a shortcut for the Sharepoint Folders I need on OneDrive - so currently I connected the data to Power BI by giving a C drive file location. So, now as we do for File type data sources, do I have to map to a network drive path and then connect this data?

Thanks in advance!

d_gosbell · ‎04-28-2022

@niyati_61 wrote:

-Our client has a strict requirement for On-premises only, so I cannot use Power BI Service.

Then why are they using Sharepoint Online which is not on-prem? It seems strange to put these files in the cloud but then say that reports against this same data cannot live in the cloud.

@niyati_61 wrote:

-What can be done in the case where the data is SharePoint Online Files to be connected to the Report Server, or Alternatives on how to connect the data and ensure live data on a refresh schedule?

You would need to build a process or script to pull down a copy of the files to a data source which Report Server does support like an on-prem network file share and then build your reports against that file share. You would then run this process using some scheduling tool like SQL Agent or the Windows task scheduler.

View solution in original post

d_gosbell · ‎04-28-2022

@niyati_61 wrote:

-Our client has a strict requirement for On-premises only, so I cannot use Power BI Service.

Then why are they using Sharepoint Online which is not on-prem? It seems strange to put these files in the cloud but then say that reports against this same data cannot live in the cloud.

@niyati_61 wrote:

-What can be done in the case where the data is SharePoint Online Files to be connected to the Report Server, or Alternatives on how to connect the data and ensure live data on a refresh schedule?

You would need to build a process or script to pull down a copy of the files to a data source which Report Server does support like an on-prem network file share and then build your reports against that file share. You would then run this process using some scheduling tool like SQL Agent or the Windows task scheduler.