Most people are familiar the relational database concepts so naturally, they try to leverage their existing knowledge while learning about another-type of database engines. 

In general, the enterprise-accumulated data (aka data warehouse) is  mostly used for reporting.  The business reporting needs are usually around some sort of aggregation of data. For example: “how much were our sales yesterday?”.  To answer this inquiry, we filter the sales records for yesterday, and we ask that the engine adds up the resulting records individual values and give us a total.  In T-SQL code: “select sum(Sale) from tblSales where Time = <yesterday> group by Time”.   

 So if a relational engine can do it, why do I need SSAS?   Here are a few reasons:

 1) TCO (Total Cost of Ownership) -  The reporting requirements change frequently so the end user may ask that the yesterday totals are broken down by product category.  The existing report either  needs to be adjusted or a new one will be created. In either case, an expensive technical expertise is required.   As soon as the new report is ready, a new requirement may come – perhaps, “totals further needs to be broken down by Employee”…which turns into a never-ending queue for change requests. On top of that, the number of reports explodes in time, which results in high support/ownership/ costs while their usefulness becomes doubtful.  With SSAS, the end user can slice and dice the data with the PC mouse on their own, without the need for technical expertise.  In other words, a static report becomes dynamic and the end user can control what it displays. Note: the SSAS contains metadata (data describing data) so all the relationships between the different tables/data are published and known; this allows for the end user interactively select objects and include them in the report.  

 2) Single Version of the Truth – Creating a single database allows for centralized reporting. Eventually, everyone agrees with the numbers and there is a consistency in the reported data.  Alternatively, different departments run their own reports and the numbers usually do not match. A good example is the sales department want to boost the numbers for higher commissions, while the accounting departments want to lower them to keep cost in check.  A relational database can also provide this service, which leads us into the next reason.

 3) Data Volumes – with disk storage becoming ridiculously low, data collection and volumes have exploded in recent years. It’s not uncommon these days do discuss Terabytes or Petabytes of data. Relational engines are very powerful but even with their pre-aggregation features, they cannot scale well beyond a certain point when it comes to ad-hoc querying and processing of large volumes of data. It doesn’t take much for one user to mistakenly query a non-indexed columns on an extremely large table and bring even the most power servers down to their knees.  SSAS solves the scalability challenge with features such as data partitioning, pre-aggregations, extremely power aggregation engines, and more.  MS SQL engine also has some of those features but it doesn’t scale as good plus it’s not as easy to create ad-hoc reports (addressed in (1) above).

4) MDX (Multi-Dimensional Expressions) Language – T-SQL (Transactional Structured Query Language) is used for querying relational data. MDX is used for multi-dimensional data. MDX is much more powerful language for data analysis.   For example: with MDX, it’s easy to perform Year-Over-Year comparison, Year-To-Date aggregations, Last Period, hierarchy navigations, member references, and much more.  The equivalent requests in TSQL have a tendency to be very challenging to implement and usually result in very slow execution times and expensive server resource usage.

 In summary, MS Analysis Server can give you scalable ad-hoc reporting for anyone who knows how to use the PC mouse (aka self-service BI).

Note: SSAS is a different engine from MS SQL.  Since Microsoft packaged them together, it’s a common misconception that they are the same engine( or very close).  With the popularization of SSAS, I’ve seen quite a few times for existing MS SQL DBAs/DEVs to inherit the responsibility of developing and supporting SSAS deployments.  Due to the difference in the engines and the complexity of the SSAS, this decision should be carefully considered before it’s made.