Alternatives to SSAS

Alternatives to SQL Server Analysis Services (SSAS)

Microsoft's SSAS is one of the most widely used OLAP engines globally. SSAS has many excellent features that have made it ideal for many traditional business intelligence solutions. But it has faced increasing challenges in the era of big data, especially with BI applications. This article will introduce several alternative solutions to SSAS and their technical criteria that you may consider in the cloud-first era.

What is SSAS

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 excellent 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 longtime users of SSAS. But it has faced increasing challenges in the era of big data. These challenges have become particularly acute with the explosive growth of data volumes.

Common SSAS problems

The longtime success of SSAS has resulted in creating a tremendous amount of value in terms of the creation and organization of structured data (cubes). But SSAS has experienced a bit of its 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 massive datasets with the new era of cloud platforms and big data tools.

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, resulting 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 single-server architecture. To improve performance, you have no choice but to scale up with larger, high-end, multi-core servers with more RAM, and faster, larger disks. But there is no escaping the architectural limits of a scale-up strategy for SSAS. Unfortunately, there is no notion of an 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.

Alternatives to SSAS

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. In addition, such architecture should enable the following:

• Scale-out architecture: One of big data technology capabilities is scaling out 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 dramatically improves the system's scalability 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 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.
• Cloud-native design: 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 significantly reduced and optimized. This is particularly helpful on pay-as-you-go cloud platforms. Users can expand clusters when they need more resources, shrink clusters when idle, and free up redundant resources to maximize resource usage and save cost.

There are many alternative solutions to SSAS that you may consider in the big data era: Distributed OLAP Limitations of traditional OLAP led many to look to new, big data approaches to analytics that took advantage of distributed computing (e.g., compute clusters). Among these was eBay, which decided to tackle the problem of implementing a distributed OLAP technology that would feature the many benefits of traditional OLAP, like fast performance and support for multidimensional queries. The resulting technology eventually became the Apache Kylin project, the first and only open-source distributed OLAP platform. Kyligence was formed in order to create an enterprise-grade commercial product based on Apache Kylin. Data Virtualization Because OLAP is usually found in the data services layer of a data pipeline, it is helpful to understand other alternative technologies that live in this layer. For example, one of this layer's common analytics technologies is Data Virtualization (DV). Both distributed OLAP and DV are trying to address the same challenge: reducing time to value by making analytics easily accessible to a broader audience in a cloud or data lake environment. Cloud OLAP platforms like Kyligence use precomputation to improve performance, response times, and concurrency in the production environment. In addition, precomputation strategies tend to reduce pressure on the source systems. They are deployed when data quality and data governance are factors, and security needs to be de-coupled from the sources. Data Virtualization technology focuses on rapid deployment and eliminating some of the IT processes associated with loading data into a data warehouse or otherwise structuring the data. Instead, users can simply connect to various data sources such as files, RDBMS, and NoSQL DBs and build a 'virtual' view which is exposed to the front-end BI layer. There is no data warehouse to be designed, no ETL jobs to be scheduled, all queries happen on demand the moment a user clicks in the BI tools (and the SQL query is fired). Data virtualization solutions can struggle when too many concurrent queries are executed due to either growing user bases or growing data volumes. For analytical workloads, the cloud OLAP has a serious advantage in this area. Massively Parallel Processing Cloud warehouses like Snowflake and Azure Synapse use massively parallel processing (MPP) architectures to parallelize query processing. Snowflake has succeeded in disruption by delivering used-to-be-on-premise data warehouses on the cloud. The promise of MPP cloud data warehouses like Snowflake:

• Data analysts can load up lots of data and get to work
• Historical data will be available without limit
• You can use the BI tool of your choice
• Your analytics will not impact production because it is now totally isolated

But there are some significant challenges with the MPP approach to analytics workloads. Parallelizing SQL execution plans in MPP systems First, customers running MPP cloud warehouses for big data analytics eventually run into performance issues that have plagued their traditional on-premise counterparts. This includes scale limitations when their data surpasses a specific size and restrictions on the number of concurrent users hitting the data. These limitations arise from performance issues typically associated with distributed MPP architecture: data skewness, network latency, and complexity. While the MPP approach to data warehousing can work to a particular scale in data volumes and concurrent users, as cluster resource consumption grows, performance suffers, and costs can rise to unsupportable levels.

How the intelligent OLAP platform helps

• Define Intelligent Data Cloud
• Describe features of the IDC that differentiate it from competing tech
• Discuss the benefits of transitioning to IDC

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's intelligent OLAP solutions can help customers bring all of the value that they created with SSAS forward into the future:

• 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. Businesses can easily consume the same data model from any Business Intelligence product of their choices.
• Excel integration: Kyligence uses MDX to interface with Excel, providing an experience similar to using Excel with SSAS.
• Retain user's habits: Business users can ultimately retain the current analytics experience using Excel or other commercial BI tools.
• Powerful backend engine: Kyligence uses the distributed 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
• Higher ROI, lower TCO
• Retains the current business analytics user experience
• Powerful semantic model capabilities

Next steps

Download our Upgrading SSAS to Big Data White Paper