Why use SQL Server Analysis Services?
Now that you understand where Analysis Services fits into the SQL Server stack and the Microsoft BI ecosystem, why would you choose to use Analysis Services? Traditionally, Analysis Services was the best option to organize data for easy and performant analysis of data at scale. I have used Analysis Services to optimize data warehouses built on a variety of relational technologies including Microsoft SQL Server and Oracle. Analysis Services is source agnostic. If you can connect to the source, you have a use case for Analysis Services if you want more efficient analytics and reporting.
Optimized for reporting and analytics
This is the primary reason OLAP servers were introduced to the market. Earlier, we called out relational solutions and their optimization for efficient transaction handling. However, many of the optimizations for transaction handling conflict with reporting needs. One key example is the complexity of a relational solution.
The following diagram shows the complexity of relational design. The number of tables and joins required for reporting and analytics hinders the performance of report writers and queries:
Figure 1.2 – Relational diagram of the Wide World Importers sales schema
As you can see in the preceding diagram, relational models make heavy use of foreign keys and related tables. Ralph Kimball introduced dimensional modeling and the star schema concepts to help optimize read techniques with relational systems. This resulted in simpler, flatter (denormalized) schemas such as the following diagram, which is the best design to support multidimensional model design:
Figure 1.3 – Star schema diagram for Wide World Importers sales facts
While the star schema and dimensional models improved the ability of relational systems to extract reporting data, they were still bound to relational rules and languages. OLAP servers were introduced to further optimize the data for end user consumption. This resulted in even simpler, user-friendly options. The following example shows a pivot table in Excel that is directly connected to an Analysis Services model. This makes the data accessible and easy for users to analyze and create reports without deep technical skills:
Figure 1.4 – Excel pivot table connected to an Analysis Services model
Let's see the relation of Analysis Services with Excel.
Works great with Excel
This leads to one of the primary reasons that Analysis Services has become a beloved delivery platform for users and IT organizations. Once data is delivered in Analysis Services, it can be easily consumed by Excel. When a user connects to an Analysis Services model, they are able to interact with the data and build what they need from the underlying database without coming back to IT for additional support.
Organized with end users in mind
The other reason that has to be considered is that the data is organized to support the business, not database or code efficiencies. Well-designed OLAP solutions use business-friendly names for the data. OLAP solutions typically hide system fields as well making sure the data in the OLAP database is relevant.
Here is a list of key user-friendly features in OLAP databases:
- Proper spelling and grammar, using spaces, capitalization, and punctuation.
- Hidden system values such as primary keys, surrogate keys, and system names.
- Relationships built in so the user does not have to determine how the data is related; it is related in the model itself.
- Pre-existing common calculations such as totals or averages, which respond correctly to filtering or slicing.
The following table shows how reporting queries becomes simpler as the database engine and structure is more focused on an aggregated and report-friendly structure:
Figure 1.5 – How reporting queries becomes simpler
Each of these queries returns the same results:
Total Sales Total Profit Buying Group 73037043.78 31660852.75 N/A 62654262.56 27125589.10 Tailspin Toys 62352133.11 26942739.05 Wingtip Toys
As you can see, making data more consumable for users is one of the key reasons to use Analysis Services. When considered in combination with OLAP-friendly tools such as Excel, Power BI, and Tableau, the use of OLAP servers is even more compelling.
