I've got a columnstore fact table with a couple million rows in a Sql Server instance.
I want that the customer can evaluate and export grouped and filtered parts of that data conveniently, and I struggle to find a good way.
- Binding the Excel PivotGrid to the table directly. That makes Excel load the data, which obviously takes forever.
- Using Power Query in Excel. That also wants to load the data in its entirety.
- Using Power BI Desktop (outside Excel). That thing can indeed bind to the datasource without copying everything (DirectQuery), but it's really nothing I would like to give to customers: The user interface is sluggish and ugly and the export function for tables only produces csv.
I think there's also the possibility of binding Excel Pivots against SSAS which in turn does DirectQuery against Sql Server. Do I really have to jump through that hoop?
Does somebody else have any more suggestions?