Data Warehousing and OLAP:
definitions, features and difference
- What is Data Warehouse?
- How does it work?
- OLAP tools in data warehouse
- Difference between OLAP and Data Warehouse
- Thread: OLAP vs. Data Warehouse
Business intelligence nowadays includes the variety of tools for almost every organization support. However, sometimes you can easily get confused with all these terms, systems and their differences. Data storage systems are the core tools among them. And the most frequently spread are OLAP and Data Warehousing tools.
Today we are going to figure out Data Warehouse and OLAP technology main characteristics and of course difference between them.
Let’s start introduction to Data Warehousing and OLAP with their defining. Data Warehouse is a collection of corporate data aggregated from one or several sources. It serves as a business analytical tool, which allows analyzing and comparing data in order to solve working issues and improve business processes.
The concept firstly appeared in the 1980s. It was developed to support operational systems’ data flow transferred to decision-making systems. These systems required the analysis of large amounts of heterogeneous data accumulated by companies over time.
Data warehouse works on the following principle:
data is extracted into one area from heterogeneous sources
converted in accordance with the needs of the decision support system
stored in the warehouse
Thus, the system provides answers for business decisions analyzing all these heterogeneous data. That is why data warehousing primarily aimed to simplify decision-making processes and helps executives to get required information based on the whole data quickly.
Data Warehouse benefits
There are two major reasons to use this technology:
- Quality data: organizations add data sources in the data warehouse, so they can be sure in their relevancy and constant availability. This provides higher data quality and data integrity for informed decision-making.
- Promotes decision-making: strategy decisions are based on facts and relevant data. They are supported by information that organization has collected over time. Another plus is that leaders are better informed about data requests and can extract information according to their specific requirements.
To summarize data warehouse overview, let’s point out some important facts:
- It is not a database and data mart as data warehouse is much bigger and aimed at huge informational amounts analyzing;
- The system helps to promote decision-making;
- Data warehousing provides capabilities for reporting, analyzing on different aggregate levels.
Continuing an overview of data warehousing and OLAP technology let’s move to online analytical processing and highlight it in detail.
What is OLAP?
We can define OLAP in data warehouse as a computing technology that allows query data and analyze it from different perspectives. The technology is a great solution for business analysts who need to pre-aggregate and pre-calculate data for fast analysis.
Talking about OLAP architecture in data warehouse, it is based on a multidimensional data structure. The data has a form of OLAP cubes, which have a star or snowflake-shape schema. In the middle of a star (or snowflake) is a table that includes data aggregations and reconciles various dimensions.
To query the data required, OLAP usually uses MDX language. MDX queries work with OLAP members, dimensions and hierarchies.
Features of OLAP in data warehouse are the following:
- Has intuitive easy-to-use interface;
- OLAP supports complex calculations;
- Provides data view in multidimensional manner;
- Time intelligence.
Different types of OLAP in data warehouse
Among OLAP variants, we can distinguish three main types and four additional.
There are following three major OLAP models in data warehouse:
- ROLAP or Relational OLAP: the kind of system where users query data from a relational database or from their own local tables. Thus, the number of potential questions is not limited.
- MOLAP or Multidimensional OLAP: this system stores the data in multidimensional database. Provides high speed of calculations.
- HOLAP or Hybrid OLAP: a mix of two above mentioned systems. Pre-computed aggregates and cube structure stored in multidimensional database.
Here are four additional types of OLAP server in data warehouse:
- WOLAP – Web OLAP
- SOLAP – Spatial OLAP
- Mobile OLAP
- DOLAP – Desktop OLAP
OLAP operations in data warehousing
Basic OLAP operations include:
OLAP tools example
If you want to learn how right OLAP system works in practice, you can see it on Ranet OLAP example. Ranet OLAP by GalaktikaSoft provides a library of different OLAP components for reporting and data analysis. This tool present business data in a simple but interactive view that makes work effective and pleasant.
Moreover, you can make reports of any complexity as such components like dynamic and simple pivot table provide you with choice: generate report manually or using the system settings.
As you already understood from the article, OLAP and data warehousing are not the same things. But what differ OLAP from Data Warehousing? Follow the table below:
- Is a kind of archive that stores historical data that can be used for analysis
- Data stored in a warehouse is taken from different sources.
- Data warehousing is an umbrella term that can use different analyzing approaches.
- Online analytical processing is a technology that can evaluate and analyze data from the warehouse.
- OLAP uses multidimensional models in order to organize data.
- The OLAP technology is one of such approaches and it also can be used in other tools.
To draw a line and consolidate the topic, let’s overview top asked questions regarding OLAP guidelines in data warehouse.
1. Are OLAP and Data Warehouse the same things?
The answer is no, they are different. Data warehouse is an archive where historical corporate data is stored and can be analyzed then. It can use different technologies for data extraction and analyzing. And OLAP is one of those technologies that analyze and evaluate data from the data warehouse.
2. What is OLAP in data warehouse?
OLAP is a computing technology that allows querying data and analyzing it form different perspectives. It provides fast analysis with a help of pre-aggregate and pre-calculate data. Online analytical processing also is one of the tools used in data warehousing.
3. What is the purpose of the data warehouse?
To begin with, data warehouse is an archive of historical corporate data stored together for analyzing and querying. It serves the following purposes:
4. What is the difference between data warehouse, database and ETL?
As it was mentioned previously in the article, data warehouse is not a database and ETL. Let’s try to formulate the clear difference overviewing their definitions.
ETL that stands for Extract, Transform and Load, is the process of extracting data from various sources, converting them to a suitable state, and loading into a data warehouse. So, as you can see it is a tool used by data warehouse for data storage.
Database in the contrary is wider term. In common sense, it is a repository of information that is used as a backup data storage for some specific purpose, while data warehouse is a type of database focused on some particular application.