$30 off During Our Annual Pro Sale. View Details »

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

  2. 2

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

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

  5. Depends on what? 5 People & Skills Infrastructure Current System

    Time & Budget Requirements • What kind of data? • What is the use-case?
  6. Today’s Questions • What types of data are there? •

    How do we interact with data? • Why bother at all? And when? 6
  7. Christopher Hertel Software Architect in Berlin @el_stoffel 7 Who is

    that guy?
  8. Example Application 8 SymfonyConBot

  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
  10. 10 Example Application

  11. Let’s have a look at the data 11 What types

    of data are there?
  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
  13. Qualitative vs. Quantitative 13 What types of data are there?

  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
  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
  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
  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
  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
  19. 19 Example Application Interaction Conference User

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

  21. 21 Dimensions Interaction Conference User Conference • Basis of business

    process • Descriptive data • Horizontal tables • Larger share in schema • Initial structure Dimension Tables
  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
  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
  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
  25. Data Processing 25 How do we interact with data?

  26. OLTP vs. OLAP 26

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

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

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

  30. OLTP vs. OLAP 30 Transactional vs. Analytical

  31. Transactional Processing[OLTP] • Focus on transaction • Operative systems •

    Validation and consistency • CRUD on single records • Real time • Well defined 31 Table
  32. Analytical Processing[OLAP] • Focus on analysis • Reporting systems •

    Filtering and reducing • Across multiple records • Periodically • Variable 32 Table
  33. 33 Example Application Interaction Conference User FAKE

  34. 34 Schedule Management Interaction Conference User • Read & write

    • Relational data • Validity & consistency • No metrics nor analysis • Real time
  35. 35 Schedule Querying Interaction Conference User • Only read •

    Ordinal data • Sequence • Relational
  36. 36 Schedule Search Interaction Conference User • Only read •

    Nominal data • Fuzzy • Delay is okay Index
  37. 37 Attending & Rating a talk Interaction Conference User •

    Append only • Facts • Discrete data • Real time
  38. 38 Conference Analysis Interaction Conference User • Read only •

    Calculations • Filtering • Delay is okay Views
  39. Why bother at all? And when? 39

  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
  41. Designing the Schema 41

  42. Designing the Schema 42

  43. 43 Flow of Data OLTP OLAP Database INSERT INTO …

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

    SELECT X FROM … Database Worker
  45. 45 Flow of Data Database Interaction Conference OLTP WRITE OLTP

    WRITE OLAP READ OLAP READ
  46. 46 Flow of Data Conference OLTP WRITE OLAP READ Index

    Interaction View OLTP WRITE OLAP READ EventBus
  47. 47 Flow of Data Source, see “Further Reading”

  48. 48 Example Decisions OLTP OLAP QUALITATIVE QUANTITATIVE ELASTICSEARCH INFLUXDB MEILISEARCH

    REDIS CLICKHOUSE SUPERSET MONGO DB POSGRESQL MYSQL SQLITE
  49. The Takeaways 49

  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
  51. New Features Requests 51 Requirements Type & Structure Maintaining, Slicing

    & Scaling a System Flow & Processing
  52. Back to the start … 52

  53. 53

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

  55. Redis CLI Application How is that solved? 55 Web Application

    Packagist Database PERIODICAL AGGREGATION OLTP WRITE OLAP READ
  56. Thank you! 56

  57. Questions? 57

  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