What Is OLAP?
OLAP, which stands for Online Analytics Processing is a category of database processing to assist Business Intelligence. It enables organisations to make effective decisions about the strategic direction of the company by providing insights into performance and highlighting areas where improvements are needed.
OLAP tools are optimised for querying and reporting, unlike OLTP (Online Transactional Processing) which primarily focus on transaction based tasks. By using OLAP tools, it allows its users to analyse multidimensional data interactivity from many different perspectives. Find trends in your data by getting a big picture view from across the organisation as a whole and get assistance with future decision making by creating “what if” scenarios.
OLAP vs OLTP
In usual use cases OLAP would involve vast amounts of data with large queries that run for far longer than a typical OLTP solution. The primary focus for OLTP is speed, accuracy and the ability to support large numbers of users. OLAP on the other hand is focused on complex aggregate queries across much larger data sets.
| OLTP | OLAP |
|---|---|
| Mostly updates | Mostly reads |
| Large amount of small transactions | Queries long, complex |
| Mb - Tb of data | Gb - Tb of data |
| Raw data | Summarised, consolidated data |
| Clerical users | Decision-makers, analysts as users |
| Up-to-date data | Historical data |
| Consistency, recoverability critical | Accuracy |
Populating an OLAP Database
OLAP databases will usually be populated via a batch query. By this we mean that all the data gets inserted at one time. In contrast to this, OLTP applications constantly have data being uploaded, updated and removed. OLAP and OLTP databases are used concurrently as you will often find that the data that populates an OLAP database has originated from an OLTP application. Batch queries are used to search the system from where the data comes from (OLPT) and then imports it into an OLAP database.
OLAP Cube
At the core of any OLAP tool is the OLAP cube. The way that the data is represented is arranged into a cubic shape, represents how OLAP follows the principle of multidimensional analysis which delivers a data structure designed for fast and effective data analysis which is in contrast to the limitations found when using relational databases.
OLAP cubes are made up of dimensions and measures:
Dimensions
A dimension is a perspective through which to analyse the data that resides within an OLAP cube. Each dimension is a cube is built on fields or columns found in the data. A dimension can be composed of a single attribute, or more. The data can exist on a single level of granularity or on multiple levels stored as a hierarchy.
Measures
A measure is a numeric piece of information attached to business fact that has occured or a record that is stored. Measures, along with dimensions are the foundation of most data analysis. Simply put, measures provide the number part of a cube while the dimensions provide the description. Measures are recorded individually and can then be aggregated to higher levels.