By: Siddharth Mehta
Overview
SQL Server Analysis Services (SSAS) is the technology from the Microsoft Business Intelligence stack, to develop Online Analytical Processing (OLAP) solutions. SSAS can be configured in three server modes - Multidimensional and Data Mining, Tabular and PowerPivot. Multidimensional and Tabular server mode are used generally for enterprise scale implementations as well as simple to medium complexity analytical data models. In this chapter we will learn some basic definitions about Multidimensional and Tabular server modes.
Explanation
The following is a list of common terms when working with SQL Server Analysis Services in Multidimensional as well as Tabular modes. We will dive into more details of SSAS for which these definitions will provide the foundation.
Multidimensional Data Model
OLAP - Online Analytical Processing is a term used to represent analytical data sources and analysis systems. The fundamental perception and expectation associated with the term OLAP is that it contains the multi-dimensional data and the environment hosting the data.
Star Schema - Star schema is an OLAP schema, where all dimension tables are directly associated with fact tables, and no normalized dimension tables are considered in the schema. For example, Time, Product, Geography dimension tables would be directly associated with a fact table like Sales. This is a very common example of a star schema.
Snowflake Schema - Snowflake schema is an OLAP schema, where one or more normalized dimension tables are associated with a fact table. For example, Product Sub Category > Product Category > Product can be three normalized dimension tables and the Product table can be associated with a fact table like Sales. This is a very common example of a snowflake schema.
Data Source View - It's an insulation layer that inherits the basic schema from the data source with the flexibility to manipulate the schema in this layer without modifying the actual schema in the data source.
Dimension - A dimension is an OLAP structure that is basically used to contain attributes related to an entity to categorize data on the row / column axis. A dimension almost never contains measurable numeric data, if at all it, it is used as an attribute. Typical examples of dimensions are Geography, Organization, Employee, Time, etc.
Fact - A fact is known as measures in a cube and is an OLAP structure that is basically used to contain measureable numeric data, for one or more entities. In cube parlance these entities are known as Dimensions. A dimension need not be necessarily associated directly with a fact, but a fact is always associated directly with at least one dimension. A typical example of facts are Sales, Performance, Tax, etc.
Hierarchy - A hierarchy is a collection of nested attributes associated in a parent-child fashion with a defined cardinality. A dimension is formed of attributes, and a hierarchy contained in a dimension is formed of one or more attributes from the same dimension.
KPI - Key Performance Indicators are logical structures defined using MDX expressions. Each KPI has a goal, status, value, trend, and indicator associated with it. The value is derived based on the definition of the KPI, all the rest of these values vary based on this derived value. KPIs are the primary elements that makes up a scorecard in a dashboard.
Cube - A cube is a multidimensional data structure composed of dimensions and measure groups. The intersection of dimension and measure groups contained in a cube returns the dataset.
Calculated Measure - Each field in a measure group is known as a base measure. Measures created using MDX expressions with/without base measures are known as calculated measures.
MDX - Multi Dimensional Expressions are considered the query language of multidimensional data structures. This can be considered as the SQL of OLAP databases, with the major difference that MDX is mostly used for reading data.
Tabular Data Model
Table - A table is a set of columns that can be imported from a data source or can be added manually as calculated columns.
Table metadata - All the database objects like relationships, measures, perspectives, etc. are all metadata objects within the context of a table.
Data - In a tabular model, data is populated either by importing data or by creating data manually in calculated fields called calculated columns.
Relationships - In a tabular model, a relationship is a connection between two tables which correlates data between two tables.
DAX - Data Analysis Expressions is an expression language that can be used to query tabular data models as well as define calculations in the data model.
Calculations - In a tabular model, DAX formulas include functions, operators, and other constructs to create advance calculations.
Measures - In a tabular model, a measure is a calculation created using a DAX formula.
Hierarchies - In a tabular model, hierarchies define relationships between two or more columns in a table. Each level in a hierarchy is a representation of a column in a table.
Calculated columns - A calculated column is a column that is added to an existing table using a DAX formula for the values stored in that column.
Now that we have some basic idea of SSAS, let's install SQL Server Analysis Services.
