We can divide database systems into Transactional(OLTP) and analytical(OLAP). In general, we can say OLTP systems run the day to day business for an organization whereas OLAP system is useful to analyze the trends.
Now lets understand the what is OLTP and OLAP system in details:
OLTP(Online Transaction Processing):-
OLTP stands for Online Transaction Processing system. As the name suggests, this is a stack of application and database which primary support the transactional nature of an organization’s business. This might include, order creation, logging service request, user creation, lead creation, opportunity creation, recording interactions with end users etc., to name a few.
These systems not only are the tools to record data into system, they are also highly effective in retrieval of small chunk of data. For the sake of explaining, let’s assume OLTP as any state run bank’s ATM. You can deposit (INSERT), withdraw(UPDATE) certain sum of money and view (SELECT) your account balance. If you have noticed, these actions in an ATM are extremely fast – this in turn means that the system supporting the ATM function is built for fast insert & updates.
This drives all the necessary technical features of OLTP. Like comparatively low database size, highly normalized database design, lack of historical data, absence of indexes on tables etc.
OLAP(Online Analytical Processing):-
OLAP Stands for Online Analytical Processing. On the other hand, OLAP systems are purely built to support an organization’s decision support system.
Organizations need large pile of data at their disposal before they take any decision which would improve the revenue over a period of time (for example). The analytical system again consist of suite of applications, database (read data warehouse) and data integration layer for integration data from different source system.
The analytic application should have a scalable and robust engine which can handle large sums of data and calculate aggregations, generates various cutting edge charts, delivers scheduled reports to end users concurrently, provide flexibility to users to download reports and charts to PPT/PDF/Excel etc.
The database supporting this also needs to stand up to the rigors of the analysis process that business users fire. This drives the features of the OLAP database such as – ability to store historical data, highly denormalized database design, presence of indexes on columns for faster data retrieval, building pre built aggregates for analytics application to consume, ability to refresh the data warehouse with incremental transactional data in a pre defined window etc. To draw parallels with the ATM example for the OLTP system, imagine a national manager of a sales team trying to generate reports which given an aggregated view of the quarterly national sales of a product.
The report will have ability to slice the national sales data further down to region (east, west, north south), region to state, state to territories and to the individual sales representative. The entire user journey through the UI, fetching of data from database upon drilling down from a higher level sales report to the lower level and how the application and database work together to give the end user the data he/she wants – all are part of suite of OLAP tools.
Difference between OLTP and OLAP:-
|
|
OLTP System |
OLAP System |
|
Source of data |
Operational data; OLTPs are the original source of the data. |
Consolidation data; OLAP data comes from the various OLTP Databases |
|
Purpose of data |
To control and run fundamental business tasks |
To help with planning, problem solving, and decision support |
|
What the data |
Reveals a snapshot of ongoing business processes |
Multi-dimensional views of various kinds of business activities |
|
Inserts and Updates |
Short and fast inserts and updates initiated by end users |
Periodic long-running batch jobs refresh the data |
|
Queries |
Relatively standardized and simple queries Returning relatively few records |
Often complex queries involving aggregations |
|
Processing Speed |
Typically very fast |
Depends on the amount of data involved; batch data refreshes and complex queries may take many hours; query speed can be improved by creating indexes |
|
Space Requirements |
Can be relatively small if historical data is archived |
Larger due to the existence of aggregation structures and history data; requires more indexes than OLTP |
|
Database Design |
Highly normalized with many tables |
Typically de-normalized with fewer tables; use of star and/or snowflake schemas |
|
Backup and Recovery |
Backup religiously; operational data is critical to run the business, data loss is likely to entail significant monetary loss and legal liability |
Instead of regular backups, some environments may consider simply reloading the OLTP data as a recovery method |
source: www.rainmakerworks.com