On-premises gateway ability to limit data source connections/disable parallel loading?

[sdlsaginaw]

I'm having a problem in that the on-premises gateway opens more ODBC connections than we have licenses for and will not refresh. 

I have disabled parallel loading for the pbix and it works in desktop but the setting is ignored when refreshing via the gateway.

Is there a way to limit the ODBC connections the on-premises gateway attempts, or to disable parallel loading as in desktop?

[v-yuezhe-msft]

Hi @sdlsaginaw，

I am not quite clear about your requirement, please help to describe more details and post relevant screenshots. However, if you have added multiple ODBC data sources in a single dataset, to refresh  the dataset in Power BI Service, you would need to add all the data sources under on-premises gateway.

And we are not able to refresh specific data source in a single dataset, an idea about this issue has been submitted in the following link, please vote it to help Power BI team prioritize this feature.

https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/9858360-refresh-specific-data-sou...

Thanks,
Lydia Zhang

[sdlsaginaw]

I am using a single ODBC data source which has multiple tables under it. 

The provider of the ODBC implementation charges a hefty license fee for every simultaneous open connection to the ODBC server process. 

With parallel loading disabled in desktop I can refresh all of my tables within the confines of our ODBC license count. 

Once the file is published the on-premises gateway fails to refresh because it's opening a separate ODBC connection for each table, which quickly exceeds our ODBC server license count. 

I need to find a way to disable parallel loading on the gateway like I can on desktop (or set a maximum number which is less than our ODBC license count). 

[v-yuezhe-msft]

Hi @sdlsaginaw,

How do you disable parallel loading in Power BI Desktop and what is the exact error message do you get in Power BI Service?

Thanks,
Lydia Zhang

[sdlsaginaw]

For desktop it's File->Options and settings->Options->CURRENT FILE Data Load->Parallel Loading of Tables.  Uncheck "Enable parallel loading of tables".  With this unchecked I can see desktop refreshing one table at a time, I can see a single connection into the ODBC server, and eventually everything finishes succesfully.

As for the error message doing a gateway refresh:  Code -2147467259, Message "ODBC: ERROR [S0000] ErrCode:8, Reason:128 ERROR [S0000] ErrCode:8, Reason:128".  This is actually the ODBC data source saying "you don't have a license for this connection", which is expected because I see a sudden blast of connections into the ODBC server, all of which beyond the license count will be instantly rejected.

[v-yuezhe-msft]

Hi @sdlsaginaw,

Based on your description, you make connections to different data sources in ODBC and your final purpose is to refresh separate source due to license limit.

However, there is no feature that disable parallel loading or limit different data source connections in Power BI Service as far as I know. And Power BI Service cannot refresh specific source in a single dataset, in your scenario, you would need to solve the license issue, or refresh your data in Power BI Desktop, then re-publish the dataset to Service each time you make changes.

Thanks,
Lydia Zhang

[sdlsaginaw · Accepted solution]

I ended up writing a proxy server that accepts all of the gateway ODBC connections then brokers out the real connections to the ODBC server within the allowed license count.  Works great.

[jeremyking77]

In case you're interested

There is an update now from June19 that allows you to control this on the gateway via a configuration

https://docs.microsoft.com/en-us/data-integration/gateway/service-gateway-performance-cpu

You can change the setting MashupDefaultPoolContainerMaxCount

From the linked article:

Most queries use mashup containers to execute. So the number of mashup containers determines the number of queries that can be executed in parallel. A working set defines the memory allocated to each container. These settings are available in \Program Files\On-premises data gateway\Microsoft.PowerBI.DataMovement.Pipeline.GatewayCore.dll.config.

[nicdiver]

Here is how you can accomplish what you are asking in the gateway configuration:

On-premise gateway admins can now control the number of containers running simultaneously on the gateway machine. The more of those containers running, the more queries will run concurrently through the gateway.

Here are the steps to change this configuration:

On the gateway machine, open the file: [Program Files]\On-premises data gateway\Microsoft.PowerBI.DataMovement.Pipeline.GatewayCore.dll.config
Find the “MashupDefaultPoolContainerMaxCount” setting.
Change its value to the desired value.
Save and restart the gateway.

You will notice that in that file the default value of this setting is ‘0’. This means that it will use the default out of the box mashup engine setting of ‘6’ containers. You can override this default by setting your own value.

Keep in mind that those mashup engine containers require machine resources and will affect your machine’s performance. You will need to strike a good balance between the efficiency you get by running concurrent queries and optimizing the local machine resources those containers use. A recommended ceiling for the maximum number of containers you can use is roughly twice the number of cores in your local machine processor.

Changing this property value to 1 means that all queries will run sequentially, which could be desired for specific data sources that don’t support or don’t perform well with concurrent requests.

source: https://powerbi.microsoft.com/sv-se/blog/on-premises-data-gateway-january-update-is-now-available/