Data Warehousing
25 BEST Data Warehouse Tools in 2021 (Open Source & Paid)
{loadposition top-ads-automation-testing-tools} A Data Warehouse is a collection of software tools...
Online Analytical Processing (OLAP) is a category of software that allows users to analyze information from multiple database systems at the same time. It is a technology that enables analysts to extract and view business data from different points of view.
Analysts frequently need to group, aggregate and join data. These operations in relational databases are resource intensive. With OLAP data can be pre-calculated and pre-aggregated, making analysis faster.
OLAP databases are divided into one or more cubes. The cubes are designed in such a way that creating and viewing reports become easy. OLAP stands for Online Analytical Processing.
In this tutorial, you will learn-
At the core of the OLAP concept, is an OLAP Cube. The OLAP cube is a data structure optimized for very quick data analysis.
The OLAP Cube consists of numeric facts called measures which are categorized by dimensions. OLAP Cube is also called the hypercube.
Usually, data operations and analysis are performed using the simple spreadsheet, where data values are arranged in row and column format. This is ideal for two-dimensional data. However, OLAP contains multidimensional data, with data usually obtained from a different and unrelated source. Using a spreadsheet is not an optimal option. The cube can store and analyze multidimensional data in a logical and orderly manner.
How does it work?
A Data warehouse would extract information from multiple data sources and formats like text files, excel sheet, multimedia files, etc.
The extracted data is cleaned and transformed. Data is loaded into an OLAP server (or OLAP cube) where information is pre-calculated in advance for further analysis.
Four types of analytical operations in OLAP are:
1) Roll-up:
Roll-up is also known as "consolidation" or "aggregation." The Roll-up operation can be performed in 2 ways
Consider the following diagram
2) Drill-down
In drill-down data is fragmented into smaller parts. It is the opposite of the rollup process. It can be done via
Consider the diagram above
3) Slice:
Here, one dimension is selected, and a new sub-cube is created.
Following diagram explain how slice operation performed:
Dice:
This operation is similar to a slice. The difference in dice is you select 2 or more dimensions that result in the creation of a sub-cube.
4) Pivot
In Pivot, you rotate the data axes to provide a substitute presentation of data.
In the following example, the pivot is based on item types.
OLAP Hierarchical Structure
Type of OLAP | Explanation |
Relational OLAP(ROLAP): | ROLAP is an extended RDBMS along with multidimensional data mapping to perform the standard relational operation. |
Multidimensional OLAP (MOLAP) | MOLAP Implementes operation in multidimensional data. |
Hybrid OnlineAnalytical Processing (HOLAP) | In HOLAP approach the aggregated totals are stored in a multidimensional database while the detailed data is stored in the relational database. This offers both data efficiency of the ROLAP model and the performance of the MOLAP model. |
Desktop OLAP (DOLAP) | In Desktop OLAP, a user downloads a part of the data from the database locally, or on their desktop and analyze it. DOLAP is relatively cheaper to deploy as it offers very few functionalities compares to other OLAP systems. |
Web OLAP (WOLAP) | Web OLAP which is OLAP system accessible via the web browser. WOLAP is a three-tiered architecture. It consists of three components: client, middleware, and a database server. |
Mobile OLAP: | Mobile OLAP helps users to access and analyze OLAP data using their mobile devices |
Spatial OLAP : | SOLAP is created to facilitate management of both spatial and non-spatial data in a Geographic Information system (GIS) |
ROLAP works with data that exist in a relational database. Facts and dimension tables are stored as relational tables. It also allows multidimensional analysis of data and is the fastest growing OLAP.
Advantages of ROLAP model:
Drawbacks of ROLAP model:
MOLAP uses array-based multidimensional storage engines to display multidimensional views of data. Basically, they use an OLAP cube.
Learn more about OLAP here
Hybrid OLAP is a mixture of both ROLAP and MOLAP. It offers fast computation of MOLAP and higher scalability of ROLAP. HOLAP uses two databases.
Benefits of Hybrid OLAP:
Drawbacks of Hybrid OLAP:
{loadposition top-ads-automation-testing-tools} A Data Warehouse is a collection of software tools...
What is OLAP? Online Analytical Processing, a category of software tools which provide analysis of data...
What is Business Intelligence? BI(Business Intelligence) is a set of processes, architectures, and technologies...
Syslog is a standard for sending log messages within a network. It supports by a variety of...
{loadposition top-ads-automation-testing-tools} Data integration is the process of combining data...
{loadposition top-ads-automation-testing-tools} What is Business Intelligence Tool? BUSINESS...