Skip to main content

What is OLAP (Online Analytical Processing): Cube, Operations & Types


What is OLAP (Online Analytical Processing): Cube, Operations & Types

What is 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. OLAP stands for Online Analytical Processing.
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.
In this tutorial, you will learn-
  • What is Online Analytical Processing?
  • OLAP cube
  • Basic analytical operations of OLAP
  • Types of OLAP systems
  • ROLAP
  • MOLAP
  • Hybrid OLAP
  • Advantages of OLAP
  • Disadvantages of OLAP

OLAP cube:

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.

Basic analytical operations of OLAP

Four types of analytical operations in OLAP are:
  1. Roll-up
  2. Drill-down
  3. Slice and dice
  4. Pivot (rotate)
1) Roll-up:
Roll-up is also known as "consolidation" or "aggregation." The Roll-up operation can be performed in 2 ways
  1. Reducing dimensions
  2. Climbing up concept hierarchy. Concept hierarchy is a system of grouping things based on their order or level.
Consider the following diagram
  • In this example, cities New jersey and Lost Angles and rolled up into country USA
  • The sales figure of New Jersey and Los Angeles are 440 and 1560 respectively. They become 2000 after roll-up
  • In this aggregation process, data is location hierarchy moves up from city to the country.
  • In the roll-up process at least one or more dimensions need to be removed. In this example, Quater dimension is removed.
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
  • Moving down the concept hierarchy
  • Increasing a dimension
Consider the diagram above
  • Quater Q1 is drilled down to months January, February, and March. Corresponding sales are also registers.
  • In this example, dimension months are added.
3) Slice:
Here, one dimension is selected, and a new sub-cube is created.
Following diagram explain how slice operation performed:
  • Dimension Time is Sliced with Q1 as the filter.
  • A new cube is created altogether.
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.

Types of OLAP systems

OLAP Hierarchical Structure
Type of OLAPExplanation
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

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:
  • High data efficiency. It offers high data efficiency because query performance and access language are optimized particularly for the multidimensional data analysis.
  • Scalability. This type of OLAP system offers scalability for managing large volumes of data, and even when the data is steadily increasing.
Drawbacks of ROLAP model:
  • Demand for higher resources: ROLAP needs high utilization of manpower, software, and hardware resources.
  • Aggregately data limitations. ROLAP tools use SQL for all calculation of aggregate data. However, there are no set limits to the for handling computations.
  • Slow query performance. Query performance in this model is slow when compared with MOLAP

MOLAP

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

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.
  1. Aggregated or computed data is stored in a multidimensional OLAP cube
  2. Detailed information is stored in a relational database.
Benefits of Hybrid OLAP:
  • This kind of OLAP helps to economize the disk space, and it also remains compact which helps to avoid issues related to access speed and convenience.
  • Hybrid HOLAP's uses cube technology which allows faster performance for all types of data.
  • ROLAP are instantly updated and HOLAP users have access to this real-time instantly updated data. MOLAP brings cleaning and conversion of data thereby improving data relevance. This brings best of both worlds.
Drawbacks of Hybrid OLAP:
  • Greater complexity level: The major drawback in HOLAP systems is that it supports both ROLAP and MOLAP tools and applications. Thus, it is very complicated.
  • Potential overlaps: There are higher chances of overlapping especially into their functionalities.

Advantages of OLAP

  • OLAP is a platform for all type of business includes planning, budgeting, reporting, and analysis.
  • Information and calculations are consistent in an OLAP cube. This is a crucial benefit.
  • Quickly create and analyze "What if" scenarios
  • Easily search OLAP database for broad or specific terms.
  • OLAP provides the building blocks for business modeling tools, Data mining tools, performance reporting tools.
  • Allows users to do slice and dice cube data all by various dimensions, measures, and filters.
  • It is good for analyzing time series.
  • Finding some clusters and outliers is easy with OLAP.
  • It is a powerful visualization online analytical process system which provides faster response times

Disadvantages of OLAP

  • OLAP requires organizing data into a star or snowflake schema. These schemas are complicated to implement and administer
  • You cannot have large number of dimensions in a single OLAP cube
  • Transactional data cannot be accessed with OLAP system.
  • Any modification in an OLAP cube needs a full update of the cube. This is a time-consuming process

Summary:

  • OLAP is a technology that enables analysts to extract and view business data from different points of view.
  • At the core of the OLAP, the concept is an OLAP Cube.
  • Various business applications and other data operations require the use of OLAP Cube.
  • There are primary five types of analytical operations in OLAP 1) Roll-up 2) Drill-down 3) Slice 4) Dice and 5) Pivot
  • Three types of widely used OLAP systems are MOLAP, ROLAP, and Hybrid OLAP.
  • Desktop OLAP, Web OLAP, and Mobile OLAP are some other types of OLAP systems.

Comments

Post a Comment

Popular posts from this blog

गरुड़ पुराण कथा और सार – Garud Puran in Hindi

