SQL Azure Import Data Refresh

[jdunmall]

Hi all,

I am trying to setup automatic refresh connecting to SQL Azure DB. The online service is prompting me to install a personal gateway. If I switch to DirectQuery, I am prompted for credentials. I need to use Import to support my data transformations instead of DirectQuery.

Why can't I setup data refresh using Import?

-jd

[BhaveshPatel]

Importing data from Azure SQL database is not supported in PBI Service. For that, You need to use PBI Desktop. Power BI Desktop connection to Azure SQL Database is an off-line connection. Off-line connection here means the data from Azure SQL Database will be loaded into the Power BI model and then reports will use the data in the model, this disconnected way of connection is what I call off-line. The off-line connection to Azure SQL DB can be scheduled in the Power BI website to be refreshed to populated updated data from the database.

To schedule a refresh in Power BI website, under Datasets click on ellipsis besides the data source that you want. and then choose Schedule Refresh.Set the Data Source Credentials for Azure SQL Database. And then you can schedule refresh. You can choose the frequency to be daily or weekly. and you can add multiple times on the day under that. 

[jdunmall]

Thanks Bhavesh. That is what I expect to happen.

Here is the challenge: when I go in to setup the credentials and schedule the refresh, instead I see this. This is a blank PBIX file with one connection to a Azure SQL DB.

Why am I being prompted for a gateway? Power BI should be able to refresh directly (which it does from the desktop).

[BhaveshPatel]

You have to have a gateway for setting up the schedule refresh in PowerBI service. 

Download the Personal Gateway and Set up connection for the first time. 

[jdunmall]

Why do I need a gateway? The data is in the cloud, and so is powerbi.

According to this FAQ, I shouldn't need one:

---

---

Question: Do I need a gateway for cloud data sources like Azure SQL Database?
Answer: No! The service will be able to connect to that data source without a gateway.

Any idea what I'm doing wrong?

[BhaveshPatel]

PBI Desktop is a standalone product and PBI Service is a cloud product. if you connect to Azure SQL Database from PBI Service, You can not import the data and it is always a direct quey mode connection. 

Since, you require importing of data, You need to use PBI Desktop where data is imported and loaded into the data model. You can upload this PBI desktop file to the PBI Service and if you would like to schedule refesh this file, You have to install gateway. 

you also need to refresh PBI Desktop file manually to get the latest data before you can decide the time of the schedule refresh in PBI Service.  

[jdunmall]

I have other examples where I connect and import data to PBI Service - for example, connecting to CRM via ODATA. No gateway required. Or connecting to Excel in O365. Again, no gateway. Both examples import data into Power BI and there is no DirectQuery option.

In this example, PBI Service would connect via the personal gateway to my machine, which then reaches back out to the internet to access Azure SQL. Why wouldn't the PBI Service just connect to Azure SQL and bypass the gateway?

It seems that the PBI Service thinks I'm asking it to connect to an on premises SQL database, and therefore asking for the gateway service.

I don't mean to be argumentative Bhavesh... just trying to find a solution here that doesn't require a gateway to be online.

[BhaveshPatel]

Unfortunately that is not possible currently. 

I still do not understand why do you not want to install Gateway. It secures your connection and on top of that, allow you to schedule refresh.

You can explore the third party product "Power Update" and it does not require a gateway to be installed. 

You can create a new ideas thread for this and I would definitely vote for this. 

I know there are certain restrictions when using Direct Query but hopefully it will go away eventually.

[jdunmall]

I don't want to install the personal gateway because it requires my machine to be online for a refresh to complete successfully. I also can't be travelling, because Azure won't let my random IP address through. If I go on vacation, the data doesn't update.

The alternative is an enterprise gateway, I assume. IT needs to be involved.

All to access data that is already in the cloud.

I found a uservoice feature request. Please vote! https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/15311712-import-azure-database-da...

[BhaveshPatel]

