Forum Discussion

jdunmall's avatar
jdunmall
Icon for Advocate I rankAdvocate I
9 years ago
Solved

SQL Azure Import Data Refresh

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 credenti...
  • jdunmall's avatar
    jdunmall
    9 years ago

    I finally figured it out: the connection FQDN included the word "secure".

     

    This won't allow server-side refresh: <database name>.database.secure.windows.net.

    This will allow servers-side refresh: <database name>.database.windows.net

     

    Both FQDNs seem to work otherwise. The 'secure' setting was used to enable auditing, but I don't think it is required anymore.

     

    Problem solved. Thanks everyone.

     

    -Jeff

BhaveshPatel's avatar
BhaveshPatel
Icon for Super User rankSuper User
9 years ago

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's avatar
jdunmall
Icon for Advocate I rankAdvocate I
9 years ago

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's avatar
    BhaveshPatel
    Icon for Super User rankSuper User
    9 years ago

    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's avatar
      jdunmall
      Icon for Advocate I rankAdvocate I
      9 years ago

      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's avatar
        BhaveshPatel
        Icon for Super User rankSuper User
        9 years ago

        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.