What is SQL Server Analysis Services (SSAS)?

SSAS is Microsoft SQL Server’s Analysis Services which is an online analytical processing (OLAP), data mining, and reporting tool used in Business Intelligence to make data work for end-users. Microsoft has included a number of services in SQL Server related to business intelligence and data warehousing. These services include Integration Services, Reporting Services, and Analysis Services. Analysis Services includes a group of OLAP and data mining capabilities and come in two flavors - Multidimensional and Tabular.

In the family of Microsoft SQL Server, SQL Server Analysis Services (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.

Microsoft Analysis Services takes a neutral position in the MOLAP vs. ROLAP arguments among OLAP products. It allows all the flavors of MOLAP, ROLAP, and HOLAP to be used within the same model.

Many large enterprises are long-time and committed 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.

SQL Server Analysis Services (SSAS) Defined

Offering multidimensional analysis with powerful data mining capabilities, SSAS involves the efficient configuration of schemas in Business Intelligence Development. Key features like the cube will be introduced in the following section.

What Are the Main Features of SSAS?

In order to understand SSAS in a better way, its key features should be highlighted first. Let us see the advantages of using SSAS Cubes over a regular data warehouse for reporting.

Ad hoc report

• Speed: Aggregating (Summarizing) the data for performance with processing Cube.
• Multidimensional analysis – slice, dice, drill-down: This very much depends on the tool or front end that is layered over the data, but the idea is that you can very quickly navigate around the data, finding trends, spotting patterns, ‘drilling down’, ‘slicing and dicing’ – all key to the concept of cubes. Allowing the user to intuitively ‘wander’ around the data, not even realizing that they performing analysis
• Built-in advanced time-calculations – i.e. 12-month rolling average: It’s very easy to implement advanced time calculations like 12-month rolling average, year-to-date, and references to parallel periods in previous years. This is typically the stuff decision-makers in the organization want to have. Imagine how many T-SQL queries are required for calculating rolling averages for each of the previous 12 months (2009-May..2010 April) => 12. Using the cube as a data source => Only 1
• Built-in support for KPI’s

Shared metadata

• Can store Hierarchies
• Can usually handle more concurrent users than the data warehouse can handle
• No need to join the fact and dimension tables, as this will be done in the cube
• Easily use Excel to view data via Pivot Tables

Security features

• Security: You can use the security setting to give end-users access to only those parts (slices) of the cube relevant to them

SSAS Data Models: Multidimensional vs. Tabular

SQL Server Analysis Services (SSAS) provides several approaches, or modes, for creating business intelligence semantic models: Tabular and Multidimensional.

Because multidimensional models are only supported in SQL Server Analysis Services, this article is not meant to be a comparison of Analysis Services platforms (SQL Server, Azure, Power BI). It is meant to provide a high-level comparison of multidimensional and tabular model constructs entirely in the context of SQL Server Analysis Services.

In SQL Server Analysis Services, having more than one approach enables a modeling experience tailored to different business and user requirements. Multidimensional is a mature technology built on open standards, embraced by numerous vendors of BI software, but can be challenging to implement. Tabular offers a relational modeling approach that many developers find more intuitive. In the long run, tabular models are easier to develop and easier to manage. While multidimensional models are still prevalent in many BI solutions, tabular models are now more widely accepted as the standard enterprise-grade BI semantic modeling solution on Microsoft platforms.

All models are deployed as databases that run on an Analysis Services instance, or with tabular models, deployed as a dataset to a Power BI Premium capacity. Models are accessed by client applications or services like Power BI. Model data is visualized in interactive and static reports via Excel, Reporting Services, Power BI, and BI tools from other vendors. Each solution yields high-performance analytical databases that integrate easily with clients' applications and data visualizations services. Yet each solution differs in how they are created, used, and deployed.

How Does SSAS Work? Breaking Down the OLAP Workflow

The Data source in SQL Server Analysis Services (DS) is a connection to the database or data warehouse from which we import (load) required data. Every Analysis services project must have at least one data source to work.

Analysis Services uses a cartridge mechanism that allows it to use the appropriate SQL language and extensions to talk to different relational database systems.

Analysis Services 2008 officially supports specific relational data sources. The major relational datasources for Analysis Services databases include Microsoft SQL Server, IBM 's DB2, Teradata, Oracle,Sybase, and Informix.

During cube processing SSAS will pre-calculate and physically store aggregations of facts (Amounts, Quantities, Money). These aggregations, for example Turnover by Year and Region, are used when a business user queries the cube for this type of information. Therefore the query response time can be very short.

When the query is fired SSAS does not have to calculate the outcome from the underlying details (like T-SQL has to do), but can take the values directly from the stored aggregations. Besides that SSAS stores query-results in a cache. So the next time the same type of query is fired, it will try to get it from the cache. Speed is especially important for a dashboard that an executive is using to slice-and-dice as any mouse click that takes more than a few milliseconds to return data will draw complaints. Data in OLAP cubes can be queried and analyzed by MDX.

What is MDX?

MDX (MultiDimensional eXpressions) is a query language used to retrieve data from multidimensional databases. More specifically, MDX is used for querying multidimensional data from Analysis Services and supports two distinct modes.

MDX is not a proprietary language; it is a standards-based query language used to retrieve data from SSAS databases. MDX is part of the OLE DB for OLAP specification sponsored by Microsoft. Many other OLAP providers support MDX, including Microstrategy’s Intelligence Server, Hyperion’s Essbase Server, and SAS’s Enterprise BI Server.

Querying a Cube via MDX will have good performance due to the pre-calculation of the Cube. Since the design of the Cube consumes cache space, it is especially important to manage the life cycle of the Cube wisely. The following image details the data cycle of a Cube in SSAS.

Basically, Cube's data goes through these five life cycles in SSAS, they are：

• Read data from a dimensional model
• Configure a schema in BI development studio
• Create dimensions, measures, and cubes from the schema
• Fine-tune cute as per needs
• Implement the cube

Key Benefits: Why SSAS Has Endured For So Long

There are many traditional OLAP engines on the market. Oracle, SAP, IBM, and Microsoft all have their own OLAP engines. You can read more to learn more details in this blog. In summary, SSAS, despite its age and limitations, still carries a few benefits:

• Helps avoid resource contention with the source system
• It is an ideal tool for numeric analysis.
• SSAS enables the discovery of data patterns that may not be immediately apparent using the data mining features built into the product.
• It offers a unified and integrated view of all your business data Reporting, analysis of Key Performance Indicator (KPI) scorecards Data mining.
• SSAS offers online analytic processing (OLAP) of data from different data sources.
• Support for MDX language.
• It allows users to analyze data with a host of tools, including SSRS, Excel, and major BI tools.

SSAS Disadvantages: How SSAS Falls Short of Modern Analytics Solutions

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 as:

• Difficulty handling large data volumes
• Poor scalability for time to compute
• Poor scalability on a hardware level
• Single server architecture
• Migration Cost & complexity
• Poor support for concurrency

You can read more to learn more details in this blog.

Industries that Rely on SSAS

SSAS is widely used in different industries like FMCG, finance, insurance, technology, and etc. Every company has its own needs and those needs will vary widely. Organizations regularly track dozens of key performance indicators. Being able to use data to make decisions is becoming increasingly critical. Modern BI practices are rising to meet the objective of enabling faster decisions by ingesting, processing, analyzing, and visualizing data at an ever-greater scale and velocity, underpinned by strong data governance and data quality processes.

“Data is now produced so fast and in such large volumes that it is impossible to analyze and use effectively when using traditional, manual methods such as spreadsheets, which are prone to human error,” says Darren Turner, head of BI at Air IT. “The advantage of BI is that it automatically analyzes data from various sources, all accurately presented in one easy-to-digest dashboard.”

The data analysis needs of various industries indicate that BI is still in strong demand, and DW as a BI platform still has a high market. Data is considered the oil of the 21 century and BI acts as a lamp, providing value from it.

Conclusion

As a veteran of OLAP tools, SSAS established the OLAP system methodology. However, with the development of the cloud computing era, some of the core functions are no longer able to meet the growing data needs of enterprises. More and more new tools and technologies have also challenged its position.

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 technology forward to a more modern, big data infrastructure.

Kyligence introduced more cost-effective solutions and almost complete functions to help users upgrade SSAS to the cloud computing era. Business users can continue to use their favorite BI tools or Excel for data analysis without feeling the changes in the underlying platform. At the same time, the existing Kyligence's ability to apply machine learning can automatically recommend an index that meets the query habits of business users for optimization, helping users to draw analysis conclusions more quickly. Please refer to this link for more detail.