I have a requirement, where i proposed to have the data to be generated in SSRS or export to excel. However, i was told to do SSAS.
What are the benefits of having SSAS Cubes over SSRS?
Apart from performance which includes Aggregate calculation , cache data storing ; What are the benefits or need of having SSAS Cubes over SSRS?
Using SSRS, you can create a report that does a static evaluation of the source data. You can define specific row and column groups to aggregate the data, and the end-user can export that aggregated result to an Excel file.
Using SSAS, you define a "cube" with multiple (hierarchical) "dimensions" and all kind of useful aggregated values ("measures") from the numbers from the source tables ("facts"). The end-eser can use Excel to connect to the cube and dynamically build a pivot-table using any dimensional level and any measure defined in the cube to build an individual on-demand report for his/her specific need.
While the reports defined with SSRS can access live data, the cube is usually updated based on a schedule, once or multiple times a day (from a "data warehouse").