Designing a reporting structure

[CarlsBerg999]

Hi,

I'm trying to visualize the "big picture" of how the data flows to a report or a dashboard in Power BI Service from different sources. I have three categories of data sources:

SAP ByD (OData that would be retrieved via URL)

Random files (xls, xlsx, csv, exported from various places)

Server (Separate database that collects data from various places)

I want to create reports and dashboards and publish them in the Power BI Service with a scheduled refresh. I have a pro license. 

Question: Where do i import data to? The goal is to automate scheduled refresh as much as possible.

Im planning to do this:

1. SAP: Import the data to a Power BI Desktop -file that is located in my computer and then publish the report in Power BI Service, where I set the scheduled refresh.

2. Random files: Store the files in a folder on my computer, Import the data to a Power BI Desktop -file that is located in my computer and then publish the report in Power BI Service where I set the scheduled refresh. 

3. Server: Import the data to a Power BI Desktop -file that is located in my computer and then publish the report in Power BI Service, where I set the scheduled refresh

Is this the best way do this?

Thank you in advance for the help! 

[AllisonKennedy]

@CarlsBerg999
Do you have a Gateway setup for your local files? You cannot schedule refresh on local files without a Gateway, so putting them in SharePoint or Onedrive as @Sumanth_23 suggested can help. https://radacad.com/schedule-refresh-local-files-on-power-bi-web-site/?ref=818

[CarlsBerg999]

Hi, Thank you for the links, they were helpful! 

I'm still wondering two things: 

Scenario: I build a report on Power BI Desktop. The repport is based on data sources (xlsx, csv) that I saved to a OneDrive folder. I publish the report on Power BI Service. 

Question: Can Power BI Service automatically refresh data from the OneDrive folder without setting up gateway?

Question: If i delete the Power BI Desktop file, what happens to the report i published in Power BI Service? 

[Sumanth_23 · Accepted solution]

hi @CarlsBerg999 - yes, as mentioned by @AllisonKennedy  OneDrive is supposed to keep your data in sync every hour; you need to only setup a gateway connection if you are trying to sync with on-premises data sources ( database server within your network, local files on your computer etc. ) 

Also once you have published the report to the service you can delete the local copy of the report as it is no longer linked to the file on service - the data model & reports are uploaded to service.

You can always download your data model & report from the service as seen below:

Please mark the post as a solution and provide a 👍 if my comment helped with solving your issue. Thanks!

[AllisonKennedy]

You have a couple of options - if you save the desktop file into onedrive too, it will sync automatically the changes, as explained here: https://docs.microsoft.com/en-us/power-bi/connect-data/refresh-desktop-file-onedrive

Otherwise, if you publish to Power BI Service, the refresh will be scheduled from dataset - you can't do scheduled refresh from OneDrive because it syncs automatically I believe, but you also don't need a gateway. Changes are supposed to show up in the Power BI service within an hour. I recommend you test it with your data though, as it depends what other sources you have involved.

I would recommend saving the Power BI desktop file in OneDrive too, but I guess technically you can delete it. When you publish a report to Power BI service, the entire dataset and all transformations are stored in the service, so don't rely on the desktop model anymore.

Please do test though, as I personally use SharePoint or gateway for my reporting, so don't have much hands on experience with OneDrive and was helping another person in this forum with some troubleshooting with OneDrive sync and don't recall what the exact problem was.

This is all OneDrive for Business too as a note.

[Sumanth_23]

hi @CarlsBerg999 - Just to note that the "import" mothod of connect just pulls the data into your dataset and refreshes require to be scheduled to update changes in data as opposed to direct query which would always be connected live to the data source and any changed would automatically reflect. 

On publishing of a report with an import connection both the reports and dataset are published to the service - there is no link to you local computer once the report is publised on the service. 

For scenario 2 (file based) it would be more advisable to post the excel, csv files to Sharepoint or OneDrive rather than storing on your local computer - this way scheduled refreshes will read data from a shared location. 

There is a detailed article on the different types of connection modes 

https://radacad.com/directquery-live-connection-or-import-data-tough-decision

Please mark the post as a solution and provide a 👍 if my comment helped with solving your issue. Thanks!