Cube hierarchy and its operations

Hierarchy
The elements of a dimension can be organized as a hierarchy, a set of parent-child relationships,
typically where a parent member summarizes its children. Parent elements can further be aggregated
as the children of another parent.


A Hierarchy is a set of logically related attributes with a fixed cardinality. While browsing the data, a hierarchy exposes the top level attribute which can be broken down into lower level attributes. For example, Year -> Semester – Quarter – Month is a hierarchy. While  analyzing the data, it might be required to drill down from a higher level to a detail level, and exposing data as a hierarchy.

example - dimemployee table have data in parent-child relation "employeeID" and "ParentEmployeeId".
SSAS example -  link soon provided


Operations to facilitate analysis.
Aligning the data content with a familiar visualization enhances analyst learning and productivity.
The user-initiated process of navigating by calling for page displays interactively, through the specification of slices via rotations and drill down/up is sometimes called "slice and dice".
Common operations include slice and dice, drill down, roll up, and pivot.

OLAP slicing
Slice is the act of picking a rectangular subset of a cube by choosing a single value for one of its dimensions, creating a new cube with one fewer dimension.

The picture shows a slicing operation: The sales figures of all sales regions and all product categories of the company in the year 2004 are "sliced" out of the data cube.
Query compare : equivalent to clause "where year = 2004"


OLAP dicing
Dice: The dice operation produces a subcube by allowing the analyst to pick specific values of multiple dimensions.

The picture shows a dicing operation: The new cube shows the sales figures of a limited number of product categories, the time and region dimensions cover the same range as before.
Query compare - equivalent to "where year in (2004,2005,2006) and category in ('bikes','helmets','acc.')"


OLAP Drill-up and drill-down
Drill Down/Up allows the user to navigate among levels of data ranging from the most summarized (up) to the most detailed (down).
Drill down presenting the data at lower level on hierarchy.
Drillup presenting the data at higher level on hierarchy.

The picture shows a drill-down operation: The analyst moves from the summary category "bike" to see the sales figures for the individual products.
Drill down and up can be possible if we have meaningful hierarchical data in schema.

Roll-up: A roll-up involves summarizing the data along a dimension. The summarization rule might be computing totals along a hierarchy or applying a set of formulas such as "profit = sales - expenses".


OLAP pivoting
Pivot allows an analyst to rotate the cube in space to see its various faces. For example, cities could be arranged vertically and products horizontally while viewing data for a particular quarter. Pivoting could replace products with time periods to see data across time for a single product.

The picture shows a pivoting operation: The whole cube is rotated, giving another perspective on the data.



Source: http://en.wikipedia.org/wiki/OLAP_cube

Some topic related posts
OLAP CUBE
Storage types of cube MOLAP, ROLAP, HOLAP
Advantage and disavantage of MOLAP, ROLAP
Create your first OLAP Cube in SSAS 

Advantage and disavantage of MOLAP, ROLAP

MOLAP
Its the traditional way of OLAP analysis. Data is stored in a multidimensional cube. The storage is not in the relational database, but in proprietary formats(cube).
Advantages:

• Excellent performance: MOLAP cubes are built for fast data retrieval, and is optimal for slicing and dicing operations.
• Can perform complex calculations: All calculations have been pre-generated when the cube is created. Hence, complex calculations are not only doable, but they return quickly.

Disadvantages:

• Limited in the amount of data it can handle: Because all calculations are performed when the cube is built, it is not possible to include a large amount of data in the cube itself. This is not to say that the data in the cube cannot be derived from a large amount of data. Indeed, this is possible. But in this case, only summary-level information will be included in the cube itself.
• Requires additional investment: Cube technology are often proprietary and do not already exist in the organization. Therefore, to adopt MOLAP technology, chances are additional investments in human and capital resources are needed.

ROLAP
This methodology relies on manipulating the data stored in the relational database to give the appearance of traditional OLAP's slicing and dicing functionality. In essence, each action of slicing and dicing is equivalent to adding a "WHERE" clause in the SQL statement.
Advantages:

• Can handle large amounts of data: The data size limitation of ROLAP technology is the limitation on data size of the underlying relational database. In other words, ROLAP itself places no limitation on data amount.
• Can leverage functionalities inherent in the relational database: Often, relational database already comes with a host of functionalities. ROLAP technologies, since they sit on top of the relational database, can therefore leverage these functionalities.

Disadvantages:

• Performance can be slow: Because each ROLAP report is essentially a SQL query (or multiple SQL queries) in the relational database, the query time can be long if the underlying data size is large.
• Limited by SQL functionalities: Because ROLAP technology mainly relies on generating SQL statements to query the relational database, and SQL statements do not fit all needs (for example, it is difficult to perform complex calculations using SQL), ROLAP technologies are therefore traditionally limited by what SQL can do. ROLAP vendors have mitigated this risk by building into the tool out-of-the-box complex functions as well as the ability to allow users to define their own functions.

HOLAP
HOLAP technologies attempt to combine the advantages of MOLAP and ROLAP. For summary-type information, HOLAP leverages cube technology for faster performance. When detail information is needed, HOLAP can "drill through" from the cube into the underlying relational data. 


Source - http://social.msdn.microsoft.com/Forums/sqlserver/en-US/6da8f3d7-e0d5-4ccc-926b-5d97eaeb5934/molap-rolap-holap?forum=sqlanalysisservices

Create your first OLAP Cube in SSAS

I get below links to create olap cube in SSAS using database AdventuresWorksDW. These links provide very clear cut guidance (step by step) to create a cube in ssas.

Let's create an Analysis Services Cube using the new Cube Wizard that comes with SQL Server 2008 Business Intelligence Development Studio.

Please follow the steps below (make a click on each step for details):

1. Create an Analysis Services Project
2. Create a Data Source
3. Create a Data Source View
4. Create a Cube
5. Create perspectives
6. Deploy the Analysis Services Project

Storage types of cube MOLAP, ROLAP, HOLAP

A cube is a structure made of number of dimensions, measures, etc.
Cubes usually rely on two kind of tables like 'fact-table' (for cubes) & 'dimension-table' (for cube’s different dimensions).
A cube can have only one fact-table and ‘n’ number of dimension tables (based on no: of dimensions in the cube).
It store the data in pre-aggreagted form.

Cube Storage type:
MOLAP (Multi dimensional Online Analytical Processing)
MOLAP is the most used storage type. Its designed to offer maximum query performance to the users. Data AND aggregations are stored in optimized format in the cube. The data inside the cube will refresh only when the cube is processed, so latency is high.

ROLAP (Relational Online Analytical Processing)
ROLAP does not have the high latency disadvantage of MOLAP. With ROLAP, the data and aggregations are stored in relational format. This means that there will be zero latency between the relational source database and the cube. Disadvantage of this mode is the performance, this type gives the poorest query performance because no objects benefit from multi dimensional storage.

HOLAP (Hybrid Online Analytical Processing)
HOLAP is a storage type between MOLAP and ROLAP. Data will be stored in relational format(ROLAP), so there will also be zero latency with this storage type. Aggregations, on the other hand, are stored in multi dimensional format(MOLAP) in the cube to give better query performance. SSAS will listen to notifications from the source relational database, when changes are made, SSAS will get a notification
and will process the aggregations again.With this mode it’s possible to offer zero latency to the users but with medium query performance compared to MOLAP and ROLAP.

We use MOLAP storage type generally for deliver high performance


More clear image to understand the storage type of cubes:-