Sometimes you want to be able to offload work for one of your Power BI Gateways as you might have many users who are building reports that need refreshing or are connecting live to the same data source, all of this is going over the same gateway machine, with the same CPU and bandwidth. It would be great if we can spread the load a bit but at the time of this writing the Enterprise gateway doesn’t have a build in load balancing mechanism that allows you to do this. Luckily there is a manual workaround for this until Power BI supports this out of the box (which is on the roadmap).
Imagine I am building several different dashboards against a single data source, in this case SSAS, one is importing data into PowerBI on a schedule others are using Direct Connect directly, or I have multiple reports that point directly to my SSAS server. This might put your gateway under load at certain moments and you want to get this under control.
In this article we will take a look at how we can use multiple gateways that point to the same data source to achieve some form of load balancing. This is not the same as high availability.
First I will set up multiple gateways, on both I add a connection to the same server and database, in this case to the Adventureworks tabular model:
Now I can create a new PowerBI desktop that points to the same AS model as setup in the gateways:
When I decide this report is finished I can publish this to Power BI by pressing the Publish button. This is where I can now select which gateway I want to use:
Power BI Desktop automatically detects that I have the same connectionstring set up twice on gateways that I have access two. This triggers the question which gateway Power BI needs to use to connect to my data source. After selecting the gateway all queries using these reports will now go through this gateway. Make sure you use the Publish functionality using Power BI Desktop, when you upload the file to Power BI yourself Power BI will pick a gateway for you.
Now I can create other reports based on the same SSAS model where I pick the other gateway on publishing. I can still add items from both reports to the same dashboard if that is required. Now I can spread, somewhat, the load on the gateway if I want, even when using the same data source.
4 Replies to “Using multiple enterprise gateways to the same data source”
Do you know of a way to “switch” gateways on a deployed data source?
Let’s say we have REPORT 1, using GATEWAY 1, attached to source SOURCE 1
Do you know of a way to force Power BI to make REPORT 1 use GATEWAY 2 that’s also attached to SOURCE 1?
Use case : the server on which GATEWAY 1 crashes and we still need access to REPORT 1.
the only way I can imagine will work is to republish the report and choose the other gateway.
I figured that was the only way –
This gets tricky in environments where either :
1) we have published content packs based on a single source. If several people are connected to a single content pack / source stemming from a Gateway (which we do)
2) We have data that needs constant access with little or no service interruptions.
There’s basically no “Gateway failover”.
Did you install the second Gateway on the same server or on another server?