BI in Excel - Multiple vs 1 connection

[cassidy]

Does anyone have knowledge of whether it's more efficient for an Excel workbook to use multiple connections to a Dataset vs 1 connection to a dataset?

Scenario is when users Insert > BI Pivot, it creates a new connection to the Dataset each and every time, this can lead to 10+ connections.  When they Refresh All, each get's processed sequentially.

The other option that is harder to train, have the user copy an existing pivot to retain it's connection, making Refresh All run all of the pivot queries through a single connection.

Which demand less processing power, is there any difference?

[GilbertQ]

Hi @cassidy 

I would go with the approach of having the pivot tables refresh sequentially. The reason for this is that the pivot table sends MDX queries back to the Power BI dataset which is less efficient than DAX. So by allow each pivot table to refresh one a time it should not overload the Power BI Dataset.

And typically they do refresh quite quickly.