OLAP (i.e. Data Warehousing) Vs. OLTP

Q. What is the difference between OLTP and OLAP?
A. OLTP stands for On-Line Transaction Processing and OLAP stands for On-Line Analytical Processing. OLAP contains a multidimensional or relational data store designed to provide quick access to pre-summarized data & multidimensional analysis.

• MOLAP: Multidimensional OLAP – enabling OLAP by provding cubes.

• ROLAP: Relational OLAP – enabling OLAP using a relational database management system

OLTP	OLAP
Source data is operational data. This data is the source of truth.	Data comes from various OLTP data sources as shown in the above diagram
Transactional and normalized data is used for daily operational business activities.	Historical, de-normalized and aggregated  multidimensional data is used for analysis and decision making (i.e. for business intelligence).
Data is inserted via short inserts and updates. The data is normally captured via user actions via web based applications.	Periodic (i.e. scheduled) and long running (i.e. during off-peak) batch jobs refresh the data. Also, known as ETL process as shown in the diagram.
The database design involves highly normalized tables.	The database design involves de-normalized tables for speed. Also, requires more indexes for the aggregated data.
Regular backup of data is required to prevent any loss of data, monetary loss, and legal liability.	Data can be reloaded from the OLTP systems if required. Hence, stringent backup is not required.
Transactional data older than certain period can be archived and purged based on the compliance requirements.	The volume of this data will be higher as well due to its requirement to maintain historical data.
The typical users are operational staff.	The typical users are management and executives to make business decisions.
The space requirement is relatively small if the historical data is archived.	The space requirement is larger due to the existence of aggregation structures and historical data. Also requires more indexes than OLTP.

There are a number of commercial and open-source OLAP  (aka Business Intelligence) tools like:

• Oracle Enterprise BI Server, Oracle Hyperion System
• Microsoft BI & OLAP tools
• IBM Cognos Series 10
• SAS Enterprise BI Server
• JasperSoft (open source)

The OLAP tools are well known for their drill-down and slice-and-dice functionality. Also they enable users to very quickly analyze data by nesting the information in tabular or graphical formats. They generally provide  good performance due to their highly indexed file structures (i.e. cubes) or in-memory technology. 


Q. What is an OLAP cube?
A. An OLAP cube will connect to a data source to read and process the raw data to perform aggregations and calculations for its associated measures. Cubes are the core components of OLAP systems. They aggregate facts from every level in a dimension provided in a schema. For example, they could take data about products, units sold and sales value, then add them up by month, by store, by month and store and all other possible combinations. They’re called cubes because the end data structure resembles a cube.