I am going to use this blog to convince a friend to use SQL Server Analysis Services (SSAS) in his projects because it makes it easier to write reports on as compared to relational databases. Part of the challenge is that people don’t realize its advantages, and part is the large learning curve to use it.
This first post is on it advantages. Next will be a whole series to make it a bit easier to learn. First there are two main advantages for using SSAS.
SSAS is fast even on a large volume of data
Well, as long as the queries return few rows, and this really gets at the heart of when you want to use SSAS and when you don’t. It is good at returning a single sum for a column over a large number of rows, but is very slow at returning a lot of detailed rows. The reason for this is the totals (and the subtotals) are stored in the SSAS database. It takes more work to read all the original rows than the one single subtotal value. In fact, relational databases will be faster at returning detailed rows compared to SSAS.
Take for example the following SQL code from a relational database:
SELECT SUM(Cost)FROM Sales
In SQL Server, this query could take a very long time as it scans the Sales table, however in SSAS the result comes back instantly because the one value is already pre-calculated and stored in the SSAS database.
SSAS’s calculated measures are fast execution-wise and easy reusable
In relational databases, calculation code can get messy. The main problem with SQL SELECT statements is that they make it difficult to write a calculation once, like in a function, and GROUP BY different ways. You might build a SELECT statement in a string and execute the string using sp_executesql, or you use a CASE statement to list the different SELECT statements that group differently. Both are messy compared to SSAS.
Calculated measures in SSAS give you a way to define the calculations in a straight forward manner that does not matter how you will group the data later. They also you another advantage in that they are defined centrally in the SSAS database, and the reports pick and choose the calculated measures they want. This moves formulas from many reports to the central SSAS database where they are tested once.
Between having one set of centralized “official” formulas, and having formulas written in clear straight forward manner, the reports will have fewer number of bugs.