The query language used for OLAP databases.
De facto standard introduced by Microsoft in 1998. Not specified by a standards organization, but appears to have many highly compatible implementations.
The opposite of OLAP: what MySQL and other relational database do well.
OLAP vs OLTP
OLTP systems are more focused on maintaining the current state of the system, to inform end users, while OLAP is more useful to store and analyse large quantities of historical data for business analysts.
As a result:
- OLTP serves small chunks of data faster
- OLAP deals better with large amounts of data
“True OLAP”: generally requires a specialized database backend.
Faster since more specialized.
“Fake OLAP”: uses a regular database backend like MySQL with very well constructed tables.
MOLAP ROLAP hybrid.
Likely the most popular one in 2014 is Mondrian, but it’s not trivial to install on Linux.
Oracle’s can be downloaded for free: http://www.oracle.com/technetwork/database/options/olap/olap-downloads-098860.html
Relational databases deal only with two dimensional data: tables.
OLAP however can deal with multiple dimensions: the basic data structure is then called a cube. More precisely it is a hypercube since N-dimensional.
OLAP give more focus to concept hierarchies, e.g., being easily able to group cities into countries.
Analogous of a group of relational columns, e.g. a country or a city.
The lowest level dimension, which contains the smallest division of data measure, i.e. numbers.
For example, it could be a City if the city is the smallest division in our database, or a streets, households, etc.
Cell in a relational database.
There are 4 basic OLAP operations:
- Slice and dice
- Pivot (rotate)
Group cities into countries.
Reduces the number of dimensions by combining related elements of a hierarchy.
Reverse of roll-up.
Select a sub-cube from the cube.
Slice is for selecting a single dimension.