How Organizations Make Use of OLAP in Data Warehouse to Meet Their Analytics Demands

28.06.2018
6 min.
title

We live in a data-driven world, where an enormous amount of data is collected and stored on a daily basis. According to the estimates, the volume of business data across all companies worldwide doubles every 1.2 years. The more data is generated, the more important becomes the ability to access and analyze it in order to use it effectively. Knowing how to handle data analysis the right way might help companies get the most out of it and make right business decisions.

However, data analysis is a weak spot for many organizations: less than 0.5% of data is being used nowadays, and businesses lose over $600 billion a year because of bad data or poor data quality. For many, the problem resides in choosing the wrong type of data storage and running ineffective analytics as a result. Building an enterprise’s data warehouse is a possible solution for effective analytics.

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 of data rather than to provide up-to-the-minute data, that is why they 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.

In most cases, a data warehouse has a three-tier architecture and consists of the following core components:

  • 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, and what-if analysis on large data volumes.
  • Frontend tools are in the top tier of the data warehouse architecture. They comprise the query, reporting, analysis, and data mining tools.
Data warehouse components

As it is seen from the data warehouse architecture, OLAP plays an important role in the process of 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 for multidimensional analysis

The OLAP’s biggest value lies in the use of a multidimensional approach to organize and analyze data. OLAP organizes information into dimensions, which are ways that the facts can be broken down. For example, total sales might be broken up by dimensions such as geography and time. In their turn, a geography dimension might contain the levels of country, state, and county, whilst a 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 that makes it easy for businesses to understand and to use the 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. The OLAP cube is good for that purpose: it is structured in a way that allows storing various types of data 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 both are common systems for data management; however, OLTP and OLAP differ in terms of 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
Characteristic
Characterized by large numbers of short online transactions. Characterized by a large volume of data.
Functionality
OLTP is an online database modifying system. OLAP is an online database query management system.
Method
OLTP uses traditional DBMS. OLAP uses the data warehouse.
Tables
Tables in OLTP database are normalized. Tables in OLAP database are not normalized.
Source
OLTP and its transactions are the sources of data. Different OLTP databases become the source of data for OLAP.
Data integrity
OLTP database must maintain data integrity constraint. OLAP database does not get frequently modified. Hence, data integrity is not an issue.
Response time
Its response time is a millisecond. Response time is from seconds to minutes.
Data quality
The data in the OLTP database is always detailed and organized. The data in OLAP process might not be organized.
Usefulness
It helps to control and run fundamental business tasks. It helps with planning, problem-solving, and decision support.
Operation
Allow read/write operations. Only read and rarely write.
Audience
It is a market-oriented process. It is a customer-oriented process.
Query type
Queries in this process are standardized and simple. Complex queries involving aggregations.
Backup Complete backup of the data combined with incremental backups. OLAP only needs backups from time to time. Backups are not important compared to OLTP.
User type
It is used by data critical users like clerks, DBAs, and database professionals. Used by data knowledge users like workers, managers, and CEOs.
Purpose
Designed for real-time business operations. Designed for analysis of business measures by category and attributes.
Challenge
Data warehouses historically have been a development project, which 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
It provides fast results for daily used data. It ensures that response to the query is quicker consistently.
Style OLTP is designed to have a fast response time, low data redundancy and is normalized. A data warehouse is 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 the fields such as banking and finance, healthcare, insurance, and manufacturing to measure facts across company’s most-interested dimensions such as geography, demography, and product.

As one of the examples, a CFO might want to report on his company’s financial data by location, by month, or by product. These elements make up the dimensions of the OLAP cube, providing ways to transform that data into the requested information and enabling a CFO to create a customized financial report quickly and easily.

Another example shows how a data warehouse and OLAP can be applied in marketing. First, it can be used for trend analysis, as it enables managers to predict future outcomes from historical results and information. Second, web marketing relies heavily on a data warehouse 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 a data warehouse for market segmentation to get the detailed analysis of the customer’s behavior.

That is only a small number of the possible scenarios of how businesses adopt data warehouses and OLAP in particular to improve their decision-making and increase organizational performance. There are many more use cases proving that a data warehouse is evolving as a hurricane, being considered critical or very important by 70% of businesses.

How to start building a data warehouse in your organization

Building a data warehouse for an enterprise is beneficial in many ways: it allows 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, creating an effective data warehouse can be a difficult prospect, which requires accurate planning. If you plan to build a data warehouse in your organization, but have troubles figuring out where to start, you may turn to our BI consultants, who will help you draft a roadmap on how to turn raw data into truly valuable insights.