By: Siddharth Mehta
Following is a list of common terms when working with SQL Server Analysis Services.
Cube - Cube is a multi dimensional 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.
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 - 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, and if at all it contains, it is used as an attribute. Typical example of dimensions are Geography, Organization, Employee, Time etc.
Fact - Fact known as a Measure Group in a cube, 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. Typical example of facts are Sales, Performance, Tax etc.
Hierarchy - Hierarchy is collection of nested attributes associated in a parent-child fashion with a defined cardinality. Dimension is formed of attributes, and 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. Value is derived based on the definition of 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.
MDX - Multi Dimensional Expressions is considered as the query language of multi dimensional data structures. This can be considered as the SQL of OLAP databases, with the major difference that MDX is mostly used for reading data only.
Named Set - Named Set is a pre-defined MDX query defined in the script of the cube. It can be thought of synonymous to Views in a SQL Server database. Named sets can be dynamic or static and this nature defines the time when this query gets evaluated.
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 would contain multi dimensional data and the environment hosting the same.
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 Product table can be associated with a fact table like Sales. This is a very common example of a snowflake schema.
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 star schema.
