OLAP Basics
What is OLAP?
Online Analytical Processing (OLAP) is a software technology used to analyze business data from various perspectives. Organizations gather and store data from multiple sources, including websites, applications, smart meters, and internal systems. OLAP categorizes this data to provide actionable insights for strategic planning. For instance, a retailer may store data about all its products, like color, size, cost, and location. In another system, it collects customer purchase information, such as the items ordered and total sales value. OLAP merges these datasets to answer questions like which color products are most popular or how product placement affects sales.
Why is OLAP Important?
Online Analytical Processing (OLAP) helps organizations process and make use of an increasing amount of digital information. The following are some of the benefits of OLAP.
Faster Decision Making
Businesses utilize OLAP to make swift and accurate decisions, crucial for remaining competitive in a fast-paced economy. Analytical queries on multiple relational databases can be time-consuming due to the system having to search through various data tables. However, OLAP systems precalculate and consolidate data, allowing business analysts to generate reports more quickly when necessary.
Support for Non-Technical Users
OLAP simplifies complex data analysis for non-technical business users. These users can create intricate analytical calculations and generate reports without needing to learn how to operate databases.
Integrated Data View
OLAP offers a unified platform for different business units, including marketing, finance, and production. This enables managers and decision-makers to see the bigger picture and effectively problem-solve. They can perform what-if analyses, which illustrate the impact of decisions made by one department on other areas of the business.
What is OLAP Architecture?
Online Analytical Processing (OLAP) systems store multidimensional data, representing information across more than two categories or dimensions. While two-dimensional data involves columns and rows, multidimensional data encompasses multiple characteristics. For instance, product sales’ multidimensional data might include the following dimensions:
- Product type
- Location
- Time
Data engineers construct a multidimensional OLAP system comprising the following elements:
Data Warehouse
A data warehouse gathers information from various sources, such as applications, files, and databases. It processes this information using different tools to make it ready for analytical purposes. For instance, the data warehouse may collect data from a relational database that stores information in tables of rows and columns.
ETL Tools
Extract, Transform, and Load (ETL) tools are database processes that automatically retrieve, alter, and prepare data in a format suitable for analytical purposes. Data warehouses use ETL to standardize information from multiple sources before making it accessible to OLAP tools.
OLAP Server
An OLAP server is the machine that drives the OLAP system. It employs ETL tools to convert information in relational databases and ready them for OLAP operations.
OLAP Database
An OLAP database is a separate entity that connects to the data warehouse. Data engineers sometimes use an OLAP database to prevent the data warehouse from being overwhelmed by OLAP analysis. They also use an OLAP database to simplify the creation of OLAP data models.
OLAP Cubes
A data cube represents a multidimensional array of information. Although it’s easier to visualize as a three-dimensional data model, most data cubes have more than three dimensions. In an OLAP system, these data cubes are referred to as OLAP cubes or hypercubes. Once modeled, the dimensions and underlying data in OLAP cubes are rigid and can’t be altered. For example, if you add the warehouse dimension to a cube already comprising product, location, and time dimensions, you need to remodel the entire cube.
OLAP Analytic Tools
Business analysts use OLAP tools to interact with the OLAP cube. They perform operations such as slicing, dicing, and pivoting to derive deeper insights into specific information within the OLAP cube.
How does OLAP work?
An online analytical processing (OLAP) system collects, organizes, aggregates, and analyzes data using the following steps:
- The OLAP server collects data from multiple sources, including relational databases and data warehouses.
- Extract, transform, and load (ETL) tools then clean, aggregate, precalculate, and store data in an OLAP cube based on the specified dimensions.
- Business analysts use OLAP tools to query and generate reports from the multidimensional data in the OLAP cube.
OLAP uses Multidimensional Expressions (MDX), a query language similar to SQL, to manipulate the OLAP cube.
What are the types of OLAP?
There are three main types of OLAP systems.
MOLAP
Multidimensional online analytical processing (MOLAP) creates a data cube to represent multidimensional data from a data warehouse. MOLAP stores precalculated data in the hypercube, providing fast analysis.
ROLAP
Relational online analytical processing (ROLAP) uses a relational database for multidimensional data analysis. It uses SQL queries to search for and retrieve specific information based on the required dimensions. ROLAP is suitable for analyzing extensive and detailed data, but it may have slower query performance compared to MOLAP.
HOLAP
Hybrid online analytical processing (HOLAP) combines MOLAP and ROLAP to provide the advantages of both architectures. HOLAP allows for quick retrieval of analytical results from a data cube and extraction of detailed information from relational databases.
What is data modeling in OLAP?
Data modeling represents data in data warehouses or OLAP databases. It’s crucial in ROLAP as it analyzes data directly from the relational database, storing multidimensional data as a star or snowflake schema.
Star schema
The star schema consists of a fact table and multiple dimension tables. The fact table contains numerical values related to a business process, and each dimension table describes attributes in the fact table. The fact table refers to dimension tables with unique identifiers that correlate to the respective information in the dimension table.
For example, a fact table for product sales might include:
- Product ID
- Location ID
- Salesperson ID
- Sales amount
Each ID references its respective dimension table, which includes more detailed information.
Snowflake schema
The snowflake schema is an extension of the star schema. Some dimension tables lead to one or more secondary dimension tables, creating a snowflake-like shape when the dimension tables are combined.
For example, the product dimension table might link to another dimension table via the ‘product type ID’ field, providing further detail.
What are OLAP operations?
Business analysts often perform several basic analytical operations using a multidimensional online analytical processing (MOLAP) cube.
Roll up
The roll up operation involves summarizing data for specific attributes in the OLAP system, resulting in less detailed information. For instance, viewing product sales by country instead of individual cities like New York, California, London, and Tokyo would be a roll-up operation.
Drill down
Drill down is the opposite of a roll-up operation. Here, business analysts descend the concept hierarchy and extract the required details. For example, they might transition from viewing sales data by years to visualizing it by months.
Slice
The slice operation allows data engineers to create a two-dimensional view from the OLAP cube. For instance, a MOLAP cube might sort data according to products, cities, and months. By slicing the cube, data engineers can create a table of products and cities for a specific month.
Dice
The dice operation helps data engineers create a smaller subcube from an OLAP cube. They choose the necessary dimensions and construct a smaller cube from the original hypercube.
Pivot
Pivoting involves rotating the OLAP cube along one of its dimensions to access a different perspective on the multidimensional data model.
How does OLAP compare with other data analytics methods?
Data mining
Data mining is an analytics technology that processes large volumes of historical data to find patterns and insights. Business analysts use data-mining tools to discover relationships within the data and predict future trends.
OLAP and data mining
While OLAP involves querying, extracting, and studying summarized data, data mining delves into unprocessed information. For example, marketers could use data-mining tools to analyze user behaviors from website visit records and then employ OLAP software to inspect those behaviors from various perspectives.
OLTP
Online transaction processing (OLTP) is a technology that quickly and reliably stores information in a database. Data engineers use OLTP tools to store transactional data, such as financial records, service subscriptions, and customer feedback, in a relational database.
OLAP and OLTP
While OLTP is excellent for managing and storing multiple streams of transactions in databases, it can’t perform complex database queries. Therefore, business analysts use an OLAP system to analyze multidimensional data. For instance, data scientists might connect an OLTP database to a cloud-based OLAP cube to perform compute-intensive queries on historical data.
Reference
https://aws.amazon.com/what-is/olap/