OLAP vs OLTP

OLAP ------ OLTP---- Whats the difference?

An OLTP and OLAP designed database serve two different functions and therefore, logically, need to be built with different design objectives.

An OLTP database is designed for real-time business operations. These databases are used to take in data instantaneously, update data throughout the day and purge data generally on a daily or weekly basis. These databases are designed for writing data. If they are used for reporting, they generally provide a snapshot of the business process it supports.

An OLAP database is designed for storing and reporting on data. These databases assimilate the data from the OLTP databases Typically the data is transformed by applying business rules and historical data is maintained. Data does not typically get updated once it’s been loaded. These databases are designed for efficient analytics. Their primary purpose is to support the Business Intelligence system.

There are numerous theories for designing both types of databases but there are some best practices that are generally followed for OLAP design. While an OLTP system is highly normalized and contains a plethora of tables, a data warehouse will generally have fewer tables and will be less normalized. 

The star and snowflake schemas are two types of design schemas for OLAP database design. A typical data warehouse will fall somewhere between the two; it won’t be fully normalized yet also not fully de-normalized. Transactional systems will generally show a very low granular view of data, for example at the transaction level. The OLAP system is optimized to provide aggregated data over a period of time, like a month or a quarter. More sophisticated BI systems will deliver predictive information after applying calculations to the summary data. Where data in the OLTP system uses natural keys and mixes dimensional and factual data in the same table, an OLAP system willcontain separate tables for dimensions and facts and will employ surrogate primary keys. 

You can see that these two systems are quite different. It’s generally considered good practice to design and build a data warehouse specifically to support your Business Intelligence initiatives. The option of going directly to your OLTP systems for BI will likely cause your reports to be frustratingly slow to return results while they simultaneously slow down the business process they support.

Below is a table that compares the differences side by side.

OLTP System Online Transaction Processing (Operational System)	OLAP System Online Analytical Processing (Data Warehouse)
Transactional data, used primarily for writing and updating.	Consolidation data, used primarily for extracting information.
The purpose is to manage real-time business operations.	The purpose is to analyze business measures and for decision support.
Data is constantly being created and updated .	Data is loaded during pre-defined intervals such as daily and isn’t typically updated.
Space Requirements can be relatively small and historical data is typically archived.	Space Requirements are large due to the existence of aggregation structures and historical data.
Highly normalized with many tables.	Typically de-normalized with fewer tables; use of star and/or snowflake schemas.
Current data or limited history.	Full historical data plus iterative data when appropriate.
Supports thousands of concurrent users.	Supports few concurrent users.
Primary keys are natural keys.	Primary keys are surrogate keys.