OLAP

OLTP

Online Transaction Protocol is what SQL uses to perform transactions - commits, rollbacks, etc.

OLAP

Online Analysis Protocol, however, is used to analyse existing data. Types of OLAP are

• Multi-Dimensional OLAP (MOLAP) - store database cubes in multidimensional arrays
• Relational OLAP (ROLAP) - uses relational databases with an OLAP engine
• Hybrid OLAP (HOLAP) - store some summaries in memory and some in a relational database

Multi-Dimensional Data

Data that can be expressed as axes and values along those axes is referred to as multi-dimensional data.

• Measure attributes - they measure some value, and can be aggregated upon
• Dimension attributes - they define the dimensions on which measure attributes are viewed. Think of them as axis labels

Data Cube

A Data Cube places data of a cross-tab in three axes. Cross-Tabs can be used as views on a data cube. In SQL we use GROUP BY CUBE to select as a cube.

Cross-Tabs

Cross-Tabs are multi-indexed tables, where one category has many rows inside it. It is possible to drill up or roll down on a hierarchy.

Rollup

The rollup operation is similar to group-by, but has slight differences. If you use ROLLUP(c1, c2, c3), it gives you the union of {(c1, c2, c3), (c1, c2), (c1), ()} This implies a sort of hierarchy between the columns. It can be used to generate multi-level aggregates.

OLAP operations

• Pivoting - changing the dimension in a cross-tab
• Slice - create cross tab for fixed values only
• Rollup - moving from finer granularity to coarser granularity
• Drill Down - moving from coarser granularity to finer granularity