Learn more about SQL Server tools

mssqltips logo

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories


SQL Server Analysis Services Glossary


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.

Last Update: 5/5/2011

More SQL Server Solutions

Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 

Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.


Learn more about SQL Server tools