498792 - Extract data from SUMO db and put it in datawarehouse

Status: RESOLVED FIXED

(support.mozilla.org :: Knowledge Base Software, task)

Description

[[:Cww]]

Attachment: first pass at data warehouse scripts

https://wiki.mozilla.org/Support/MetricsDashboardPRD/Setup has the needed steps.

The attached files are the pentaho scripts for a few metrics.  (They are an early version which may have usernames and passwords inside, therefore this bug is security+).

Comment 1

[[:Cww]]

Attachment: files as a patch with security sensitive stuff removed

Patch for SVN.  This has all the security sensitive stuff removed.   Usernames, DB names and passwords need to be put into kettle.properties (see kettle.properties.dist for template) and that needs to be in ~/.kettle/ for the user running the metrics updates (annoying as heck but I can't find another way right now).

Comment 2

[Laura Thomson :laura]

Comment on attachment 383842 [details] [diff] [review]
files as a patch with security sensitive stuff removed

this is fine.

For your next patch, generate it from the root dir of your checkout so it's obvious to the reviewer where it goes (was fine in this case).

Comment 3

[[:Cww]]

Committed revision 28019

Comment 4

[[:Cww]]

r28031 on prod

Comment 5

[Laura Thomson :laura]

Committed = resolved fixed

Comment 6

[[:Cww]]

Attachment: dw schema

In order to run the scripts you need the database already set up with tables etc.  This patch just adds the schema file to svn.

Comment 7

[[:Cww]]

Attachment: patch from /scripts

File should be in /metrics  (Also don't need review since this is the schema that's already running on sumotools and reviewed with the other patch in this bug)

Also committed in trunk and prod as r28046/r28047

Comment 8

[[:Cww]]

Attachment: adds a job file to pre-fill the date table (run once)

Another patch on a fixed bug (sorry... I apparently just realized we should ALSO include the script file that initializes the DB tables for kettle)

When I was setting up on sumotools, I just copied over the database from my local copy which is why it never occurred to me to also bring over the files required to set them up.

Comment 9

[[:Cww]]

Comment on attachment 384132 [details] [diff] [review]
adds a job file to pre-fill the date table (run once)

getting review

Comment 10

[Laura Thomson :laura]

Attachment: bit of SQL that was missing from dwschema, was needed to fix stage

Comment 11

[Laura Thomson :laura]

Once this patch is r+, I'll need to commit the patches from comments #8 and #10.  Reopening and assigning to myself until that's done.

Comment 12

[Laura Thomson :laura]

patch from #10 in r28495 / r28496.

Comment 13

[Laura Thomson :laura]

patch from #8 in r28499 / r28504.

Reclosing.

Comment 14

[Laura Thomson :laura]

Apparently I have a bug in #10 (database name is included, needs to be out).  Recommitting in r28505 / r28506.

Comment 15

[Will Kahn-Greene [:willkg] ET needinfo? me]

These bugs are all resolved, so I'm removing the security flag from them.