Data sources and refresh

[Krcmajster]

Hi all, 

I am currently developing dashboards and I need to start thinking on setting automatic refresh. Currently I am pulling data from csv files stored on my drive but I am planning to use a few tools to transform the data and upload it to a datasource power BI can use. The data would not be big or complicated so my initial thought was OneDrive but I am having trouble using the ETL tools to upload the files there. MS Access is another thing I am just not sure if it can be stored online for Power BI to access all the time since ETL process should refresh the data daily. SQL Server and Analysis services should be the best option, but I believe there is a cost associated to it and it's fairly complicated. Any suggestion? Any links or resources would be great

[v-lid-msft]

Hi @Krcmajster ,

If you are looking for a data source which can connect from Power BI Service directly without using a On-Premises data gateway, we can find it from this document: https://docs.microsoft.com/en-us/power-bi/power-bi-data-sources, most of them can connect from Power BI Service, but not all of data sources can connect without gateway. For example, we do need a gateway to connect SQL Server or csv file, but if your csv file in come from web or sharepoint, we do not need gateway. If you want to connect Access database from service, it need a gateway according to document. We think maybe you can try to use csv file stored in Sharepoint if you had used sharepoint since it support organization account authentication and connect from Power BI Datasource directly. If you do not mind using a gateway, csv stored in local drive can also connected from Power BI Service.

Best regards,

[Krcmajster]

@v-lid-msft Thanks. If I managed to get csv. files on OneDrive for business, how will I be able to connect to them and will it be automatically refreshed (when scheduled)? I have tried https://docs.microsoft.com/en-us/power-bi/service-connect-to-files-in-app-workspace-onedrive-for-bus... but I can't make relationships between tables, basically one tabel is one dataset. Or if I create in Pover BI desktop version, publish it to workspace, am I able to overwrite the dataset by creating web application configured with permissions to access the desired Power BI instance? https://help.alteryx.com/current/MicrosoftPowerBIOutput.htm