What are the advantages of using SSAS Cubes over a regular data warehouse for reporting? Note I’m not asking why report on a data warehouse instead of a OLTP database. which is covered at Why You Need a Data Warehouse. Think of it as if I have a reporting tool and have the option to link to tables in a data warehouse or an SSAS cube, what would be the benefits if I linked to the SSAS cube:
- 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
- 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
- Can store Hierarchies
- No need to join the fact and dimension tables, as this will be done in the cube
- 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
- Easily use Excel to view data via Pivot Tables
- Security: You can use the security setting to give end-users access to only those parts (slices) of the cube relevant to them
- Automatically handles Slowly Changing Dimensions (SCD)
- Built-in support for KPI’s
- Ability to automatically link and display the records that make up an aggregation (“Show Details”)
- Support for drillthrough actions such as generating an SSRS report or linking to a URL based on the value selected
- Reinforces best practice by requiring you to build a good data model (star schema) that’s created for business reporting and analysis
- Can use tools PerformancePoint and Power View
- 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?
Back to basics: Why do you need OLAP cubes?
