SQL Server What are the differences between OLTP and OLAP Systems May 21, 20161399 views0 Share By IG Share Question: What are the differences between OLTP and OLAP Systems Answer: In a production environment, OLTP system deals with high number of transactions, i.e. inserts/deletes/updates on an on-going basis and is optimized for such activities. An OLAP system on the other hand refers to carrying out analysis, on historical data transactions that have occurred over time. It is mainly used for read-only querying, and therefore is optimized to serve that purpose. The system should achieve a response time within seconds for any activity, for example: expanding from a product’s sales information to the products categories sales hierarchical information or comparing numbers with previous year’s sales numbers. OLAP systems are used for their heavy read-access on contiguous blocks of data and you need good in-memory and disk performance. Typically they are ideal for SSD (Solid State Drive) based fast storage for high random and sequential reads. Also Read: SQL Server Performance I/O Characteristics A Data Warehouse contains the OLTP system data in de-normalized form typically in a Star or Snowflake schema structure. It stores a subset of the data in the system that is later going to be analyzed. A Data Mart is sometimes used synonymously with Data-Warehouse. Specifically, when it comes to an Enterprise, a DWH (Data-Warehouse) is the encompassing structure, whereas Data Marts then refer to self-contained parts that sum up to the DWH but can as well be managed and analyzed independently and provide a restricted view on a certain area of the enterprise, for example on a certain business process like Purchase, IT, HR etc. Decision Support Systems facilitate decision-taking processes and often include data not just from an existing OLAP system but include knowledge from external resources as well. OLTP OLAP OLTP stands for Online Transactional Processing System Mostly Production database Optimized for (mostly fine-grained) inserts, updates, deletes, selects, creates Focus on maintaining ACID properties and data constancy. User concurrency is high. Storage subsystem needs to be highly responsive to handle random read-writes. Highly Normalized Table structure OLAP stands for Online Analytical System The main purpose is rapid data analyis on (historical) data sources from OLTP systems. Optimized for querying on large sequential data sets Operational Data Store Purpose is to integrate from sources Data Warehouse with de-normalization. Enterprise-wide database that holds historical data Data Mart Specific to a business area Decision Support System Include knowledge from other sources Hopefully that gives you a good insight of what the difference is between OLTP and OLAP systems. Disclaimer: The Questions and Answers provided on https://www.gigxp.com are for general information purposes only. We make no representations or warranties of any kind, express or implied, about the completeness, accuracy, reliability, suitability or availability with respect to the website or the information, products, services, or related graphics contained on the website for any purpose. Share What's your reaction? Excited 0 Happy 0 In Love 0 Not Sure 0 Silly 0 IG Website Twitter
TSQL How to Effectively split a string using STRING_SPLIT function in SQL server By DataGeekApril 15, 2019
SQL Server SQL Server 2019 System Requirements – Hardware & Software Prereqs In this article, we will be sharing the SQL Server 2019 System Requirements based on ...
Interview Questions T-SQL Performance Tuning on Bulk Load Data in SQL Server 2017 In this article, we are going to talk about a specific scenario where T-SQL Performance ...
SQL Server How To Check & Fix Index Fragmentation on SQL Server via Script & ssms Before we learn to fix Index fragmentation on SQL Server, let us understand why this ...
SQL Server Fixing SQL Server Max Worker Threads: error: 35217, severity: 16, state: 1 If you try fixing SQL Server Max Worker Threads for errors such as: “The thread pool ...