Best solution for generating flat report

Question

I have an SSAS multidimentional cube (sql server 2019) of 350 GB with a retention of 10 years of data. I noticed that users often use the cube to extract data at the leaf level (Excel tables with multiple columns).

I think that SSAS is not suited for producing these type of reports.

What is the best tool / solution to let users genrate flat reports ? I know that sql is good for that but users aren't sql developers.

Could a PowerBI Model with direct query be more efficient than tha actual SSAS cube ?

score 0 · Answer 1 · answered Jul 30 '22 at 17:04

0

Could a PowerBI Model with direct query be more efficient than tha actual SSAS cube ?

SSAS Multidimensional is exceptionally bad at generating large flattened results. Almost anything will be better. A PowerBI or SSASS Tabular DirectQuery model is much better, but not ideal for very large extracts. But be sure to extract through DAX not MDX. A Paginated Report exported to CSV or Excel is a good choice too.

answered Jul 30 '22 at 17:04

David Browne - Microsoft

80,331
6
39
67

Browne I think paginated report is a tool for developers. – SniperPro Jul 30 '22 at 18:18
Yes. The idea there is to publish a Paginated report for them to use to extract the data. They could filter the data using parameters, but not change what table is used by each report. – David Browne - Microsoft Jul 30 '22 at 18:27
Could also use ssrs to generate reports. – MrHappyHead Aug 03 '22 at 07:39

Best solution for generating flat report

1 Answers1