गरुड़ पुराण कथा और सार – Garud Puran in Hindi गरुड़ पुराण कथा – Garud Puran in Hindi गरुड़ पुराण  हिन्दू धर्म के प्रसिद्ध वेद पुराण में से एक है। वैष्णव सम्प्रदाय से सम्बन्धित गरुण पुराण हिन्दू धर्म में मृत्यु के बाद सद्गति प्रदान करने वाला माना जाता है। इसलिये सनातन हिन्दू धर्म में मृत्यु के बाद ‘गरुड पुराण’ के श्रवण का प्रावधान है। इस पुराण के अधिष्ठातृ देव भगवान विष्णु  हैं। अठारह पुराणों में ‘गरुड़ महापुराण’ का अपना एक विशेष महत्व है। क्योंकि इसके देव स्वयं विष्णु माने जाते हैं, इसीलिए यह वैष्णव पुराण है। गरुड़ पुराण के अनुसार हमारे कर्मों का फल हमें हमारे जीवन में तो मिलता ही है, परंतु मरने के बाद भी कार्यों का अच्छा-बुरा फल मिलता है। इसी वजह से इस ज्ञान को प्राप्त करने के लिए घर के किसी सदस्य की मृत्यु के बाद का अवसर निर्धारित किया गया, ताकि उस समय हम जन्म-मृत्यु से जुड़े सभी सत्य जान सके और मृत्यु वश बिछडऩे वाले सदस्य का दुख कम हो सके। भगवान विष्णु की भक्ति |  Devotion to Lord Vishnu वास्तविक तथ्य यह है कि ‘गरुड़ पुराण’ में भगवान विष्णु ...
3 comments
Read more

Science, Tech, Math › Science Laws of Thermodynamics as Related to Biology Share Flipboard Email Energy and Thermodynamics Mikael Häggström/Public Domain by Regina Bailey Updated July 09, 2019 The laws of thermodynamics are important unifying principles of biology. These principles govern the chemical processes (metabolism) in all biological organisms. The First Law of Thermodynamics, also known ​as the law of conservation of energy, states that energy can neither be created nor destroyed. It may change from one form to another, but the energy in a closed system remains constant. The Second Law of Thermodynamics states that when energy is transferred, there will be less energy available at the end of the transfer process than at the beginning. Due to entropy, which is the measure of disorder in a closed system, all of the available energy will not be useful to the organism. Entropy increases as energy is transferred. In addition to the laws of thermodynamics, the cell theory, gene theory, evolution, and homeostasis form the basic principles that are the foundation for the study of life. First Law of Thermodynamics in Biological Systems All biological organisms require energy to survive. In a closed system, such as the universe, this energy is not consumed but transformed from one form to another. Cells, for example, perform a number of important processes. These processes require energy. In photosynthesis, the energy is supplied by the sun. Light energy is absorbed by cells in plant leaves and converted to chemical energy. The chemical energy is stored in the form of glucose, which is used to form complex carbohydrates necessary to build plant mass. The energy stored in glucose can also be released through cellular respiration. This process allows plant and animal organisms to access the energy stored in carbohydrates, lipids, and other macromolecules through the production of ATP. This energy is needed to perform cell functions such as DNA replication, mitosis, meiosis, cell movement, endocytosis, exocytosis, and apoptosis. Second Law of Thermodynamics in Biological Systems As with other biological processes, the transfer of energy is not 100 percent efficient. In photosynthesis, for example, not all of the light energy is absorbed by the plant. Some energy is reflected and some is lost as heat. The loss of energy to the surrounding environment results in an increase of disorder or entropy. Unlike plants and other photosynthetic organisms, animals cannot generate energy directly from the sunlight. They must consume plants or other animal organisms for energy. The higher up an organism is on the food chain, the less available energy it receives from its food sources. Much of this energy is lost during metabolic processes performed by the producers and primary consumers that are eaten. Therefore, much less energy is available for organisms at higher trophic levels. (Trophic levels are groups that help ecologists understand the specific role of all living things in the ecosystem.) The lower the available energy, the less number of organisms can be supported. This is why there are more producers than consumers in an ecosystem. Living systems require constant energy input to maintain their highly ordered state. Cells, for example, are highly ordered and have low entropy. In the process of maintaining this order, some energy is lost to the surroundings or transformed. So while cells are ordered, the processes performed to maintain that order result in an increase in entropy in the cell's/organism's surroundings. The transfer of energy causes entropy in the universe to increase.

Laws of Thermodynamics as Related to Biology Mikael Häggström/Public Domain The laws of thermodynamics are important unifying principles of biology. These principles govern the chemical processes (metabolism) in all biological organisms. The First Law of Thermodynamics, also known ​as the law of conservation of energy, states that energy can neither be created nor destroyed. It may change from one form to another, but the energy in a closed system remains constant. The Second Law of Thermodynamics states that when energy is transferred, there will be less energy available at the end of the transfer process than at the beginning. Due to entropy, which is the measure of disorder in a closed system, all of the available energy will not be useful to the organism. Entropy increases as energy is transferred. In addition to the laws of thermodynamics, the cell theory, gene theory, evolution, and homeostasis form the basic princi...
Post a Comment
Read more