OLAP vs. OLTP: What's the Difference?

Confused by online transaction processing (OLTP) and online analytical processing (OLAP) in the world of databases? Fear not. We have a simple explanation right here.

To understand the differences between OLTP and OLAP, we first need to understand where they fit into the world of data and databases. And the answer is “data warehousing”.

A data warehouse is a system used for reporting and data analysis. They are central repositories of data from one or more disparate sources, including relational databases.

In online transaction processing (OLTP), tables store individual details like orders, customer information, shipping address, etc. When you access the data, typically you are filtering out the vast majority of records to focus only on those records that concern you.

In online analytical processing (OLAP), you are typically creating a report across various tables from numerous sources. OLAP may scan many tables and aggregate data across a huge number of records. With OLTP, we expect a response in a fraction of a second; with OLAP, getting the response may require much more time.

What Is OLTP? What Is OLAP?

An OLTP system captures transaction data that it stores in an OLTP database. Each transaction may involve several database records with multiple fields. OLTP focuses on processing quickly and delivering immediate responses. Tables in OLTP databases are updated frequently. If a transaction fails, the system logic that is built into OLTP must ensure data integrity. OLTP data needs to be ATOMIC, CONSISTENT, ISOLATED, AND DURABLE (ACID). This ensures that each transaction will still be valid if unexpected errors occur. Good OLTP database design keeps redundant and duplicate data to a minimum.

OLAP is designed for quick, efficient reporting and the analysis of large quantities of data. It uses complex queries on very large amounts of historical data. This data has been aggregated from various sources that likely include OLTP databases. OLAP is used for analytics, reporting, data mining, and Business Intelligence. OLAP queries should have low response times. Query failure in OLAP will not affect transaction processing, but it may delay or reduce the accuracy of the information extracted from the OLAP database.

Extract-Transform-Load (ETL) is often used to extract data from OLTP sources, transform it, and load it into an OLAP system.

If you'd like more details about data warehouse terminology and design, see our articles on OLAP FOR OLTP PRACTITIONERS and WHAT YOU NEED TO KNOW ABOUT DATA WAREHOUSES.

OLTP and OLAP Database Models

A simple example of a NORMALIZED transactional database model is the link between an order and the products included in that order. One table stores all product data, one table stores order data, and a third table links each order to the items (products) that it contains.

In such a model, there is no duplication of data from the Product table into the Order table. We can create several products in the product table, then create orders that contain different combinations of products. The tables required for these transactions would look something like this:

In contrast, data warehouses generally have a STAR SCHEMA DESIGN that is based on fact and dimension tables; the star schema is the simplest model used in OLAP.

Fact tables contain reporting data that has been aggregated from related dimension tables. Its columns store values and reference dimension tables via foreign keys. When designing a fact table, you should keep in mind the concept of SPARSITY.

Dimension tables describe the data that is stored. Each dimension table has a category like orders, employees, and stores. Data warehouses use denormalized data; in other words, they have redundant data for the sake of performance.

Here’s an example of a star schema with FACT and DIMENSION tables:

The good news is that Vertabelo can help you create a database model for both OLTP and OLAP. As you see above, with Vertabelo, you can create models from simple to complex for both transaction processing and analytics.

OLTP vs. OLAP: Which Is Best for You?

The answer to that question will depend on the usage that will be made of the data. What are you going to do with the information stored in the database? What’s its business purpose?

To put it in technical terms, is the APPLICATION ORIENTED TO ONLINE TRANSACTION PROCESSING (OLTP) OR ONLINE ANALYTICAL PROCESSING (OLAP)? Your data model should be NORMALIZED FOR OLTP and DENORMALIZED FOR OLAP. That being said, you should only denormalize when you have a reason to do so. Denormalization leads to redundant data that will be difficult to maintain.

Online transactional processing (OLTP) is used for CRUD (Create, Read, Update, Delete) applications, while online analytical processing (OLAP) is used for reporting and Business Intelligence. The main difference between the underlying transactional and analytical databases is the nature of the application using the data.

A transactional database is designed for an application in which the user is more interested in CRUD, i.e. creating, reading, updating, and deleting records. An analytical database is designed for an application in which the user is more interested in analysis, reporting, and forecasting. In OLAP, the focus is to store data for analysis with no user maintenance of data. Thus, analytical databases rarely have inserts and updates. The goal of an analytical database is to analyze data quickly. When designing a data model, consider the usage of the data.

