By Use Cases
By BI Tool
Subscribe to our newsletter>
Get the latest products updates, community events and other news.
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.
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.
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
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.
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.
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：
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:
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:
You can read more to learn more details in this blog.
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.
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.
Learn about the fundamentals of a data product and how we help build better data products with real customer success stories.
Come to see the Next Generation of SQL Query Engine
Learn how to achieve alternatives to SSAS.
In this article, we’ll dive into the unified Metrics Platform at Beike, introduce Beike’s practice of building the Metrics Platform infrastructure using Apache Kylin and some real use cases at Beike.
Learn Kyligence Cloud model design principles and how to use Kyligence Cloud to build models.
99 Almaden Boulevard Suite #663
San Jose, CA 95113
+1 (669) 256-3378
Ⓒ 2022 Kyligence, Inc. All rights reserved.
Already have an account? Click here to login