cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jesse_james
Helper I
Helper I

OneDrive refresh of report in DirectQuery mode

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!

 

1 ACCEPTED SOLUTION
RicoZhou
Community Support
Community Support

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.

1.png

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. 

View solution in original post

4 REPLIES 4
RicoZhou
Community Support
Community Support

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.

1.png

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. 

View solution in original post

jesse_james
Helper I
Helper I

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

jesse_james_0-1610636027958.png

 

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.


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

Happy to help!

LaDataWeb Blog

ibarrau
Super User II
Super User II

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:

ibarrau_0-1610627279584.png

 

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,


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

Happy to help!

LaDataWeb Blog

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Top Solution Authors
Top Kudoed Authors