Definition and basic concepts of SSAS technology, short for SQL Server Analysis Services.
The definition of the basic SSAS concepts allows for a better understanding of the functioning of MS OLAP cubes, i.e. multidimensional databases. Indeed, the Analysis Services module has been at the heart of Microsoft’s Business Intelligence suite for years.
Firstly, SSAS stands for SQL Server Analysis Services. It is the part of the MS BI suite that enables multidimensional analysis through the processing and storage of data in OLAP cubes.
1. Definition of a multidimensional MS SSAS cube
What is the definition of an SSAS cube? It is a multidimensional structure or database composed of groups of measures also called indicators and dimensions. The dimensions in business intelligence are also called analysis axes.
The intersection of the groups of measures and dimensions contained in an SSAS cube returns a dataset. An SSAS cube is an OLAP cube which can be of the MOLAP, ROLAP or HOLAP type. A SSAS cube is essentially used to analyse the data it contains.
2. What is OLAP?
What is OLAP SSAS? OLAP or Online Analytical Processing is a term that encompasses business intelligence databases and all systems used to process and analyse data.
OLAP differs from traditional data processing systems such as OLTP (Online Transactional Processing), which allows the capture and processing of operational data. OLAP systems are also called OLAP engines because of the complexity of the processing.
3. Definition of SSAS dimensions
What is an SSAS dimension? An SSAS dimension is an element of an SSAS cube that allows for the structuring and prioritisation of an analysis axis. It is essential to discuss the definition of dimensions with the basic SSAS concepts.
An SSAS dimension in an OLAP cube contains dimensional attributes and allows data to be ranked, selected and organised across rows and columns. Common analysis axes are the dimensions Time, Geography, Organisation, Accounting panel, etc. These analysis axes are technically defined as dimensions in business intelligence.
4. Analysis Services hierarchies
What is an SSAS hierarchy? It is a list of attributes grouped by parent-child relationships. An SSAS dimension therefore consists of attributes and SSAS hierarchies within a dimension. A hierarchy is structured with members of the same dimension. The following is an example of an SSAS hierarchy for a five-level Time dimension.
5. Definition of a calculated SSAS indicator
What is a calculated SSAS indicator? The definition of a SSAS calculated indicator is a measure created with the MDX language. It is not derived from a base member from an SQL column, but is calculated using an MDX formula.
A member calculated in MDX under SSAS is therefore dynamic and allows so-called on-the-fly calculations to be carried out because they depend on the filters applied to the cube at a given moment. In fact, a dynamic calculated member is recalculated each time the user changes dimension or filter from a given report.
6. The MDX language
What is the MDX language? MDX stands for Multidimensional Expressions. It stands for Multidimensional Expressions. The MDX language used by SSAS is the query language for cubes, i.e. multidimensional databases. It is in a way the SQL for OLAP cubes.
MDX is different because it is used much more for reading data than for manipulating data. MDX is therefore a language that allows you to create dynamic or static measures. This is because the result is either pre-calculated and stored, or calculated on the fly during the call of the MDX formula. Such as aggregating the amounts of all months in a year for example.
7. Conclusion on SSAS cubes
This article therefore provides a definition of the basic concepts of SSAS cubes and a better understanding of their purpose and theoretical operation. To move on to the practical side, here is an article on how to calculate the median of MDX members with a calculated member that uses the MEDIAN function in a formula.
Frequently asked questions about SSAS cubes
A SSAS cube or SQL Server Analysis Services is a multidimensional database composed of groups of measures also called indicators and dimensions to aggregate, analyse and cross-reference the data.
OLAP or Online Analytical Processing is a term for systems to process and analyse business intelligence data. The data in an OLAP cube is pre-calculated to ensure optimal performance.
The MDX language, used by OLAP cubes, is the query language for multidimensional databases. It is therefore the SQL for multidimensional cubes.