how to automate my sources? (python and SqlServer)

[Anonymous]

I have a .pbix in a workspace.

The data consists of 4 queries:

One query is a     Python.Execute     the other 3 are Sql.Database.

So I have “mixed” sources: python + sql server.

I want to automate this to update weekly… what’s the best/standard way of achieving this?

• Should I create gateways and let PowerBI consume live from the source? Or…
• Do I need to create a dataset in the cloud and let it live in app.powerbi.com

I don’t know how to do any of the above, so, any links/guides on how to execute any of the 2 strategies are welcome.

Ps: size -> when I download the .pbix from the workspace… with data its 1,29MB; with live connection to data online 62KB.

[ibarrau]

Hi. Let's see. A SQL Server on Azure could work without a gateway, but a python script can't. If you sql data is in Azure I would consider solving the transformations without Python in order to avoid a gateway. If you can't or your data is on premise, then let's continue with the approach.

The solution here would be Installing an OnPremise Data Gateway in Personal Mode. Why Personal? because that's the only way Power Bi can run python scripts. Consider that "live" it's not an option for python. It will be imported data with scheduled refreshes. Once the gateway was installed and logged in, you can configure the power bi dataset credentials to let it schedule the refresh.

I hope that helps,

[Anonymous]

My sql is on-prem. I already have a gateway there.

The python query is a query to an API where I bring a FACT TABLE from an API, then all my dimensions come from a  SqlServer.

My questions now are?

• I already have installed the gateway on the On-prem (since last year), how can I check if that is a “Personal Mode” gateway??
• If my gateway in the on-prem is not in “Personal Mode”… where do I install the gateway?? on my laptop?

I also wonder what credentials to use, as I want this to continue working even if I leave the organization.

[ibarrau]

Ok. Then the solution must include gateway. In order to know what gateway you have, just go to the dataset and open the settings or schedule refresh:

This is an example of both gateways. When it's personal, it will say it.

Installing personal gateway can be tricky because of the user and stuff. The best approach would be asking IT to create a Service Account to let your user out of this. Then ask for a VM or Machine that will be turned on each time a refresh will happen. The best solution would be a VM turned on 24 hours a day. That way you can refresh it at any time of the day. 

Worst case scenario you can install it in your laptop with your user and it will totally depend on you. The laptop turned on and your user active in order to refresh.

I hope that make sense.

[Anonymous]

thanks @ibarrau  

 But this brings me more doubts…

Can I just rdp to the on-prem where the Sql Server is; and install there a Personal gateway?? (I already have the standard gateway there; I guess I can have a personal and a standard coexisting…).

If not; can I just pick any other on-prem that I have and install a personal gateway there with a service account?

[ibarrau]

Yes. Both Gateways can coexists at the same VM. That would be a solution yes.

I usually don't recommend having the database and the Gateway at the same VM because they would be sharing resources and the transaction at the DB can be critical for some businesses but if you already have the Standard one in there, there might no be issues with resources. That's something for IT to consider.

Regards