Publishing with mix of on prem and cloud data source best practices

[Ouytree]

Hi all,

I'm a sysadmin and my organisation is looking more into powerbi where it can. One particular situation is we have an in-house time recording app that is all on premise with a sql db, so we've got a data gateway set up for it and thats all fine, but now the devs have just told me that they also use an excel file for extra information which currently sits in one of the devs OneDrive.

I'm trying to explore any pitfalls or implement some best practices now to future proof the company, and overall gain a better understanding of this. The few ways I can see this going are:

1. Put the file on the data gateway server, make the folder it sits in a share so people can update it (which happens 3-4 times a day currently) then add it as a file data source.
2. One dev shares the file through his one drive so others can modify it, and publishes this to powerbi pointing one of his data sources to one drive, the other to the on premise sql db
3. Create a sharepoint site specifically for the app and put the file(s) in there, share it with the devs and whoever makes updates to it

My main concern has been what happens if its on the users onedrive and they change their domain password. I imagine it needs to be republished?

Any guidance or resources on this are greatly appreciated.

[v-juanli-msft]

Hi @Ouytree

My main concern has been what happens if its on the users onedrive and they change their domain password. I imagine it needs to be republished?

---

In this case, you need to edit the data source credential when they change the onedrive domain password.

As you described, you need connect a mix data source which contains a sql db on prem and an excel stored on onedrive, then you need to publish the Power BI Desktop Report to service and make schedule refresh for the dataset, right?

To achieve requirement above,here are some reference for you:

Merge or append on-premises and cloud data sources

The On-premises data gateway enables you to merge or append on-premises and cloud data sources in the same query

use and refresh excel data source stored on onrdrive

schedule refresh local files on power bi service

Manage your data source - SQL Server

Best Regards

Maggie

[Ouytree]

Hi Maggie,

Thank you for the links thats certainly helps a lot.

One thing I should have clarified in my original post is the third point would be a sharepoint online site not an on-premise one. So looking at the link about using onedrive with power BI, I could just change the onedrive for business link to a sharepoint online link and it would work the same way correct?

In this scenario I could not set up a sharepoint online datasource in the data gateway since its a cloud service, but would still probably need a generic O365 account for authenticating against the sharepoint online site in power bi desktop otherwise if a user uses their Oauth2 credentials and publishes the report, if they change their domain password it would still need to be republished?

[v-juanli-msft · Accepted solution]

Hi @Ouytree

Refreshing a dataset stored on OneDrive or SharePoint Online can be both obtained. There is someting different about specific steps, for sharepoint online datasource, you can refer to the following article.

https://www.netwoven.com/2016/07/21/analyzing-sharepoint-list-data-using-power-bi-2/

http://www.flyview365.com/2018/02/22/integrating-sharepoint-online-power-bi-part-2/

https://docs.microsoft.com/en-us/power-bi/refresh-desktop-file-onedrive

Based on my experience, you can successfully set schedule refresh on service for mixed sources of cloud(onedrive or sharpoint online) and on prem.

Best Regards

Maggie