What is OLAP in data warehouse, and how can organizations make use of it?

21.01.2020
6 min.

We live in a data-driven world, where an enormous amount of data is collected and stored on a daily basis. In its report on global digitization, IDC estimates that worldwide data creation will grow to a massive 175 zettabytes by 2025—ten times the amount of data produced in 2017.

The more data is generated, the more important it becomes to have the ability to access and analyze it in order to use it effectively. Help from BI consultants can be valuable because they know how to handle data analysis in the right way. They can help companies get the most out of their data and make the right business decisions.

However, data analysis is a weak spot for many organizations: only 31% of the participants of the Big Data and AI Executive Survey 2019 by NewVantage Partners said they were data-driven, a decline from 37.1% in 2017 and 32.4% in 2018. Additionally, poor data quality is estimated to cost businesses an average of $15 million per year, according to Gartner.

For many, the problem resides in choosing the wrong type of data storage and running ineffective analytics as a result. Building an enterprise-grade data warehouse is a possible solution for effective analytics. So, what is OLAP in data warehouse, and how can it be used effectively?

Data warehouse and its core components

In general terms, a data warehouse is a database that stores current and historical data so that it can be analyzed for market research, analytical reports, and decision-making. The key difference from traditional operational databases is that data warehouses are typically designed to give a historical view rather than to provide up-to-the-minute data.

That is why data warehouses are perfectly suited for long-term comprehensive analytics. For instance, companies can use the information stored in data warehouses to monitor or modify their marketing campaigns or improve customer relationships.

  • Data sources, such as CRM, HRM, SCM, and ERP, include operational databases that record the company’s daily transactions.
  • Data warehouse application server is the bottom tier of the architecture represented by the relational database system. To build a data warehouse, organizations first need to copy the raw data from each of their data sources, cleanse, and optimize it. This process is called ETL (extract, transform, load).
  • OLAP server is the middle tier and one of the most important components. OLAP stands for online analytical processing and allows for rapid calculation of key business metrics, planning and forecasting functions, as well as what-if analysis of large data volumes.
  • Frontend tools are in the top tier of the data warehouse architecture. They are comprised of the query, reporting, analysis, and data mining tools.
Data warehouse components

As seen in the data warehouse architecture, OLAP plays an important role in data analysis. It transforms historical data into derived and projected data and enables users to easily and selectively view data from different perspectives.

The use of OLAP in data warehouse for multidimensional analysis

OLAP’s biggest value lies in its multidimensional approach to organizing and analyzing data. OLAP breaks down data into dimensions; for example, total sales might be broken into such dimensions as geography and time.

The geography dimension might contain the levels of country, state, and county, while the time dimension might be broken down by year, month, and day. Thus, OLAP in a data warehouse enables companies to organize information in multiple dimensions, which makes it easy for businesses to understand and use data.

Since OLAP contains multidimensional data usually obtained from different and unrelated sources, it requires a special method of storing that data. Using a spreadsheet with rows and columns is good for two-dimensional data, but not for multidimensional data. Instead, OLAP cubes should be used for that purpose. They are structured in a way that allows for storing various data types from heterogeneous sources and analyzing it in a logical and orderly manner.

OLAP cube

OLAP vs OLTP

OLAP demonstrates a slight variation from the Online Transaction Processing (OLTP), which is a more traditional technology. They are both common systems for data management. However, OLTP and OLAP differ in terms of their objectives: while the former aims at data processing, the latter is focused on data analysis. The table below summarizes the other differences between OLTP and OLAP system design.

 

Parameters OLTP OLAP
Characterized by
Large numbers of short online transactions Large volumes of data
Functionality
An online database modification system An online database query management system
Method
Uses a traditional DBMS Uses a data warehouse
Tables
Normalized. Not normalized.
Data sources
OLTP and its transactions Different OLTP databases
Data integrity
Of critical importance Not an issue
Response time
One millisecond From seconds to minutes
Data quality
Detailed and organized data Data can be non-organized
Business utility
Controlling and running fundamental business tasks Planning, problem-solving, and decision support
Operation support
Read/write operations Mostly read-only operations
Query type
Standardized and simple Complex queries that can involve aggregations
Backup
Complete backup combined with incremental backups Backups are not important compared to OLTP and performed only periodically
User type
Critical users (clerks, DBAs, database professionals) Data knowledge users (regular employees, managers, CEOs)
Purpose
Real-time business operations Analysis of business measures by category and attributes
Challenge
Data warehouses require development and may prove costly to build An OLAP cube is not an open SQL server data warehouse. Therefore, technical knowledge and experience are essential to manage the OLAP server
Process
Fast results for daily used data Consistently quick response to a query
Style Designed to have a fast response time and low data redundancy; normalized Created uniquely so that it can integrate different data sources for building a consolidated database

Use cases of OLAP in a data warehouse

OLAP plays a vital role in meeting organizations’ analytical demands by assisting decision-makers in fields such as banking and finance, healthcare, insurance, retail, and manufacturing. It allows them to measure facts across the company’s most-interested dimensions such as geography, demography, and product.

In healthcare, for example, a data warehouse can be used for predicting health risks and outcomes, generating reports, and sharing data with insurance companies. In the insurance sector, data warehouses can be used to analyze customer trends and data patterns. In retail, it can be utilized to track items and customer buying patterns, as well for determining dynamic pricing.

In a more specific example, a CFO might want to report on their company’s financial data by location, month, or product. These elements will make up the dimensions of the OLAP cube, providing ways to transform that data into the requested information. It will also enable the CFO to create a customized financial report quickly and easily.

Another example shows how an OLAP-based data warehouse can be applied in marketing. First, it can be used for trend analysis, as it enables managers to predict future outcomes from historical results. Second, digital marketing relies heavily on data warehouses to encompass versatile data from web analytics, PPC campaigns, display ads, social channels, CRM, and email service providers. Third, many organizations are focusing on integrating data warehouses for market segmentation to get detailed analysis of customer behavior.

Application of a data warehouse in marketing

That is only a small number of the possible scenarios of how businesses can adopt data warehouses, and OLAP in particular, to improve their decision-making and increase organizational performance. There are many more use cases proving that data warehouses are evolving quickly and that companies are seeing their importance.

Unlocking new benefits of a data warehouse in your organization

Building a data warehouse for an enterprise is beneficial in many ways: it allows for performing comprehensive analysis and supports decision-making. The global giants such as Apple, Walmart, eBay, and Verizon, are analyzing their data with the help of online analytical processing to maintain and strengthen their market power. However, every organization can benefit from an operational data warehouse.

When the information available is current, fast, and scalable, it provides a more comprehensive picture of business health. This also means that if all the right systems are in place, incoming data is consistent and reliable. A properly used data warehouse can become economical over time, providing otherwise unattainable access to invaluable information.