Vertabelo won't help you decide which type of database model you need, but once you have figured that out, the database modeler can help you create both OLTP and OLAP models. If you are not sure how, check out other articles related to OLTP (there are tons of articles on this, but you might start with OLAP FOR OLTP PRACTITIONERS) and OLAP: FACT TABLES, DIMENSION TABLES, STAR SCHEMAS, SNOWFLAKE SCHEMAS, and DATA WAREHOUSING, among other things.

Side-by-Side Comparison of OLTP vs. OLAP

First off, there is not a single type of database model that you should always use. As mentioned above, one main distinction is OLTP vs. OLAP; the one you choose will depend on how your application uses the data in your database.

Criteria	OLTP	OLAP
Purpose	Process transactions quickly.	Business intelligence or reporting.
Characteristic	Handles a large number of small transactions.	Handles large volumes of data.
Query type	Optimized for all kinds of simple standard queries, typically filtering for a few records.	Optimized for complex queries that aggregate multiple fact and dimension tables.
SQL Operations	INSERT, UPDATE, DELETE statements to update data and SELECT to read it.	SELECT statements to aggregate data.
Update	Short, fast updates are initiated by the user. Processes all kinds of frequently-occurring updates (insert, update, and delete).	Data is periodically refreshed via scheduled, long-running batch jobs. Optimized for reading data, typically run on a less frequent basis than OLTP.
Performance	Queries should execute for immediate response to requests, i.e. in milliseconds.	Queries execute in seconds, minutes, or hours, depending on the amount of data to process.
Data model type	NORMALIZED MODELS, LIKE BCNF, with many tables.	Denormalized model, usually with fewer tables and often based on a STAR SCHEME, SNOWFLAKE SCHEME, or similar.
Design	Industry-specific, such as retail, manufacturing, or banking.	Subject-specific, such as sales, inventory, or marketing.
Data quality	Efforts to ensure ACID COMPLIANCE.	Data may not be organized: what matters is the ability to manage the dimensions of the data.
Space requirements	Space will depend on the number of transactions to be processed and the length of online storage. Generally smaller than OLAP if historical data is archived.	Generally large, due to the aggregation of large datasets.
Availability	Generally, 24x7x365 is essential when transactions are performed every second of every day.	Interactions are less frequent; the absence of an OLAP system should not impact operations.
Use Case Examples	Operational: Applications used concurrently by many users, such as order entry, financial transactions, customer relationship management, and retail sales. Examples are online ticket bookings, banking, e-commerce websites, fintech, and other businesses where there are thousands or millions of transactions per day.	Informational: Trend analysis and data patterns, predicting risks and outcomes, generating reports, and tracking customer behavior and buying patterns. Examples include creating sales and marketing reports, preparing forecasts, and business process management.

In short, OLTP provides a record of transactional activity; OLAP provides insights from that data over time.

Combining OLTP and OLAP Systems

Both online transaction and analytic processing are essential parts of business data management. OLTP and OLAP are complementary to each other, as analytics can only be carried out based on effective OLTP systems. Based on the analytics requirements, an OLTP data model may need to be designed differently or re-designed to support changes in trends.

Original article source at: https://www.vertabelo.com

#database #analytics 

What is GEEK

Buddha Community

How native is React Native? | React Native vs Native App Development

If you are undertaking a mobile app development for your start-up or enterprise, you are likely wondering whether to use React Native. As a popular development framework, React Native helps you to develop near-native mobile apps. However, you are probably also wondering how close you can get to a native app by using React Native. How native is React Native?

In the article, we discuss the similarities between native mobile development and development using React Native. We also touch upon where they differ and how to bridge the gaps. Read on.

A brief introduction to React Native

Let’s briefly set the context first. We will briefly touch upon what React Native is and how it differs from earlier hybrid frameworks.

React Native is a popular JavaScript framework that Facebook has created. You can use this open-source framework to code natively rendering Android and iOS mobile apps. You can use it to develop web apps too.

Facebook has developed React Native based on React, its JavaScript library. The first release of React Native came in March 2015. At the time of writing this article, the latest stable release of React Native is 0.62.0, and it was released in March 2020.

Although relatively new, React Native has acquired a high degree of popularity. The “Stack Overflow Developer Survey 2019” report identifies it as the 8th most loved framework. Facebook, Walmart, and Bloomberg are some of the top companies that use React Native.

