Reporting Databases

Lennart Regebro

Plone Conference 2016

You need a reporting database!

What?

When?

How?

Star schemas

The Date Dimension

Column

Value

Column

Value

id

20160906

weekday

2

date

<2016-09-06>

weekday_name

'Tuesday'

day

6

weekday_abbr

'Tue'

day_ordinal

'6th'

is_weekend

False

month

9

dow_in_month

1

month_name

'September'

day_of_year

250

month_abbr

'Sep'

week_of_month

2

month_of_quarter

3

week_of_year

36

quarter

3

iso_week_of_year

36

quarter_ordinal

'3rd'

mmyyyy

'092016'

month_year

'09-1026'

is_holiday

False

year

2016

holiday_name

''

year_name

'CY 2016'

Extract, Transform, Load

Extract

Big Batch

Change Data Capture

Change detection

Update on Change

Transform

Load

TADA!

Reporting tools

Libreoffice Base

BIRT

Pentaho Community Edition

Libraries/Frameworks

How we do this at Shoobx

That means

Good infrastructure

Extract

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

Transform and Load

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

Questions?

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

SpaceForward
Right, Down, Page DownNext slide
Left, Up, Page UpPrevious slide
POpen presenter console
HToggle this help