Import vs Direct query for reports visualized on sharepoint

[BaronSdG]

Hello everyone,

I am making a report that will be published and shared/visualized on a sharepoint page.

The data will come from an Azure DB and I don't understand if I should use import or direct query.

I notice that direct query doesn't allow me to do many operations/customization on the dataset, but I need to do many customizations in order to make the report. I would prefer to make an import connection but I am not sure if it can be 1) shared on sharepoint 2) be interactive 3) be up to date periodically.

So my question is:

1) Can I create an import connection to the Azure DB which allows the data to be refreshed periodically (once per day)

2) If I share this report with users in a sharepoint, can users interact with it and visualize up to date data?

[V-lianl-msft]

Hi @BaronSdG ,

As @edhans  said,add some details.

If the directquery connection is used, the gateway is not needed, and the data is updated in real time. If you use import mode, you need to configure a gateway for this data source.

Configure scheduled refresh 

Embed the Power BI project report in SharePoint Online 

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

[BaronSdG]

Hi @V-lianl-msft,

I will be using import as suggested by @edhans.

Now, I have published the report and I went into the Dataset and clicked on Schedule Refresh. I have downloaded the gateway connection and entered with my credentials. I don't get why I need to have a gateway connection if the only data I need is provided only by the Azure DB. 

Also, I don't understand why I can't setup a refresh schedule yet.

[V-lianl-msft]

Hi @BaronSdG ,

---

@BaronSdG wrote:

Hi @V-lianl-msft,

I will be using import as suggested by @edhans.

Now, I have published the report and I went into the Dataset and clicked on Schedule Refresh. I have downloaded the gateway connection and entered with my credentials. I don't get why I need to have a gateway connection if the only data I need is provided only by the Azure DB. 

I misunderstood your description before. As you said, using only azure SQL database as the data source does not require a gateway.

In my previous tests, when I changed the direct query mode to import mode, I also failed to set the schedule refresh. I deleted the old dataset in service and republished the changed report, and it worked.

If the problem persists,you could create a support ticket for further help.

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

[BaronSdG]

"In my previous tests, when I changed the direct query mode to import mode, I also failed to set the schedule refresh. I deleted the old dataset in service and republished the changed report, and it worked."

I don't understand how to delete the old dataset service. When I click  on the 'Ok convert to import mode instead of direct query', I get the dataset downloaded automtically, so it doesn't seem like I need to do further actions.

Anyway, after I shifted to import mode, I can see that in my Azure SQL database there are new rows and they are not retrieved by the Power BI report, so I will open a ticket as something is not working as intended.

Thanks

[BaronSdG]

I can't open a ticket and request support as I am only offered Q&A/fixed replies...

[edhans]

I've not tested with Azure SQL but according to this article, a gateway is not required. It is an online resource.

[edhans]

You definitely want Import. Import is what is used over 90% of the time. Direct Query is for very specific circumstances, and as you noted, it is limited in what you can do on the report. DAX and M are also both limited.

You just need to set up scheduled refresh to the Azure DB in the report dataset settings in the service once you publish. Then get the embed code for the report from the service and put that in SharePoint. The report is fully interactive there for your users.