(→Architecture) |
(→Data to report) |
||
| Line 218: | Line 218: | ||
FROM mw_revision | FROM mw_revision | ||
GROUP BY rev_page; | GROUP BY rev_page; | ||
| - | |||
| - | |||
| - | |||
| - | |||
| - | |||
| - | |||
| - | |||
| - | |||
| - | |||
| - | |||
| - | |||
| - | |||
| - | |||
| - | |||
| - | |||
| - | |||
| - | |||
| - | |||
| - | |||
=== IRC === | === IRC === | ||
| Line 268: | Line 249: | ||
group by `q_lines`.`ruid` | group by `q_lines`.`ruid` | ||
order by `q_activity_by_month`.`l_total` desc, `q_lines`.`ruid` asc limit 30 | order by `q_activity_by_month`.`l_total` desc, `q_lines`.`ruid` asc limit 30 | ||
| + | |||
| + | === Transifex === | ||
| + | * Total languages ordered by translation coverage (+ evolution) | ||
| + | * Top languages | ||
| + | * Top translators/teams | ||
| + | |||
| + | This all depends on what is available from Transifex. | ||
| + | |||
| + | === Git === | ||
| + | * Commits this month (+ evolution) | ||
| + | * Top committers (+ evolution) | ||
| + | * Committers by company (possible) | ||
| + | * Active modules (+ evolution) | ||
| + | |||
| + | Using a modified version of gitdm to dump Git logs into a MySQL database for analysis. Modifications required: | ||
| + | * Create database & tables based on the gitdm data structures | ||
| + | * Dump data in correct order, avoiding redundancy if possible, into SQL database | ||
| + | |||
| + | gitdm has 3 basic data structures: Hacker, Employer & Patch. Each changeset is a Patch object, each Patch has an Author, and is assigned to an Employer (based on who the Hacker was working for at the time of the Patch). Each Patch also has a list of Hackers who reviewed, reported, signed-off on and tested the patch. Each Hacker links to a list of Patches for which they are the author, a list of email addresses they have used to commit, and separate lists for reviewed, reported, SOB and tested. In addition, each Hacker has a list of Employers he has worked for, and each Employer has a list of Hackers who have worked for them. | ||
=== Community & Official OBS === | === Community & Official OBS === | ||
Contents |
The goal is to provide a web page summarising metrics about various aspects of the MeeGo project. The data should update regularly - depending on the metric, that could be real time or updated automatically on a regular basis.
The dashboard will track the following community resources, ideally:
The data should also be available for custom reports for usage and analysis in the monthly MeeGo Metrics report published by User:DawnFoster
To fulfill these goals, the dashboard will gather data from the various resource into a centralised database, using some sort of Business Intelligence platform including ETL for data acquisition and storage, and a reporting service for generating reports and dashboards.. A web page will provide a view into this database with predefined reports.
Candidate reporting solutions:
The following are essentially ETL engines, and do not provide reporting or dashboard functionality:
MuleSoft is an open source ESB, but does not seem adapted to our needs. The field is thus narrowed to Pentaho and JasperReports.
For each community resource, we need to figure out how to get the data into a usable form, and come up with appropriate queries for metrics reports, and finally present the results on a webpage.
The area of Business Intelligence is littered with acronyms. Here's a quick overview of the main ones, and how they all fit together.
So, in short, the community dashboard project will likely use an ETL to plug data into an OLAP server, and then use a business reporting engine to query that data and present it in a dashboard.
Modules available:
| Software | License | ETL | OLAP database | BI server | Reporting | Dashboard module |
|---|---|---|---|---|---|---|
| Pentaho | EPL | Kettle | Mondrian | Pentaho BI Platform | Pentaho Reporting | Community Dashboard Framework |
| Jaspersoft | AGPL v3 | JasperETL (Talend Open Studio) | JasperOLAP | JasperReports Server | iReports editor | No (commercial only) |
Pentaho is used as the basis of Mozilla's metrics project, and provides a very strong community software option for both the dashboard and for managing the BI server. Since Mozilla metrics work overlaps what we are trying to achieve, particularly their work on SQR, the Software Quality Reports analytic module for Bugzilla and JIRA, Pentaho is my preference for the dashboard project. In general, I have observed that the Pentaho community provides very good support.
Pentaho runs as a webapp in Tomcat6. It can use a variety of databases for its internal data structures, the default (Hypersonic) is a Java database. However, because it's both standard & well understood and to allow consolidation of databases under one DB server, I prefer to use MySQL. The configuration of Pentaho with a MySQL database is a little tricky, but almost all of the steps are covered well in this tutorial.
The data which is useful for metrics will be copied into a local database from each of the services we query. The copying of data will be accomplished by a set of Kettle "xactions", which can be created and edited easily with the Spoon tool.
A number of reports will be generated using the Pentaho Report Designer, including a static HTML/Flash dashboard which will be published regularly. Other reports can be created for the community managers, and a more advanced dashboard, allowing detailed analysis of basic metrics, can be provided via the Community Dashboard Framework.
We will need to see how much load the dashboard will generate on the server. I suspect that it will not be practical to expose the dashboard in public.
We will document here everything you need to do to replicate the MeeGo Community Dashboard, with the exception of data which is not publicly available because it contains security related or confidential information (mainly bugzilla).
For SQL databases, this implies that the server where the dashboard will run should have access to the database server for MediaWiki, Bugzilla, and Drupal.
For the forum, we will integrate the CSV files currently being exported, which provide the basic analytics we need.
Individual mailing lists will be parsed by MLStats. We will use the resulting database directly in the dashboard.
Git repositories will be queried with "git log", and parsed with the parser module from gitdm, before being stored directly in a database. we will be able to run analytics on the results from there. gitdm can also do basic analytics of git logs, and we may decide to simply reuse gitdm's analytics. However, if we want to extend them, we will want to have the raw data.
IRC logs will be parsed with superseriousstats, a PHP command line tool that parses IRC logs and stores the results in an SQL database.
We still need to figure out how to do data interchange with Transifex and OBS. Dimitris tells me that there are already some analytics available on Transifex, and that there is a RESTful API available to query this data.
For each of the resources, the following statistics (at a minimum) should be extracted:
select subject,year(first_date) as y, monthname(first_date),count(*) as c from messages group by subject, month(first_date) order by y, month(first_date), c;
select p.email_address,year(m.first_date) as y, monthname(m.first_date),count(*) as c from messages as m,messages_people as p where m.message_id=p.message_ID group by p.email_address, month(m.first_date) order by y, month(m.first_date), c;
where month(first_date)=3 and year(first_date)=2010 for March 2010. For the current month, month(m.first_date)=month(NOW()) and year(first_date)=year(NOW()) works.
Stats are exported from the Forum in CSV format monthly.
A MediaWiki extension exists to provide "user scores" for MediaWiki users, ordered by number of edits and number of pages changed. The guts of the query is:
SELECT COUNT(wr.rev_id) as value,
COUNT(DISTINCT wr.rev_page) as page_value,
wu.user_name as name,
wu.user_real_name as real_name
FROM $user wu,
$revision wr,
$page wp
WHERE wu.user_id = wr.rev_user
and wp.page_id = wr.rev_page
and wp.page_namespace = 0
GROUP BY wu.user_name
ORDER BY value desc;
where $user, $revision and $page are the names of the respective MediaWiki tables (MediaWiki tables have a prefix associated with them for a given instance, specified by $wgDBprefix in LocalSettings.php).
For the following group-by-month queries, I did a cross join of (2008,2009,2010,2011) and (01-12) to generate a "year and month" data table.
Top editors by month:
SELECT mon.timestamp_year AS yyyy,
mon.timestamp_month AS mm,
rev_user_text AS user,
COUNT(*) AS c
FROM $revision AS rev,
years_months AS mon
WHERE rev.rev_timestamp LIKE concat(concat(mon.timestamp_year,mon.timestamp_month),'%')
GROUP BY yyyy,mm,user
HAVING c>5
ORDER BY yyyy,mm,c desc;
Number of edits by month:
SELECT mon.timestamp_year as yyyy,
mon.timestamp_month as mm,
COUNT(*) AS edits
FROM $revision AS rev,
years_months as mon
WHERE rev.rev_timestamp LIKE concat(concat(mon.timestamp_year,mon.timestamp_month),'%')
GROUP BY yyyy,mm;
New pages per month: To get the number of new pages per month is a bit trickier - first we need to query $revision to get the page_ids and their date of creation, then group by date. The query is O(n²) on the number of pages, although it should be possible to make it O(n) by grouping the result of the subquery without doing in() on the list of timestamps.
SELECT mon.timestamp_year as yyyy,
mon.timestamp_month as mm,
COUNT(*)
FROM mw_revision as rev,
years_months as mon
WHERE rev.rev_timestamp LIKE CONCAT(CONCAT(mon.timestamp_year,mon.timestamp_month),'%')
AND rev.rev_timestamp in (
SELECT MIN(rev_timestamp)
FROM mw_revision
GROUP BY rev_page)
GROUP BY yyyy,mm;
To get just the list of pages & timestamps (this is used as the subquery for above):
SELECT rev_page as p,
MIN(rev_timestamp) as t
FROM mw_revision
GROUP BY rev_page;
superseriousstats does some preliminary analysis on data it stores in its database. Its author (tommyrot) has kindly added a parser for the format of the IRC logs we use (supybot) on my request. The database schema is a little hard to work out; Several key tables have fields with undescriptive names like l_01. There are some queries in html.class.php which we can use to generate some reports, though.
select sum(`l_00`) as `l_00`, sum(`l_01`) as `l_01`, sum(`l_02`) as `l_02`,
sum(`l_03`) as `l_03`, sum(`l_04`) as `l_04`, sum(`l_05`) as `l_05`,
sum(`l_06`) as `l_06`, sum(`l_07`) as `l_07`, sum(`l_08`) as `l_08`,
sum(`l_09`) as `l_09`, sum(`l_10`) as `l_10`, sum(`l_11`) as `l_11`,
sum(`l_12`) as `l_12`, sum(`l_13`) as `l_13`, sum(`l_14`) as `l_14`,
sum(`l_15`) as `l_15`, sum(`l_16`) as `l_16`, sum(`l_17`) as `l_17`,
sum(`l_18`) as `l_18`, sum(`l_19`) as `l_19`, sum(`l_20`) as `l_20`,
sum(`l_21`) as `l_21`, sum(`l_22`) as `l_22`, sum(`l_23`) as `l_23`
from `channel`
select `q_lines`.`ruid`, `csnick`,
sum(`q_activity_by_month`.`l_total`) as `l_total`,
sum(`q_activity_by_month`.`l_night`) as `l_night`,
sum(`q_activity_by_month`.`l_morning`) as `l_morning`,
sum(`q_activity_by_month`.`l_afternoon`) as `l_afternoon`,
sum(`q_activity_by_month`.`l_evening`) as `l_evening`,
`quote` from `q_lines`
join `q_activity_by_month` on `q_lines`.`ruid` = `q_activity_by_month`.`ruid`
join `user_status` on `q_lines`.`ruid` = `user_status`.`uid`
join `user_details` on `q_lines`.`ruid` = `user_details`.`uid`
where `status` != 3
and `date` = '2011-02'
group by `q_lines`.`ruid`
order by `q_activity_by_month`.`l_total` desc, `q_lines`.`ruid` asc limit 30
This all depends on what is available from Transifex.
Using a modified version of gitdm to dump Git logs into a MySQL database for analysis. Modifications required:
gitdm has 3 basic data structures: Hacker, Employer & Patch. Each changeset is a Patch object, each Patch has an Author, and is assigned to an Employer (based on who the Hacker was working for at the time of the Patch). Each Patch also has a list of Hackers who reviewed, reported, signed-off on and tested the patch. Each Hacker links to a list of Patches for which they are the author, a list of email addresses they have used to commit, and separate lists for reviewed, reported, SOB and tested. In addition, each Hacker has a list of Employers he has worked for, and each Employer has a list of Hackers who have worked for them.
I have not yet considered how I might get web analytics and download stats.