Explain By Example: OLTP vs OLAP — Transcript

Hi YouTube, welcome to another Explain by Example video. Today, we're going to take a look at OLTP versus OLAP. I remember in my first semester of university, I came across OLTP and OLAP in my information systems course. I read the textbook definition and didn't quite understand it, but rather than asking my professors about it, I memorized the definitions and prayed that it wouldn't come up in my exams. A couple of years down the road, I came across it again and thought maybe it's time I'll try to learn the differences, and what better way to do this than explain it to myself with an example. So here we go.

didn't quite understand it but rather than asking my professors about it i memorized the definitions and pray that it wouldn't come up in my exams a couple of years down the road i came across it again and thought

Let's go back to school. The school contains students, teachers, and the principal. The school also interacts with parents, and the school puts all of their data into a single database. The student data, the teacher data, and the course data all go into the same database. Now, if you don't know what a database is, it's essentially a place that stores data in some sort of structured way. Think of a telephone book, for example. If you have ever used a physical telephone book, you would know that a telephone book stores contact details, which is data, in an alphabetical ordering, which is also known as a structured format. Except with this telephone book, you can make changes to it. For example, you can cross out numbers, you can update it, you can add to it as well, you know, add new contacts, or you can even remove some of the contacts by ripping out the pages.

the school also interacts with parents and the school puts all of their data into a single database the student data the teacher data and the course data all goes into the same database now if you don't know what a database is

As you can imagine, over time, the school population grows, the school offering grows, so naturally, the data it needs to store will also grow with it. If a student has graduated from the school more than five years ago, do we really want to keep his or her data in the school systems? There's a couple of options: A for yes, B for no, and C for I don't know.

ordering which is also known as a structured format except with this telephone book you can make changes to it for example you can cross out numbers you can update it you can add to it as well you know add new

Now, the answer is D for data warehouse. Data warehouses are used to store aggregated data that is generally more high level than the data found in the database and usually less real-time for analytical purposes. For example, it may store some of the data from the students that graduated more than five years ago so that the school can use it to track things like how many students have graduated from the school in the last decade. That's a very simple use case, but you get the idea that students' data may no longer be relevant in the database that should only really contain data that is up to date and relevant to the school's current state. But it doesn't mean that their data is completely irrelevant just because they left the school five years ago.

if a student has graduated from the school more than five years ago do we really want to keep his or her data in the school systems there's a couple of options a for yes b for no and c for i don't know

So how does OLTP and OLAP come into play? Because the type of data stored in the database is different from a data warehouse, it makes sense that the type of information a database can provide differs from the information that a data warehouse can provide. Think about your parents going to your school and asking the teachers and the principal the same questions. Question 1: How is my child doing in class? And question 2: Will my child succeed at the school?

data from the students that graduated more than five years ago so that the school can use it to track things like how many students have graduated from the school in the last decade that's a very simple use case but you get the

If your parents asked your teacher question one, they would probably get a response that is along the lines of, "Oh, Johnny is doing very well in class at the moment. You should be proud of him," or "Johnny is a bit of a slacker and keeps distracting his peers with his Pokémon Go nonsense." If your parents asked the principal question 1, they would probably respond with, "Who?"

because they left the school five years ago so how does oltp and olap come into play because the type of data stored in the database is different to a data warehouse it makes sense that the type of information a database can provide

If we think of the teacher as the database and the principal as a data warehouse, the teacher gets to see Johnny every day. They teach and interact with Johnny every day, and so they would have the most up-to-date information about Johnny at school. The principal might be able to find out that information if they remembered which class Johnny was taught in, but that's really inefficient, and you might run into the issue that the principal did not keep track of which class Johnny belongs to at all.

succeed at the school if your parents asked your teacher question one they would probably get a response that is along the lines of oh johnny is doing very well in class at the moment you should be proud of him

On the other hand, if your parents ask the teacher question two, the teacher won't have enough data to tell your parents straight away. They might be able to tell your parents eventually once they've hunted down all the teachers and all the classes to find out the class average or how well Johnny has been doing in other classes. If your parents ask the principal question 2, the principal will say straight away, "Our school has experienced a 95% passing rate over the last five years, so I can extremely and confidently say that your child will also succeed at our school."

database and the principal as a data warehouse the teacher gets to see johnny every day they teach and interact with johnny every day and so they would have the most up-to-date information about johnny at school the principal might be able to find out

The principal, or the data warehouse in this case, aggregates all the data that is necessary from students, teachers, and classes, which are the databases, over time to make big bold claims or analytical statements like that one.

your parents ask the teacher question two the teacher won't have enough data to tell your parents straight away they might be able to tell your parents eventually once they've hunted down all the teachers and all the classes to find

So far, you probably have a better understanding of what OLTP versus OLAP means than the definitions provided by Wikipedia, which states OLTP is online transaction processing information systems that facilitate and manage transaction-oriented applications, or OLAP, which is online analytical processing, is an approach to answer multi-dimensional analytical queries swiftly also. So basically, OLTP are the type of queries or operations executed on a database, and OLAP are the type of queries executed in a data warehouse.

extremely and confidently say that your child will also succeed at our school the principal or the data warehouse in this case aggregates all the data that is necessary from students teachers and classes which are the databases over time to make big bold claims or

Now, let's say the school got its funding cut, and the principal suddenly has to reduce the school staff. They know the mathematics department has been getting a lot of complaints lately, so the principal simply asks the school database, "Hey, which teacher should I get rid of based on the number of complaints?"

or olap which is online analytical processing is an approach to answer multi-dimensional analytical craze swiftly also so basically oltp are the type of queries or operations executed on a database and olap are the type of queries executed in a data warehouse now

All right, so just a note: the principal is no longer acting as a data warehouse in this scenario anymore, but an actual human principal. Or in a happier scenario, the school receives a funding grant, so now they have to choose which department to invest in. Let's say the data warehouse is built to be able to answer that exactly. So by aggregating the data from the classes, in which case these are the educational departments and the finance departments, in the data warehouse, the principal can execute OLAP queries to find out that the science department has been heavily underfunded.

based on the number of complaints all right so just a note the principal is no longer acting as a data warehouse in this scenario anymore but an actual human principle or in a happier scenario the school receives a funding grant so now they

And there you have it. That is the blog post on OLTP versus OLAP. I hope you learned something from that, and I hope you enjoyed this video. Remember to check out my blog at medium.com/atmichelle.z for more content. Otherwise, don't forget to like, subscribe, and comment if you enjoyed this video. Thank you for watching.

in the data warehouse the principal can execute olap queries to find out that the science department has been heavily underfunded and there you have it that is the blog post on oltp versus olap i hope you learned something from that and i hope

you enjoyed this video remember to check out my blog at medium.com forward slash at michelle.z for more content otherwise don't forget to like subscribe and comment if you enjoyed this video thank you for watching