Thread: OLAP v OLTP: A concrete example?
11-27-09, 22:09 #1Registered User
- Join Date
- Nov 2009
OLAP v OLTP: A concrete example?
Hi - I'm trying to demystify the difference between OLTP and OLAP data modeling. I understand the difference conceptually, but what would crystalize things for me would be to see a simple data model represented twice: once in OLTP form, and once in OLAP form. Unfortunately, and surprisingly, I've been unable to find such a concrete example anywhere. Might anyone here have seen one they might be able to point me to?
11-28-09, 03:16 #2Resident Curmudgeon
- Join Date
- Feb 2004
- In front of the computer
OLTP is for transactional systems, such as you would find on a baseball coach's "play sheet". It shows every pitch, swing, etc. because the coach needs to look at that level of detail... They need to know which players bat well agains left handed pitchers and whne an opposing left fielder frequently fails to catch balls hit by right handed batters.
OLAP is for analytical systems, such as you'd see on the late night news. It shows the final scores of the games and possibly the team position in the league. At this level of detail, people are interested in which team won the game, and who is likely to play in the World Series.
The OLTP and OLAP systems track different information about the same system. The informaiton is at a different level of detail, and the OLAP system is derived from data in the OLTP system, but they are truly different systems tracking different information instead of just different levels of abstraction for a single thing.
-PatPIn theory, theory and practice are identical. In practice, theory and practice are unrelated.
11-28-09, 07:31 #3Registered User
- Join Date
- Dec 2007
- London, UK
OLAP and OLTP refer to different kinds of usage and technologies used to access data. They don't necessarily require differences in data models and the same data modelling principles can be applied in each case.
As a general rule decision support systems (aka "OLAP") need access to historical data which the operational ("OLTP") systems may not do. The operational systems are frequently organised around the needs of individual applications whereas the decision support data usually needs to represent a single, organisation-wide view.
Regarding Pat's example, the level of detail in each case is not necessarily a distinguishing feature. It is a good principle of design for decision support systems to store data to the finest level of detail possible because that detail may not be retained in operational systems and might not otherwise be available for future analysis.
I can recommend Bill Inmon's book, "Building the Data Warehouse".