SSAS Disadvantages: Opportunities for SSAS in the Cloud Era

Business analysts use a variety of tools to analyze data. There are many traditional OLAP engines, among which Microsoft SQL Server Analysis Services (SSAS) is one of the most widely used in the world. In the family of Microsoft SQL Server, SSAS comes up as an ideal data mining and multidimensional online analytical processing (OLAP) tool, especially for BI applications. It facilitates users in designing, creating, and managing multidimensional structures/mining models with data collected from disparate data sources/relational databases and with the help of data mining algorithms. This offers an augmented level of decision-making for better business output.

Many large enterprises are deep and long-time users of SSAS. SSAS has many excellent features that have made it an ideal choice for many traditional business intelligence solutions. But it has faced increasing challenges in the era of big data. With the explosive growth of data volumes, these challenges have become particularly acute.

Key Benefits of SSAS: What Has it Endured?

There are many traditional OLAP engines on the market. Oracle, SAP, IBM, and Microsoft all have their own OLAP engines. The role of an OLAP engine is to provide high-performance analytics and an enterprise-level semantic data model and use it for business intelligence reports and client applications. Their most important business value is to turn raw data in the database into a business-centric and user-friendly semantic model, and finally, to query the data. On Azure, you can also access its sibling Azure Analysis Services (AAS). However, it is important to note that AAS and SSAS are NOT the same thing. In particular, AAS does not offer multi-dimensional modeling. The success of SSAS is linked to its following characteristics:

• Deep integration with Excel: Excel is the world's most widely used business analysis tool, and SSAS is deeply integrated with Excel. Users can use Excel to flexibly query SSAS cubes, and quickly slice and dice, filter, rollup, and drill down into the data until they find the business insights they need.
• Support for MDX language: SSAS supports MDX (Multidimensional Expressions) for advanced business intelligence needs, such as the commonly used Year-to-date, Quarter-to-date, and Month-to-date calculations. MDX can also support a variety of business intelligence needs more simply and broadly than SQL.
• Integration with major BI tools: Power BI, Tableau, and several other BI tools can be easily integrated with SSAS. SSAS receives the queries sent by the BI front-end, calculates it in its own engine, and returns the aggregated value to the BI front-end to improve the query experience.

SSAS Disadvantages: The Shortcomings of SSAS in the Era of Big Data

The longtime success of SSAS has resulted in the creation of a huge amount of value in terms of the creation and organization of structured data (cubes). But SSAS has experienced a bit of its own success crisis. As companies relied more and more on SSAS, the explosive growth in data volumes stressed its single server architecture to the breaking point. At the same time, BI and analytics teams have raised expectations for processing huge datasets with the new era of cloud platforms and big data tools. While this has seemingly left SSAS behind, Kyligence provides a powerful path forward for the massive amount of valuable intellectual property that lives in SSAS data and applications.

Poor handling of large data volumes and dimensions

The SSAS multidimensional mode has acceptable performance with small volumes of data, but performance suffers when querying or cubing large datasets, especially with many dimensions. Like most of the other MOLAP solutions, SSAS pre-calculates every intersection of the dimension combination by all measures, this results in the curse of dimensionality. The cube can become so large that it cannot be calculated within a reasonable time frame.

Lack of Scalability: The Limitations of Single-Server Architecture

Existing SSAS processing capabilities have run into the natural limitations of a single server architecture. To improve performance, you have no choice but to scale up with larger, high-end, multi-core servers with a large number of cores, more RAM, and faster, larger disks. But there is no escaping the architecture limits of a scale up strategy for SSAS. Unfortunately, there is no notion of a SSAS cluster for scale out.

An Inability to Handle Cloud-Era User Concurrency

Another limitation of a single server architecture is the ability to support large numbers of concurrent users. SSAS may struggle or fail when too many users query at the same time. Once again, a distributed architecture could provide improved concurrency. With the growth of the citizen data scientist phenomenon, the need to support more and more concurrent users will become a critical requirement of any future analytics architecture.

Resolving SSAS Limitations in the Cloud Era

Today, with big data technology evolving, an ideal BI/OLAP analysis architecture should retain all SSAS analysis capabilities and leverage the scale-out capability of a big data approach. This could address all of the pain points that SSAS faces. Such architecture should enable the following:

• Scale-out architecture: One of the capabilities of big data technology is scaling processing power by adding more server nodes to the cluster. A distributed OLAP solution like Kyligence can provide this powerful advantage. A scale-out solution greatly improves the scalability of the system and reduces the difficulty of expanding the system’s capabilities.
• Remove limits on the number of dimensions: Multidimensional cubes in SSAS need to be aggregated according to all of the dimensions in the cube. Although optimization strategies exist, the curse of dimensionality is real and inevitable. The ideal OLAP technology would allow users to customize the aggregation dimension. This effectively removes the upper limit on the number of dimensions supported in a cube.
• Moving to the cloud: More and more enterprises are starting to build their end-to-end data analytics platforms in the cloud. An OLAP solution that can natively work in a public cloud is becoming essential. This requires the OLAP solution to be able to ingest data from cloud data lakes or data warehouses, build their data pipelines using cloud resources, and integrate with cloud native applications, services, or tools.
• Higher ROI: With an OLAP on big data solution, building a cluster does not require high-end hardware, and therefore compute costs can be greatly reduced and optimized. This is particularly helpful on pay-as-you-go cloud platforms. Users can expand clusters when they need a lot of resources, shrink clusters when they are idle, and free up redundant resources to maximize resource usage and cost savings.

