What is difference between OLAP and OLTP ?

What is the nature of the application (OLTP or OLAP), when you design database?

When you begin your database design  plan the important thing to investigate is the way of the application you are planning for, is it Transnational or Analytical. You will discover numerous engineers as a matter of course applying standardization rules without considering the way of the application and afterward later getting into execution and customization issues. As said, there are two sorts of uses: exchange based and scientific based, how about we comprehend what these sorts are.

Transnational :- In this sort of utilization, your end client is more inspired by CRUD, i.e., Creating,Reading ,Updating , and Deleting records. The official name for such a sort of database is OLTP. 


Analytical:- In these sorts of utilizations your end client is more intrigued by analysis , reporting, forecasting , and so on. These sorts of databases have a less number of additions and Updation. The principle goal here is to bring and break down information as quick as could be expected under the circumstances. The official name for such a sort of database is OLAP.
Shown in Image below:-

In other way  you think Insert, Update, and delete are more conspicuous then go for Normalize  table configuration, else make a level denormalized database structure.

What is difference between OLAP and OLTP ?

The following table summarizes the major differences between OLTP and OLAP system design.

	OLTP System Online Transaction Processing (Operational System)	OLAP System Online Analytical Processing (Data Warehouse)
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 decisionsupport.
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 datarefreshes 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.
DatabaseDesign::	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.

ETL Process - Extract, Transform and Load

ETL stands for Extract, Transform and Load, which is a procedure used to gather information from different sources, change the information relying upon business rules/needs and load the information into a destination database. The need to utilize ETL emerges from the way that in cutting edge registering business information lives in numerous areas and in numerous incongruent organizations. For instance business information may be put away on the document framework in different organizations (Word docs, PDF, spreadsheets, plain content, and so forth), or can be put away as email records, or can be kept in a different database servers like MS SQL Server, Oracle and MySQL for instance. Taking care of this business data proficiently is an incredible test and ETL assumes an essential part in taking care of this issue.

You have just read an article that categorized by title Interview Question by title What is difference between OLAP and OLTP ?. You can bookmark this page with a URL https://bikeshsrivastava.blogspot.com/2016/07/part-27what-is-difference-between-olap.html. Thank You!

Author: Bikesh Srivastava - Thursday, July 7, 2016