Transactional vs. Analytical Processing Christopher Hertel

2

Let’s talk about data 3 … and decision making

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

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

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

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

Example Application 8 SymfonyConBot

• 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 Example Application

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

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

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

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

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

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

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

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

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

Data Processing 25 How do we interact with data?

OLTP vs. OLAP 26

OLTP vs. OLAP 27 Online Transactional Processing Online Analytical Processing

OLTP vs. OLAP 28 Online Transactional Processing Online Analytical Processing

OLTP vs. OLAP 29 Online Transactional Processing Online Analytical Processing

OLTP vs. OLAP 30 Transactional vs. Analytical

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

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

33 Example Application Interaction Conference User FAKE

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

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

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

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

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

Why bother at all? And when? 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

Designing the Schema 41

Designing the Schema 42

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

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

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

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

47 Flow of Data Source, see “Further Reading”

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

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

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

Back to the start … 52

53

Redis CLI Application How is that solved? 54 OLTP WRITE

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

Thank you! 56

Questions? 57

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