Build the Common Data Language with the Metrics Platform Start Now
Kyligence Zen Kyligence Zen
Kyligence Enterprise Kyligence Enterprise
Cloud
Services
By Use Cases
By BI Tools
Customers
Resources
Apache Kylin
About
Partners
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
Shared metadata
Security features
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.
Learn about the importance of the Metrics Layer and its impact on data analysis and decision-making. Enables businesses to measure, track, and interpret KPI effectively.
Learn about metrics store and how it can help enterprises achieve metrics reusability, consistency, self-service definition, and scalability.
Everything you should know about Metrics Store and how to extend DataOps practices to managing your business metrics. Read Now.
Read on to learn the key competencies and critical features to look for when evaluating a semantic layer offering for your BI tool.
Kyligence Zen intelligently manages data in the retail industry. Read to learn how to develop the "North Star Metric" system to track goals and progress.
Already have an account? Click here to login
预约演示,您将获得
完整的产品体验
从数据导入、建模到分析的全流程操作演示。
行业专家解惑
与资深行业专家的交流机会,解答您的个性化问题。
您还可以在云平台中 部署 Kyligence
直接获得 30 天免费试用
请填写真实信息,我们会在 1-2 个工作日内电话与您联系。