I use numerous SQL Data Connections to import data into Excel for use in pivot tables / slicers. Some of these take a while to update and display.
Is there is any advantage in swapping some of these larger queries to Powerpivot imports?
Is Powerpivot more efficient or is it essentially doing the same job as a SQL data connection?
Really it depends on the setup.
If the pivot table is using the SQL database directly, e.g. change in the slicers results in an SQL statement issued to the database server, then yes power pivot would be more be more efficient. This would be due to the fact that the pivot table would then query the Power Pivot data model which would be static snapshot of data in the data model. Only when the Power Pivot data model is refreshed would it query the SQL back-end.
The main advantage to Power Pivot would be the following:
Anything involving the pivot table would hit the Power Pivot data model, which would be local processing on the computer running excel
If data is loaded directly into the Power Pivot data model it allows you to bypass the max number of rows in an excel sheet
In addition the data within the data model is typically compressed by a factor of 10x. With a data set that has values what repeat frequently having a higher compression. Row_IDs, being unique would compress poorly.
As a real life example, I have managed to load 4.8 GB of CSV files of a retailer's by store by item by week POS data (34M rows) using excel 2016 on my low power work laptop. Since the data was fairly repetitive, it ended up creating a 280 MB excel file.
Fact that the excel version, Power BI desktop, Power BI Web Service and SSAS tabular model all use the same calculation language and design. In fact an excel Power Pivot Model can be directly loaded into Power BI desktop and then used to make dashboards
Allows for complex math to be preformed within the pivot table.
Downsides