Power BI Report Server - Data Sources to Use and Connect

[niyati_61]

Hi @d_gosbell ,

Currently I'm working with Power BI Reports on PBI Desktop for Report Server. Have the PBIRS (Jan 22) Version via SQL Enterprise. Edition.

Most of my company's data is stored in Excel Shared Spreadsheets on Sharepoint Online, or the Excel File is locally maintained and sent via emails to the team. 

Data Sources Currently - Shared Excel Doc on Sharepoint Online, Excel Doc - received by email weekly or so, and an Excel Sheet (Connected to SQL directly) - but I need to actually click on the refresh button to refesh the data in the sheet. 

1) Shared Excel Spreadsheet on Sharepoint Online - As PBIRS does not support refresh for sharepoint online data sources. What is the alternative here? Saving the Excel Sheet on the test server (where the PBIRS system is) - leading to question 2 below.

2) Excel Doc via Email weekly - An Excel File saved locally on my own System. If there are any updates - I would want it to reflect in the PBI Report too. Now, since I cannot refresh a locally connected file on my system in the report in the web portal. 

When I asked this previously, you mentioned that you need to change the data source in Power BI Desktop to point to a network share using a UNC path. These usually look something like the following   \\server\sharename\folder\file.xlsx 

Can you elaborate what this means? Does this mean that I should save this Excel file on the Test Server (Where PBIRS is present) so that the Data source also refreshes in the web portal?

3) Refreshable Excel Sheet - If this Excel Sheet - Connected to PBI Directly. I would still have to click the refresh button myself in the Excel Sheet - and then those updates - will come up in PBI when refreshed. There is no way to automatically refresh and update the data in PBI right. So, as Excel is directly connected to the Company's SQL Server, I would have to do the same with PBI and directly connect it to SQL. 

So, in PBIRS Web Portal, I click on Manage - Data Source - Give the creds for SQL then? Is that correct? How is PBIRS connected to SQL?

You had mentioned I can take the Query from Advanced Editor (in the Refreshable Excel Sheet - Query Editor) then paste the same in a blank query in PBI to directly connect it to SQL right. The issue is that, the Query Editor in Excel is blocked, I don't seem to have access to view the query. 

So, I have to request my Company's IT to connect the PBI too SQL then right?

4) Last Q, there is a lot of data saved on internal Company systems (on SQL or Oracle Databases) - for that connection to PBI, I will have to request IT from my company to assist on that right.

Thanks in advance! Really appreciate all your support.

[d_gosbell · Accepted solution]

---

@niyati_61 wrote:

1) Shared Excel Spreadsheet on Sharepoint Online - As PBIRS does not support refresh for sharepoint online data sources. What is the alternative here? Saving the Excel Sheet on the test server (where the PBIRS system is) - leading to question 2 below.

---

There are two main alternatives:

1. Use the online Power BI service instead of Report Server, the cost is similar and the main reason people use the on-prem Report Server is because they have data sources which they are not able or allowed to put on the cloud somewhere like Sharepoint Online or One Drive.
2. You would have to build a process to copy the data from the external locations to somewhere that the Report Server can access like an on-prem file share or an on-prem SQL Server.

---

@niyati_61 wrote:

2) Excel Doc via Email weekly - An Excel File saved locally on my own System. If there are any updates - I would want it to reflect in the PBI Report too. Now, since I cannot refresh a locally connected file on my system in the report in the web portal. 

When I asked this previously, you mentioned that you need to change the data source in Power BI Desktop to point to a network share using a UNC path. These usually look something like the following   \\server\sharename\folder\file.xlsx 

Can you elaborate what this means? Does this mean that I should save this Excel file on the Test Server (Where PBIRS is present) so that the Data source also refreshes in the web portal?

This means you need to share a folder from a server, so if you shared a folder called "Budgets" from ServerA then you would end up with a UNC path that looked like \\ServerA\Budgets  and then you can access the files and folders under that. Your IT department should be able to help getting something like this configured. You probably already have network file shares in use at your company and it's just a matter of finding a suitable one, getting it's location in the UNC format (most people access these as mapped drives, but PBIRS cannot use that technique since different users can have a different drive letter mapped for the same UNC).

---

@niyati_61 wrote:

3) Refreshable Excel Sheet - If this Excel Sheet - Connected to PBI Directly. I would still have to click the refresh button myself in the Excel Sheet - and then those updates - will come up in PBI when refreshed. There is no way to automatically refresh and update the data in PBI right. So, as Excel is directly connected to the Company's SQL Server, I would have to do the same with PBI and directly connect it to SQL. 

So, in PBIRS Web Portal, I click on Manage - Data Source - Give the creds for SQL then? Is that correct? How is PBIRS connected to SQL?

You had mentioned I can take the Query from Advanced Editor (in the Refreshable Excel Sheet - Query Editor) then paste the same in a blank query in PBI to directly connect it to SQL right. The issue is that, the Query Editor in Excel is blocked, I don't seem to have access to view the query. 

So, I have to request my Company's IT to connect the PBI too SQL then right?

Yes you should connect PBI directly to SQL whether that is copying the query or just re-building the table in PBI Desktop directly against the SQL source.

---

@niyati_61 wrote:

4) Last Q, there is a lot of data saved on internal Company systems (on SQL or Oracle Databases) - for that connection to PBI, I will have to request IT from my company to assist on that right.

---

Yes, you would need to get IT to grant you access and provide you with credentials. Ideally you want credentials where the password does not expire so you don't have to go in and update the password every few months.