Hoping that It will sort out someday. Till then Good Luck..

[jdunmall]

Bhavesh, I'm trying to switch back to DirectQuery from Import, but it is greyed out, even for new data connections in the same PBIX. Feels like this is a one-way trip, and to go back I need to recreate my entire PBIX file. Is that right?

[v-yuezhe-msft]

Hi @jdunmall,

Based on my test, when we connect to Azure SQL database via Import mode in Power BI Desktop, after we publish the Power BI Desktop file to Power BI Service, no gateway is required if we want to set schedule refresh for the dataset, you can check the example below.






In your scenario, do you combine other on-premises data source and Azure SQL database data source in a single Power BI Desktop file(PBIX file)? If that is the case, when you publish the PBIX file to Power BI Service, personal gateway is required when setting schedule refresh.

In addition, when you firstly connect to Azure SQL database via DirectQuery mode in Power BI Desktop, you are able to switch it to Import mode, however, it will not allow you switch back to DirectQuery mode from Import mode. You would need to recreate the PBIX file if you want to use DirectQuery mode.

Thanks,

Lydia Zhang

[jdunmall]

I do see one thing different here - you are using Windows credentials to access SQL Azure and I am using database credentials. Is that a problem?

[jdunmall]

Thanks Lydia! That is exactly what I suspected - no gateway required.

Here is the problem: I create a brand new empty PBIX file, connect to Azure SQL, and save it. Nothing else. I publish, and I don't see the same interface. I am prompted to install a personal gateway.

Here is the code behind the PBIX file:

let
    Source = Sql.Database("XXXX.database.secure.windows.net", "YYYY"),
    dbo_vw_ClientStatus = Source{[Schema="dbo",Item="vw_ClientStatus"]}[Data]
in
    dbo_vw_ClientStatus

What does your code look like? I can't figure out what I'm doing wrong.

[v-yuezhe-msft]

Hi @jdunmall,

Below is my code. What is the version of your Power BI Desktop?

let Source = Sql.Database("powebiazure.database.windows.net", "powerbisqlazure"), SalesLT_Address = Source{[Schema="SalesLT",Item="Address"]}[Data]in SalesLT_Address

Besides, do you use the following entry to connect to Azure SQL? And does your PBIX file contain only Azure SQL data source as shown in the second screenshot?



Thanks,
Lydia Zhang

[jdunmall]

Hi Lydia,

Yes - exactly that. What a mystery! Perhaps I should try a different machine?

Version: 2.40.4554.463 64-bit (October, 2016)

Here is my data source settings in the same nearly empty PBIX file I gave you the source for earlier, which looks just like yours.

[v-yuezhe-msft]

Hi @jdunmall,

Would you please install personal gateway in other machine and test the scenario?

Thanks,
Lydia Zhang

[jdunmall]

I've tested the scenario on a new machine with a fresh copy of PowerBI and see the same result: the only way I can connect to SQL Azure is via a personal gateway. The only variable I haven't tested is the instance of SQL Azure.

Ah!

[jdunmall]

Hi Lydia, I can confirm that the personal gateway works. But I cannot as your screenshot indicates. I am going to try a brand clean install on another machine, and connect to Azure SQL with a minimal PBIX file.

This is what I see with the personal gateway installed and working:

[v-yuezhe-msft]

Hi @jdunmall,

Based on your desktop,  you combine Azure SQL database data source and web data source in a dataset, when you remove the data source from the dataset, do you require gateway? In addition, I don't use Windows credential, I use database credential.

Thanks,
Lydia Zhang

[jdunmall]

Yes Lydia, I've tried a basic PBIX with only one Azure SQL database connection. Same problem.

I have since gone through the process of creating a brand new Azure SQL database, and it works! I can refresh directly through PowerBI. Now I need to track down why it doesn't work with the "real" Azure SQL DB. But some progress.

I'll post back when I work out what is special about the one that isn't working.

Thank you again for your help.