Moving Azure SQL database away from personal gateway to allow refresh

[harryws]

Hi, I'm hoping someone call help me!

I'll start by trying to summarise my use-case for Power BI:

I work for a company with a factory and I have built an analysis tool that essentially reports "live" KPIs like yield, run rate and machine run rates on our line. The current schema involves an SQL database (imported) and an excel spreadsheet (imported via web) which are installed on a personal gateway on my laptop.

The problem we face is that we cannot refresh the dataset outside of office hours if my laptop is closed (since it says data gateway is offline). The reason we require this is because our factory is 24 hours and we often view the report via and app on the mobile service.

We have since migrated our SQL database to the Azure service to hopefully allow seamless refresh even if my laptop is closed. However this isn't the case, and it still isn't working...

I've looked into potential other solutions including Direct Query (which I read allows for live data transfer) but from what I've read I wouldn't be able to create calculated columns or measures which are absolutely essential to my report.

Any advice or help would be greatly appreciated, thank you!

[v-heq-msft]

Hi @harryws ,
According to your description, you want to refresh the plant data during non-business hours while you have migrated the SQL database to Azure, but the refresh is not successful.
First of all, Azure SQL Database supports DirectQuery, which allows Power BI to query the database directly without importing the data, thus keeping the data up-to-date in real-time. You can check out this document for details on connecting Power BI to Azure SQL Database using DirectQuery.
Second, you mentioned that your computer is turned off during non-working hours. However, Excel spreadsheets still require a gateway to refresh if the data is not hosted in a cloud service such as OneDrive or SharePoint. You might consider moving the Excel data to OneDrive or SharePoint.Power BI can connect to Excel files stored in OneDrive or SharePoint Online, thus allowing scheduled refreshes without the need for a personal gateway. You can refer to this documentation.
Finally, although DirectQuery has some limitations, you can still create calculated columns and metrics. These limitations are primarily related to the complexity and performance impact of the calculation.

Best regards,

Albert He

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

[harryws]

Thanks Albert for your considered response! 

I looked into DirectQuery and I've given it a shot. However when I tried to publish my report using DQ it says "Your file was published, but disconnected" and "...is not connected to one or more data sources"

I've tried playing aroundwith a few things on the BI Service online but nothing is working... the data set credentials on the semantic model settings is greyed out so I can't change anything on there either.