The popularity of React Native comes from its advantages. Some of its advantages are as follows:

• Performance: It delivers optimal performance.
• Cross-platform development: You can develop both Android and iOS apps with it. The reuse of code expedites development and reduces costs.
• UI design: React Native enables you to design simple and responsive UI for your mobile app.
• 3rd party plugins: This framework supports 3rd party plugins.
• Developer community: A vibrant community of developers support React Native.

Why React Native is fundamentally different from earlier hybrid frameworks

Are you wondering whether React Native is just another of those hybrid frameworks like Ionic or Cordova? It’s not! React Native is fundamentally different from these earlier hybrid frameworks.

React Native is very close to native. Consider the following aspects as described on the React Native website:

• Access to many native platforms features: The primitives of React Native render to native platform UI. This means that your React Native app will use many native platform APIs as native apps would do.
• Near-native user experience: React Native provides several native components, and these are platform agnostic.
• The ease of accessing native APIs: React Native uses a declarative UI paradigm. This enables React Native to interact easily with native platform APIs since React Native wraps existing native code.

Due to these factors, React Native offers many more advantages compared to those earlier hybrid frameworks. We now review them.

#android app #frontend #ios app #mobile app development #benefits of react native #is react native good for mobile app development #native vs #pros and cons of react native #react mobile development #react native development #react native experience #react native framework #react native ios vs android #react native pros and cons #react native vs android #react native vs native #react native vs native performance #react vs native #why react native #why use react native

Lean vs Agile vs Waterfall | What is Lean | Difference between Waterfall and Agile

In this video you will learn the difference between waterfall and agile model, what is lean, lean vs agile vs waterfall differences in detail.

Why DevOps is important?

DevOps implementation is going through the roof with most of the largest software organizations around the world invested heavily in its implementation. The core values of devops is effectively based on the Agile Manifesto but with one slight change which moves the focus from creating a working software to one that is more interested in the end-to-end software service mechanism and delivery.

#agile vs waterfall vs lean #Learn Lean #What is Lean #Waterfall vs Agile #Difference Between Waterfall and Agile

PWA vs Native App: Which Is Better Option In 2021?

Every year, the world is expanding with the launch of new smartphones and other gadgets available in the market. According to Statista, more than 50% of the population will be using smartphones by the end of 2021.

Hence, businesses worldwide have understood the importance of smartphones and are joining the mobile industry by launching native apps.

Apart from native apps, progressive web apps is another technology that is gaining a lot of attention among businesses. Moreover, various leading companies worldwide have openly accepted PWA and built progressive web apps.

Now, the question arises, how is PWA different from the native apps? Read More

#pwa vs native #pwa vs native app #progressive web app vs native #progressive web app vs native app #pwa vs native app performance

Hadoop vs Spark | Hadoop MapReduce vs Spark

In this video on Hadoop vs Spark you will understand about the top Big Data solutions used in the IT industry, and which one should you use for better performance. So in this Hadoop MapReduce vs Spark comparison some important parameters have been taken into consideration to tell you the difference between Hadoop and Spark also which one is preferred over the other in certain aspects in detail.

Why Hadoop is important

Big data hadoop is one of the best technological advances that is finding increased applications for big data and in a lot of industry domains. Data is being generated hugely in each and every industry domain and to process and distribute effectively hadoop is being deployed everywhere and in every industry.

#Hadoop vs Spark #Apache Spark vs Hadoop #Spark vs Hadoop #Difference Between Spark and Hadoop #Intellipaat

CockroachDB vs (MySQL, PostgreSQL, MongoDB & Cassandra)

An introduction to CockroachDB

If you are an entrepreneur or an enterprise IT leader, then you need to plan the technology stack for your software development project. You need to choose the right database for your project. If you are developing a heavy-duty transaction processing system or a high-demand analytics system, you will likely use an RDBMS (Relational Database Management System). You can choose one from several popular RDBMSs.

However, do you want an assurance that your data in an RDBMS will survive even large-scale failures in application systems and servers? This narrows the choice. This is where CockroachDB becomes important. This relatively new RDBMS offers a high degree of survivability to your data. In this article, we review CockroachDB and its features. We assess its pros and cons. Subsequently, we review its use cases. Finally, we compare CockroachDB with some of the popular databases. Read on.

#cockroachdb vs cassandra #cockroachdb vs mongodb #cockroachdb vs mysql #cockroachdb vs postgres #mysql