Results 1 to 3 of 3
  1. #1
    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?


  2. #2
    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.

    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    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".

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts