SSAS Tutorial: What is SSAS Cube, Architecture & Types

What is SSAS?

SQL Server Analysis Services (SSAS) is a multi-dimensional 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 2 variants Multidimensional and Tabular. The SSAS full form is SQL Server Analysis Services.

Architecture of SSAS

First in this SSAS tutorial, we will learn about the SSAS architecture:

Architecture of SSAS

The architectural view of SQL server analysis services is based on a three-tier architecture, which consists of

  1. RDBMS: The data from different sources like Excel, Database, Text, others can be pulled with the help of ETL tool into the RDBMS.
  2. SSAS: Aggregate data from RDBMS is pushed into SSAS cubes by using analysis services projects. The SSAS cubes will create an analysis database, and once the analysis database is ready, it can be used for many purposes.
  3. Client: Clients can access data using Dashboards, Scorecards, Portals etc.

History of SSAS

Now in this SSAS tutorial, we will go through the history of SSAS:

  • MSOLAP feature first included in SQL Server 7.0. This technology was later purchased from an Israeli company called Panorama.
  • Soon it becomes most used OLAP engines because it was included as a part of SQL Server.
  • SSAS was renovated entirely with the release of MS-SQL Server 2005
  • This latest version also offers a feature for “subcubes” with the Scope statement. This has increased the functionality of SSAS cubes.
  • SSAS 2008R2 and 2012 versions are mainly concerned with query performance and scalability
  • In Microsoft excel 2010 came an add-in called PowerPivot which uses a local instance of Analysis service with new XVelocity engine which increases the query performance

Important SSAS Terminology

Now in this SSAS tabular model tutorial, we will learn some important terminologies of SSAS:

  • Data Source
  • Data Source View
  • Cube
  • Dimension Table
  • Dimension
  • Level
  • Fact Table
  • Measure
  • Schema

Data Source

Data Source is a kind of connection string. It establishes a connection between the analysis database and RDBMS.

Data Source View

Data source view is a logical model of Database

Cube

A cube is a basic unit of storage. It is a collection of data which has been aggregated to allow queries to return data quickly.

MOLAP

The MOLAP is made of data cube which contains of measures and dimensions. It includes all the members who may be in a hierarchical relationship.

It is specific set of rule which helps you to determine how certain cells are computed in a sparse cube and measures values rolled up inside that hierarchies.

Dimension Table

  • A dimension table contains dimensions of a fact.
  • They are joined to fact table using a foreign key.
  • Dimension tables are de-normalized tables.
  • Dimensions offer characteristics of the facts with the help of their attributes.
  • Not offers limit set for a given number of dimensions
  • The dimension contains one or more hierarchical relationships.

Dimension

Dimension offers the context surrounding a business process event. In simple terms, they give who, what, 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, you can say that a dimension is a window to view the information in the facts.

Level

Each type of summary which can be retrieved from the single dimension is called label.

Fact Table

A fact table is the most important table in a dimensional model. A Fact Table contains Measurements/fact and Foreign key to the dimension table. For example, payroll operations.

Measure

Each fact table contains one or more table which should be analyzed. For example, a book sells information table. It can be a profit or loss for the number of book’s sold.

Schema

The database schema of a database system and its structure described in a formal language. It supports the database management system. The term “schema” means to the organization of data as a blueprint of a manner of which database is constructed.

Type of Models in SSAS

Now, we will learn types of models in SSAS in this SSAS cube tutorial:

Multi-Dimensional Data Model

The Multi-Dimensional Data Model, which consists of a data cube. It is a group of operations which allows you to query the value of cells by using cube and dimension members as coordinates.

It defines rules which decide the way that 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 table doesn’t designate as “dimensions” or “facts” and development time is less with tabular because of all related tables able to 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 It is best when data is put into a star schema.
Type of Model Relational model Dimensional model
DAX MDX
Complexity Simple Complex
Size Smaller Larger

Key Features of SSAS

Essential features of SSAS are :

  • It offers backward compatibility on the API level.
  • You can use OLEDB for OLAP for the client access API and MDX as a query language.
  • SSAS helps you to build MOLAP, HOLAP, and ROLAP architectures
  • It allows you to work on client-server mode or offline mode.
  • You can use SSAS tool with different wizards and designers.
  • Data model creation and management is flexible.
  • Customize application from extensive support
  • Offers Dynamic structure, Ad hoc Report, Shared metadata, and Security features

SSAS vs. PowerPivot

Parameter SSAS PowerPivot
What is SSAS Multidimensional is “Corporate BI” Microsoft PowerPivot is a “Self- Service BI
Deployment Deploy to SSAS It is deployed to SharePoint
Use for Visual Studio Project Excel
Size 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 & dynamic security Workbook file security

Advantages of SSAS

Pros/benefits of SSAS are:

  • Helps you to 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.
  • 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 proves to be risky if requirements change mid-way through the project

Best practices of using SSAS

  • Optimize Cube And Measure Group Design
  • You should define useful Aggregations
  • Use Partitions method
  • Write Efficient MDX
  • Use The Query Engine Cache Efficiently
  • Scale Out When You Can No-Longer Scale Up