SSAS

What is SSAS?

SQL Server Analysis service (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 full form of SSAS is SQL Server Analysis Service.

Architecture of SSAS

The architectural view of SSAS 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 in to the RDBMS.
2. SSAS : Aggregate data from RDBMS is pushed into SSAS cubes by using analysis services projects. The 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

• 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 the 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 SAAS Terminology

• 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

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
•