[kyllo]

I agree and I do this via Power BI. If you import data into a Power BI report, create a data model with calculated measures (in DAX, not MDX), and publish it to the online service, then users can click on "Analyze in Excel" and it downloads an Excel workbook with a pivot table connected to that data model. I provide this to the PMs for the product I work on and they're able to answer a lot of their questions just by pivoting instead of having to write bespoke SQL.

[tqi]

Oh that's cool - does it work for business users on OSX? I feel like the fall of Thinkpads was the final nail in the coffin. We tried running a dedicated windows machine that people could remote into, but it was just too much friction.

Edit: One tool that looks promising is Equals (equals.com), but I haven't had a chance to play with it directly to see how it compares.

[kyllo]

Assuming the company has a Microsoft 365 subscription, Mac users can just use the Excel web client in their browser to access the data via pivot tables.

[breadwinner]

Where does Power Pivot fit into all this? The word "Pivot" in the name seems to indicate that it has Pivot Table-like functionality.

[kyllo]

Power Pivot is the name for the tabular data modeling feature in Excel. It works similarly to Power BI or SQL Server Analysis Services, sharing the underlying database technology. You can load data from queries and files, transform it, add relationships, hierarchies, and calculated measures, and connect pivot tables to it.