OneDrive refresh of report in DirectQuery mode

[jesse_james]

Hi,

Can anyone help me to understand how OneDrive refresh works with reports connected in DirectQuery mode to data source?

Does this refresh import some data to PBI service dataset? Do end users still need to have access to the orginal data source? We noticed that some users have temporary access to report visuals even without access to source data 

I ask only about OneDrive refresh not refresh via Gateway.

Thank you!

Best regards!

[v-rzhou-msft · Accepted solution]

Hi @jesse_james 

For more details about how does Onedrive refresh work, you may refer to this blog: OneDrive refresh.

I get data from SQL Server by Direct Query, then upload this pbix file to my Onedrive for Business.

Then I get  file from my Onedrive for Business.

My Dataset setting screenshot is as below.

You see I need to configure gateway to connect to the datasource.

Your connection mode is DirectQuery, so there are metadata (not all data) and credentials in your pbix file.

When you get data from pbix file in Onedrive, you will get metadata and credential from Onedrive and you need to configure gateway to connect to datasource.

I can use Onedrive refresh and Scheduled refresh(set interval).

Due to you configure the gateway, the end users don't need to have access to the orginal data source(they need pro license).

Best Regards,

Rico Zhou

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

[jesse_james]

thank you for answer. Actually I wonder that will happen if I upload a pbix file with direct query connection inside using this OneDrive business and share report via App to end users. Do they need to have access to data source (lets say Azure SQL BD) or like in Import mode it is not required

[ibarrau]

If you upload a pbix, it doesn't matter if it comes from one drive or local. Power Bi Service will read it as a report with its own data source. That might be imported data or direct query to a DB.

Even if you have Imported data or direct query, the shared user will only need a Power Bi Pro account to see the report. Once the dataset is configured, the users won't need any kind of credentials or connections with data. That's how Power Bi works. A Dev user builds and configure everything and a shared pro user can see it.

Hope this make sense.

[ibarrau]

Hi. Just to be clear there is no such thing as Direct Query for OneDrive. You can import data in Power Bi Desktop with 8 refreshes by day like any other data source or you can import it in service to keep an hourly refresh with out transforming/cleaning data. I think you might be talking about the second option. When you click on get data from file onedrive, Power Bi will show the details:

This will create a new dataset in the workspace. You can create reports with it. It takes a define "table" inside a xlsx file by default. Once this is created it's also connected. This means that if it is in a collaborative workspace, all members can create reports and it will be refreshed by your account hourly.

Hope this helps,