SSAS Cube

Cubes in [[articles:SQL Server Analysis Services (SSAS)|SQL Server Analysis Services]] are sets of related measures and [[dimension]]s.

Overview

A cube is used to analyze data. This activity targets the measues of the cube, which are grouped into measure groups. The dimensions are groups of the analytical attribute sets used for filtering, sorting and aggregating the value of the measures.
These attributes sometimes can be organized into hierarchies. The most common hierarchy is based on time periods: in a date dimension, a year-month-day hierarchy can be created to help the navigation of the analyst in the dimension.

In most of the cases, analysis requires extracting information from aggregated data. This means the data consumers are interested in having results in a summarized form, instead of the result series of the events. For example, a group of the analysts might be interested in a given measure value summarized only on the month level of the mentioned year-month-day hierarchy, but another one group might be interested in navigating to the daily level. This is where pre-aggregations help the servers keeping the query response times down in case when aggregated data needed. OLAP solutions let their users getting the requested information fast, even if they quickly change the aspect of the analysis regarding the same subject.

Development

Usually, a cube is developed, based on relational tables and views. These are logically connected, the "central" table or view represents the fact table, it will be the data source of a measure group. Multiple measures can be sourced from the same table. This fact table references the rest, which are the source tables of the dimensions.

When the development is done, the definition of the cube is deployed to the Analysis Services instance. This means that the server will know where the data can be found in a non-aggregated form. Based on the storage model selected, a processing command might have to be issued against the cube, to request the instance to start querying the data source. Then it aggregates the fact values (the measures) across the dimensions, based on the hierarchies defined in the dimensions. After then, the cube is available for querying.

Analyis Services augments this further with calculatons based on custom formulas, KPI metrics, perspectives and the possibility of translation. Custom actions can be associated with dimension members or levels - these actions can help in connecting Analysis Services solutions with external applications as well.

For more details regarding Analysis Services cubes, please refer to this MSDN article (and visit the links under the article as well, in the navigation pane at the left).

Usage

In a typical scenario, end-users are not aware of that they are consuming data from an SSAS cube. They are displaying measures, grouped and sorted by dimension or hierarchy members - this is more native than fabricating queries.

Of course, there are plenty of tools which can interact with SSAS, but in a very simple case, cubes can be accessed with Excel, which is able to represent the data in a cube as a pivottable. The end-users are selecting which measures they would like to display, setting the filters using dimensions or hierarchies, and eventually splitting the summarized data using hierarchies on the row and the column area of the pivottable.

All OLAP tools - like Excel - must be able to turn this layout to an MDX query, run it against an SSAS server instance in the background, then to display the results from the cube.

Credits

This article was originally written by Zoltán Horváth.