Reporting Databases

Lennart Regebro

Plone Conference 2016

You need a reporting database!

What?

• Made to do reporting easy
• Not afraid of duplicating data
• Can integrate data from several sources
• Often, but not necessarily, "read-only"

When?

• When transaction performance is critical
• If you have multiple databases
• If the reports are long running
• If you need to do "massage" the data

How?

• Separate database
• Maybe even separate server
• Asynchronous updates
• Data must be rebuildable
• Use a star or snowflake schema

Star schemas

The Date Dimension

Extract, Transform, Load

Extract

Big Batch

• May bog down database server
• Can run on separate server instance
• Ready made tools exist!

Change Data Capture

• Risk inconsistent data if data is lost or one update fails
• No long running main database usage

Change detection

• Time stamps
• Sequenced IDs
• Database logs

Update on Change

• Database triggers/events
• Application events
• Near-real-time updates

Transform

Load

TADA!

Reporting tools

Libreoffice Base

BIRT

Pentaho Community Edition

Libraries/Frameworks

• Reportlab

How we do this at Shoobx

• Near real-time
• Super sensitive customer data
• Reports available to run as needed

That means

• Update on change
• One database per customer
• Pentaho for the reports

Good infrastructure

• Only one source for data
• App is written by us
• We already had events and Celery

Extract

Events -> Celery -> Collect data -> REST call

Transform and Load

• Flask
• Sqlalchemy + PostgreSQL
• REST API

Reporting

Queries

SELECT
     "entity_dim"."title" AS Entity,
     "process_dim"."definition_title" AS Process,
     "workitem_type_dim"."title" AS Workitem_type,
     "workitem_fact"."started" AS Started,
     "user_dim"."name" AS User
FROM
     "public"."workitem_fact" "workitem_fact"
     LEFT OUTER JOIN "public"."user_dim" "user_dim"
         ON "workitem_fact"."participant_user" = "user_dim"."id"
     INNER JOIN "public"."workitem_type_dim" "workitem_type_dim"
         ON "workitem_fact"."type" = "workitem_type_dim"."id"
     INNER JOIN "public"."entity_dim" "entity_dim"
         ON "workitem_fact"."entity" = "entity_dim"."id"
     INNER JOIN "public"."process_dim" "process_dim"
         ON "workitem_fact"."process" = "process_dim"."id"
WHERE
     "workitem_fact"."priority" >= 0
 AND "workitem_fact"."finished" IS NULL
 AND "entity_dim"."id" = ${ENTER_ENTITY_ID}
ORDER BY
     "entity_dim"."title" DESC,
     "process_dim"."definition_title" DESC,
     "workitem_type_dim"."title" DESC

Some problems

• How do you know if data is missing? - You don't.
• Synchronous updates slow down the app. Async updates can come in the wrong order. - timestamps
• requests library does NOT timeout by default! As a result Celery workers would get stuck.
• For speed we wanted multi-threaded server, but we got massive write conflicts! - Choose the right conflict strategy on the postgresl server.

Questions?

http://slides.colliberty.com/PloneConference-2016