Reporting and the First Law of Holes

Ah, reporting. Every application developer’s bane. It starts with yesterday’s sales. Then top-selling countries. How well each category does. Which items have been popular in the last month. The business needs these reports and they grow, bit by bit, over time. Before you know it, you’re in a hole. Running expensive reports on the production systems is a performance killer. It gets hard to extract meaningful reporting data out of your ODBMS. Schema updates in your relational database break everything. If you have a data warehouse (and eventually, you’ll need one) the ETL process is painful and slow.

There’s another way. A better way. A simple pattern that is fast, scalable and robust, saving blood, sweat, and tears. It’s a pattern you will want in your application from day one, but works even if you have a legacy application with reporting problems. You’re in deep, but fear not, for the First Law of Holes will save the day.

Transcript

1. Reporting and the First Law of Holes
2. Sebastian von Conrad
3. Work President Organiser Citizen @ @ @ @ Envato Ruby Australia RubyConf AU 2015 Sweden (There will be some Swedish in this presentation.)
4. Story time!
5. This happened to me. Pretty much everywhere I’ve worked.
6. Image credits: http://www.gifbooster.com/pin/2345/troll-face-stick-figure-dances_58
7. Sale Item User
8. Item Category
9. User Profile
10. Image credits: http://www.gifbooster.com/pin/2345/troll-face-stick-figure-dances_58
11. Yesterday’s sales? Mission:
12. We can solve this with ActiveRecord! This is the point where we realise:
13. Sale.where(created_at: ).count # or, a named scope Sale.yesterday.count
14. Let’s automate!
15. Reporting admin panel!
16. We’re in a hole. It’s a small one, but still a hole.
17. Image credits: http://www.gifbooster.com/pin/2345/troll-face-stick-figure-dances_58
18. Last month’s top countries? Mission:
19. Sale User Profile
20. sales = Sale.includes(user: :profile) by_country = sales.group_by { |s| s.user.country } by_country.map do |(country, records)| { country: country, sales: records.count } end
21. sales = Sale.includes(user: :profile) by_country = sales.group_by { |s| s.user.country } by_country.map do |(country, records)| { country: country, sales: records.count } end InEfficient
22. Sale.select('country, count(sales.id)') .join(:user).join(:profile) .where('sales.created_at BETWEEN ? AND ?', ) .group(:country)
23. Sale.select('country, count(sales.id)') .join(:user).join(:profile) .where('sales.created_at BETWEEN ? AND ?', ) .group(:country) Messy
24. We can solve this with SQL! This is the point where we realise:
25. SELECT p.country, count(s.id) AS num_sales FROM sales s INNER JOIN users u ON s.user_id = u.id INNER JOIN user_profiles p ON p.user_id = u.id WHERE s.created_at BETWEEN AND GROUP BY p.country;
26. Sweet! Let’s add it to the admin panel.
27. But we don’t have time to update the existing Ruby reports.
28. And so the hole gets a little bit deeper.
29. Image credits: http://www.gifbooster.com/pin/2345/troll-face-stick-figure-dances_58
30. Which categories do well in which countries? Mission:
31. Sale Item User Category Profile
32. SELECT p.country, c.name, count(s.id) AS num_sales FROM sales s INNER JOIN items i ON s.item_id = i.id INNER JOIN categories c ON i.category_id = c.id INNER JOIN users u ON s.user_id = u.id INNER JOIN user_profiles p ON p.user_id = u.id WHERE s.created_at BETWEEN AND GROUP BY p.country, c.name;
33. We never liked writing SQL in the first place. This is the point where we realise:
34. SQL is not DRY. SQL is ugly. Ruby is DRY. Ruby is beautiful.
35. Ruby is great for defining custom DSLs. This is the point where we realise:
36. …like a custom DSL for writing SQL queries.
37. reports/generic_accounts do name 'Categories/countries' description 'Category sales per country' column_groups do [['Total', :sum], ['Average', :average], ['Count', :count]].each do |group_title, function| reports/column_group(title: group_title) do columns do categories.product(countries).each do |(category, country)| reports/column do title "#{category}/#{country}" aggregate_type function account :sales code :purchase filter [{ category: category, country: country }] end end end end end end end
38. reports/generic_accounts do name 'Categories/countries' description 'Category sales per country' column_groups do [['Total', :sum], ['Average', :average], ['Count', :count]].each do |group_title, function| reports/column_group(title: group_title) do columns do categories.product(countries).each do |(category, country)| reports/column do title "#{category}/#{country}" aggregate_type function account :sales code :purchase filter [{ category: category, country: country }] end end end end end end end Nightmare
39. Add to admin panel, just for the heck of it!
40. The hole is pretty big by now.
41. But we’re not done yet, are we?
42. Image credits: http://www.gifbooster.com/pin/2345/troll-face-stick-figure-dances_58
43. Items featured (and not) on homepage? Mission:
44. We can do this, because we think about the future. Right?
45. + ------- + ---------------- + -------------- + | item_id | feature_start_at | feature_end_at | | ------- | ---------------- | -------------- | | 34292 | 2015-02-01 | 2015-02-03 | | 64233 | 2015-02-02 | 2015-02-03 | | 77245 | 2015-02-05 | 2015-02-05 | | 212 | 2015-01-23 | 2015-02-08 | | 22196 | 2015-02-06 | 2015-02-08 | | ... | ... | ... | + ------- + ---------------- + -------------- +
46. No, we’re not.
47. + ------- + ---------------- + -------------- + | id | price | category_id | ... | featured | | --- | ----- | ------------ | --- | -------- | | 209 | 40.00 | 112 | ... | false | | 210 | 34.00 | 2 | ... | false | | 211 | 78.00 | 61 | ... | false | | 212 | 53.00 | 54 | ... | true | | 213 | 12.00 | 14 | ... | false | | ... | ... | ... | ... | ... | + ------- + ---------------- + -------------- +
48. We don’t have historical data.
49. We can’t solve this problem. This is the point where we realise:
50. Not only are we in a hole, it can’t even do what we need it to.
51. So why are we in this hole?
52. The way we build applications does not cater well to reporting.
53. As a result, our database is good for the application, but bad for reporting.
54. join.join.join
55. Sub-optimal indexing.
56. Oh so brittle. Schema changes suck.
57. You have to understand the domain in order to understand the data.
58. Expensive reporting queries can hurt prod.
59. ORM maintains current state, not history.
60. (ORMs are bad.)
61. First law of holes: if you find yourself in one, stop digging. - Will Rogers, 1911
62. Step 1: What do you care about? Step 2: ??? Step 3: ??? Step 4: ??? Step 5: ???
63. Image credits: http://www.gifbooster.com/pin/2345/troll-face-stick-figure-dances_58
64. Not a trick question!
65. Events!
66. What entities are? What do they do?
67. Signup. Sale. Subscription. Cancellation. Refund. Etc.
68. The events are key.
69. Step 1: What do you care about? Step 2: What do you know? Step 3: ??? Step 4: ??? Step 5: ???
70. When the event happens, what info do you have?
71. Collect everything.
72. Buyer. Who are they? Where are they? When did they sign up? Previous purchases?
73. Buyer. Who are they? Where are they? When did they sign up? Previous purchases? Seller. Who are they? Where are they? When did they sign up? Previous sales?
74. Buyer. Who are they? Where are they? When did they sign up? Previous purchases? Seller. Who are they? Where are they? When did they sign up? Previous sales? Transaction. Amount? Payment gateway? Coupon? Tax?
75. Buyer. Who are they? Where are they? When did they sign up? Previous purchases? Seller. Who are they? Where are they? When did they sign up? Previous sales? Transaction. Amount? Payment gateway? Coupon? Tax? Item. Category? Featured on homepage? Price?
76. Buyer. Who are they? Where are they? When did they sign up? Previous purchases? Seller. Who are they? Where are they? When did they sign up? Previous sales? Transaction. Amount? Payment gateway? Coupon? Tax? Item. Category? Featured on homepage? Price? Category. Number of items? Price range?
77. Buyer. Who are they? Where are they? When did they sign up? Previous purchases? Seller. Who are they? Where are they? When did they sign up? Previous sales? Transaction. Amount? Payment gateway? Coupon? Tax? Item. Category? Featured on homepage? Price? Category. Number of items? Price range? Not exhaustive
78. Step 1: What do you care about? Step 2: What do you know? Step 3: JSON.generate() Step 4: ??? Step 5: ???
79. (Doesn’t have to be JSON.)
80. Denormalised. Immutable. Descriptive.
81. job_id job_purchased_at service_id service_name service_price_in_cents service_turnaround_in_days service_revision_requests service_currently_featured service_created_at service_approved_at service_approved_by_id service_approved_by_envato_id service_approved_by_username service_approved_by_full_name service_approved_by_email_address service_category_id service_category_name service_category_top_level_name service_category_min_price_in_cents service_category_max_price_in_cents service_category_parent_id service_category_parent_name duration_between_approval_and_purchase_in_seconds service_enquiry_count service_enquiry_message_count custom_job buyer_login_method buyer_id buyer_envato_id buyer_username buyer_full_name buyer_email_address buyer_ip_address buyer_city buyer_country buyer_account_created_at provider_id provider_envato_id provider_username provider_full_name provider_email_address provider_city provider_country provider_account_created_at provider_became_provider_at payment_uuid payment_gateway_name payment_gateway_reference payment_discount_amount_in_cents payment_charged_amount_in_cents coupon_code coupon_expiry coupon_name coupon_discount_percentage coupon_discount_dollar_value_in_cents
82. Step 1: What do you care about? Step 2: What do you know? Step 3: JSON.generate() Step 4: Where do you want it? Step 5: ???
83. Easiest difficulty: Log table.
84. (PostgreSQL hstore.)
85. Moderate difficulty: Log file (+ Hadoop?).
86. Expert difficulty: Data warehouse.
87. Step 1: What do you care about? Step 2: What do you know? Step 3: JSON.generate() Step 4: Where do you want it? Step 5: GOTO 1
88. Rinse, repeat.
89. You can add more data, but never backfill. (Unless your job is on the line.)
90. Combine event logs for realtime data.
91. Step 1: What do you care about? Step 2: What do you know? Step 3: JSON.generate() Step 4: Where do you want it? Step 5: GOTO 1
92. Decouple reporting from application.
93. Application generates data, not reports.
94. Application must not be allowed to read the data.
95. But other things can!
96. Build reports in tools designed to build reports.
97. Example: Export to .csv, import in Excel.
98. Easier to maintain. Easier to scale.
99. Easier all the way. Seriously.
100. Immutability <insert superlatives>.
101. * Caveats.
102. Image credits: http://www.gifbooster.com/pin/2345/troll-face-stick-figure-dances_58
103. Image credits: http://forum.deviantart.com/devart/drawplz/1704047
104. Tack så mycket. (I told you there’d be a little bit of Swedish.)