Data Warehousing - OLAP & Relational OLAP

Data Warehousing - OLAP

Introduction

Online Analytical Processing Server (OLAP) is based on multidimensional data model. It allows the managers , analysts to get insight the information through fast, consistent, interactive access to information. In this chapter we will discuss about types of OLAP, operations on OLAP, Difference between OLAP and Statistical Databases and OLTP.

Types of OLAP Servers

We have four types of OLAP servers that are listed below.
·         Relational OLAP(ROLAP)
·         Multidimensional OLAP (MOLAP)
·         Hybrid OLAP (HOLAP)
·         Specialized SQL Servers


Relational OLAP(ROLAP)

The Relational OLAP servers are placed between relational back-end server and client front-end tools. To store and manage warehouse data the Relational OLAP use relational or extended-relational DBMS.
ROLAP includes the following.
·         implementation of aggregation navigation logic.
·         optimization for each DBMS back end.
·         additional tools and services.

Multidimensional OLAP (MOLAP)

Multidimensional OLAP (MOLAP) uses the array-based multidimensional storage engines for multidimensional views of data.With multidimensional data stores, the storage utilization may be low if the data set is sparse. Therefore many MOLAP Server uses the two level of data storage representation to handle dense and sparse data sets.

Hybrid OLAP (HOLAP)

The hybrid OLAP technique combination of ROLAP and MOLAP both. It has both the higher scalability of ROLAP and faster computation of MOLAP. HOLAP server allows to store the large data volumes of detail data. the aggregations are stored separated in MOLAP store.

Specialized SQL Servers

specialized SQL servers provides advanced query language and query processing support for SQL queries over star and snowflake schemas in a read-only environment.

OLAP Operations

As we know that the OLAP server is based on the multidimensional view of data hence we will discuss the OLAP operations in multidimensional data.
Here is the list of OLAP operations.
·         Roll-up
·         Drill-down
·         Slice and dice
·         Pivot (rotate)


ROLL-UP

This operation performs aggregation on a data cube in any of the following way:
·         By climbing up a concept hierarchy for a dimension
·         By dimension reduction.
Consider the following diagram showing the roll-up operation.

·         The roll-up operation is performed by climbing up a concept hierarchy for the dimension location.
·         Initially the concept hierarchy was "street < city < province < country".
·         On rolling up the data is aggregated by ascending the location hierarchy from the level of city to level of country.
·         The data is grouped into cities rather than countries.
·         When roll-up operation is performed then one or more dimensions from the data cube are removed.

DRILL-DOWN

Drill-down operation is reverse of the roll-up. This operation is performed by either of the following way:
·         By stepping down a concept hierarchy for a dimension.
·         By introducing new dimension.
Consider the following diagram showing the drill-down operation:

·         The drill-down operation is performed by stepping down a concept hierarchy for the dimension time.
·         Initially the concept hierarchy was "day < month < quarter < year."
·         On drill-up the time dimension is descended from the level quarter to the level of month.
·         When drill-down operation is performed then one or more dimensions from the data cube are added.
·         It navigates the data from less detailed data to highly detailed data.


SLICE

The slice operation performs selection of one dimension on a given cube and give us a new sub cube. Consider the following diagram showing the slice operation.


·         The Slice operation is performed for the dimension time using the criterion time ="Q1".
·         It will form a new sub cube by selecting one or more dimensions.

DICE

The Dice operation performs selection of two or more dimension on a given cube and give us a new subcube. Consider the following diagram showing the dice operation:

The dice operation on the cube based on the following selection criteria that involve three dimensions.
·         (location = "Toronto" or "Vancouver")
·         (time = "Q1" or "Q2")
·         (item =" Mobile" or "Modem").

PIVOT

The pivot operation is also known as rotation.It rotates the data axes in view in order to provide an alternative presentation of data.Consider the following diagram showing the pivot operation.

In this the item and location axes in 2-D slice are rotated.

OLAP vs OLTP

