SSAS Tutorial: What is SSAS Cube, Architecture & Types
Fiona Brown
⚡ Smart Summary
SSAS, SQL Server Analysis Services, is Microsoft’s OLAP server and analytics engine for slicing large volumes of data across dimensions. It ships in two variants, multidimensional and tabular, and consumes aggregated data from an RDBMS to build cubes and models for fast analysis.
What is SSAS?
SQL Server Analysis Services (SSAS) is a multidimensional OLAP server as well as an analytics engine that allows you to slice and dice large volumes of data. It is part of Microsoft SQL Server and helps perform analysis using various dimensions. It has two variants, multidimensional and tabular. The SSAS full form is SQL Server Analysis Services.
Architecture of SSAS
First, we will learn about the SSAS architecture.
The architectural view of SQL Server Analysis Services is based on a three-tier architecture, which consists of:
- RDBMS: data from different sources like Excel, database, and text can be pulled with the help of an ETL tool into the RDBMS.
- SSAS: aggregated data from the RDBMS is pushed into SSAS cubes using analysis services projects. The SSAS cubes create an analysis database, which can then be used for many purposes.
- Client: clients can access data using dashboards, scorecards, portals, and so on.
History of SSAS
Now we will go through the history of SSAS:
- The MSOLAP feature was first included in SQL Server 7.0. This technology was later purchased from an Israeli company called Panorama.
- It soon became one of the most used OLAP engines because it was included as part of SQL Server.
- SSAS was renovated entirely with the release of MS SQL Server 2005.
- That version also offered a feature for “subcubes” with the Scope statement, which increased the functionality of SSAS cubes.
- The SSAS 2008 R2 and 2012 versions were mainly concerned with query performance and scalability.
- In Microsoft Excel 2010, an add-in called PowerPivot arrived, which uses a local instance of Analysis Services with the new xVelocity engine to increase query performance.
Important SSAS Terminology
Now we will learn some important terminologies of SSAS:
- Data Source
- Data Source View
- Cube
- Dimension Table
- Dimension
- Level
- Fact Table
- Measure
- Schema
Data Source
A data source is a kind of connection string. It establishes a connection between the analysis database and the RDBMS.
Data Source View
A data source view is a logical model of the database.
Cube
A cube is a basic unit of storage. It is a collection of data that has been aggregated to allow queries to return data quickly.
MOLAP
MOLAP is made of a data cube that contains measures and dimensions, including all the members that may be in a hierarchical relationship. It is a specific set of rules that helps determine how certain cells are computed in a sparse cube and how measure values are rolled up inside those hierarchies.
Dimension Table
- A dimension table contains the dimensions of a fact.
- They are joined to the fact table using a foreign key.
- Dimension tables are de-normalized tables.
- Dimensions offer characteristics of the facts through their attributes.
- There is no set limit for a given number of dimensions.
- A dimension contains one or more hierarchical relationships.
Dimension
A dimension offers the context surrounding a business process event. In simple terms, it gives the who, what, and where of a fact. In the sales business process, for the fact “sales number”, dimensions would be:
- Who – customer names.
- Where – location.
- What – product name.
In other words, a dimension is a window to view the information in the facts.
Level
Each type of summary that can be retrieved from a single dimension is called a level.
Fact Table
A fact table is the most important table in a dimensional model. It contains measurements or facts and a foreign key to the dimension table, for example payroll operations.
Measure
Each fact table contains one or more measures that should be analyzed. For example, a book sales information table can measure the profit or loss for the number of books sold.
Schema
The database schema of a database system is its structure described in a formal language, supported by the database management system. The term “schema” refers to the organization of data as a blueprint of how the database is constructed.
Types of Models in SSAS
Now we will learn the types of models in SSAS.
Multi-Dimensional Data Model
The multi-dimensional data model consists of a data cube. It is a group of operations that allows you to query the value of cells by using cube and dimension members as coordinates. It defines rules that decide the way measure values are rolled up within hierarchies or how specific values are computed in a sparse cube.
Tabular Modeling
Tabular modeling organizes data into related tables. The tables are not designated as “dimensions” or “facts”, and development time is shorter with tabular because all related tables can serve both roles.
Tabular vs Multidimensional Model
| Parameters | Tabular | Multidimensional |
|---|---|---|
| Memory | In-memory cache | File-based storage |
| Structure | Loose structure | Rigid structure |
| Best feature | Data does not need to move from source | Best when data is put into a star schema |
| Type of model | Relational model | Dimensional model |
| Query language | DAX | MDX |
| Complexity | Simple | Complex |
| Size | Smaller | Larger |
Key Features of SSAS
The essential features of SSAS are:
- It offers backward compatibility at the API level.
- You can use OLE DB for OLAP for the client access API and MDX as a query language.
- SSAS helps you build MOLAP, HOLAP, and ROLAP architectures.
- It allows you to work in client-server mode or offline mode.
- You can use the SSAS tool with different wizards and designers.
- Data model creation and management is flexible.
- You can customize applications with extensive support.
- It offers a dynamic structure, ad-hoc reports, shared metadata, and security features.
SSAS vs PowerPivot
| Parameter | SSAS | PowerPivot |
|---|---|---|
| What it is | SSAS Multidimensional is “Corporate BI” | Microsoft PowerPivot is “Self-Service BI” |
| Deployment | Deployed to SSAS | Deployed to SharePoint |
| Used for | Visual Studio project | Excel |
| Size | Limited to memory | Capacity limited to 2 GB |
| Partition support | Supports partitioning | No partitions |
| Query type | DirectQuery and Vertipaq | Allows only Vertipaq queries |
| Admin tools | Server admin tools (e.g., SSMS) | Excel and SharePoint admin |
| Security | Row-level and dynamic security | Workbook file security |
Advantages of SSAS
The benefits of SSAS are:
- Helps you 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 for reporting, KPI scorecard analysis, and data mining.
- SSAS offers online analytic processing (OLAP) of data from different data sources.
- It allows users to analyze data with a host of tools, including SSRS and Excel.
Disadvantages of Using SSAS
- Once you select a path (tabular or multidimensional) you cannot migrate to the other version without starting over.
- You are not allowed to merge data between tabular and multidimensional cubes.
- Tabular can be risky if requirements change midway through the project.
Best Practices for Using SSAS
- Optimize cube and measure group design.
- Define useful aggregations.
- Use the partitions method.
- Write efficient MDX.
- Use the query engine cache efficiently.
- Scale out when you can no longer scale up.
