Upgrade to Pro — share decks privately, control downloads, hide ads and more …

Transactional vs. Analytical Processing

Transactional vs. Analytical Processing

This talk was given at SymfonyCon Disneyland Paris 2022.

When it comes to the design of your Symfony application, data plays a central role. From an architectural point of view there are two common ways this data is processed. Transactional processing ensures that changes to your data are consistent and safe. Analytical processing aims to make even complex queries fast and efficient.
We should always consider the nature of processing while implementing our application’s use cases. So let's have a look at the main criterias, strategies and trade offs that will help us to navigate through all the options we have and see how we can bring your own data warehouse to life in your Symfony application leveraging tools like Doctrine, Messenger and more.

Christopher Hertel

November 17, 2022
Tweet

More Decks by Christopher Hertel

Other Decks in Technology

Transcript

  1. Transactional vs.
    Analytical Processing
    Christopher Hertel

    View full-size slide

  2. Let’s talk about data
    3
    … and decision making

    View full-size slide

  3. What’s the best database?
    4
    „It depends …“

    View full-size slide

  4. Depends on what?
    5
    People & Skills
    Infrastructure
    Current System
    Time & Budget
    Requirements • What kind of data?
    • What is the use-case?

    View full-size slide

  5. Today’s Questions
    • What types of data are there?
    • How do we interact with data?
    • Why bother at all? And when?
    6

    View full-size slide

  6. Christopher Hertel
    Software Architect in Berlin
    @el_stoffel
    7
    Who is that guy?

    View full-size slide

  7. Example Application
    8
    SymfonyConBot

    View full-size slide

  8. • Telegram Bot “SymfonyConBot”
    • Features
    • Access SymfonyCon’s schedule
    • Lookup current and next talks
    • Search for talks and speakers
    • Confirm attendance of a talk
    • Rate a talk
    • Build with Symfony
    9
    Example Application

    View full-size slide

  9. 10
    Example Application

    View full-size slide

  10. Let’s have a look at the data
    11
    What types of data are there?

    View full-size slide

  11. 12
    Example Application
    live.symfony.com Telegram App
    User
    • Id
    • First name
    • Last name
    Talk
    • Title
    • Speaker
    • Description
    • Slot
    • Track
    Slot
    • Start date
    • End date
    Track
    • Name
    Rating
    • Talk
    • User
    • Stars
    Attendance
    • Talk
    • User

    View full-size slide

  12. Qualitative vs. Quantitative
    13
    What types of data are there?

    View full-size slide

  13. 14
    Example Application
    live.symfony.com Telegram App
    User
    • Id
    • First name
    • Last name
    Talk
    • Title
    • Speaker
    • Description
    • Slot
    • Track
    Slot
    • Start date
    • End date
    Track
    • Name
    Rating
    • Talk
    • User
    • Stars
    Attendance
    • Talk
    • User

    View full-size slide

  14. 15
    Qualitative Data
    User
    • Id
    • First name
    • Last name
    Talk
    • Title
    • Speaker
    • Description
    • Slot
    • Track
    Slot
    • Start date
    • End date
    Track
    • Name
    Rating
    • Talk
    • User
    • Stars
    Attendance
    • Talk
    • User
    Nominal Data
    • Names/Labels
    • Not ordered
    • Potentially static
    Ordinal Data
    • Ordered
    • Sequence
    • No arithmetics
    • Not numeric
    • Not measurable
    • Categorical data
    • Content
    Qualitative

    View full-size slide

  15. 16
    Quantitative Data
    User
    • Id
    • First name
    • Last name
    Talk
    • Title
    • Speaker
    • Description
    • Slot
    • Track
    Slot
    • Start date
    • End date
    Track
    • Name
    Rating
    • Talk
    • User
    • Stars
    Attendance
    • Talk
    • User
    Discrete Data
    • Only integers
    • Limited
    • No subdivision
    • Quantified
    • Numeric
    • Measurable
    • Process Artifacts
    Quantitative
    Continuous Data
    • Processing
    • Any numeric value
    • Unlimited
    Average Rating of Talk

    View full-size slide

  16. Quantitative
    • Quantified
    • Numeric
    • Measurable
    • Process Artifacts
    Discrete Data
    • Only integers
    • Limited
    • No subdivision
    Continuous Data
    • Processing
    • Any numeric value
    • Unlimited
    Qualitative
    • Not numeric
    • Not measurable
    • Categorical data
    • Content
    Nominal Data
    • Names/Labels
    • Not ordered
    • Potentially static
    Ordinal Data
    • Ordered
    • Sequence
    • No arithmetics
    Types of Data
    17

    View full-size slide

  17. 18
    Example Application
    live.symfony.com Telegram App
    User
    • Id
    • First name
    • Last name
    Talk
    • Title
    • Speaker
    • Description
    • Slot
    • Track
    Slot
    • Start date
    • End date
    Track
    • Name
    Rating
    • Talk
    • User
    • Stars
    Attendance
    • Talk
    • User

    View full-size slide

  18. 19
    Example Application
    Interaction
    Conference User

    View full-size slide

  19. Facts vs. Dimensions
    20
    What types of data are there?

    View full-size slide

  20. 21
    Dimensions
    Interaction
    Conference User
    Conference
    • Basis of business process
    • Descriptive data
    • Horizontal tables
    • Larger share in schema
    • Initial structure
    Dimension Tables

    View full-size slide

  21. 22
    Facts
    Interaction
    Conference User
    Interaction
    • Outcome of business process
    • Numbers & foreign keys
    • Vertical tables
    • Less often than dimensions
    • Comes after dimensions
    Fact Tables

    View full-size slide

  22. Dimension Tables
    • Basis of business process
    • Descriptive data
    • Horizontal tables
    • Larger share in schema
    • Initial structure
    Slots & Talks
    Dimensions vs. Facts
    23
    Fact Tables
    • Outcome of business process
    • Numbers & foreign keys
    • Vertical tables
    • Less often than dimensions
    • Comes after dimensions
    Attendance & Ratings

    View full-size slide

  23. 24
    Example Application
    live.symfony.com Telegram App
    User
    • Id
    • First name
    • Last name
    Talk
    • Title
    • Speaker
    • Description
    • Slot
    • Track
    Slot
    • Start date
    • End date
    Track
    • Name
    Rating
    • Talk
    • User
    • Stars
    Attendance
    • Talk
    • User

    View full-size slide

  24. Data Processing
    25
    How do we interact with data?

    View full-size slide

  25. OLTP vs. OLAP
    26

    View full-size slide

  26. OLTP vs. OLAP
    27
    Online
    Transactional
    Processing
    Online
    Analytical
    Processing

    View full-size slide

  27. OLTP vs. OLAP
    28
    Online
    Transactional
    Processing
    Online
    Analytical
    Processing

    View full-size slide

  28. OLTP vs. OLAP
    29
    Online
    Transactional
    Processing
    Online
    Analytical
    Processing

    View full-size slide

  29. OLTP vs. OLAP
    30
    Transactional vs. Analytical

    View full-size slide

  30. Transactional Processing[OLTP]
    • Focus on transaction
    • Operative systems
    • Validation and consistency
    • CRUD on single records
    • Real time
    • Well defined
    31
    Table

    View full-size slide

  31. Analytical Processing[OLAP]
    • Focus on analysis
    • Reporting systems
    • Filtering and reducing
    • Across multiple records
    • Periodically
    • Variable
    32
    Table

    View full-size slide

  32. 33
    Example Application
    Interaction
    Conference User
    FAKE

    View full-size slide

  33. 34
    Schedule Management
    Interaction
    Conference User
    • Read & write
    • Relational data
    • Validity & consistency
    • No metrics nor analysis
    • Real time

    View full-size slide

  34. 35
    Schedule Querying
    Interaction
    Conference User
    • Only read
    • Ordinal data
    • Sequence
    • Relational

    View full-size slide

  35. 36
    Schedule Search
    Interaction
    Conference User
    • Only read
    • Nominal data
    • Fuzzy
    • Delay is okay
    Index

    View full-size slide

  36. 37
    Attending & Rating a talk
    Interaction
    Conference User
    • Append only
    • Facts
    • Discrete data
    • Real time

    View full-size slide

  37. 38
    Conference Analysis
    Interaction
    Conference User
    • Read only
    • Calculations
    • Filtering
    • Delay is okay
    Views

    View full-size slide

  38. Why bother at all? And when?
    39

    View full-size slide

  39. Common pitfalls
    40
    • Monolithic persistence
    => Everything in the same tables
    • Missing isolation
    => Which classes write in this table?
    • Performance issues
    => Mixing engine & user land logic
    Maintaining,
    Slicing & Scaling
    Systems

    View full-size slide

  40. Designing the Schema
    41

    View full-size slide

  41. Designing the Schema
    42

    View full-size slide

  42. 43
    Flow of Data
    OLTP
    OLAP
    Database
    INSERT INTO …
    SELECT X FROM …

    View full-size slide

  43. 44
    Flow of Data
    OLTP
    OLAP
    Database
    INSERT INTO …
    SELECT X FROM …
    Database
    Worker

    View full-size slide

  44. 45
    Flow of Data
    Database
    Interaction Conference
    OLTP
    WRITE
    OLTP
    WRITE
    OLAP
    READ
    OLAP
    READ

    View full-size slide

  45. 46
    Flow of Data
    Conference
    OLTP
    WRITE
    OLAP
    READ Index
    Interaction View
    OLTP
    WRITE
    OLAP
    READ
    EventBus

    View full-size slide

  46. 47
    Flow of Data
    Source, see “Further Reading”

    View full-size slide

  47. 48
    Example Decisions
    OLTP OLAP
    QUALITATIVE
    QUANTITATIVE
    ELASTICSEARCH
    INFLUXDB
    MEILISEARCH
    REDIS
    CLICKHOUSE
    SUPERSET
    MONGO DB
    POSGRESQL
    MYSQL
    SQLITE

    View full-size slide

  48. The Takeaways
    49

    View full-size slide

  49. The Takeaways
    • Consider the data structure & role in business processes early on
    • Focus on data processing while choosing persistence engine
    • Don’t always use MySQL, sane start though
    • Decouple data like you do with software components & services
    • Don’t avoid redundancy by all means
    • Consider the flow of data, evaluate on change & don’t act too early
    50

    View full-size slide

  50. New Features Requests
    51
    Requirements
    Type & Structure
    Maintaining, Slicing & Scaling a System
    Flow & Processing

    View full-size slide

  51. Back to the start …
    52

    View full-size slide

  52. Redis
    CLI Application
    How is that solved?
    54
    OLTP
    WRITE

    View full-size slide

  53. Redis
    CLI Application
    How is that solved?
    55
    Web Application
    Packagist
    Database
    PERIODICAL
    AGGREGATION
    OLTP
    WRITE
    OLAP
    READ

    View full-size slide

  54. Thank you!
    56

    View full-size slide

  55. Questions?
    57

    View full-size slide

  56. Further Reading
    • SymfonyCon Bot
    Source code on GitHub github.com/chr-hertel/symfonycon-bot
    • 6 Types of Data in Statistics & Research: Key in Data Science
    By Silvia Valcheva www.intellspot.com/data-types/
    • SQL vs. NoSQL Database: When to Use, How to Choose
    By Satish Chandra Gupta www.ml4devs.com/articles/datastore-choices-sql-vs-nosql-database/
    • Difference between Fact Table and Dimension Table
    By MKS075 www.geeksforgeeks.org/difference-between-fact-table-and-dimension-table/
    • Emerging Architectures for Modern Data Infrastructure
    By Bornstein, Li & Casado future.com/emerging-architectures-modern-data-infrastructure/
    58

    View full-size slide