Opportunities for SSAS in the Cloud

Author
Joanna He
Director of Product Management, Kyligence
Jun. 29, 2021

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. 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.


Why SSAS has 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.


What the cloud could bring to SSAS in the big data era

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.

Increased data volumes and dimensions

The SSAS multi-dimensional 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. Doesn’t this sound like an ideal application for the elastic scalability of the cloud?

Unlimited scalability

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. But Kyligence offers a way forward for SSAS cubes. The distributed architecture of Apache Kylin - the heart of the Kyligence OLAP engine - means that our aggregate indexes (cubes) are virtually unlimited in size and scale. While there is some engineering work involved in migrating SSAS cubes to Kyligence, the payoff can be enormous. And the BI users that have been querying SSAS systems do not have to change tools or behavior. They will just see a boost in performance and greater stability.

Increased 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.


Things to consider when moving your SSAS data to the cloud

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 an 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 Kyligence can move your SSAS investment forward

Figure 1. Migration scenarios for enterprise SSAS deployments
Figure 1. Migration scenarios for enterprise SSAS deployments

Powerful analytics capabilities are the main reason why SSAS is widely adopted by users. Kyligence offers a variety of features that should be familiar to those who have relied on the analysis capabilities of SSAS:

  • Complete semantic model: Semantic modeling is an important part of business analysis, including business analysis ideas, dimensions, measures, hierarchies, and dimension-usage, which are the most common business concepts. Kyligence supports complete semantic model definitions such as calculated measures and hierarchies.
  • Multiple query language support: Kyligence provides a variety of interfaces including SQL, MDX, and REST APIs. It can be integrated with a variety of BI tools such as Excel, Power BI, Tableau, and MicroStrategy.
  • Excel integration: Kyligence uses MDX to interface with Excel, providing an experience similar to using Excel with SSAS. Users can conduct drag-and-drop analysis using Excel's PivotTable Direct Query.
  • Isolate user experience from IT internal processes: Business users can completely retain the current analytics experience using Excel or other commercial BI tools. At the same time, IT can modernize the underlying analytics infrastructure and architecture without disrupting the business user experience.
  • Powerful backend engine: Kyligence uses the computing power of Apache Spark to pre-calculate data, and our AI-augmented intelligent engine can identify usage patterns to automatically make calculations in the background. Kyligence uses Spark's distributed architecture for data processing and commodity cluster resources for storage. These aggregations are automatically created and stored in big data clusters. Using machine learning, Kyligence can automatically discover and identify the most important data from SQL history, analyst behavior, data sampling, and runtime indicators.
Figure 2. AI-Augmented Modeling and Indexing
Figure 2. AI-Augmented Modeling and Indexing


Success stories from top enterprises


Fortune 500 restaurant chain big data analysis platform

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 SSAS. Here is a real-world example of a Kyligence customer who is a Fortune 500 restaurant chain.

Situation

  • Customer used Greenplum (MPP database) + SQL Server Analysis Service (SSAS) + Excel / Tableau to perform restaurant operational analysis.
  • Since SSAS is not designed for scale-out, it has limited the analysis performance and breadth of business scenarios when analyzing large datasets.
  • With expansion of restaurants and the wide variety of product offerings, the customer has to deal with large data volumes for analytics.
  • Customer wishes to establish a unified big data analysis platform to improve efficiency and support future growth.

Challenges

  • Restaurant business requires complicated data models, advanced measure calculations, and flexible calculations in data analytics to support its 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.
  • Business users would like to keep using Excel as their front-end tool.

Solution

  • Customer uses Kyligence on-premises to provide high performance and a highly concurrent query service.
  • Kyligence provides the Unified Semantic Layer that can integrate seamlessly with Tableau and Excel.

Why Kyligence

  • Kyligence enables the customer to monitor restaurant operations quickly and accurately and to make data-informed decisions.
  • By implementing Kyligence on a big data architecture, the company realized a 100x performance improvement, without disrupting the original analysis experience, and while having granular control of permissions.
  • Customer chose Kyligence to 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.


Financial risk management for top financial services company - replacing the world's largest SSAS cube

Situation

A household name financial services company has one of the largest SQL Server Analysis (SSAS) OLAP cubes in the world. They are using SSAS to support their risk management analysis.

Challenge

  • The current solution is running into scaling limits due to increased data volumes.
  • Users prefer to maintain their analysis using Excel and MDX queries.
  • Analysts' work is locked 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.

Solution

  • Customer deploys Kyligence Cloud on Azure to provide highly scalable big data analysis.
  • Customer uses Kyligence to provide unified semantics and MDX calculation capability.

Benefits

  • Deploying a single Kyligence 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 - a predicted 40% data volume growth, migration to cloud.

Why Kyligence?

Figure 3. Kyligence Architecture
Figure 3. Kyligence Architecture

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:

  • 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

Kyligence not only offers many of the same excellent capabilities of SSAS, but it also overcomes the limitations of SSAS, making it an excellent choice for traditional architecture migration to a big data architecture.


TEST DRIVE with $500 worth of free usage

 
test drive thumbnail