cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sdlsaginaw Frequent Visitor
Frequent Visitor

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

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?

1 ACCEPTED SOLUTION

Accepted Solutions
sdlsaginaw Frequent Visitor
Frequent Visitor

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

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.

8 REPLIES 8
Moderator v-yuezhe-msft
Moderator

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

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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
sdlsaginaw Frequent Visitor
Frequent Visitor

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

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

 

Moderator v-yuezhe-msft
Moderator

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

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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
sdlsaginaw Frequent Visitor
Frequent Visitor

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

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.

Moderator v-yuezhe-msft
Moderator

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

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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
sdlsaginaw Frequent Visitor
Frequent Visitor

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

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.

nicdiver Frequent Visitor
Frequent Visitor

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

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/

 

jeremyking77 Frequent Visitor
Frequent Visitor

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

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.

Helpful resources

Announcements
Community Highlights

Community Highlights

Find out what's new in the Power BI Community!

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 322 members 3,228 guests