How Can Organizations Migrate Away From SSAS?

Kyligence is now working with many companies to help them migrate from a traditional business intelligence infrastructure to a big data architecture. Some of these customers are also working closely with Kyligence to solve the additional challenge of migrating from their SSAS solution. We included two case studies to help illustrate the ways in which Kyligence can help resolve the disadvantages caused by a reliance on SSAS.

Case Study #1: Migrating From Traditional SSAS Infrastructure to Big Data Architecture

In this real-world example, a Fortune 500 restaurant chain wanted to migrate their restaurant operational analytics solution away from SSAS towards a cloud solution. The goal was to establish a unified big data analysis platform to improve efficiency and support future growth.

Challenges Caused by SSAS

• Since SSAS is not designed to scale-out, it was limiting the analytics performance and reducing the breadth of business scenarios that could be analyzed.
• Large-scale Restaurant businesses require complicated data models, advanced measure calculations, and flexible calculations in data analytics to support their business decision making. 
• The company hoped to expand analysis scenarios involving more than 50 billion lines of data for sales and product intelligence, and user behavior analysis. 
• The existing architecture, however, could not support the increased demands.
• This was further complicated by the fact that the organization's business users wanted to keep using Excel as their front-end tool.

Benefits of Migrating Away From SSAS

• Using Kyligence on-premises solution provides high performance and a highly concurrent query service.
• Kyligence’s Unified Semantic Layer  integrates seamlessly with Tableau and Excel

• Kyligence’s integration with big data architecture removes performance bottlenecks (100x performance improvement) without disrupting the original analysis experience granular control of permissions.
• Kyligence’s ability to scale helps support future business growth. When data volumes grow with the business, the customer can easily add more cluster nodes and expand the query performance horizontally with Kyligence.

Case Study #2: Replacing a Giant SSAS OLAP Cube with a Cloud Solution

In this real-world example, A large-scale financial services company relied on one of the largest SQL Server Analysis (SSAS) OLAP cubes in the world to support their risk management analysis, but the current solution was running into scaling limits due to increased data volumes.

Challenges Caused by SSAS

• Architecture scaling limits
• Untenable data sizes
• Analysts' work would get locked out by incremental loading workload, with system crashes happening frequently.
• Expensive (time, effort, budget) to maintain redundant cubes and sub-cubes.
• Poor performance on data loading and queries - especially on high cardinality, count distinct, correlation.
• Limited user concurrency.

Benefits of Migrating Away From SSAS

• Kyligence Cloud on Azure provides highly scalable big data analysis.
• Unified semantics and MDX calculation capability
• Deploying a single cube provides much easier, more efficient management
• Analysts' work is no longer interrupted by data loading processes
• Transparent to business users, same analysis behaviors using Excel
• Improved query and loading performance
• Supports 100+ concurrent users
• Architecture meets future requirements

Conclusion: Move your SSAS Investment Into The Cloud Era With Kyligence

There have been many advantages of the SSAS platform that have made it a popular OLAP platform. But many companies are looking for ways to bring the technology forward to a more modern, big data infrastructure. Limited scalability and limited data capacity make it difficult for SSAS to meet the requirements of the big data era. Kyligence can help customers bring all of the value that they created with SSAS forward into the future.

Kyligence's big data solutions provide customers with the following value:

• Complete semantic model: Semantic modeling is an important part of business analysis, including business analysis ideas, dimensions, measures, hierarchies, and more.
• Multiple query language support: Kyligence provides a variety of interfaces including SQL, MDX, and REST APIs.
• Excel integration: Kyligence uses MDX to interface with Excel, providing an experience similar to using Excel with SSAS. 
• Isolate user experience from IT internal processes: Business users can completely retain the current analytics experience using Excel or other commercial BI tools. 
• Powerful backend engine: Kyligence uses the computing power of Apache Spark to pre-calculate data.

Kyligence not only offers many of the same excellent capabilities of SSAS, but it also overcomes the limitations of SSAS by providing customers with the following value:

• Scale-out architecture
• Unlimited cube/index size
• Sub-second queries against even petabytes of SSAS data
• More analysis scenarios
• High ROI, lower TCO
• Retains the current business analytics user experience
• Powerful semantic model capabilities

Interested in learning more? Discover how Kyligence can Replace Microsoft SSAS and Scale OLAP​ on Modern Big Data Architecture.

TEST DRIVE TODAY with $300 worth of free usage