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.

I tried

  • 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?

  • 1
    Yes. If you don't want to include all the data directly within excel that is. Alternatively teach them how to modify the SQL statement. As for other options, Reporting Services and the corresponding reports would be an option. You won't be able to make it convenient for "everything he could possibly want" from that data. if you have a set of known reports you could also use VBA within Excel to provide some kind of interface. – Seth Jul 18 '17 at 8:30

You should probably take another look at Power BI.

I've never noticed Power BI Desktop to be generally "sluggish" and wouldn't expect that in DirectQuery mode against SQL tables with comprehensive columnstore indexes.

Publish your Report from PBI Desktop to the app.powerbi.com service and you will find it is less "ugly" - PBI Desktop has design-time frames and is often much lower resolution.

"Ugly" is also relative - most business users much prefer clean, well designed visualizations in Power BI compared to the typical grid of hundreds of cells in Excel. Power BI is about getting the user to the actual information they need to make a decision, without needing to hack data around manually in Excel.

If you really need the results in Excel, you can use the Analyze in Excel feature against any dataset published in the Power BI service. This gives you Excel Pivot Tables against a Power BI data model, which can DirectQuery your SQL server. It's similar to the SSAS scenario you described, but hosted on the Power BI service.

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.