Definition
Microsoft SQL Server Analysis Services (SSAS) offers online analytical processing (OLAP) and data mining capabilities, enabling business users to make sense of the data stored across their data warehouses, lakes, and lakehouses. It enables organizations to pull data from across the organization, analyze it, then make data-driven business decisions.
Why Do Businesses Use SSAS?
The average business has massive data stores spread across the entire organization. So when it comes time to use this data for making business decisions, how do companies parse through it all?
SQL Server Analysis Services (SSAS) is one of the technologies that seeks to solve this issue. It gets deployed as a database, then populated with data from all corners of the business. Then, users can display this compiled data in a visualization tool of some kind. For many organizations, these semantic data models are invaluable for reporting services and data analysis. Plus, they can integrate with a variety of client applications.
What is SQL Server Analysis Services Used For?
SSAS came out of a need for processing massive amounts of data. At its release in the 1990s, this technology was a game changer for organizations.
Before SSAS, there were only two-dimensional options for processing data. Data pros had to manually parse through columns and rows to draw conclusions. Plus, they had to know SQL to search through databases in this way.
Instead, SSAS empowers teams to ask specific questions of their data and get faster answers. Its drill-down and drag-and-drop features enable teams to discover powerful insights without requiring knowledge of SQL.
SQL Server Analysis Services uses three OLAP conventions to display data:
- Dimension – A categorical grouping that enables users to view specific breakdowns of non-numerical data (Date, Location, Product, etc.).
- Measure – A categorical grouping allowing users to see numerical data points breakdowns (Time, Quantity, etc.).
- Cube – A data store made up of a particular combination of dimensions and measures.
What are the two types of SSAS?
Users can view SSAS data output in two different ways: multidimensional or tabular. Businesses decide which type of SSAS to use based on the size of their data stores and the expertise of their teams.
Multidimensional SSAS
Multidimensional SSAS is highly scalable and robust, making it fit for processing massive amounts of data. It displays data directly as the three OLAP constructs (dimensions, measures, and cubes). Because of this, it’s sometimes known as OLAP SSAS.
But multidimensional models are so computationally heavy that they can sometimes be challenging to use. It’s also common to see multidimensional models create enormous cubes that exceed server memory. For these reasons, many organizations are fazing out multidimensional SSAS.
Tabular SSAS
Tabular models inherit metadata from those same OLAP constructs but display them in relational constructs (model, tables, columns). They are more lightweight than multidimensional and much easier to use.
But tabular models have limited functionality when compared to multidimensional SSAS. For example, they can’t support a role-playing relationship (associating multiple data keys with a single dimension).
Advantages and Disadvantages of SSAS
SSAS has a lot of advantages over older methods of processing data.. These benefits include:
- Automated analytics processing. SSAS can automatically process and parse data rather than relying on manual analysis to draw conclusions.
- Flexible breakdowns. Thanks to the OLAP cube structure, users can slice and dice their data as needed.
- Compatibility with other tools. SSAS works alongside other data visualization and BI tools such as Excel, Power BI, etc.
- Smaller learning curve. With the option to use tabular or multidimensional modeling, users don’t need as much specialized expertise as older relational databases required.
While advantageous in many ways, SQL Server Analysis Services also has some limitations in today’s world ruled by big data, such as:
- Computationally-heavy processes. Rather than pulling data as needed, SSAS must parse through all your data. This process ends up taking tons of time and resources.
- Cube limitations. Since building out a cube involves moving and re-organizing data, it can take hours, or even days, to create a large cube — which a modern-day business often requires.
- Limited number of dimensions. Today’s businesses require complex data processing, with several dimensions and measures in play. Once you add lots of dimensions into the mix, SSAS slows down substantially.
- On-premise storage. Rather than working in the cloud, SSAS uses single-server architecture. As with other on-prem tools, this means that whenever you run out of space, your team must add on more hardware. This process gets very pricey, very fast.
- Inability to support several users at once. While SSAS supports a handful of concurrent users, it can’t support enough users to enable data access for the entire organization (aka data democratization).
AtScale’s Alternative to SSAS
Rather than using cubes to store data, AtScale uses a universal semantic layer to make raw data business-ready. It’s a modernized approach to data analytics — all the benefits of OLAP without the drawbacks.
A universal semantic layer is much faster and less computationally heavy because data doesn’t need to get physically moved (as with SSAS cubes). Instead, AtScale generates queries against source data in real-time as users request it. We use pre-defined metrics to streamline this process.
The results: centralized data assets and flexible query access to users across the organization without the slowdowns and high consumption of SSAS.
Additional Resources:
NEW BOOK