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 Slide

  2. 2

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  8. Example Application
    8
    SymfonyConBot

    View Slide

  9. • 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 Slide

  10. 10
    Example Application

    View Slide

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

    View Slide

  12. 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 Slide

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

    View Slide

  14. 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 Slide

  15. 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 Slide

  16. 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 Slide

  17. 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 Slide

  18. 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 Slide

  19. 19
    Example Application
    Interaction
    Conference User

    View Slide

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

    View Slide

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

    View Slide

  22. 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 Slide

  23. 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 Slide

  24. 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 Slide

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

    View Slide

  26. OLTP vs. OLAP
    26

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  30. OLTP vs. OLAP
    30
    Transactional vs. Analytical

    View Slide

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

    View Slide

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

    View Slide

  33. 33
    Example Application
    Interaction
    Conference User
    FAKE

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  39. Why bother at all? And when?
    39

    View Slide

  40. 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 Slide

  41. Designing the Schema
    41

    View Slide

  42. Designing the Schema
    42

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  49. The Takeaways
    49

    View Slide

  50. 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 Slide

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

    View Slide

  52. Back to the start …
    52

    View Slide

  53. 53

    View Slide

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

    View Slide

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

    View Slide

  56. Thank you!
    56

    View Slide

  57. Questions?
    57

    View Slide

  58. 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 Slide