James is a big data and data warehousing technology specialist at Microsoft. He is a thought leader in the use and application of Big Data technologies, including MPP solutions involving hybrid technologies of relational data, Hadoop, and private and public cloud. Previously he was an independent consultant working as a Data Warehouse/Business Intelligence architect and developer. He is a prior SQL Server MVP with over 30 years of IT experience. James is a popular blogger (JamesSerra.com) and speaker, having presented at dozens of PASS events including the PASS Business Analytics conference and the PASS Summit. He is the author of the book “Reporting with Microsoft SQL Server 2012”. He received a Bachelor of Science degree in Computer Engineering from the University of Nevada-Las Vegas.

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

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