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.

Transcript

1. Transactional vs.
   Analytical Processing
   Christopher Hertel
   

   View Slide

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