Share Excel file with BI capability in Sharepoint or Onedrive

[gavin007]

I have an Excel file connected with Power Bi dataset. I want to share with a group of branch managers who need to refresh the pivot table and do some manual input to come up with a consolidated forecast. Currently I have to either create one file for branch and a master file to consolidate the input, or I have just use create a branch tab and consolidated tab in one Excel file but the file can't be shared as it will lose the Bi connection.

Either way, it is not a sleek solution. I then think about putting the Excel in Onedrive or Sharepoint solution.  I did some research, my question is, 

1. which one is better, Onedrive or Sharepoint?

2.  does it mean each user has to has the same AD log in Onedrive or Sharepoint  as Power BI in order to refresh the data?

3. if I have Onedrive or Sharepoint subscription and the other don't. Can i still let them has the read and write access to the file? I assume they can't refresh the data since they only have Power Bi subscription not Onedrive or Sharepoint?

I am new to Onedrive or Sharpoint in terms of Direct Query in Excel. hope i explain enough here.

[v-xulin-mstf]

Hi @gavin007,

About how to refresh a dataset created from an Excel workbook on OneDrive, or SharePoint Online, please refer:

https://docs.microsoft.com/en-us/power-bi/connect-data/refresh-excel-file-onedrive

Best Regards,
Link

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

[lbendlin]

"Analyze in Excel Online"  is not yet available - wait until after MBAS.  

Or are you saying that you manually connected to a Power BI Dataset from Excel via the SSAS option?

[gavin007]

Mine is "Analyze in Excel" and want to "Analyze in Excel Online". My current Excel 2013 need to install an add on to do it, I thought Excel 365 online doesn't need that but looks like I have to wait.

I dont use SSAS. But are you saving Excel report based on SSAS data can be refreshed in onedrive or sharepoint?

[lbendlin]

Please define what you mean by "refresh the Excel data" - are you expecting them to manually enter data into an Excel spreadsheet? Or are you expecting them to refresh that Excel file from existing data connection in that file? If so, what are these connections pointing to?

[gavin007]

Refreshing the pivot table that is connected to power bi dateset for actual, budget etc.  then base on the data, They will need to manually enter the forecast number in a seperate table. Hence there is no write back required. 

[lbendlin]

1. they are functionally equivalent

2. Not relevant - they cannot "refresh the data"  the way you imagine. Data write back from Power BI into the data source is not fully implemented yet.

3. Not relevant yet.  May change  after MBAS.

[gavin007]

@lbendlin Thanks for the reply.  here is my further comments.

2. I don't need to write back from Pbi. I am wandering if onedrive or sharepoint require the same AD login as Power Bi to refresh the Excel data.

3. If question 2 answer is no,  when the power bi user want to refresh the Excel data shared in Onedrive or Sharepoint, do they need to have a subscription on Onedrive or Shareporint?