OLAP (Online Analytical Processing) is a category of database processing that facilitates business intelligence.
OLAP provides analysts, managers, and executives with the information they need to make effective decisions about an organization’s strategic directions. OLAP can provide valuable insights into how their business is performing, as well as how they can make improvements.
OLAP tools are optimized for querying and reporting. This is in contrast to Online Transactional Processing (OLTP) applications, which are mainly concerned with transaction based tasks. OLAP tools enable users to analyze multidimensional data interactively from multiple perspectives. OLAP can be used to find trends and get a big picture view of the data. It can also be used for complex number crunching, and to create “what if” scenarios for forward planning.
Typical OLAP applications include business reporting for sales, marketing, management reporting, business process management, budgeting and forecasting, financial reporting, and more.
Smaller User Base, Larger Queries
OLAP applications usually have a smaller number of users than OLTP applications.
For example, an OLTP application might be used to provide internet banking for a bank’s 1.2 million customers. But an OLAP application that analyzes those customers’ internet banking habits would only be used by a select number of bank staff.
OLAP usually involves very large amounts of data, and queries can be very large and run for much longer than in a typical OLTP application. An OLTP application is concerned with speed, precision, and supporting a large number of users. OLTP is also primarily concerned with inserting, updating, and deleting small amounts of data that is specific to a particular record (such as an individual’s bank account). OLAP on the other hand, is usually concerned with complex aggregate queries across large data sets (such as querying all customers’ bank accounts).
Populating an OLAP Database
OLAP databases are usually populated via batch query. That is, all data is inserted at the same time.
This is in contrast to OLTP, where data is constantly being inserted, updated, and deleted.
The data that’s inserted into an OLAP database usually originates from an OLTP application. A batch query is used to scan the source system and import the data into the OLAP database.
OLAP and Data Warehouses
OLAP and data warehouses are two different things. However, OLAP can be used to transform the data from a data warehouse into strategic information.
A data warehouse stores and manages data, typically in relational databases. These could be extremely large databases with enormous amounts of data.
OLAP data, on the other hand, is stored in a multidimensional database. In a multidimensional database, each data attribute (such as product, region, time period, etc) is considered a separate “dimension”. OLAP tools can be used to extract data from the intersections of such dimensions. For example, all products sold in the Northeast region during the last quarter.
OLAP Cubes
An OLAP cube is typically a multi-dimensional array of data. If we visualize a three dimensional cube, we could have products along the x axis, regions along the y axis, and time period along the z axis.
So a an OLAP tool could be used to summarize sales data by product, region, and time period, for example.
However, OLAP cubes are not restricted to three dimensions. An OLAP cube could have any number of dimensions. In these cases, such a cube is sometimes referred to as a hypercube.
The sides of an OLAP cube are not necessarily of equal length – OLAP cubes are not cubes in the strictly mathematical sense. However, the term is a commonly used one when working with OLAP.
Examples of OLAP Tools
Here’s a list of some of the more popular OLAP tools available: