Cubes

The data structures used in the OLAP are multidimensional data cubes or OLAP cubes:

image1

An OLAP cube is a multidimensional database that is optimized for data warehouse and online analytical processing (OLAP) applications.

An OLAP cube is an array of data understood in terms of its 0 or more dimensions. OLAP is an acronym for online analytical processing. OLAP is a computer-based technique for analyzing business data in the search for business intelligence.

So, in simple it is the data presentation in multiple dimensions.

Example: A company needs survey of their products all over the world.

So for this it requires answer of some questions like

–          Sell in USA?

–          Sell in USA of different products?

–          Sell in USA of different products in last 6 month?

–          Sell in Other country and total sell?

–          What type of consumers attract by this product?

–          Product details, with sell?

…etc

In this case we need one master table/Fact table where we store values commonly used (like last month sell) and also the relation of other table by using foreign key (Customer detail , USA sell). In such a manner that data retrieve much faster as possible.

 

The OLAP cube consists of facts, also called measures, categorized by dimensions (it can be much more than 3 Dimensions; dimensions referred from Fact Table by “foreign keys”). Measures are derived from the records in the Fact Table and Dimensions are derived from the dimension tables, where each column represents one attribute (also called dictionary; dimension can have many attributes)

Facts and Measures

Fact is most detailed information that can be measured.

In simple it works like central control, which has information of whole cube like where we find which thing.

image2

OLAP Common operations include slice and dice, drill down, roll up, and pivot:

 

Slice:

A slice is a subset of a multi-dimensional array corresponding to a single value for one or more members of the dimensions not in the subset.

image3

 

Dice:

The dice operation is a slice on more than two dimensions of a data cube (or more than two consecutive slices).

image4

 

Drill Down/Up:

Drilling down or up is a specific analytical technique whereby the user navigates among levels of data ranging from the most summarized (up) to the most detailed (down).

image5

 

Roll-up:

(Aggregate, Consolidate) A roll-up involves computing all of the data relationships for one or more dimensions. To do this, a computational relationship or formula might be defined.

image6

 

Pivot:

This operation is also called rotate operation. It rotates the data in order to provide an alternative presentation of data – the report or page display takes a different dimensional orientation.

image7

 

Summary

OLAP cube is really helpful, when we are talking about billions of data. It is very easy and straightforward to translate business questions into multidimensional query.

By which we can really increase data performance as well as understandability of data in a better manner.

Reusability is also possible in cube, which make this more scalable.

Thanks for reading : Pushpraj Kumar ( Helical IT Solutions )

Leave a Reply