May 14, 2020 at 7:07 am
Hello Everyone, I am learning DBMS and I am confused to understand the difference between OLAP and OLTP in terms of storage space. As I know, OLAP systems ask for significant storage space. This is due to the existence of aggregation structures and historical data, which calls for much more indexes than a typical OLTP system. Can anyone know is it the right information as i know, OLTP is less storage? If someone knows the head to head comparison of both of them, please explain me.
May 14, 2020 at 3:32 pm
That is a big "it depends" question. OLAP is used for reporting and analytics. OLTP is used for transactional data.
If you have a bunch of data used for application purposes, but not used for reporting or analytics, your OLAP for that specific application/process will be TINY while the OLTP for it may be huge.
On the other hand if you have some weird set of processing for some data or need to keep track of a lot of historical data, you may have a much larger OLAP than OLTP. For example, in a OLTP system you may not care what the price of a bag of flour was a year ago; just the current price. But in an OLAP system you may want to see the trend over time.
As to which is "less storage", it depends. You may not need to report on ALL data, and you may have a very small set of data to report on. At my workplace, our non-reporting databases (ie OLTP) are over 1 TB in size, whereas the reporting database is only 150 GB. This is because we do not do reporting and analytics against ALL of our data. Some of it is used for very specific purposes and has no reporting needs. Or is PII in which case we obfuscate the data or only pull across the non-PII (that is usually the case) prior to putting it in the reporting database. For example, customers we sell products to, there is no need for us to do reporting on their physical address, but we care about how many units we sell to a specific customer per month, quarter, and year. But if they change their location from USA to Canada to Mexico or whatever, we do not need to report on that bit of data and thus it doesn't go into the OLAP. But some companies may care about that data.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
May 18, 2020 at 1:36 pm
That is a big "it depends" question. OLAP is used for reporting and analytics. OLTP is used for transactional data.
If you have a bunch of data used for application purposes, but not used for reporting or analytics, your OLAP for that specific application/process will be TINY while the OLTP for it may be huge.
On the other hand if you have some weird set of processing for some data or need to keep track of a lot of historical data, you may have a much larger OLAP than OLTP. For example, in a OLTP system you may not care what the price of a bag of flour was a year ago; just the current price. But in an OLAP system you may want to see the trend over time.
As to which is "less storage", it depends. You may not need to report on ALL data, and you may have a very small set of data to report on. At my workplace, our non-reporting databases (ie OLTP) are over 1 TB in size, whereas the reporting database is only 150 GB. This is because we do not do reporting and analytics against ALL of our data. Some of it is used for very specific purposes and has no reporting needs. Or is PII in which case we obfuscate the data or only pull across the non-PII (that is usually the case) prior to putting it in the reporting database. For example, customers we sell products to, there is no need for us to do reporting on their physical address, but we care about how many units we sell to a specific customer per month, quarter, and year. But if they change their location from USA to Canada to Mexico or whatever, we do not need to report on that bit of data and thus it doesn't go into the OLAP. But some companies may care about that data.
This is a good answer.
Still I would recommend you to go through with this article to know more and get the deeper insights about Storage Space Requirements in OLAP and OLTP
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply