Why use a SSAS cube?

1.       Speed: Aggregating (Summarizing) the data for performance: During cube processing SSAS will pre-calculate and physically stores aggregations of facts (Amounts, Quantities, Money).  These aggregations, for example Turnover by Year and Region, are used when a business user queries the cube for this type of information.  Therefore the query response time can be very short.  When the query is fired SSAS does not have to calculate the outcome from the underlying details (like T-SQL has to do), but can take the values directly from the stored aggregations.  Besides that SSAS stores query-results in a cache.  So the next time the same type of query is fired, it will try to get it from the cache.  Speed is especially important for a dashboard that an executive is using to slice-and-dice as any mouse click that takes more than a few milliseconds to return data will draw complaints

2.       Multidimensional analysis – slice, dice, drilldown: This very much depends on the tool or front end that is layered over the data, but the idea is that you can very quickly navigate around the data, finding trends, spotting patterns, ‘drilling down’, ‘slicing and dicing’ – all key to the concept of cubes.  Allowing the user to intuitively ‘wander’ around the data, not even realising that they performing analysis

3.       Multiple data intergration. On a cube you can easily use multiple data sources and do minimal work with many automated tasks (especially when you use SSIS) to intergrate them on a single analysis system.

4.       Can store Hierarchies

5.       Built-in advanced time-calculations – i.e. 12-month rolling average: It’s very easy to implement advanced time calculations like 12-month rolling average, year-to-date and references to parallel periods in previous years.  This is typically the stuff decision-makers in the organisation want to have.  Imagine how many T-SQL queries are required for calculating rolling averages for each of the previous 12 months (2009-May..2010 April) => 12.  Using the cube as a datasource => Only 1

6.       Easily use Excel to view data via Pivot Tables

7.       Security: You can use the security setting to give end-users access to only those parts (slices) of the cube relevant to them

8.       Automatically handles Slowly Changing Dimensions (SCD)

9.       Built-in support for KPI’s

10.   Ability to automatically link and display the records that make up an aggregation (“Show Details”)

11.   Support for drillthrough actions such as generating an SSRS report or linking to a URL based on the value selected

12.   Can use Analysis Services Data Mining

      More info:

What are the advantages of using BI Cubes over a regular Warehouse?
Beginner questions: Benefits of using an AS cube, over just database & SSRS?
Why Use SSAS?
Why Analysis Services?
Back to basics: Why do you need OLAP cubes?