SQL Server Analysis service (SSAS) is the tool offered by Microsoft, which allows you to create a database which acts as an OLAP database. SSAS offers analysis service using various dimensions.
SSAS is a multi-dimensional OLAP server as well as an analytics engine that allows you to slice and dice large volumes of data.
In this beginner's tutorial you will learn:
- What is SSAS?
- Architecture of SSAS
- History of SSAS
- Important SAAS Terminology
- Type of Models in SSAS
- Tabular vs. Multidimensional Model
- Key Features of SSAS
- SSAS vs. PowerPivot
- Advantages of SSAS
- Disadvantages of using SSAS
- Best practices of using SSAS
The architectural view of SSAS is based on a three-tier architecture, which consists of
- RDBMS: The data from different sources like Excel, Database, Text, others can be pulled with the help of ETL tool in to the RDBMS.
- 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.
- Client: Clients can access data using Dashboards, Scorecards, Portals etc.
- 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
- Data Source
- Data Source View
- Dimension Table
- Fact Table
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
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.
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.
- 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 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.
Each type of summary which can be retrieved from the single dimension is called label.
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.
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.
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.
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 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.
|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|
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
|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|
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.
- 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
- 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