On-Cloud Data Gateway Power BI
A 10 steps solution to refresh you data in Power BI Portal by using a “cloud hosted on-premise data gateway”.
If you are in a hurry go directly to the summary.
Are you using Power BI to a connect to any hosted database different from Microsoft SQL Server?
Do you need to share your report with other people from your organization and often you need to refresh it with new data?
Have you already faced the fact that you need a data gateway and now you are wondering how to use it?
If the answer to these questions is “YES” this story suits to you !!
So let’s start from the beginning.
You have an hosted database.
So do I.
:)
Power BI Data Sources
If you have a database hosted in a cloud service you probably access the data contained on ti through Power BI. With Power BI we will be able transform, modify and aggregate the data and to create wonderful and human readable visuals suitable to be shared with your team and colleagues.
Power BI supports a quantity of different data source as you imagine and for sure the source you are using is among them… I hope. Anyway, if not, there is still the possibility for everyone to implement its own custom connector.
Fortunately it was not my case: I was a MySQL database hosted in a server on the cloud. There is a ready made connector in Power BI that allow connecting through import method to a MySQL Server. And for your information, in case you need direct query with MySQL you could use MariaDB connector and adapt the settings to your MySLQ Server (Power BI Import vs Direct Query: Everything You Need to Know).
Once you are connected the data source and after the data have been retrieved… you can finally start building your report and visualizations!! Enjoy it!
Publish your Data
We will skip to the part where you have created some visualizations and is needed to have these reports online, maybe shared with you colleagues or anyone who may be interested in .
You only need to click on “Publish” form the right on the “Home” panel (Please note that you have to have an account on Power BI in order to publish any data).
You can publish the dashboard online…
- on your personal Power BI workspace, and in this case only you can access the dashboard;
- or in a shared workspace previously created, such that any member of the workspace can see/modify/own the data, depending on the different permission assigned.
In both cases nest steps will be the sames.
Refresh your Data
Once reports are online, a “dataset” is created on the Power BI portal. The dataset is a sort of snapshot of the dashboard data at the moment in which you published the report from your local desktop app. It means in case of any changing on the data source, the published report will not update itself immediately, but you need to refresh it by manually by the portal !
So, refreshing the report is important and the service will not provide doing it automatically….
unless you have scheduled a refresh on the portal !!! 😉
It’s plenty of tutorials about that on the web, and it is generally pretty easy. You could program when your report will be refreshed, and how often (once per day the report will refresh automatically e.g.).
I won’t dwell too much on this one but I will limit to link the documentation.
Practically when the refresh is started, the Power BI Service connects to the data source and query it, finding modification on the data. At that point, it will perform a series of operation as developed by the dashboard designer and it will update the online shared report.
It works pretty well when the data source is Microsoft SQL Server, that is obviously completely integrated in the Microsoft Environment, as well as Power BI.
…the problem begins when the data source is not MS SQL Server.
Most of other data sources (it was mySQL in my case), even if they are online (or cloud hosted ) and reachable by public network, are not directly reachable by Power BI service, such that, in a nutshell, you can not directly refresh your published dataset without using an additional actor…
The Data Gateway
As mentioned in the documentation, in order to refresh the dataset you need a data-gateway.
Practically, it is a entrance that allows the transition of the data by the original data source to the dataset saved on the online Power BI workspace. This transition happens through this gateway, that is, in the most simple situation, located in your local machine.
It is in that case called On-Premise data gateway, just because it is hosted on your computer.
As you can see by this brief introduction (On-premises data gateway — Power BI | Microsoft Learn) is not difficult to be configured. The point is, using this solution, data are passing inevitably through your PC.
As in my case and as for many other business cases, it could be required to refresh the data daily or even every few time. If you want to use the on-premise data gateway you may consider the PC hosting the data gateway have to be switched on and connected to the internet every time the dashboard will be refreshed.
That’s not suitable at all, unless you obviously have arranged an on-premise efficient infrastructure.
Otherwise, a solution can be to rely on the “vnet data gateway”. The configuration is a little bit more complex, but it trusts on a cloud virtual network infrastructure and the gateway results always-online. Beyond this, you should know that in order to plan this kind of arrangement, you need the Power BI Premium License and it can be maybe not allowed to you to have one for any business reason.
Our solution…
The solution I’m proposing in this story is quite easy to be implemented. What you need is an account on a cloud provider (AWS, Azure, GCP, etc… ).
The big idea is to request a Windows virtual machine from the cloud provider (see here How to create a Windows VM on Azure), and install the on-premises data gateway over it.
N.B. It is important that the dashboards are published by the Windows VM.
However, you can use it the VM just for publishing the dashboards you have prevously comfortably developed on your local laptop; and certainly you should use it as a data gateway. You definitively need some RAM to develop a dashboard with Power BI, and using the VM just for publishing, you avoid to require an higher performance VM…
…so you could save some money !!!
For sure the solution is not for free… for sure is less expensive than a Premium License for Power BI.
In fact, you should take into consideration that when a VM is deallocated (or shutted down), the cloud provider is usually not billing you.
And if you think about it, at this point the VM has to be in a running state just in the moment the Power BI Service is refreshing the dashboard.
It means that if you refreshing your reports once per day, you need the VM to be Up and Running for something like 20/30 minutes. It means you are not going to spend more than a few dollars on a month for this VM.
For example, using a basic Windows VM instance in Azure, with 2 cores and 3.5 GB RAM, and running it for 20 minutes per day, it will cost to you just 0.78$ per month.
Price has been verified though Azure Pricing Calculator.
But how to manage it ?
Obviously for let it happening you do not want every time to start and stop manually your VM. The goal of this solution is to keep everything updated with the less possible effort.
In other words, you want to schedule automatic start and stop for your VM.
Each different cloud-provider offers a different solution:
I will leave the reader doing its considerations, since I did not test all of these services. I just allow myself to write about Azure Automation Runbook, since my infrastructure was hosted in Azure. The solution is completely free because automation account allows 500 minutes free units included per month, quiet enough for out operations.
And the powershell code is really easy… if you configured your automation account with the appropriate variables and schedules.
Finally what you need is to schedule your refresh on Power BI, and schedule the virtual machine to start 10 minutes before and to stop 10 later (or as you prefer for any reason).
Another suggestion: if your database has got only an analytical purpose, and it’s updated with new data by batch → you can save more money applying start/stop automation even to the database.
To conclude let’s summarize in 10 steps !!!
I hope and think you understood what the goal of this story was. Since the web is full of content and tutorial on the most of pieces treated on the story, I did not enter to much into details, but I preferred to describe the cheap solution summarized in those 10 steps:
- Download Power BI Desktop on your Laptop
- Connect your data source to your Power BI dashboard and enjoy building your visualizations!! 😉
- Deploy a Windows virtual machine from the portal of your favorite cloud provider
- Download and install these two app on the VM:
— Power BI Desktop
— Microsoft on-premise Data Gateway - Send your dashboard (.pbix format) to the VM, in the way you prefer.
- From the VM Login to the Power BI Online Portal .
- From the VM open the dashboard, enable the connection to the datasource, and publish it to a workspace in Power BI Portal.
- Configure the on-premise data gateway on the VM.
- By the VM, configure the dataset in the portal by using the on-premise data gateway.
- Schedule the data refresh from the Power BI portal.
At this point your service should be up and running !
The procedure is not so long to implement, excluding the dashboard designing part, it will take half a day. However as I have already suggested previously in this guide: think to implement automatic start and stop of the VM such that it will be running and billing just when it needed, or rather, when the Online Power BI dataset is refreshing.
It will save you money!!
That said, let me know if you have any doubt or suggestion and don’t hesitate to contact me!!! 🤠
keep in touch…
BYE