SN
Data Warehouse (OLAP)
Operational Database(OLTP)
1
This involves historical processing of information.
This involves day to day processing.
2
OLAP systems are used by knowledge workers such as executive, manager and analyst.
OLTP system are used by clerk, DBA, or database professionals.
3
This is used to analysis the business.
This is used to run the business.
4
It focuses on Information out.
It focuses on Data in.
5
This is based on Star Schema, Snowflake Schema and Fact Constellation Schema.
This is based on Entity Relationship Model.
6
It focuses on Information out.
This is application oriented.
7
This contains historical data.
This contains current data.
8
This provides summarized and consolidated data.
This provide primitive and highly detailed data.
9
This provide summarized and multidimensional view of data.
This provides detailed and flat relational view of data.
10
The number or users are in Hundreds.
The number of users are in thousands.
11
The number of records accessed are in millions.
The number of records accessed are in tens.
12
The database size is from 100GB to TB
The database size is from 100 MB to GB.
13
This are highly flexible.
This provide high performance.

Data Warehousing - Relational OLAP

Introduction

The Relational OLAP servers are placed between relational back-end server and client front-end tools. To store and manage warehouse data the Relational OLAP use relational or extended-relational DBMS.
ROLAP includes the following.
·         implementation of aggregation navigation logic.
·         optimization for each DBMS back end.
·         additional tools and services.
Note: The ROLAP servers are highly scalable.

Points to remember

·         The ROLAP tools need to analyze large volume of data across multiple dimensions.
·         The ROLAP tools need to store and analyze highly volatile and changeable data.

Relational OLAP Architecture

The ROLAP includes the following.
·         Database Server
·         ROLAP Server
·         Front end tool

Advantages

·         The ROLAP servers are highly scalable.
·         They can be easily used with the existing RDBMS.
·         Data Can be stored efficiently since no zero facts can be stored.
·         ROLAP tools do not use pre-calculated data cubes.
·         DSS server of microstrategy adopts the ROLAP approach.

Disadvantages

·         Poor query performance.

·         Some limitations of scalability depending on the technology architecture that is utilized.

Comments

Post a Comment

Popular posts from this blog

How to Configure Report Scheduler to work on your Jasper Server

Introduction To leverage this capability to the greatest extent, it is important to get familiar with configurability of this feature. This tutorial covers some of the most commonly used configuration parameters associated with the scheduling feature. Pre-Requisites Access to the file system of and the ability to stop and start an operational installation of JasperReports Server Access to a running outbound mail server. Setting Up the Connection to your Outbound Email Server In order for the scheduler to successfully send email notifications and distribute reports, it must be configured to connect to an outbound email server. Step Action 1 Open the following configuration file in your preferred text editor:  /WEB-INF/js.quartz.properties 2 Set the value for the  mail.sender.host  property to your outbound mail server. Example :  report.scheduler.mail.sender.host=outbound.yourdomain.com ...
Read more

Data Warehousing - Process Managers & Security

Image
Data Warehousing - Process Managers Data Warehouse Load Manager This Component performs the operations required to extract and load process. The size and complexity of load manager varies between specific solutions from data warehouse to data warehouse. LOAD MANAGER ARCHITECTURE The load manager does the following functions. Extract the data from source system. Fast Load the extracted data into temporary data store. Perform simple transformations into structure similar to the one in the data warehouse. EXTRACT DATA FROM SOURCE The data is extracted from the operational databases or the external information providers. Gateways is the application programs that are used to extract data. It is supported by underlying DBMS and allows client program to generate SQL to be executed at a server. Open Database Connection( ODBC), Java Database Connection (JDBC), are examples of gateway. FAST LOAD ·          ...
Read more

Data Warehousing - System Processes & Architecture

Image
Data Warehousing - System Processes We have fixed number of operations to be applied on operational databases and we have well defined techniques such as   use normalized data , keep table small   etc. These techniques are suitable for delivering a solution. But in case of decision support system we do not know what query and operation need to be executed in future. Therefore techniques applied on operational databases are not suitable for data warehouses. In this chapter We'll focus on designing data warehousing solution built on the top open-system technologies like Unix and relational databases. Process Flow in Data Warehouse There are four major processes that build a data warehouse. Here is the list of four processes: ·          Extract and load data. ·          Cleaning and transforming the data. ·          Backup and Archive the data. ...
Read more