Analysis Services Cube Concepts – Made Simple

This post defines the basic terminology of Analysis Services Cube from an Information Consumer/Analyst perspective.

Analysis Services Analysis Services (AS) is the engine that hosts and manages cubes
Analysis Services Database Analysis Services Database (ASD) is the repository or collection of cubes. An Analysis Service Database can host 1…n cubes.
Cube Cube is a data repository analogous to database. While databases are primed for transactions, cubes are primed for multi-dimensional analysis.Cube is also called as OLAP cube, an acronym for On-Line Analytical Processing.
Measure Measures are numerical facts and figures. E.g. Net revenue, Sales, Insurance premium.
Measure Groups Measure Group is a collection of related measures.
Dimension Dimension is an element that categorizes the measures. E.g.  Time is a dimension over which Net Revenue is analysed. Dimensions are comprised of

  • Attributes.
  • Hierarchies.
Attribute An attribute is a property or characteristic of the dimension.A dimension usually has several attributes. E.g. Fiscal year, calendar year, month and date are attributes of Time dimension.Some attributes may be related to each other while others may not be related. E.g. Fiscal year and month attributes exhibit a parent-child relationship, while fiscal year and calendar year are not related.
Hierarchy Hierarchy is created from attributes that are related. The hierarchy is a series of parent-child relationships, typically where a parent member represents the consolidation of the members which are its children. Parent members can be further aggregated as the children of another parent.E.g. Hierarchy called Fiscal is composed of Fiscal Year which is a Parent to Month which is a Parent to Date. Fiscal Year→Month→Date. Hierarchies create pre-defined drill-paths and are primed for faster response times.
Level A level is a term associated with Hierarchies. Level represents the position of the parent-child relationship. In the above example Fiscal Year is at Level1, Month is at Level 2 and Date is at Level 3.
Set Set is the merger of a measure and a dimension.
Slice A slice of data is the set of measures versus a single dimension.
Dice Dice is a set of measures versus multiple dimensions. In other words dice is a collection of slices.
Drill-Down & Drill-Up Drill-down or Drill-up is a specific analytical technique whereby the information consumer navigates among levels of data ranging from the most summarized (up) to the most detailed (down)
Roll-up/Aggregation A roll-up/aggregation is a mathematical operation that is applied to a measure when the cube is sliced and diced. Usually a mathematical operation like Sum, Min, and Max defined on the Measures.
Drill-through Drill-through is a technique where the aggregated or rolled-up Measure is broken down into its components at a much detailed level.  For e.g. Analysing the components that make up Net Revenue is a drill-through action.Drill-through is different from Drill Down/Up. While Drill Down/Up can be achieved by using hierarchies, Drill-though often requires supporting reports or queries sourced externally from the cube.
Pivot Pivot is a two-dimensional representation of multi-dimensional data.

 

The illustration given below diagrammatically represents the basic elements of an Analysis Services Cube Model.

Analysis Services Cube Model

Analysis Services Cube Model

Benny Austin

Related Posts

Benefits of Creating SSAS Cube from Database Views
One Database, many Cubes Vs. One Database per Cube
Process Incremental – What it is and isn’t
Reporting Action in Analysis Services
Cell Data Access Vs Dimension Data Access in Analysis Services
Analysis Service (SSAS) Vs. PowerPivot

3 thoughts on “Analysis Services Cube Concepts – Made Simple

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s