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:
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.