Dataflows that can be created within a PowerBI Service Workspace (Pro or Premium licensed), are useful for reusing the logic developed with PowerQuery while making ETL processes (M scripts), so we can have queries with the following advantages:
- can serve more than one report, simplifying its creation and maintenance in a single instance
- can have a refresh rate independent of that assigned to the report dataset
To be able to use them with On-Premise data, for example, a local database, we must configure access permissions by installing a Gateway. If our database is on a virtual machine, as usual, we must ensure that the entire configurable string is correct.
Taking advantage of my tutorial with Dataflows, I have copied here a series of configuration steps, which can be useful as a guide. We start that way.
Accessing a database from a computer that does not belong to the local network (in this case, the PowerBI service)
Take note of the local IP of the virtual machine
Verify that the TCP protocol is enabled,
In case it is a named instance, assign a static port (in this case, I chose port 14333)
Open the input port in the Database Server machine’s OS Firewall
Open a port on the router/firewall to allow internal connection, choose an external port number, it can be the same, or another (I chose port 14339) and assign the internal port according to that of the previous point (14333)
Check if the connection is achieved. We’ll use a remote server (not in your LAN). I use the virtual machine’s host’s IP, or a dynamic DNS to access the virtual machine host, on port 14339. The router forwards it to the virtual machine with port 14333
Verify if the connection has been achieved by expanding the instance to see the databases it contains.
Installing Power BI Gateway
After deciding between the Personal Gateway and the standard, I choose the latter because it allows more functionality and is centrally managed. I download it from https://powerbi.microsoft.com/es-es/gateway/ or the one that corresponds to your language.
Run the installation (verify that you meet all requisites) and complete the required information
Using a PowerBI Pro or Premium account, configure the credentials (password) when prompted
I review the results
I can also see that there is a new service running on the computer
Creating a DataFlow in my Workspace
From the options below, I choose
Select the Gateway that is already registered for this PowerBI user, and complete the Gateway credentials
and finally I see the available tables
Now I can use PowerQuery and make the necessary transformations to get my queries done. I do the ETL process, which can then be reused to create more than one model. Where? On the PowerBI Desktop
Please bear in mind that a DataFlow is just a structure, which allows you to generate a dataset (it will be hosted in Azure), from which you only have to manage the update (manual or scheduled), and give the necessary permissions for other users to use it. But it is not part of any model: it is one more source for the construction of our model. Models are ALWAYS built outside of the POWERBI service, i.e. on our local computer with PowerBI Desktop
I have access to the object selection and content preview interface
I can now continue to use Powerquery for this report (.pbix), making specific ETLs, and designing the report.