Enable BBDD on MV (VMWARE) for access from a DATAFLOW POWERBI (step-by-step)

This post is also available in: Español

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

 class

Verify that the TCP protocol is enabled,

 class

In case it is a named instance, assign a static port (in this case, I chose port 14333)

 class

Open the input port in the Database Server machine’s OS Firewall

 class

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)

 class

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

 class

Verify if the connection has been achieved by expanding the instance to see the databases it contains.

 class

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.

 class

Run the installation (verify that you meet all requisites) and complete the required information

 class

Using a PowerBI Pro or Premium account, configure the credentials (password) when prompted

 class

I review the results

 class

🙂

I can also see that there is a new service running on the computer

Creating a DataFlow in my Workspace

 class

From the options below, I choose

 class

Select the Gateway that is already registered for this PowerBI user, and complete the Gateway credentials

 class

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

 class

I can now continue to use Powerquery for this report (.pbix), making specific ETLs, and designing the report.

="

Original
RobertoMirelman

Leave a Comment