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