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