What is OLAP and What is it Used For?

OLAP stands for Online Analytical Processing. OLAP is an application used to ease the query processing on data warehouse. The terminology OLAP was created by modifying the traditional database related term online transaction processing (OLTP). OLTP facilitates and also manages the transaction-oriented applications. OLAP is an approach to multi-dimensional analytical (MDA) queries and is a part of business intelligence, data mining etc. A data warehouse represents a consolidated data of an organisation for taking business decisions. Larger magnitude of orders makes the queries extremely complex. We use OLAP to facilitate the query processing.

Use of OLAP includes for sales, marketing, management reporting, business process management, finance etc. Data warehouses are modeled as multi-dimensional data. OLAP has three analytical operations, namely consolidation (or roll up), drill-down, slicing and dicing, pivot. Roll Up increasrs the level of aggregation. Drill down opens up more dimension hierarchies. Slice and Dice helps in selection and projection. Pivot is for re-orienting the view of data, which are multidimensional in nature.

So, OLAP allow the users to inspect the data from different perspectives. The actual usefulness of OLAP is limited to certain use cases. OLAP resolves the issue of resource contention. OLAP cubes are built on the top of data warehouse. OLAP tends to be less useful when data dimensions cannot be determined in advance or data requested for a limited number of times. OLAP is always supported by dedicated software. Different OLAP software have their own approach on the process of retriving, storing and making the data available. Those are often said as categorization such as Multidimensional OLAP (MOLAP), Relational OLAP (ROLAP), Hybrid OLAP (HOLAP), Other types such as Web-based OLAP (WOLAP), Desktop OLAP (DOLAP), Real-Time OLAP (RTOLAP), CaseOLAP and so on.

The relational databases use SQL as the standard query language with APIs such as ODBC, JDBC and OLEDB etc. But OLAP world had no much unificationfor a long time. OLE DB API for OLAP specification was developed by Microsoft using MDX query language. OLAP clients include some of the spreadsheet programs like Excel. Apache Kylin is a distributed analytics engine designed to provide a SQL interface and multi-dimensional analysis (OLAP) on Hadoop.