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
|
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.
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
Thanks and keep posting the analysis content.
Regards,
Suresh
Thanks and keep posting the analysis content.
Regards,
Suresh