MeeGo wiki - User contributions [en]

Metrics/Dashboard

2011-10-18T09:35:17Z

Dneary:

File:Meego metrics summary.prpt

2011-10-18T09:27:20Z

Dneary: MeeGo metrics monthly summary statistics. All data is for the previous calendar month. Statistics are grouped to allow qualitative and quantitative analysis to be done afterwards.

MeeGo metrics monthly summary statistics. All data is for the previous calendar month. Statistics are grouped to allow qualitative and quantitative analysis to be done afterwards.

Metrics/Bugzilla queries

2011-10-14T18:34:46Z

Dneary: Created page with " === BZ bugs opened and resolved month by month === <pre> <nowiki> select opened.monthnum as monthnum, opened.monthstr as monthstr, opened_count, resolved_count from ( SEL..."

=== BZ bugs opened and resolved month by month ===

<pre>
<nowiki>
select
opened.monthnum as monthnum,
opened.monthstr as monthstr,
opened_count,
resolved_count
from
(
SELECT
date_format(creation_ts,'%Y%m') as monthnum,
date_format(creation_ts,'%b %y') as monthstr,
count(bugs.bug_id) as opened_count
FROM bugs
GROUP BY monthnum
) AS opened
INNER JOIN
(
SELECT
date_format(bug_when, '%Y%m') AS monthnum,
date_format(bug_when, '%b %y') AS monthstr,
COUNT(*) as resolved_count
FROM bugs_activity
WHERE
fieldid=9 AND
added='RESOLVED'
GROUP BY monthnum
) AS resolved
ON
opened.monthnum = resolved.monthnum
where opened.monthnum <= date_format(${last_month},'%Y%m')
ORDER BY opened.monthnum;
</nowiki>
</pre>

=== BZ active users last month ===

<pre>
<nowiki>
select
count(distinct userid)
from
(
select
ba.who as userid,
ba.bug_when as action_date
from bugs_activity ba
where
date_format(ba.bug_when,'%Y%m')=date_format(${last_month},'%Y%m') and
ba.fieldid in (2,4,5,6,7,8,9,10,11,12,13,14,15,16,18,19,30,35,36,37,38,40,41,42,47,55,56,57,58)
group by action_date,userid
union all
select
b.reporter,
b.creation_ts
from bugs b
where
date_format(b.creation_ts,'%Y%m')=date_format(${last_month},'%Y%m')
) as filtered_actions;
</nowiki>
</pre>

=== BZ active users month by month ===

<pre>
<nowiki>
select
date_format(action_date,'%Y%m') as monthnum,
date_format(action_date,'%b %y') as monthstr,
count(distinct userid)
from
(
select
ba.who as userid,
ba.bug_when as action_date
from bugs_activity ba
where
ba.fieldid in (2,4,5,6,7,8,9,10,11,12,13,14,15,16,18,19,30,35,36,37,38,40,41,42,47,55,56,57,58)
group by action_date,userid
union all
select
b.reporter,
b.creation_ts
from bugs b
) as filtered_actions
where date_format(action_date,'%Y%m') <= date_format(${last_month},'%Y%m')
group by monthnum
order by monthnum;
</nowiki>
</pre>

=== BZ top 20 bug reporters last month ===

<pre>
<nowiki>
SELECT
profiles.realname AS username,
COUNT(bugs.bug_id) AS bugcount
FROM bugs
INNER JOIN profiles
ON
bugs.reporter = profiles.userid
WHERE date_format(creation_ts,'%Y%m')=date_format(${last_month},'%Y%m')
GROUP BY username
ORDER BY bugcount DESC
LIMIT 20;
</nowiki>
</pre>

=== BZ top 20 active users last month ===

<pre>
<nowiki>
select
p.realname,
count(*) as activity_count
from
(
select
ba.bug_id as bug_id,
ba.who as userid,
ba.bug_when as date
from bugs_activity ba
where
date_format(ba.bug_when,'%Y%m')=date_format(${last_month},'%Y%m') and
ba.fieldid in (2,4,5,6,7,8,9,10,11,12,13,14,15,16,18,19,30,35,36,37,38,40,41,42,47,55,56,57,58)
group by ba.bug_when,ba.who
union all
select
b.bug_id,
b.reporter,
b.creation_ts
from bugs b
where
date_format(b.creation_ts,'%Y%m')=date_format(${last_month},'%Y%m')
order by bug_id
) as filtered_actions
inner join profiles p
on
(
p.userid=filtered_actions.userid
)
group by filtered_actions.userid
order by activity_count desc
limit 20;
</nowiki>
</pre>

Metrics/Forum queries

2011-10-14T18:32:48Z

Dneary: Created page with " The forum database structure could not be simpler, it mirrors precisely [http://forum.meego.com/stats/ the CVS files which are generated every month]. === Forum posts per forum..."

The forum database structure could not be simpler, it mirrors precisely [http://forum.meego.com/stats/ the CVS files which are generated every month].

=== Forum posts per forum by month ===

<pre>
<nowiki>
SELECT
`forum_posts`.`month` as month,
`forum_posts`.`year` as year,
date_format(date_add( makedate(year, 1), interval month-1 MONTH), '%b %Y') as monthstr,
concat(cast(year as char), LPAD(cast(month as char), 2, '0')) as monthnum,
`forum_posts`.`forum` as forum,
`forum_posts`.`posts` as posts
FROM
`forum_posts`
ORDER BY
`forum_posts`.`forum` ASC,
`forum_posts`.`year` ASC,
`forum_posts`.`month` ASC
</nowiki>
</pre>

=== Forum top 20 posters last month ===

<pre>
<nowiki>
SELECT
`forum_top_posters`.`month`,
`forum_top_posters`.`year`,
`forum_top_posters`.`rank`,
`forum_top_posters`.`member`,
`forum_top_posters`.`posts`,
monthname(${last_month}) AS monthname
FROM
`forum_top_posters`
WHERE
month = month(${last_month})
AND year = year(${last_month})
limit 20
</nowiki>
</pre>

=== Forum most viewed threads last month ===

<pre>
<nowiki>
SELECT
month,
year,
rank,
title,
views
FROM
`forum_most_viewed_threads`
WHERE
month=MONTH(${last_month})
AND
year=YEAR(${last_month})
ORDER BY
views ASC
</nowiki>
</pre>

=== Forum posts per forum cumulative ===

<pre>
<nowiki>
SELECT
`forum_cumulative_posts`.`month`,
`forum_cumulative_posts`.`year`,
`forum_cumulative_posts`.`forum`,
`forum_cumulative_posts`.`posts`
FROM
`forum_cumulative_posts`
ORDER BY
`forum_cumulative_posts`.`forum`,
`forum_cumulative_posts`.`year` ASC,
`forum_cumulative_posts`.`month`
</nowiki>
</pre>

=== Forum top thanks last month ===

<pre>
<nowiki>
SELECT
`forum_top_thanked`.`month`,
`forum_top_thanked`.`year`,
`forum_top_thanked`.`rank`,
`forum_top_thanked`.`member`,
`forum_top_thanked`.`thanks`
FROM
`forum_top_thanked`
WHERE
month = month(${last_month})
AND year = year(${last_month})
order by rank
limit 20
</nowiki>
</pre>

=== Forum threads per forum cumulative ===

<pre>
<nowiki>
SELECT
`forum_cumulative_threads`.`month`,
`forum_cumulative_threads`.`year`,
date_format(date_add( makedate(year, 1), interval month-1 MONTH), '%b %Y') as monthstr,
concat(cast(year as char), LPAD(cast(month as char), 2, '0')) as monthnum,
`forum_cumulative_threads`.`forum`,
`forum_cumulative_threads`.`threads`
FROM
`forum_cumulative_threads`
ORDER BY
`forum_cumulative_threads`.`forum` ASC,
`forum_cumulative_threads`.`year` ASC,
`forum_cumulative_threads`.`month` ASC
</nowiki>
</pre>

=== Forum top 10 posters last month ===

<pre>
<nowiki>
SELECT
`forum_top_posters`.`month`,
`forum_top_posters`.`year`,
`forum_top_posters`.`rank`,
`forum_top_posters`.`member`,
`forum_top_posters`.`posts`,
monthname(${last_month}) AS monthname
FROM
`forum_top_posters`
WHERE
month = month(${last_month})
AND year = year(${last_month})
limit 10
</nowiki>
</pre>

=== Forum posts per forum last 4 months ===

<pre>
<nowiki>
SELECT
`forum_posts`.`month` as month,
`forum_posts`.`year` as year,
date_format(date_add( makedate(year, 1), interval month-1 MONTH), '%b %Y') as monthstr,
concat(cast(year as char), LPAD(cast(month as char), 2, '0')) as monthnum,
`forum_posts`.`forum` as forum,
`forum_posts`.`posts` as posts
FROM
`forum_posts`
where
month>=MONTH(DATE_SUB(NOW(), INTERVAL 4 MONTH)) and
year>=YEAR(DATE_SUB(NOW(), INTERVAL 4 MONTH))
ORDER BY
`forum_posts`.`forum` ASC,
monthnum ASC
</nowiki>
</pre>

=== Forum repeat posters last month ===

<pre>
<nowiki>
SELECT
count(`forum_top_posters`.`member`) as active_count,
monthname(${last_month}) AS monthname
FROM
`forum_top_posters`
WHERE
month = month(${last_month})
AND year = year(${last_month})
AND `forum_top_posters`.`posts`>=2
</nowiki>
</pre>

=== Forum posters last month ===

<pre>
<nowiki>
SELECT
count(`forum_top_posters`.`member`) as poster_count,
monthname(${last_month}) AS monthname
FROM
`forum_top_posters`
WHERE
month = month(${last_month})
AND year = year(${last_month})
</nowiki>
</pre>

=== Forum total posts last month ===

<pre>
<nowiki>
select
sum(posts) as total_posts
FROM
forum_posts
WHERE
forum_posts.month = MONTH(${last_month})
AND
forum_posts.year = YEAR(${last_month})
</nowiki>
</pre>

=== Forum hottest threads last month ===

<pre>
<nowiki>
SELECT
`forum_hottest_threads`.`month`,
`forum_hottest_threads`.`year`,
`forum_hottest_threads`.`rank`,
`forum_hottest_threads`.`title`,
`forum_hottest_threads`.`posts`
FROM
`forum_hottest_threads`
WHERE
`forum_hottest_threads`.`month`=month(${last_month})
AND
`forum_hottest_threads`.`year`=year(${last_month})
ORDER BY
`forum_hottest_threads`.`rank` ASC
LIMIT 10
</nowiki>
</pre>

Metrics/IRC queries

2011-10-13T15:59:24Z

Dneary: Created page with " For IRC, we use [https://github.com/tommyrot/superseriousstats/wiki superseriousstats] with a daily cron job. The database stores aggregated data. Its schema is a little funky, ..."

For IRC, we use [https://github.com/tommyrot/superseriousstats/wiki superseriousstats] with a daily cron job. The database stores aggregated data. Its schema is a little funky, but effective enough. As before lastmonth is a parameter containing a date one month ago.

=== Hour by hour (all time) ===

<pre>
<nowiki>
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`
</nowiki>
</pre>

=== Top participants ===

<pre>
<nowiki>
select `q_lines`.`ruid`,
`csnick` as nick,
`l_total` as total,
`l_night` as night,
`l_morning` as morning, `l_afternoon` as afternoon,
`l_evening` as evening,
`quote` from `q_lines`
join `user_details`
on `q_lines`.`ruid` = `user_details`.`uid`
join `user_status`
on `q_lines`.`ruid` = `user_status`.`uid`
where `status` != 3
order by `l_total` desc,
`q_lines`.`ruid` asc
limit 20
</nowiki>
</pre>

=== Hour by hour (last month) ===

<pre>
<nowiki>
select 'Time of day (UTC)' as timeofday,
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`
where month(`date`) = month(DATE_SUB(CURDATE(),INTERVAL 1 MONTH)) and
year(`date`) = year(DATE_SUB(CURDATE(),INTERVAL 1 MONTH))
;
</nowiki>
</pre>

=== Month by month (<= last month) ===

<pre>
<nowiki>
select date_format(`date`, '%Y-%m') as `date`,
date_format(date, '%b %y') as monthname,
month(date) as month,
year(date) as year,
sum(`l_total`) as total,
sum(`l_night`) as night,
sum(`l_morning`) as morning,
sum(`l_afternoon`) as afternoon,
sum(`l_evening`) as evening
from `channel`
where year(date) <= year(${last_month}) and
month(date) <= month(${last_month})
group by year(`date`), month(`date`);
</nowiki>
</pre>

=== Top participants last month ===

<pre>
<nowiki>
select `q_lines`.`ruid`,
`csnick` as nick,
sum(`q_activity_by_month`.`l_total`) as total,
sum(`q_activity_by_month`.`l_night`) as night,
sum(`q_activity_by_month`.`l_morning`) as morning,
sum(`q_activity_by_month`.`l_afternoon`) as afternoon,
sum(`q_activity_by_month`.`l_evening`) as 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` = date_format(${last_month}, '%Y-%m')
group by `q_lines`.`ruid`
order by `q_activity_by_month`.`l_total` desc,
`q_lines`.`ruid` asc
limit 20
</nowiki>
</pre>

=== Popular words ===

<pre>
<nowiki>
select `total` as `v1`,
`word` as `v2`
from `words`
where length(`word`) > 4
order by `v1` desc,
`v2` asc
limit 20
</nowiki>
</pre>

=== Day by day (last month) ===

<pre>
<nowiki>
select `date`,
`l_total` as total,
`l_night` as night,
`l_morning` as morning,
`l_afternoon` as afternoon,
`l_evening` as evening
from `channel`
where
year(date) = year(${last_month}) and
month(date) = month(${last_month});
</nowiki>
</pre>

== SSS database schema ===

The schema reflects the fact that there is already aggregation done by SSS when it reads logs. The database schema is [https://github.com/tommyrot/superseriousstats/blob/master/empty_database_v4.sql documented in the SSS source code].

Metrics/Dashboard

2011-10-13T14:19:24Z

Dneary: /* Architecture */

== Community Metrics Dashboard ==

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:
* Drupal members
* Bugzilla (bugs opened, bugs closed, active users)
* Mailing lists (members, posts, threads)
* gitorious (commits, employer details for committers) - should use Jon Corbet's scripts like are used in the LF yearly kernel data.
* Wiki (edits, new pages)
* Forums (members, posts)
* IRC (total comments, people on channel)
* Transifex (Languages, translators, strings translated)
* Community OBS (uploads, users)
* SDK downloads (potentially extrapolated from meego.com)

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:

* [http://jasperforge.org/index.php?q=project/jasperreports JasperReports]
* [http://www.pentaho.com/ Pentaho]

The following are essentially ETL engines, and do not provide reporting or dashboard functionality:

* [http://www.talend.com/index.php Talend]
* [http://petals.ow2.org/ Petals]

[http://www.mulesoft.com/ 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.

=== Business intelligence engines ===

The area of Business Intelligence is littered with acronyms. Here's a quick overview of the main ones, and how they all fit together.

; BI
: Business Intelligence - general name for any middleware which allows you to query business processes (sales, inventory, etc) and get data overviews from it
; ETL
: Extract, Transform, and Load - the process if extracting data from a data source (database, screen scraping, text file parsing, whatever), transforming it to a well understood format, and loading it in your BI engine database or data warehouse. Good ETL solutions provide a nice way for you to connect another database and have new data sucked in at regular intervals, define views into the source data store which you can then query within your BI engine, etc. Pentaho's ETL, [http://kettle.pentaho.com/ Kettle], and [http://www.jaspersoft.com/jasperetl JasperETL], used by JasperReports, both provide (kind of) straightforward ways to hook into a MySQL database.
; ESB
: [http://en.wikipedia.org/wiki/Enterprise_service_bus Enterprise Service Bus] - a middleware bus providing a unique interface to applications on the front-end and data stores on the back end. Often used to link up many front-end applications (eg. library, student registration, employee payroll, syllabus management, accounting, supply-chain, student lodgement programmes, etc in a university). Not really useful for us, as far as I can tell.
; EAI
: Enterprise Application Integration - using software to integrate different applications together. As far as I can tell, this is a meaningless catch-all phrase for anything from kludges to architected business intelligence solutions.
; DW
: Data Warehouse. Basically the same thing as a database, as far as I can tell, but bigger and more impressive sounding.
; OLAP
: On-Line Analytical Processing. Commonly used acronym for extracting data via multi-dimensional queries. Databases can be configured to provide the results of this kind of query. As far as I can tell this is mostly a buzzword - an "OLAP database" like [http://mondrian.pentaho.com/ Mondrian] is basically the same thing as a database. "speed-of-thought" response times indeed.
; Business reporting
: An application which allows a graphical view of a database, and allows you to construct queries interactively, often using drag & drop. The results of these queries can then be plugged into graphing software for presentation in a dashboard.
; Dashboard
: Organised presentation of information in a web-page or other similar format allowing an at-a-glance overview of the situation for the data being measured.

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.

=== Comparison of candidate ETL/reporting ===

Modules available:

{|
! Software !! License !! ETL !! OLAP database !! BI server !! Reporting !! Dashboard module
|-
| Pentaho || EPL || [http://kettle.pentaho.com/ Kettle] || [http://mondrian.pentaho.com/ Mondrian] || [http://community.pentaho.com/projects/bi_platform/ Pentaho BI Platform] || [http://reporting.pentaho.com/ Pentaho Reporting] || [http://wiki.pentaho.com/display/COM/Community+Dashboard+Framework Community Dashboard Framework]
|-
| [http://www.jaspersoft.com/editions 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.

== Architecture ==

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 [https://docs.google.com/Doc?docid=0AdJmocc0fj_EZDJ3YmZiZF83M2RtaHhwcmRk&hl=en 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).

* [[../Installing Pentaho]]: A guide to getting Pentaho up and running for MeeGo Dashboard
* [[../Gathering data]]: A guide to installing and using the tools to gather data for a dashboard
* [[../Creating a report]]: Given data in a database, how do we generate a report in Pentaho, and deploy it to the dashboard?

* [[../Mailing list queries]]: SQL queries against the MLStats database
* [[../MediaWiki queries]]: SQL queries against a <nowiki>MediaWiki</nowiki> database
* [[../IRC queries]]: SQL queries against the superseriousstats database
* [[../Forum queries]]: SQL queries against the forum database
* [[../Bugzilla queries]]: SQL queries against the Bugzilla database

=== Extracting data ===

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 integrate the CSV files currently being exported, which provide the basic analytics we need.

Individual mailing lists are parsed by [http://forge.morfeo-project.org/projects/libresoft-tools/ MLStats]. We use the resulting database directly in the dashboard.

IRC logs will be parsed with [http://code.google.com/p/superseriousstats/ superseriousstats], a PHP command line tool that parses IRC logs and stores the results in an SQL database.

Git repositories will be queried with "git log", and parsed with the parser module from [http://lwn.net/Articles/290957/ 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.

We still need to figure out how to do data interchange with Transifex and OBS. Dimitris tells me that there are already [http://meego.transifex.net/stats/ some analytics] available on Transifex, and that there is a RESTful API available to query this data.

== Data to report ==

For each of the resources, the following statistics (at a minimum) should be extracted:

=== Drupal ===
* Members of meego.com (+ evolution month over month)
* Active members (need a decent way to hook up different ways a person can be active: wiki, ML, IRC, forum, git)

=== Mailing lists ===
* Subscriber numbers (+ evolution) - from Mailman directly, not available in mlstats
* Emails sent (+ evolution) - from mlstats
* Active participants (individuals with >=2 emails during month) - from mlstats
* Hot threads - from mlstats
* Top posters - from mlstats

==== Useful queries ====

* Count posts of most popular threads:
*: <code>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;</code>
* Count the number of posts for each person
*: <code>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;</code>
* Restricting queries to a date range / month
*: The query above gives month-by-month totals, you could add an order by year(first_date) to get the year too
*: The easiest way is to add <code>where month(first_date)=3 and year(first_date)=2010</code> for March 2010. For the current month, <code>month(m.first_date)=month(NOW()) and year(first_date)=year(NOW())</code> works.

=== Forum ===
* Posts per month (+evolution)
* Active posters (2+ posts during month)
* Hot topics
* Top posters

[http://forum.meego.com/stats/ Stats] are exported from the Forum in CSV format monthly.

=== Bugzilla ===
* Bugs created (+ evolution)
* Bugs resolved (+ evolution)
* Comments on bugs this month
* Active Bugzilla contributors (2+ comments during month)

==== Useful queries ====

* [http://sourceforge.net/projects/qareports/ Software Quality Reports for Pentaho] of course
* See the [http://bugs.meego.com/query.cgi?format=report-table bugzilla tabular reports] and relative dates for more.
* Other potential resources:
** [http://www.ravenbrook.com/project/p4dti/tool/cgi/bugzilla-schema/ Bugzilla database schema by version]
** [https://landfill.bugzilla.org/bugzilla-tip/report.cgi Demo site for standard Bugzilla reports]
** [https://wiki.mozilla.org/Bugzilla:SQLCookBook The Bugzilla SQL cookbook]
** [http://www.bugzillametrics.org/ Bugzilla Metrics] - Third party add-on to Bugzilla that produces lots of metrics
** browse.cgi and weekly-summary.html extensions of GNOME Bugzilla: [https://launchpad.net/bugzilla.gnome.org Code], examples in practice: [http://bugzilla.gnome.org/browse.cgi?product=Evolution browse.cgi], [https://bugzilla.gnome.org/page.cgi?id=weekly-bug-summary.html weekly-summary.cgi]

=== Mediawiki ===
* New wiki pages (+ evolution)
* Edits this month (+ evolution)
* Pages deleted this month (+ evolution)
* Unique editors this month (+ evoluion)

==== Queries ====

A [http://www.mediawiki.org/wiki/Extension:SpecialUserScore 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;

=== IRC ===

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 [https://github.com/tommyrot/superseriousstats/blob/master/empty_database_v3.sql 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 [https://github.com/tommyrot/superseriousstats/blob/master/html.class.php html.class.php] which we can use to generate some reports, though.

* Total IRC activity (by hour)
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`
* Total active participants (+ evolution) - we may be able to get "number of participants per hour/day/month" (so you can see if it's 2 guys taking amongst themselves or a larger group) - I'll ask tommyrot what the query should look like.
* Top contributors (per month)
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

=== 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 ===
* Package submissions (+ evolution)
* Active participants (+ evolution)

== Not yet in scope ==

I have not yet considered how I might get web analytics and download stats.

Metrics/MediaWiki queries

2011-10-13T14:19:07Z

Dneary: MediaWiki queries & data structure.

A word of warning with <nowiki>MediaWiki</nowiki>: when initialising the database, a table prefix is used for table names, which is configured on a per-wiki database (this allows different <nowiki>MediaWiki</nowiki> instances to be stored in the same database instance, which is kind of a useless feature, but anyway...). On wiki.meego.org, this prefix is set to "", while the detault <nowiki>MediaWiki</nowiki> prefix is "mw_", and this is what I used. Ideally, we would do like Wikipedia, and have a set of parameters containing the table names which would be used: <pre><nowiki>${revision}</nowiki></pre> instead of <pre>mw_revision</pre>

=== Wiki editors per month ===

<pre>
<nowiki>
select
user_edits.monthnum,
user_edits.monthstr,
count(*) as editor_count,
user_active_edits.active_editor_count as active_editor_count
from
(
select
date_format(rev_timestamp,'%Y%m') as monthnum,
date_format(rev_timestamp,'%b %y') as monthstr,
CAST(rev_user_text as CHAR) AS username,
count(*) as editcount
from
`mw_revision`
group by
monthnum,username
HAVING monthnum > 201001
and monthnum <= date_format(${last_month},'%Y%m')
order by
monthnum,editcount desc
)
as user_edits
inner join
(
select
monthnum,
count(*) as active_editor_count
from
(
select
date_format(rev_timestamp,'%Y%m') as monthnum,
CAST(rev_user_text as CHAR) AS username,
count(*) as editcount
from
`mw_revision`
group by
monthnum,username
having
editcount >1
and monthnum > 201001
and monthnum <= date_format(${last_month},'%Y%m')
)
as
active_editors
group by
monthnum
order by
monthnum
)
as user_active_edits
on user_active_edits.monthnum=user_edits.monthnum
group by
user_edits.monthnum
order by
user_edits.monthnum;
</nowiki>
</pre>

=== Wiki edits per month ===

<pre>
<nowiki>
SELECT
date_format(rev_timestamp,'%Y%m') as monthnum,
count(*) as edit_count
FROM `mw_revision`
GROUP BY monthnum
HAVING monthnum > 201001 and
monthnum <= date_format(${last_month},'%Y%m')
</nowiki>
</pre>

=== Wiki top editors last month ===

<pre>
<nowiki>
SELECT
CAST(rev_user_text as CHAR) AS user,
COUNT(*) AS edits
FROM `mw_revision`
WHERE date_format(rev_timestamp, '%Y%m') = date_format(${last_month}, '%Y%m')
GROUP BY user
ORDER BY edits desc
LIMIT 20;
</nowiki>
</pre>

=== Edits all time ===

A test query, unused in the report.
<pre>
<nowiki>
SELECT
COUNT(*) AS edits
FROM
`mw_revision` rev
</nowiki>
</pre>

=== Wiki edits and new pages per month ===

<pre>
<nowiki>
select
new_pages.monthnum,
new_pages.monthstr,
edit_count,
count(*) as new_page_count
from
(
select
page_id,
date_format(min(rev_timestamp),'%Y%m') as monthnum,
date_format(min(rev_timestamp),'%b %y') as monthstr
from
mw_page,
mw_revision
where
page_id=rev_page and
date_format(rev_timestamp,'%Y%m')>'201001'
group by page_id
) as new_pages
inner join
(
SELECT
date_format(rev_timestamp,'%Y%m') as monthnum,
count(*) as edit_count
FROM `mw_revision`
GROUP BY monthnum
HAVING monthnum > 201001
) as edits
on
edits.monthnum=new_pages.monthnum and
new_pages.monthnum <= date_format(${last_month},'%Y%m')
group by
new_pages.monthnum
order by
new_pages.monthnum;
</nowiki>
</pre>

=== Wiki new pages per month ===

<pre>
<nowiki>
SELECT
COUNT(*) AS pages,
date_format(rev_timestamp,'%Y%m') as monthnum,
date_format(rev_timestamp, '%M %Y') as monthstr,
year(rev_timestamp) as year,
monthname(rev_timestamp) as month
FROM
`mw_revision` rev
WHERE
date_format(rev_timestamp,'%Y%m') <= date_format(${last_month},'%Y%m')
GROUP BY
year,
month
HAVING
monthnum > 201001
ORDER BY monthnum
</nowiki>
</pre>

=== Wiki editors >1 edits per month ===

<pre>
<nowiki>
select
monthnum,
count(*) as active_editor_count
from
(
select
date_format(rev_timestamp,'%Y%m') as monthnum,
CAST(rev_user_text as CHAR) AS username,
count(*) as editcount
from
`mw_revision`
group by
monthnum,username
having
editcount >1
order by
monthnum,editcount desc
)
as user_edits
group by
monthnum
order by
monthnum
</nowiki>
</pre>

=== Top editors all time ===

<pre>
<nowiki>
SELECT CAST(rev_user_text as CHAR) AS user,
COUNT(*) AS c
FROM `mw_revision` AS rev
GROUP BY user
ORDER BY c desc
LIMIT 10;
</nowiki>
</pre>

== <nowiki>MediaWiki</nowiki> database schema ==

<nowiki>MediaWiki</nowiki> uses 41 different tables in version 1.15. Not all of them are useful for community metrics. Those with potentially useful information are:

* mw_page: Each page in the wiki has one entry in the "page" table, containing the name of its creator, a unique ID, and a pointer to the latest revision.
* mw_revision: Every edit made in the wiki is recorded in the revision page. Information recorded includes the author, timestamp, and a pointer to the text of the page after the revision
* mw_text: Contains the page text for a given revision of a given page. Potentially content analysis possible.
* mw_user: Allows cross-referencing of usernames and user IDs, among other things.

What follows is the schema for the "important" tables (user, page, revision, text)

=== Table 'user' ===

<pre>
<nowiki>
CREATE TABLE `mw_user` (
`user_id` int(10) unsigned NOT NULL,
`user_name` varbinary(255) NOT NULL DEFAULT '',
`user_real_name` varbinary(255) NOT NULL DEFAULT '',
`user_password` tinyblob NOT NULL,
`user_newpassword` tinyblob NOT NULL,
`user_newpass_time` binary(14) DEFAULT NULL,
`user_email` tinyblob NOT NULL,
`user_options` blob NOT NULL,
`user_touched` binary(14) NOT NULL DEFAULT '\0\0\0\0\0\0\0\0\0\0\0\0\0\0',
`user_token` binary(32) NOT NULL DEFAULT '\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0',
`user_email_authenticated` binary(14) DEFAULT NULL,
`user_email_token` binary(32) DEFAULT NULL,
`user_email_token_expires` binary(14) DEFAULT NULL,
`user_registration` binary(14) DEFAULT NULL,
`user_editcount` int(11) DEFAULT NULL,
PRIMARY KEY (`user_id`),
UNIQUE KEY `user_name` (`user_name`),
KEY `user_email_token` (`user_email_token`)
);
</nowiki>
</pre>

=== Table 'page' ===

<pre>
<nowiki>
CREATE TABLE `mw_page` (
`page_id` int(10) unsigned NOT NULL,
`page_namespace` int(11) NOT NULL,
`page_title` varbinary(255) NOT NULL,
`page_restrictions` tinyblob NOT NULL,
`page_counter` bigint(20) unsigned NOT NULL DEFAULT '0',
`page_is_redirect` tinyint(3) unsigned NOT NULL DEFAULT '0',
`page_is_new` tinyint(3) unsigned NOT NULL DEFAULT '0',
`page_random` double unsigned NOT NULL,
`page_touched` binary(14) NOT NULL DEFAULT '\0\0\0\0\0\0\0\0\0\0\0\0\0\0',
`page_latest` int(10) unsigned NOT NULL,
`page_len` int(10) unsigned NOT NULL,
PRIMARY KEY (`page_id`),
UNIQUE KEY `name_title` (`page_namespace`,`page_title`),
KEY `page_random` (`page_random`),
KEY `page_len` (`page_len`)
);
</nowiki>
</pre>

=== Table 'revision' ===

<pre>
<nowiki>
CREATE TABLE `mw_revision` (
`rev_id` int(10) unsigned NOT NULL,
`rev_page` int(10) unsigned NOT NULL,
`rev_text_id` int(10) unsigned NOT NULL,
`rev_comment` tinyblob NOT NULL,
`rev_user` int(10) unsigned NOT NULL DEFAULT '0',
`rev_user_text` varbinary(255) NOT NULL DEFAULT '',
`rev_timestamp` binary(14) NOT NULL DEFAULT '\0\0\0\0\0\0\0\0\0\0\0\0\0\0',
`rev_minor_edit` tinyint(3) unsigned NOT NULL DEFAULT '0',
`rev_deleted` tinyint(3) unsigned NOT NULL DEFAULT '0',
`rev_len` int(10) unsigned DEFAULT NULL,
`rev_parent_id` int(10) unsigned DEFAULT NULL,
PRIMARY KEY (`rev_id`),
UNIQUE KEY `rev_page_id` (`rev_page`,`rev_id`),
KEY `rev_timestamp` (`rev_timestamp`),
KEY `page_timestamp` (`rev_page`,`rev_timestamp`),
KEY `user_timestamp` (`rev_user`,`rev_timestamp`),
KEY `usertext_timestamp` (`rev_user_text`,`rev_timestamp`)
);
</nowiki>
</pre>

=== Table 'text' ===

<pre>
<nowiki>
CREATE TABLE `mw_text` (
`old_id` int(10) unsigned NOT NULL,
`old_text` mediumblob NOT NULL,
`old_flags` tinyblob NOT NULL,
PRIMARY KEY (`old_id`)
);
</nowiki>
</pre>

Metrics/Mailing list queries

2011-10-12T16:22:55Z

Dneary: Add schema for mlstats

These are all of the queries which we have used for mailing list statistics in the monthly report:

Note: last_month is a parameter set once in the master report representing the month and year of the previous month, in the format 'YYYYMM'. For example, in September 2011, lastmonth is '201108'.

=== ML posts per list last 4 months ===

<pre><nowiki>SELECT
if(messages.mailing_list_url like '%-community%' or
messages.mailing_list_url like '%meego-dev%' or
messages.mailing_list_url like '%-sdk%' or
messages.mailing_list_url like '%-packaging%' or
messages.mailing_list_url like '%-qa%',
substring_index(TRIM(TRAILING '/' FROM `messages`.`mailing_list_url`), '/', -1),
'Other') as list,
year(first_date) AS y,
monthname(first_date) AS mon,
month(first_date) AS m,
date_format(first_date, '%b %Y') as monthstr,
date_format(first_date,'%Y%m') as monthnum,
count(*) AS posts
FROM
`messages`
WHERE
month(first_date)>=MONTH(DATE_SUB(NOW(), INTERVAL 4 MONTH)) and
year(first_date)>=YEAR(DATE_SUB(NOW(), INTERVAL 4 MONTH)) and
mailing_list_url not like '%meego-commits%' and
date_format(first_date,'%Y%m') <= date_format(${last_month},'%Y%m')
GROUP BY
list,
y,m
ORDER BY
list ASC,
monthnum ASC
</nowiki></pre>

=== ML message count per list name first ===

This query is unused in the report, I think. Left in for posterity.

<pre>
<nowiki>
SELECT
substring_index(TRIM(TRAILING '/' FROM `messages`.`mailing_list_url`), '/', -1) AS list,
year(first_date) AS y,
monthname(first_date) AS mon,
month(first_date) AS m,
date_format(first_date, '%M %Y') as monthstr,
date_format(first_date,'%Y%m') as monthnum,
count(*) AS c
FROM
`messages`
WHERE
year(first_date) > 1979 and
mailing_list_url not like '%meego-commits%'
GROUP BY
`messages`.`mailing_list_url`,
y,m
ORDER BY
list ASC,
monthnum ASC,
c ASC
</nowiki>
</pre>

=== ML repeat posters last month ===

Provides the number of active mailing list participants - sending more than one email per month.

<pre>
<nowiki>
select
monthname(${last_month}) as monthname,
year(${last_month}) as y,
count(*) as c
from
( select
p.email_address as member,
count(*) AS c
from messages as m,
messages_people as p
where
m.message_id=p.message_ID and
month(m.first_date)=month(${last_month}) and
year(m.first_date)=year(${last_month}) and
p.email_address NOT LIKE '%no_reply@%'
group by p.email_address
)
as repeat_posters
where c>=2;
</nowiki>
</pre>

=== ML total mails last month ===
<pre>
<nowiki>
SELECT
monthname(${last_month}) as monthname,
count(*) AS message_count
FROM
`messages`
WHERE
month(first_date) = month(${last_month}) and
year(first_date) = year(${last_month}) and
mailing_list_url not like '%meego-commits%'
</nowiki>
</pre>

=== ML popular threads last month ===
<pre>
<nowiki>
select
subject,
count(*) as c
from messages
where year(first_date)=year(${last_month})
and month(first_date)=month(${last_month})
group by subject
order by c desc
limit 10;
</nowiki>
</pre>

=== ML top 10 posters last month ===

<pre>
<nowiki>
select
p.email_address,
count(*) as c
from
messages as m,
messages_people as p
where
m.message_id=p.message_ID
and month(m.first_date)=month(${last_month})
and year(m.first_date)=year(${last_month})
and p.email_address NOT LIKE '%no_reply@%'
group by p.email_address
order by c desc
limit 10;
</nowiki>
</pre>

=== ML message count top 5 lists time first ===

This query is used to graph the evolution of each mailing list in a graph. The top 5 lists are taken out, and the others are grouped together.

<pre>
<nowiki>
select
if(messages.mailing_list_url like '%-community%' or
messages.mailing_list_url like '%meego-dev%' or
messages.mailing_list_url like '%-sdk%' or
messages.mailing_list_url like '%-packaging%' or
messages.mailing_list_url like '%-qa%',
substring_index(TRIM(TRAILING '/' FROM `messages`.`mailing_list_url`), '/', -1),
'Other') as list,
year(first_date) AS y,
monthname(first_date) AS mon,
month(first_date) AS m,
date_format(first_date, '%b %Y') as monthstr,
date_format(first_date,'%Y%m') as monthnum,
count(*) AS c
FROM
`messages`
WHERE
year(first_date) > 1979 and
date_format(first_date,'%Y%m') <= date_format(${last_month},'%Y%m') and
messages.mailing_list_url not like '%meego-commit%'
GROUP BY
list,y,m
ORDER BY
monthnum ASC,
list asc,
c asc;
</nowiki>
</pre>

=== ML top 20 posters last month ===

I should really have done a "top N posters last month" and made N a parameter. This was the quick copy & paste way to go.

<pre>
<nowiki>
select
p.email_address,
count(*) as c
from
messages as m,
messages_people as p
where
m.message_id=p.message_ID
and month(m.first_date)=month(${last_month})
and year(m.first_date)=year(${last_month})
and p.email_address NOT LIKE '%no_reply@%'
group by p.email_address
order by c desc
limit 20;
</nowiki>
</pre>

=== ML posters count last month ===

<pre>
<nowiki>
select
count(distinct p.email_address) as member_count,
monthname(${last_month}) AS monthname
from
messages as m,
messages_people as p
where
m.message_id=p.message_ID
and month(m.first_date)=month(${last_month})
and year(m.first_date)=year(${last_month})
and p.email_address NOT LIKE '%no_reply@%';
</nowiki>
</pre>

=== ML message count per list time first ===

All mailing lists. The graph was far too cluttered.

<pre>
<nowiki>
SELECT
substring_index(TRIM(TRAILING '/' FROM `messages`.`mailing_list_url`), '/', -1) AS list,
year(first_date) AS y,
monthname(first_date) AS mon,
month(first_date) AS m,
date_format(first_date, '%b %Y') as monthstr,
date_format(first_date,'%Y%m') as monthnum,
count(*) AS c
FROM
`messages`
WHERE
year(first_date) > 1979 and
mailing_list_url not like '%meego-commits%' and
date_format(first_date,'%Y%m') <= date_format(${last_month},'%Y%m')
GROUP BY
list,
y,m
ORDER BY
monthnum ASC,
list ASC,
c ASC;
</nowiki>
</pre>

== MLStats database schema ==

=== Table compressed_files ===

<pre>
<nowiki>
CREATE TABLE `compressed_files` (
`url` varchar(255) CHARACTER SET utf8 NOT NULL,
`mailing_list_url` varchar(255) CHARACTER SET utf8 NOT NULL,
`status` enum('new','visited','failed') DEFAULT NULL,
`last_analysis` datetime DEFAULT NULL,
PRIMARY KEY (`url`),
KEY `mailing_list_url` (`mailing_list_url`),
CONSTRAINT `compressed_files_ibfk_1` FOREIGN KEY (`mailing_list_url`) REFERENCES `mailing_lists` (`mailing_list_url`)
);
</nowiki>
</pre>

=== Table mailing_lists ===
<pre>
<nowiki>
CREATE TABLE `mailing_lists` (
`mailing_list_url` varchar(255) CHARACTER SET utf8 NOT NULL,
`mailing_list_name` varchar(255) CHARACTER SET utf8 DEFAULT 'NULL',
`project_name` varchar(255) CHARACTER SET utf8 DEFAULT 'NULL',
`last_analysis` datetime DEFAULT NULL,
PRIMARY KEY (`mailing_list_url`)
);
</nowiki>
</pre>

=== Table mailing_lists_people ===
<pre>
<nowiki>
CREATE TABLE `mailing_lists_people` (
`email_address` varchar(255) CHARACTER SET utf8 NOT NULL,
`mailing_list_url` varchar(255) CHARACTER SET utf8 NOT NULL,
PRIMARY KEY (`email_address`,`mailing_list_url`),
KEY `mailing_list_url` (`mailing_list_url`),
CONSTRAINT `mailing_lists_people_ibfk_1` FOREIGN KEY (`mailing_list_url`) REFERENCES `mailing_lists` (`mailing_list_url`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `mailing_lists_people_ibfk_2` FOREIGN KEY (`email_address`) REFERENCES `people` (`email_address`) ON DELETE CASCADE ON UPDATE CASCADE
);
</nowiki>
</pre>

=== Table messages ===

<pre>
<nowiki>
CREATE TABLE `messages` (
`message_ID` varchar(255) CHARACTER SET utf8 NOT NULL,
`mailing_list_url` varchar(255) CHARACTER SET utf8 NOT NULL,
`mailing_list` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
`first_date` datetime DEFAULT NULL,
`first_date_tz` int(11) DEFAULT NULL,
`arrival_date` datetime DEFAULT NULL,
`arrival_date_tz` int(11) DEFAULT NULL,
`subject` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
`message_body` text CHARACTER SET utf8,
`is_response_of` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
`mail_path` text CHARACTER SET utf8,
PRIMARY KEY (`message_ID`),
KEY `response` (`is_response_of`),
KEY `mailing_list_url` (`mailing_list_url`),
CONSTRAINT `messages_ibfk_1` FOREIGN KEY (`mailing_list_url`) REFERENCES `mailing_lists` (`mailing_list_url`) ON DELETE CASCADE ON UPDATE CASCADE
);
</nowiki>
</pre>

=== Table messages_people ===

<pre>
<nowiki>
CREATE TABLE `messages_people` (
`type_of_recipient` enum('From','To','Cc') NOT NULL DEFAULT 'From',
`message_id` varchar(255) CHARACTER SET utf8 NOT NULL,
`email_address` varchar(255) CHARACTER SET utf8 NOT NULL,
PRIMARY KEY (`type_of_recipient`,`message_id`,`email_address`),
KEY `m_id` (`message_id`),
KEY `message_id` (`message_id`),
KEY `email_address` (`email_address`),
CONSTRAINT `messages_people_ibfk_1` FOREIGN KEY (`message_id`) REFERENCES `messages` (`message_ID`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `messages_people_ibfk_2` FOREIGN KEY (`email_address`) REFERENCES `people` (`email_address`) ON DELETE CASCADE ON UPDATE CASCADE
);
</nowiki>
</pre>

=== Table people ===

<pre>
<nowiki>
CREATE TABLE `people` (
`email_address` varchar(255) CHARACTER SET utf8 NOT NULL,
`name` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
`username` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
`domain_name` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
`top_level_domain` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
PRIMARY KEY (`email_address`)
);
</nowiki>
</pre>

Metrics/Mailing list queries

2011-10-11T16:33:41Z

Dneary: MLStats queries

Metrics/Dashboard

2011-10-11T16:21:44Z

Dneary: /* Architecture */

== Community Metrics Dashboard ==

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:
* Drupal members
* Bugzilla (bugs opened, bugs closed, active users)
* Mailing lists (members, posts, threads)
* gitorious (commits, employer details for committers) - should use Jon Corbet's scripts like are used in the LF yearly kernel data.
* Wiki (edits, new pages)
* Forums (members, posts)
* IRC (total comments, people on channel)
* Transifex (Languages, translators, strings translated)
* Community OBS (uploads, users)
* SDK downloads (potentially extrapolated from meego.com)

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:

* [http://jasperforge.org/index.php?q=project/jasperreports JasperReports]
* [http://www.pentaho.com/ Pentaho]

The following are essentially ETL engines, and do not provide reporting or dashboard functionality:

* [http://www.talend.com/index.php Talend]
* [http://petals.ow2.org/ Petals]

[http://www.mulesoft.com/ 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.

=== Business intelligence engines ===

The area of Business Intelligence is littered with acronyms. Here's a quick overview of the main ones, and how they all fit together.

; BI
: Business Intelligence - general name for any middleware which allows you to query business processes (sales, inventory, etc) and get data overviews from it
; ETL
: Extract, Transform, and Load - the process if extracting data from a data source (database, screen scraping, text file parsing, whatever), transforming it to a well understood format, and loading it in your BI engine database or data warehouse. Good ETL solutions provide a nice way for you to connect another database and have new data sucked in at regular intervals, define views into the source data store which you can then query within your BI engine, etc. Pentaho's ETL, [http://kettle.pentaho.com/ Kettle], and [http://www.jaspersoft.com/jasperetl JasperETL], used by JasperReports, both provide (kind of) straightforward ways to hook into a MySQL database.
; ESB
: [http://en.wikipedia.org/wiki/Enterprise_service_bus Enterprise Service Bus] - a middleware bus providing a unique interface to applications on the front-end and data stores on the back end. Often used to link up many front-end applications (eg. library, student registration, employee payroll, syllabus management, accounting, supply-chain, student lodgement programmes, etc in a university). Not really useful for us, as far as I can tell.
; EAI
: Enterprise Application Integration - using software to integrate different applications together. As far as I can tell, this is a meaningless catch-all phrase for anything from kludges to architected business intelligence solutions.
; DW
: Data Warehouse. Basically the same thing as a database, as far as I can tell, but bigger and more impressive sounding.
; OLAP
: On-Line Analytical Processing. Commonly used acronym for extracting data via multi-dimensional queries. Databases can be configured to provide the results of this kind of query. As far as I can tell this is mostly a buzzword - an "OLAP database" like [http://mondrian.pentaho.com/ Mondrian] is basically the same thing as a database. "speed-of-thought" response times indeed.
; Business reporting
: An application which allows a graphical view of a database, and allows you to construct queries interactively, often using drag & drop. The results of these queries can then be plugged into graphing software for presentation in a dashboard.
; Dashboard
: Organised presentation of information in a web-page or other similar format allowing an at-a-glance overview of the situation for the data being measured.

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.

=== Comparison of candidate ETL/reporting ===

Modules available:

{|
! Software !! License !! ETL !! OLAP database !! BI server !! Reporting !! Dashboard module
|-
| Pentaho || EPL || [http://kettle.pentaho.com/ Kettle] || [http://mondrian.pentaho.com/ Mondrian] || [http://community.pentaho.com/projects/bi_platform/ Pentaho BI Platform] || [http://reporting.pentaho.com/ Pentaho Reporting] || [http://wiki.pentaho.com/display/COM/Community+Dashboard+Framework Community Dashboard Framework]
|-
| [http://www.jaspersoft.com/editions 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.

== Architecture ==

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 [https://docs.google.com/Doc?docid=0AdJmocc0fj_EZDJ3YmZiZF83M2RtaHhwcmRk&hl=en 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).

* [[../Installing Pentaho]]: A guide to getting Pentaho up and running for MeeGo Dashboard
* [[../Gathering data]]: A guide to installing and using the tools to gather data for a dashboard
* [[../Creating a report]]: Given data in a database, how do we generate a report in Pentaho, and deploy it to the dashboard?

* [[../Mailing list queries]]: SQL queries against the MLStats database

=== Extracting data ===

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 [http://forge.morfeo-project.org/projects/libresoft-tools/ 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 [http://lwn.net/Articles/290957/ 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 [http://code.google.com/p/superseriousstats/ 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 [http://meego.transifex.net/stats/ some analytics] available on Transifex, and that there is a RESTful API available to query this data.

== Data to report ==

For each of the resources, the following statistics (at a minimum) should be extracted:

=== Drupal ===
* Members of meego.com (+ evolution month over month)
* Active members (need a decent way to hook up different ways a person can be active: wiki, ML, IRC, forum, git)

=== Mailing lists ===
* Subscriber numbers (+ evolution) - from Mailman directly, not available in mlstats
* Emails sent (+ evolution) - from mlstats
* Active participants (individuals with >=2 emails during month) - from mlstats
* Hot threads - from mlstats
* Top posters - from mlstats

==== Useful queries ====

* Count posts of most popular threads:
*: <code>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;</code>
* Count the number of posts for each person
*: <code>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;</code>
* Restricting queries to a date range / month
*: The query above gives month-by-month totals, you could add an order by year(first_date) to get the year too
*: The easiest way is to add <code>where month(first_date)=3 and year(first_date)=2010</code> for March 2010. For the current month, <code>month(m.first_date)=month(NOW()) and year(first_date)=year(NOW())</code> works.

=== Forum ===
* Posts per month (+evolution)
* Active posters (2+ posts during month)
* Hot topics
* Top posters

[http://forum.meego.com/stats/ Stats] are exported from the Forum in CSV format monthly.

=== Bugzilla ===
* Bugs created (+ evolution)
* Bugs resolved (+ evolution)
* Comments on bugs this month
* Active Bugzilla contributors (2+ comments during month)

==== Useful queries ====

* [http://sourceforge.net/projects/qareports/ Software Quality Reports for Pentaho] of course
* See the [http://bugs.meego.com/query.cgi?format=report-table bugzilla tabular reports] and relative dates for more.
* Other potential resources:
** [http://www.ravenbrook.com/project/p4dti/tool/cgi/bugzilla-schema/ Bugzilla database schema by version]
** [https://landfill.bugzilla.org/bugzilla-tip/report.cgi Demo site for standard Bugzilla reports]
** [https://wiki.mozilla.org/Bugzilla:SQLCookBook The Bugzilla SQL cookbook]
** [http://www.bugzillametrics.org/ Bugzilla Metrics] - Third party add-on to Bugzilla that produces lots of metrics
** browse.cgi and weekly-summary.html extensions of GNOME Bugzilla: [https://launchpad.net/bugzilla.gnome.org Code], examples in practice: [http://bugzilla.gnome.org/browse.cgi?product=Evolution browse.cgi], [https://bugzilla.gnome.org/page.cgi?id=weekly-bug-summary.html weekly-summary.cgi]

=== Mediawiki ===
* New wiki pages (+ evolution)
* Edits this month (+ evolution)
* Pages deleted this month (+ evolution)
* Unique editors this month (+ evoluion)

==== Queries ====

A [http://www.mediawiki.org/wiki/Extension:SpecialUserScore 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;

=== IRC ===

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 [https://github.com/tommyrot/superseriousstats/blob/master/empty_database_v3.sql 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 [https://github.com/tommyrot/superseriousstats/blob/master/html.class.php html.class.php] which we can use to generate some reports, though.

* Total IRC activity (by hour)
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`
* Total active participants (+ evolution) - we may be able to get "number of participants per hour/day/month" (so you can see if it's 2 guys taking amongst themselves or a larger group) - I'll ask tommyrot what the query should look like.
* Top contributors (per month)
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

=== 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 ===
* Package submissions (+ evolution)
* Active participants (+ evolution)

== Not yet in scope ==

I have not yet considered how I might get web analytics and download stats.

Metrics/Dashboard

2011-09-30T13:07:25Z

Dneary: /* Data to report */

== Community Metrics Dashboard ==

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:
* Drupal members
* Bugzilla (bugs opened, bugs closed, active users)
* Mailing lists (members, posts, threads)
* gitorious (commits, employer details for committers) - should use Jon Corbet's scripts like are used in the LF yearly kernel data.
* Wiki (edits, new pages)
* Forums (members, posts)
* IRC (total comments, people on channel)
* Transifex (Languages, translators, strings translated)
* Community OBS (uploads, users)
* SDK downloads (potentially extrapolated from meego.com)

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:

* [http://jasperforge.org/index.php?q=project/jasperreports JasperReports]
* [http://www.pentaho.com/ Pentaho]

The following are essentially ETL engines, and do not provide reporting or dashboard functionality:

* [http://www.talend.com/index.php Talend]
* [http://petals.ow2.org/ Petals]

[http://www.mulesoft.com/ 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.

=== Business intelligence engines ===

The area of Business Intelligence is littered with acronyms. Here's a quick overview of the main ones, and how they all fit together.

; BI
: Business Intelligence - general name for any middleware which allows you to query business processes (sales, inventory, etc) and get data overviews from it
; ETL
: Extract, Transform, and Load - the process if extracting data from a data source (database, screen scraping, text file parsing, whatever), transforming it to a well understood format, and loading it in your BI engine database or data warehouse. Good ETL solutions provide a nice way for you to connect another database and have new data sucked in at regular intervals, define views into the source data store which you can then query within your BI engine, etc. Pentaho's ETL, [http://kettle.pentaho.com/ Kettle], and [http://www.jaspersoft.com/jasperetl JasperETL], used by JasperReports, both provide (kind of) straightforward ways to hook into a MySQL database.
; ESB
: [http://en.wikipedia.org/wiki/Enterprise_service_bus Enterprise Service Bus] - a middleware bus providing a unique interface to applications on the front-end and data stores on the back end. Often used to link up many front-end applications (eg. library, student registration, employee payroll, syllabus management, accounting, supply-chain, student lodgement programmes, etc in a university). Not really useful for us, as far as I can tell.
; EAI
: Enterprise Application Integration - using software to integrate different applications together. As far as I can tell, this is a meaningless catch-all phrase for anything from kludges to architected business intelligence solutions.
; DW
: Data Warehouse. Basically the same thing as a database, as far as I can tell, but bigger and more impressive sounding.
; OLAP
: On-Line Analytical Processing. Commonly used acronym for extracting data via multi-dimensional queries. Databases can be configured to provide the results of this kind of query. As far as I can tell this is mostly a buzzword - an "OLAP database" like [http://mondrian.pentaho.com/ Mondrian] is basically the same thing as a database. "speed-of-thought" response times indeed.
; Business reporting
: An application which allows a graphical view of a database, and allows you to construct queries interactively, often using drag & drop. The results of these queries can then be plugged into graphing software for presentation in a dashboard.
; Dashboard
: Organised presentation of information in a web-page or other similar format allowing an at-a-glance overview of the situation for the data being measured.

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.

=== Comparison of candidate ETL/reporting ===

Modules available:

{|
! Software !! License !! ETL !! OLAP database !! BI server !! Reporting !! Dashboard module
|-
| Pentaho || EPL || [http://kettle.pentaho.com/ Kettle] || [http://mondrian.pentaho.com/ Mondrian] || [http://community.pentaho.com/projects/bi_platform/ Pentaho BI Platform] || [http://reporting.pentaho.com/ Pentaho Reporting] || [http://wiki.pentaho.com/display/COM/Community+Dashboard+Framework Community Dashboard Framework]
|-
| [http://www.jaspersoft.com/editions 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.

== Architecture ==

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 [https://docs.google.com/Doc?docid=0AdJmocc0fj_EZDJ3YmZiZF83M2RtaHhwcmRk&hl=en 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).

* [[../Installing Pentaho]]: A guide to getting Pentaho up and running for MeeGo Dashboard
* [[../Gathering data]]: A guide to installing and using the tools to gather data for a dashboard
* [[../Creating a report]]: Given data in a database, how do we generate a report in Pentaho, and deploy it to the dashboard?

=== Extracting data ===

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 [http://forge.morfeo-project.org/projects/libresoft-tools/ 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 [http://lwn.net/Articles/290957/ 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 [http://code.google.com/p/superseriousstats/ 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 [http://meego.transifex.net/stats/ some analytics] available on Transifex, and that there is a RESTful API available to query this data.

== Data to report ==

For each of the resources, the following statistics (at a minimum) should be extracted:

=== Drupal ===
* Members of meego.com (+ evolution month over month)
* Active members (need a decent way to hook up different ways a person can be active: wiki, ML, IRC, forum, git)

=== Mailing lists ===
* Subscriber numbers (+ evolution) - from Mailman directly, not available in mlstats
* Emails sent (+ evolution) - from mlstats
* Active participants (individuals with >=2 emails during month) - from mlstats
* Hot threads - from mlstats
* Top posters - from mlstats

==== Useful queries ====

* Count posts of most popular threads:
*: <code>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;</code>
* Count the number of posts for each person
*: <code>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;</code>
* Restricting queries to a date range / month
*: The query above gives month-by-month totals, you could add an order by year(first_date) to get the year too
*: The easiest way is to add <code>where month(first_date)=3 and year(first_date)=2010</code> for March 2010. For the current month, <code>month(m.first_date)=month(NOW()) and year(first_date)=year(NOW())</code> works.

=== Forum ===
* Posts per month (+evolution)
* Active posters (2+ posts during month)
* Hot topics
* Top posters

[http://forum.meego.com/stats/ Stats] are exported from the Forum in CSV format monthly.

=== Bugzilla ===
* Bugs created (+ evolution)
* Bugs resolved (+ evolution)
* Comments on bugs this month
* Active Bugzilla contributors (2+ comments during month)

==== Useful queries ====

* [http://sourceforge.net/projects/qareports/ Software Quality Reports for Pentaho] of course
* See the [http://bugs.meego.com/query.cgi?format=report-table bugzilla tabular reports] and relative dates for more.
* Other potential resources:
** [http://www.ravenbrook.com/project/p4dti/tool/cgi/bugzilla-schema/ Bugzilla database schema by version]
** [https://landfill.bugzilla.org/bugzilla-tip/report.cgi Demo site for standard Bugzilla reports]
** [https://wiki.mozilla.org/Bugzilla:SQLCookBook The Bugzilla SQL cookbook]
** [http://www.bugzillametrics.org/ Bugzilla Metrics] - Third party add-on to Bugzilla that produces lots of metrics
** browse.cgi and weekly-summary.html extensions of GNOME Bugzilla: [https://launchpad.net/bugzilla.gnome.org Code], examples in practice: [http://bugzilla.gnome.org/browse.cgi?product=Evolution browse.cgi], [https://bugzilla.gnome.org/page.cgi?id=weekly-bug-summary.html weekly-summary.cgi]

=== Mediawiki ===
* New wiki pages (+ evolution)
* Edits this month (+ evolution)
* Pages deleted this month (+ evolution)
* Unique editors this month (+ evoluion)

==== Queries ====

A [http://www.mediawiki.org/wiki/Extension:SpecialUserScore 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;

=== IRC ===

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 [https://github.com/tommyrot/superseriousstats/blob/master/empty_database_v3.sql 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 [https://github.com/tommyrot/superseriousstats/blob/master/html.class.php html.class.php] which we can use to generate some reports, though.

* Total IRC activity (by hour)
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`
* Total active participants (+ evolution) - we may be able to get "number of participants per hour/day/month" (so you can see if it's 2 guys taking amongst themselves or a larger group) - I'll ask tommyrot what the query should look like.
* Top contributors (per month)
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

=== 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 ===
* Package submissions (+ evolution)
* Active participants (+ evolution)

== Not yet in scope ==

I have not yet considered how I might get web analytics and download stats.

Metrics/Gathering data

2011-07-26T15:01:09Z

Dneary:

=== Data sources ===

For SQL databases, the metrics server has access to the database server for MediaWiki, Bugzilla, and Drupal directly.

For the forum, [[#Forums | we integrate the CSV files currently being exported]], which provide the basic analytics we need.

Mailing lists are parsed by [http://forge.morfeo-project.org/projects/libresoft-tools/ MLStats]. We will use the resulting database directly in the dashboard. See [[#Mailing lists - MLStats]]

We may eventually parse git activity in repositories with [http://lwn.net/Articles/290957/ gitdm], before storing the results directly in a database. For the moment, though, we extract developer information from the meego-commits mailing list.

[[#IRC - SuperSeriousStats | IRC logs]] will be parsed with [http://code.google.com/p/superseriousstats/ 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 [http://meego.transifex.net/stats/ some analytics] available on Transifex, and that there is a RESTful API available to query this data.

For each of the services we gather data for, here's a guide to getting that data:

== Mailing lists - MLStats ==

Mailman mailing lists can be downloaded, parsed and stored in a MySQL database using [https://projects.libresoft.es/projects/show/mlstats MLStats].

The general idea is to point mlstats at the list archive page, and let it do the work of figuring out what to download.

We are carrying a small local patch to mlstats to ensure that it re-downloads the current month's archives and reparses them. The patch [https://bugzilla.libresoft.es/attachment.cgi?id=137 has been submitted upstream].

Once you have downloaded and unpacked MLStats 0.4, patched it with the patch above, and installed it with <pre>setup.py install --prefix=/install/path</pre> you will need to "prime the pump", and download and import the archives to all of the Maemo mailing lists.

The format of the mlstats command line is:
/path/to/mlstats --db-user=<username> --db-password=<password> http://lists.meego.com/pipermail/meego-announce/

The command line option <pre>--no-report</pre> suppresses the creation of a report after the import, useful for a cron job.

You can list the archives for a number of mailing lists together on the command line. The list of MeeGo mailing lists is [http://lists.meego.com/mailman/listinfo here].

for list in meego-adaptation-intel-automotive meego-announce meego-architecture \
meego-commits meego-community meego-dev meego-distribution-tools \
meego-events meego-handset meego-il10n meego-inputmethods meego-it \
meego-ivi meego-kernel meego-packaging meego-pm meego-porting \
meego-python meego-qa meego-releases meego-sdk meego-security \
meego-security-discussion meego-touch-dev meego-tv;
do
/path/to/mlstats --no-report --db-user=<username> --db-password=<password> http://lists.meego.com/pipermail/${list} >> /tmp/output.txt
done;

This should be run every night through cron.

=== Mailing list graph over time ===

The SQL query (yes, a Big Hairy Beast) which can be used to create a report graphing the messages posted to each list, month by month, is this:
<pre>
SELECT
`messages`.`mailing_list_url` AS list,
year(first_date) AS y,
monthname(first_date) AS mon,
month(first_date) AS m,
date_format(first_date, '%M %Y') as monthstr,
date_format(first_date,'%Y%m') as monthnum,
count(*) AS c
FROM
`messages`
WHERE
year(first_date) > 1979 and
mailing_list_url not like '%meego-commits%' and first_date<'2011-05-01'
GROUP BY
`messages`.`mailing_list_url`,
y,m
ORDER BY
monthnum ASC,
list ASC,
c ASC
</pre>

We use $monthnum to order the results over time, and $monthstr as a more meaningful X axis label. We eliminate all emails with bad Date headers and filter meego-commits out of our analysis, and group the messages by mailing list, to give the following graph as the final result (how to create a report will follow later):

[[Image:Mlgraph.png]]

== IRC - SuperSeriousStats ==

[http://code.google.com/p/superseriousstats/ SuperSeriousStats] is a super serious IRC log analyser, developed by Jos de Ruiter. It is written in PHP.

To use SuperSeriousStats, you need to install the php-cli command line interface. Once you have this, and the [https://github.com/tommyrot/superseriousstats/wiki/Setup-guide other dependencies for sss listed here], you will be able to run sss.

Before running it for the first time, [https://github.com/tommyrot/superseriousstats/blob/master/empty_database_v4.sql initialise the database]. You will need to download all of the [http://mg.pov.lt/meego-irclog/index.html IRC logs] you wish to import first. Logs are expected in the Supybot format ([http://mg.pov.lt/meego-irclog/%23meego.2011-06-15.log example]).

=== Getting the IRC logs ===
For the initial download, the following script should help get all the logs:

<pre>
for y in 2010 2011; do
for m in `seq -f '%02g' 1 12`; do
for d in `seq -f '%02g' 1 31`; do
wget http://mg.pov.lt/meego-irclog/%23meego.$y-$m-$d.log;
done;
done;
done;
</pre>

Thereafter, you can add the following line in your cron job to get the previous day's logs:
<pre>
logdate= `date +%Y-%m-%d -d yesterday`
wget -P $logdir http://mg.pov.lt/meego-irclog/%23meego.${logdate}.log;
</pre>

=== Parsing the logs ===

To run sss, you will need to modify a number of fields in sss.conf. Notably, I had to change the filename format to "*.Y-m-d.\lo\g" to match the log file names, and set log format to supybot. I recommend creating a separate config file for each channel you want to parse. Here is the appropriate sss.conf for #meego logs:
<pre>
#
# This file contains all of sss' settings along with their defaults.
# All values must be placed between double quotes, even empty ones.
#
################################# Required ###################################

channel = "#meego" # Name of the IRC channel.
timezone = "UTC" # Timezone the logs are in. Used for time offset
# calculations and conversions.
# See http://php.net/manual/en/timezones.php
db_host = "host" # IP address or FQDN of the MySQL server.
db_port = "3306" # Port the MySQL server is listening on.
db_user = "db_user" # MySQL user.
db_pass = "db_password" # MySQL password.
db_name = "db_name" # Name of the MySQL database used for sss.
parser = "parser_supybot" # The parser to use depending on logfile format.
# e.g. "parser_irssi" or "parser_eggdrop"

# This string contains the format of the date within a logfile filename.
# Examples:
# filename: #chatroom.20030131 dateformat: *.Ymd
# filename: #chatroom.20030131 dateformat: \#c\h\atroo\m.Ymd
# filename: chatroom.log-31012003 dateformat: *.*-dmY
# filename: chatroom.log-31012003.gz dateformat: *.*-dmY.\g\z
# filename: chatroom.log-31012003.gz dateformat: *.*-dmY.*
# See http://php.net/date_create_from_format for more specific syntax options.
logfile_dateformat = "*.Y-m-d.\lo\g"
</pre>

sss has a mechanism to prevent it from re-importing log files for the same channel, by storing dates for which it has already parsed files in the table "parse_history", but you can store summary data from several channels in one database.

To do the initial import, just run
php sss.php -c meego.conf -i ${logdir} -o report.html

And to do the nightly import, put the following in a shell script, and call it from cron:
<pre>
logdate=`date +%Y-%m-%d -d yesterday`
logdir=/path/to/logfiles
sssdir=/path/to/sss-4.0

/usr/bin/wget -P $logdir http://mg.pov.lt/meego-irclog/%23meego.${logdate}.log;
/usr/bin/php ${sssdir}/sss.php -c meego.conf -i ${logdir}/\#meego.${logdate}.log
</pre>

=== Queries ===

The sss report is built from a number of queries. A number of other useful sss queries are already listed in [[Metrics/Dashboard#IRC | the Dashboard page]]

'''Activity by month'''
<pre>
select date_format(`date`, '%Y-%m') as `date`,
sum(`l_total`) as `l_total`,
sum(`l_night`) as `l_night`,
sum(`l_morning`) as `l_morning`,
sum(`l_afternoon`) as `l_afternoon`,
sum(`l_evening`) as `l_evening`
from `channel`
group by year(`date`), month(`date`);
</pre>

'''Activity by day over last 30 days'''

<pre>
select `date`, `l_total`, `l_night`, `l_morning`, `l_afternoon`, `l_evening` from `channel`
where `date` > DATE_SUB(CURDATE(),INTERVAL 30 DAY);
</pre>

== Forums ==

Forum stats are available as a series of CSV files on [http://forums.meego.com/stats forums.meego.com], supplied monthly. We need to download the .csv files every month (just the latest ones), parse the CSV files into a database and generate a report from that.

=== Downloading CSV files ===

To get started and load up all of the old stats, run the following:

<pre>
wget -nd -P <local dir for data> -r -l1 --no-parent -A.csv http://forum.meego.com/stats
</pre>

This will download all CSV files to the local directory specified.

For the monthly refresh, we use wget in a cron script as follows:
<pre>
y=`/bin/date -d "1 month ago" +%Y`
m=`/bin/date -d "1 month ago" +%m`

wget -nd -P <local dir for data> -r -l1 --no-parent -A "${y}${m}*.csv" http://forum.meego.com/stats

</pre>

That wget command line is worth explaining:
* -nd: Don't create the remote directory structure when downloading the files locally
* -P <directory>: Download files to the parent directory specified
* -r: Recursively download
* -l1: Limit to 1 level of directories (combining -r and -l1 allows us to download several files at the same time)
* --no-parent: Ignore the .. link
* -A "${y}${m}*.csv": Match filenames of the form "YYYYMM*.csv" - gives us the latest stats files only

=== Database schema ===

We created 7 tables, one for each of the statistics provided by the forum.

Here is the database schema:
<pre>--
-- Table structure for table `forum_cumulative_posts`
--

DROP TABLE IF EXISTS `forum_cumulative_posts`;
CREATE TABLE `forum_cumulative_posts` (
`month` int(11) NOT NULL,
`year` int(11) NOT NULL,
`forum` varchar(50) NOT NULL DEFAULT '',
`posts` int(11) DEFAULT NULL,
PRIMARY KEY (`month`,`year`,`forum`)
);

--
-- Table structure for table `forum_cumulative_threads`
--

DROP TABLE IF EXISTS `forum_cumulative_threads`;
CREATE TABLE `forum_cumulative_threads` (
`month` int(11) NOT NULL,
`year` int(11) NOT NULL,
`forum` varchar(50) NOT NULL DEFAULT '',
`threads` int(11) DEFAULT NULL,
PRIMARY KEY (`month`,`year`,`forum`)
);

--
-- Table structure for table `forum_hottest_threads`
--

DROP TABLE IF EXISTS `forum_hottest_threads`;
CREATE TABLE `forum_hottest_threads` (
`month` int(11) NOT NULL,
`year` int(11) NOT NULL,
`rank` int(11) NOT NULL DEFAULT '0',
`title` varchar(255) DEFAULT NULL,
`posts` int(11) DEFAULT NULL,
PRIMARY KEY (`month`,`year`,`rank`)
);

--
-- Table structure for table `forum_most_viewed_threads`
--

DROP TABLE IF EXISTS `forum_most_viewed_threads`;
CREATE TABLE `forum_most_viewed_threads` (
`month` int(11) NOT NULL,
`year` int(11) NOT NULL,
`rank` int(11) NOT NULL DEFAULT '0',
`title` varchar(255) DEFAULT NULL,
`views` int(11) DEFAULT NULL,
PRIMARY KEY (`month`,`year`,`rank`)
);

--
-- Table structure for table `forum_posts`
--

DROP TABLE IF EXISTS `forum_posts`;
CREATE TABLE `forum_posts` (
`month` int(11) NOT NULL,
`year` int(11) NOT NULL,
`forum` varchar(50) NOT NULL DEFAULT '',
`posts` int(11) DEFAULT NULL,
PRIMARY KEY (`month`,`year`,`forum`)
);

--
-- Table structure for table `forum_top_posters`
--

DROP TABLE IF EXISTS `forum_top_posters`;
CREATE TABLE `forum_top_posters` (
`month` int(11) NOT NULL,
`year` int(11) NOT NULL,
`rank` int(11) NOT NULL DEFAULT '0',
`member` varchar(50) DEFAULT NULL,
`posts` int(11) DEFAULT NULL,
PRIMARY KEY (`month`,`year`,`rank`)
);

--
-- Table structure for table `forum_top_thanked`
--

DROP TABLE IF EXISTS `forum_top_thanked`;
CREATE TABLE `forum_top_thanked` (
`month` int(11) NOT NULL,
`year` int(11) NOT NULL,
`rank` int(11) NOT NULL DEFAULT '0',
`member` varchar(50) DEFAULT NULL,
`thanks` int(11) DEFAULT NULL,
PRIMARY KEY (`month`,`year`,`rank`)
);

</pre>

We import the files into the databases via <pre>LOAD DATA LOCAL INFILE</pre>

<pre>
cd <local data directory>

if [ -f ${y}${m}_forum_cumulative_posts.csv ]; then
echo "Importing ${y}${m}_forum_cumulative_posts.csv";

# forum,posts
mysql -u<db_user> -p<db_password> -h <host> <database> -e \
"LOAD DATA LOCAL INFILE '${y}${m}_forum_cumulative_posts.csv'
INTO TABLE forum_cumulative_posts
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"'
IGNORE 1 LINES
(forum, posts)
set year=$y, month=$m"
fi;

if [ -f ${y}${m}_forum_cumulative_threads.csv ]; then
echo "Importing ${y}${m}_forum_cumulative_threads.csv";

# forum,threads
mysql -u<db_user> -p<db_password> -h <host> <database> -e \
"LOAD DATA LOCAL INFILE '${y}${m}_forum_cumulative_threads.csv'
INTO TABLE forum_cumulative_threads
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"'
IGNORE 1 LINES
(forum, threads)
set year=$y, month=$m"
fi;

if [ -f ${y}${m}_forum_posts.csv ]; then
echo "Importing ${y}${m}_forum_posts.csv";

# forum,posts
mysql -u<db_user> -p<db_password> -h <host> <database> -e \
"LOAD DATA LOCAL INFILE '${y}${m}_forum_posts.csv'
INTO TABLE forum_posts
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"'
IGNORE 1 LINES
(forum, posts)
set year=$y, month=$m"
fi;

if [ -f ${y}${m}_hottest_threads.csv ]; then
echo "Importing ${y}${m}_hottest_threads.csv";

# rank,title,posts
mysql -u<db_user> -p<db_password> -h <host> <database> -e \
"LOAD DATA LOCAL INFILE '${y}${m}_hottest_threads.csv'
INTO TABLE forum_hottest_threads
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"'
IGNORE 1 LINES
(rank, title, posts)
set year=$y, month=$m"
fi;

if [ -f ${y}${m}_most_viewed_threads.csv ]; then
echo "Importing ${y}${m}_most_viewed_threads.csv";

# rank,title,views
mysql -u<db_user> -p<db_password> -h <host> <database> -e \
"LOAD DATA LOCAL INFILE '${y}${m}_most_viewed_threads.csv'
INTO TABLE forum_most_viewed_threads
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"'
IGNORE 1 LINES
(rank, title, views)
set year=$y, month=$m"
fi;

if [ -f ${y}${m}_top_posters.csv ]; then
echo "Importing ${y}${m}_top_posters.csv";

# rank,member,posts
mysql -u<db_user> -p<db_password> -h <host> <database> -e \
"LOAD DATA LOCAL INFILE '${y}${m}_top_posters.csv'
INTO TABLE forum_top_posters
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"'
IGNORE 1 LINES
(rank, member, posts)
set year=$y, month=$m"
fi;

if [ -f ${y}${m}_top_thanked.csv ]; then
echo "Importing ${y}${m}_top_thanked.csv";

# rank,member,thanks
mysql -u<db_user> -p<db_password> -h <host> <database> -e \
"LOAD DATA LOCAL INFILE '${y}${m}_top_thanked.csv'
INTO TABLE forum_top_thanked
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"'
IGNORE 1 LINES
(rank, member, thanks)
set year=$y, month=$m"
fi;

</pre>

We run this script (which downloads and imports the .csv files froim the server) monthly through cron. Not sure when the files are put up on the 1st, so I get them on the 2nd:
<pre>
# Retrieve forum stats monthly on 2nd of month
15 2 2 * * /home/dneary/bin/forum_stats.sh
</pre>

Community Office/Meetings

2011-07-13T16:23:32Z

Dneary: /* Next CO meeting */

== Purpose and Intent ==

The purpose of these meetings is to provide status updates on community topics and help get people involved in the MeeGo community. This is the regular meeting of the [[Community Office]] and anyone with an interest in the MeeGo community is welcome to attend.

A few notes:
* Anyone can attend these meetings or participate in the process!
* Anyone can add any proposed topic below.
* Learn more about the [[Community_Office]].

== Meeting Logistics ==

The Community Office meetings are held every other Tuesday at 14:00 UTC. Meetings will end promptly after 1 hour.

All CO meetings take place in the MeeGo [http://meego.com/community/irc-channel IRC channels]:
* Main meeting: #meego-meeting
* Back channel & other discussions (optional): #meego

== Next CO meeting ==

[http://www.timeanddate.com/worldclock/fixedtime.html?year=2011&month=7&day=26&hour=14&min=0&sec=0 July 26 at 14:00 UTC (7am Pacific)]

'''Agenda'''
* Agenda and Process Review (Dawn)
* [[MeeGo Apps|Community Apps Update]] (Niels Breet)
* [[Meego IT]] (Stefano)
* [[Community Office/Community device program|Community Device Program]] (Randall)
* [[Events]] Update (Dawn)
* [http://developer.meego.com/ developer.meego.com updates] (Mike)
* [[Metrics/Dashboard | Community metrics dashboard]] (Dave Neary)
* Next Community Office Meeting (Dawn)
* All Other Business / General Questions

Notes:
* You might want to read our [[IRC Presentation Tips]] and [[IRC guidelines]].

== Proposed Brainstorming Topics ==

* Add your topics here.

'''Standing Agenda Items (we'll have these updates on most weeks)''':
* Community Apps Update (Niels)
* Events Update (Amy)
* Community Device Program (Randall)
* IT (Stefano)

== Past meetings ==
Click the links to get the meeting minutes. From there you can access the full logs.
* [http://irclogs.meego.com/meetbot/meego-meeting/2011/meego-meeting.2011-07-12-13.55.html 2011-7-12]
* [http://irclogs.meego.com/meetbot/meego-meeting/2011/meego-meeting.2011-06-28-13.56.html 2011-6-28]
* [http://irclogs.meego.com/meetbot/meego-meeting/2011/meego-meeting.2011-06-14-13.56.html 2011-6-14]
* [http://trac.tspre.org/meetbot/meego-meeting/2011/meego-meeting.2011-03-01-19.58.html 2011-3-1]
* [http://trac.tspre.org/meetbot/meego-meeting/2011/meego-meeting.2011-02-01-19.59.html 2011-2-1]
* [http://trac.tspre.org/meetbot/meego-meeting/2011/meego-meeting.2011-01-18-14.59.html 2011-1-18]
* [http://trac.tspre.org/meetbot/meego-meeting/2010/meego-meeting.2010-12-07-19.59.html 2010-12-7]
* [http://trac.tspre.org/meetbot/meego-meeting/2010/meego-meeting.2010-11-02-18.59.html 2010-11-2]
* [http://trac.tspre.org/meetbot/meego-meeting/2010/meego-meeting.2010-10-05-04.57.html 2010-10-5]
* [http://trac.tspre.org/meetbot/meego-meeting/2010/meego-meeting.2010-09-21-13.57.html 2010-9-21]
* [http://trac.tspre.org/meetbot/meego-meeting/2010/meego-meeting.2010-08-31-04.57.html 2010-8-31]
* [http://trac.tspre.org/meetbot/meego-meeting/2010/meego-meeting.2010-08-17-13.58.html 2010-8-17]
* [http://trac.tspre.org/meetbot/meego-meeting/2010/meego-meeting.2010-08-03-04.58.html 2010-8-3]
* [http://trac.tspre.org/meetbot/meego-meeting/2010/meego-meeting.2010-07-20-13.59.html 2010-7-20]
* [http://trac.tspre.org/meetbot/meego-meeting/2010/meego-meeting.2010-07-06-18.58.html 2010-7-6]
* [http://trac.tspre.org/meetbot/meego-meeting/2010/meego-meeting.2010-06-15-13.59.html 2010-6-15]
* [http://trac.tspre.org/meetbot/meego-meeting/2010/meego-meeting.2010-05-04-18.57.html 2010-05-04] - First Community team meeting under the new regular process.
* [http://meego.mkdir.name/logs/meego-meeting/2010/meego-meeting.2010-02-24-20.04.html 2010-02-24] - First MeeGo community meeting, prior to the formalization of the Community WG (see [[Community website meeting 2010 2 24|wiki page]]).

MeeGoBugzilla Customization

2011-07-11T10:11:04Z

Dneary: moved MeeGoBugzilla Customization to MeeGo Bugzilla customization: Naming conventions

#REDIRECT [[MeeGo Bugzilla customization]]

MeeGo Bugzilla customization

2011-07-11T10:11:04Z

Dneary: moved MeeGoBugzilla Customization to MeeGo Bugzilla customization: Naming conventions

== MeeGo Bugzilla Features ==

Configurations and customizations have been applied to MeeGo Bugzilla to support additional features for MeeGo bug tracking.

* Support Drupal user authentication system
** MeeGo bugzilla share the same user DB in meego.com, so there is no need to ask user to register multiple accounts
* Copyright waiver flag for attachment
** User is provide a copyright waiver option when submit a patch
* Privilege Control for bugs' priority setting
* Bug vote option
** User is able to vote the bug, vote number indicates how many people care a specific bug
* Optimized the new bug navigation web page
** Expand the classification "MeeGo Platform" by listing products under it so that user could choose the product under this classification directly. This helps to reduce the redundant step when filing new bug to most common used classification "MeeGo Platform".
* Use customized flag to manage release blocker bugs
** Normal user can propose blocker bugs for a specific release;
** Users in a privileged group can approve/reject blocker bugs;
* Customized Bugzilla new bug template
* Set the default value for cloned bugs
* Security flag
** A check box is provided to user to mark a bug as security bug;
* Security product
** The bugs in security product are limited to only visible to security group, bug reporter and users in bug cc list.
* Field name display renaming
** OS --> Architecture
** Hardware --> Profile
* New custom fields
** "UX Status" Track UX design status for those UX features. This field is expected to be showed only in feature products
** "Platform" To track which platform this bug is found. It's 1:M mapping relationship with Profile field
** "Update Release" To track which update release number the bug is expected to be fixed
** "Triaged By" To track who made the triage to the bug
* Show custom fields in advanced page
** Show dropdown custom fields as well as "security" checkbox in search page
* Per product configurable priority change control group
** This is the enhanced patch for customization "Privilege Control for bugs' priority setting"
** Admin is able to configure which group is able to change priority of given product from product administration page
* Admin configurable release blocker flag rendering
** This is the enhanced patch for customization "Use customized flag to manage release blocker bugs"
** Admin is able to configure which flag need to be showed as Release Blocker fashion. And most of hardcode is removed
* Known bug fixes for MeeGo 3.6 Bugzilla upgrading
** A meanlingless text box is showed next to resolution field even the bug is not the duplicated resolution
** RESOVED REJECTED feature is not showed correctly for users only granted with Feature Owners privilege
** Bugzilla should disallow to submit bug specific status to a feature and vice verse to submit feature specific status to a bug
* Toolkits for feature management
** Feature import tool to bulk load features from xls
** Export features' description to csv file

[[Category:QA]]

Quality/getting-started

2011-07-11T10:08:43Z

Dneary: moved Quality/getting-started to Quality/Getting started: Naming conventions

#REDIRECT [[Quality/Getting started]]

Quality/Getting started

2011-07-11T10:08:43Z

Dneary: moved Quality/getting-started to Quality/Getting started: Naming conventions

There are a lot of ways for you to become part of MeeGo quality team, no matter you are technical person or not.

* '''Try our daily build'''
** MeeGo release engineers are producing in-development daily builds, which are available [http://repo.meego.com/MeeGo/snapshots/stable/ HERE]. By trying these builds, you will get the opportunity to enjoy latest fancy features, although you will also get chance to spot new issues as a side effect. Daily builds are not targeted for everyone, as many features may not be fully functional and regressions may happen, but your feedback for them would be very helpful to improve the quality of our releases. If it sounds too scary for you to try daily builds, you could pick up our [http://repo.meego.com/MeeGo/releases/ stable releases] by providing same types of feedback.
** You could feedback to us by reporting bugs @ https://bugs.meego.com, or sending emails to [http://lists.meego.com/listinfo/meego-qa meego-qa@lists.meego.com mailing list].

* '''Help out one of our [http://wiki.meego.com/Quality#Projects quality teams for projects]'''
** If you have special interest or expertise in one of our projects, you are welcomed to help corresponding quality team to contribute in executing existing test cases, developing new test cases, reporting and following up bugs etc.

* '''Participate in QA tools development activities'''
** MeeGo QA tools team is developing and maintaining a bunch of [http://wiki.meego.com/Quality/QA-tools tools for quality assurance]. You are welcomed to contribute in [http://wiki.meego.com/Quality/QA_tools_development QA tools development activities].

* '''Contribute in sys-debug activities to root cause bugs and assign them to right package'''
** sys-debug activities are meaningful in facilitating bug fixes since a number of bugs especially system level bugs could be fixed more efficiently if they could be analyzed first to decide the guilty package so that right owner could be assigned the bug and work on it. The details of sys-debug process has been defined @ http://wiki.meego.com/Quality/SysDebug

Metrics/Creating a report

2011-07-07T15:16:32Z

Dneary: /* How to create a Pentaho report */

== How to create a Pentaho report ==

There are two ways to create a Pentaho report: the first is a method to create simple tabular reports using the Pentaho user console "ad-hoc reporting" capability, but as the more powerful method, we will concentrate on the [http://wiki.pentaho.com/display/Reporting/Report+Designer Pentaho Report Designer], available [http://sourceforge.net/projects/pentaho/files/Report%20Designer/ from Sourceforge].

There are four basic steps to creating a report in PRD:
# Make a database connection available as a JNDI source for the Pentaho platform
# Duplicate the JDNI configuration locally for PRD (since the designer will not have direct access to the Pentaho database)
# Create queries which will be made available to the report designer
# Lay out the report to make the results of those queries available to the user (as a graph, tabular data, etc)

This tutorial assumes that you have obtained and stored forum statistics in a local database, as described in [[../Gathering data#Forums]].
=== Configuring a new JNDI connection ===

The easiest way to configure a new JNDI connection is to start the Pentaho Administration Console (found in $PENTAHO_JOME/administration_console) with "start_pac.sh". The reason we use JNDI rather than a direct JDBC connection is that when we deploy the report to a different instance, where the username, password and database host are potentially different, we don't have to change the report, we simply use the same symbolic name for the JNDI source.

In Pentaho Admin Console (PAC), click "Administration", then "Database connections", and create a new connection. For the forum database, for example, the local parameters here were:

* Name: Forum
* Driver class: com.mysql.jdbc.Driver
* User: <db_user>
* Password: <sure_ill_tell_you>
* URL: jdbc:mysql://localhost:3306/forum

Then click "Test", and if everything is fine, you should see "Connection test successful".

Potential pitfalls are if you are using c3p0 libraries to do connection pooling [http://wiki.pentaho.com/display/ServerDoc2x/Configuring+for+MySQL as described in the Pentaho wiki], but [http://forums.pentaho.com/archive/index.php/t-78383.html have not included c3p0.jar on the classpath for PAC], or if the database user does not have the appropriate permissions or access for the database. You can troubleshoot permissions issues using the MySQL command line client.

=== Configuring JNDI for PRD ===

Before you can use a "pure" JNDI connection in PRD, you need to declare it locally to make it available.

Open the file <pre>~/.pentaho/simple-jndi/default.properties</pre> and add this to the bottom:

<pre>
Forum/type=javax.sql.DataSource
Forum/driver=com.mysql.jdbc.Driver
Forum/user=<db_user>
Forum/password=<sure_ill_tell_you>
Forum/url=jdbc:mysql://localhost:3306/forum
</pre>

Hopefully the parallels between this configuration and the JNDI configuration in PAC are obvious! The prefix "Forum/" represents the symbolic name of the JNDI connection.

=== Starting Pentaho Report Designer ===

Once you have downloaded PRD, you can run report_designer.sh directly from the directory in which you uncompressed the package. PRD looks like a typical Java application (that is to say, on Linux it's not very pretty).

[[Image:PRD_screenshot.png]]

Create a new report with File->New, and you will see on the right-hand side of the screen a pane with two tabs, "Data" and "Structure". PRD is semi-WYSIWYG. Reports have a fixed structure, and each part of the report is evaluated based on a query associated with the report. The group header, for example, will appear once for every group defined in a query, what we put in the data section will be evaluated once for each row returned by the query, and the report header will be evaluated only once per report generation. Styling of elements in the report is cascading and hierarchical: if I set a font family for the report root, this will cascade throughout the report. The styling syntax is very similar to CSS.

To make a report do anything useful, we much associate a query with it. If we want to use several queries in one single document, we must declare all the queries at the top level, and then use sub-reports for each query to display.

=== Creating and testing queries ===

Click on the Data tab in PRD. We can define a number of sources of data for the report by right-clicking on "Data sets". For a database source, we use the JDBC source type.

On the screen which pops up, we can add a new connection by clicking on the + icon above the connection names, and then choose "JNDI" as our connection type, and set the name of the connection for PRD with the name of the JNDI connection we configured before (see screenshot).

[[Image:Configure_JNDI.png]]

Test the connection with the "Test" button, then click "OK" to finalise things and make the connection available for queries.

Once the connection is correctly configured, you can create and edit queries. Click the "+" icon above the "Available queries" frame, type in a name, and you can start typing directly into the Query text entry area.

There is a graphical tool to help you join tables and perform more complicated wueries, which you can access by double-clicking the pencil icon above this area. By clicking on it, you will be brought to the SQL query designer. I have found this useful in the early stages of writing a queries, for simple joins between tables and for situations where I am not using any SQL functions, but for polishing queries, I tend to use the raw text.

For now, let's create a query to list the top 20 posters for last month. Drag the "top_posters" table onto the query canvas area on the right; all of the fields will be selected by default, and that's fine. Just to test the query, start with "where month=6 and year=2011" as the condition.

The final query is:
<pre>
SELECT
month,
year,
rank,
member,
posts
FROM
forum_top_posters
WHERE
month=6
and year=2011
LIMIT 20
</pre>

and when you hit "Preview", you should get a data table like this:
{|style="border-collapse: separate; border-spacing: 0; border-width: 1px; border-style: solid; border-color: #000; padding: 0" cellpadding="5"
!month!!year!!rank!!member!!posts
|-
|6|| 2011|| 1|| qgil|| 185
|-
|6|| 2011|| 2|| sjgadsby|| 115
|-
|6|| 2011|| 3|| shmerl|| 104
|-
|6|| 2011|| 4|| texrat|| 77
|-
|6|| 2011|| 5|| jaffa|| 60
|-
|6|| 2011|| 6|| swift11|| 52
|-
|6|| 2011|| 7|| jonay|| 42
|-
|6|| 2011|| 8|| mja|| 35
|-
|6|| 2011|| 9|| vitna|| 35
|-
|6|| 2011|| 10|| timoph|| 35
|-
|6|| 2011|| 11|| hartti|| 29
|-
|6|| 2011|| 12|| lm|| 27
|-
|6|| 2011|| 13|| rubear2009|| 26
|-
|6|| 2011|| 14|| andre|| 25
|-
|6|| 2011|| 15|| profebral|| 23
|-
|6|| 2011|| 16|| mikecomputing|| 21
|-
|6|| 2011|| 17|| ritratt|| 21
|-
|6|| 2011|| 18|| helex|| 19
|-
|6|| 2011|| 19|| cckwes|| 19
|-
|6|| 2011|| 20|| pycage|| 19
|}

Hit OK, and now your query is selected for use with your report.

To make the query always refer to "last month", you need to resort to datetime functions in MySQL in the WHERE clause.
<pre>
WHERE
month = month(date_add(now(), INTERVAL -1 MONTH))
AND year = year(date_add(now(), INTERVAL -1 MONTH))
</pre>

=== Laying out a report ===

By default, there are 5 visible areas on the report canvas:
* Page Header and Footer: These are what they usually are in documents, a short text on the top/bottom of every page
* Report Header and Footer: you can think of these as the introduction and conclusion of your report, they will be evaluated once when the report. This is a good place to put an overview graph, or some introductory text, a report summary, or perhaps a sub-report (we'll get to those later)
* Details: The meat and two veg of the report, the "details" section is evaluated for each row in the associated query

On the left hand side of the window, we have a number of different types of widgets (labels, text/number/date fields, included resources, images, shapes and so on.

You can drag and drop elements from the query (under the Data tab) or from the left of the window into the "Details" section now and preview the report, just to see what happens. For now, let's create a "Username" label, drag & drop the "member" field beside it, a "Posts" label, with the number of posts associated, and let's add a horizontal rule underneath. Select the horizontal rule and set its x offset to 5% and width to 90%, in the "Style" tab which is visible when the element is active. Then we can preview the report, just to make sure it's working OK, with "View->Preview".

If all is going well, you should see something like this:

[[Image:Report_preview.png]]

Now we can start making the report a little prettier.

There are a few areas in the document structure which are not visible on the canvas by default (and so we can't drop elements onto them). The important ones for us are:
* Group Header/Footer: You can use "GROUP BY" in your SQL queries to aggregate data corresponding to certain criteria together - this is useful for generating statistics per mailing list, per author, per month, etc. You can associate a group with a group header/footer (the outermost group will necessarily be the first field in the "group by" clause) to have the report broken up into different groups. The group header and footer appear at the top & bottom of each group
* Details Header/Footer: "Details header" is typically used to provide the header row in a results table.

To make these visible on the canvas, enabling you to drag & drop elements onto them, select them in the "Structure" tab on the left of the window, and under "Attributes", change the attribute "hide-on-canvas" to false.

You can then cut & paste the "Username" and "Posts" labels, add a "Rank" label, and arrange them as a header in the "Details header" area. You can set all the labels in the header to bold by setting the "font"/"bold" attribute in the "Style" tab to "true" for the details header area (the value will be inherited by all labels in the area).

Now set a report title using a Message field (you can include data from a query in the text of the field using $(parameter), and add <pre>monthname(date_add(now(), INTERVAL -1 MONTH)) as monthname</pre> to the fields in the query, and let's set the report title to "Top forum posters for $(monthname) $(year)". PS, if anyone knows how I can avoid those repeated calls to <pre>date_add(now(), INTERVAL -1 MONTH)</pre> in Pentaho, I would be delighted to know how.

Just to pretty things up a touch more, let's set a background colour for the header, and alternate the row colours for odd & even rows between white & pale yellow. To do the even/odd banding, we're going to use a Pentaho "Row banding" function, which you'll find in the Format->Row-Banding menu. For the moment, choose a colour for the "Visible colour" area, and click OK. Now in the Data tab, we can see our new row banding function. All we need to do is give it a name in that area, and drag & drop it to the Details area. I just called it "band". Then, select "band" in the Structure tab, and in the "Style" tab, set the field "visible" to "false".

If everything has gone to plan, when you choose "View->Preview", you should see something like the following:

[[Image:Report_table.png]]

== Next steps ==

As with anything graphical, the possibilities are boundless. The next step will be to explain how to create graphics, and integrate many queries into a singnle report using subreports, and finally the deployment of reports so that we can generate them on demand via the Pentaho BI server, or schedule regular static report generation for publication.

Metrics/Creating a report

2011-07-07T15:14:41Z

Dneary: /* Laying out a report */

== How to create a Pentaho report ==

There are two ways to create a Pentaho report: the first is a method to create simple tabular reports using the Pentaho user console "ad-hoc reporting" capability, but as the more powerful method, we will concentrate on the [http://wiki.pentaho.com/display/Reporting/Report+Designer Pentaho Report Designer], available [http://sourceforge.net/projects/pentaho/files/Report%20Designer/ from Sourceforge].

There are four basic steps to creating a report in PRD:
1. Make a database connection available as a JNDI source for the Pentaho platform
2. Duplicate the JDNI configuration locally for PRD (since the designer will not have direct access to the Pentaho database)
3. Create queries which will be made available to the report designer
4. Lay out the report to make the results of those queries available to the user (as a graph, tabular data, etc)

=== Configuring a new JNDI connection ===

The easiest way to configure a new JNDI connection is to start the Pentaho Administration Console (found in $PENTAHO_JOME/administration_console) with "start_pac.sh". The reason we use JNDI rather than a direct JDBC connection is that when we deploy the report to a different instance, where the username, password and database host are potentially different, we don't have to change the report, we simply use the same symbolic name for the JNDI source.

In Pentaho Admin Console (PAC), click "Administration", then "Database connections", and create a new connection. For the forum database, for example, the local parameters here were:

* Name: Forum
* Driver class: com.mysql.jdbc.Driver
* User: <db_user>
* Password: <sure_ill_tell_you>
* URL: jdbc:mysql://localhost:3306/forum

Then click "Test", and if everything is fine, you should see "Connection test successful".

Potential pitfalls are if you are using c3p0 libraries to do connection pooling [http://wiki.pentaho.com/display/ServerDoc2x/Configuring+for+MySQL as described in the Pentaho wiki], but [http://forums.pentaho.com/archive/index.php/t-78383.html have not included c3p0.jar on the classpath for PAC], or if the database user does not have the appropriate permissions or access for the database. You can troubleshoot permissions issues using the MySQL command line client.

=== Configuring JNDI for PRD ===

Before you can use a "pure" JNDI connection in PRD, you need to declare it locally to make it available.

Open the file <pre>~/.pentaho/simple-jndi/default.properties</pre> and add this to the bottom:

<pre>
Forum/type=javax.sql.DataSource
Forum/driver=com.mysql.jdbc.Driver
Forum/user=<db_user>
Forum/password=<sure_ill_tell_you>
Forum/url=jdbc:mysql://localhost:3306/forum
</pre>

Hopefully the parallels between this configuration and the JNDI configuration in PAC are obvious! The prefix "Forum/" represents the symbolic name of the JNDI connection.

=== Starting Pentaho Report Designer ===

Once you have downloaded PRD, you can run report_designer.sh directly from the directory in which you uncompressed the package. PRD looks like a typical Java application (that is to say, on Linux it's not very pretty).

[[Image:PRD_screenshot.png]]

Create a new report with File->New, and you will see on the right-hand side of the screen a pane with two tabs, "Data" and "Structure". PRD is semi-WYSIWYG. Reports have a fixed structure, and each part of the report is evaluated based on a query associated with the report. The group header, for example, will appear once for every group defined in a query, what we put in the data section will be evaluated once for each row returned by the query, and the report header will be evaluated only once per report generation. Styling of elements in the report is cascading and hierarchical: if I set a font family for the report root, this will cascade throughout the report. The styling syntax is very similar to CSS.

To make a report do anything useful, we much associate a query with it. If we want to use several queries in one single document, we must declare all the queries at the top level, and then use sub-reports for each query to display.

=== Creating and testing queries ===

Click on the Data tab in PRD. We can define a number of sources of data for the report by right-clicking on "Data sets". For a database source, we use the JDBC source type.

On the screen which pops up, we can add a new connection by clicking on the + icon above the connection names, and then choose "JNDI" as our connection type, and set the name of the connection for PRD with the name of the JNDI connection we configured before (see screenshot).

[[Image:Configure_JNDI.png]]

Test the connection with the "Test" button, then click "OK" to finalise things and make the connection available for queries.

Once the connection is correctly configured, you can create and edit queries. Click the "+" icon above the "Available queries" frame, type in a name, and you can start typing directly into the Query text entry area.

There is a graphical tool to help you join tables and perform more complicated wueries, which you can access by double-clicking the pencil icon above this area. By clicking on it, you will be brought to the SQL query designer. I have found this useful in the early stages of writing a queries, for simple joins between tables and for situations where I am not using any SQL functions, but for polishing queries, I tend to use the raw text.

For now, let's create a query to list the top 20 posters for last month. Drag the "top_posters" table onto the query canvas area on the right; all of the fields will be selected by default, and that's fine. Just to test the query, start with "where month=6 and year=2011" as the condition.

The final query is:
<pre>
SELECT
month,
year,
rank,
member,
posts
FROM
forum_top_posters
WHERE
month=6
and year=2011
LIMIT 20
</pre>

and when you hit "Preview", you should get a data table like this:
{|style="border-collapse: separate; border-spacing: 0; border-width: 1px; border-style: solid; border-color: #000; padding: 0" cellpadding="5"
!month!!year!!rank!!member!!posts
|-
|6|| 2011|| 1|| qgil|| 185
|-
|6|| 2011|| 2|| sjgadsby|| 115
|-
|6|| 2011|| 3|| shmerl|| 104
|-
|6|| 2011|| 4|| texrat|| 77
|-
|6|| 2011|| 5|| jaffa|| 60
|-
|6|| 2011|| 6|| swift11|| 52
|-
|6|| 2011|| 7|| jonay|| 42
|-
|6|| 2011|| 8|| mja|| 35
|-
|6|| 2011|| 9|| vitna|| 35
|-
|6|| 2011|| 10|| timoph|| 35
|-
|6|| 2011|| 11|| hartti|| 29
|-
|6|| 2011|| 12|| lm|| 27
|-
|6|| 2011|| 13|| rubear2009|| 26
|-
|6|| 2011|| 14|| andre|| 25
|-
|6|| 2011|| 15|| profebral|| 23
|-
|6|| 2011|| 16|| mikecomputing|| 21
|-
|6|| 2011|| 17|| ritratt|| 21
|-
|6|| 2011|| 18|| helex|| 19
|-
|6|| 2011|| 19|| cckwes|| 19
|-
|6|| 2011|| 20|| pycage|| 19
|}

Hit OK, and now your query is selected for use with your report.

To make the query always refer to "last month", you need to resort to datetime functions in MySQL in the WHERE clause.
<pre>
WHERE
month = month(date_add(now(), INTERVAL -1 MONTH))
AND year = year(date_add(now(), INTERVAL -1 MONTH))
</pre>

=== Laying out a report ===

By default, there are 5 visible areas on the report canvas:
* Page Header and Footer: These are what they usually are in documents, a short text on the top/bottom of every page
* Report Header and Footer: you can think of these as the introduction and conclusion of your report, they will be evaluated once when the report. This is a good place to put an overview graph, or some introductory text, a report summary, or perhaps a sub-report (we'll get to those later)
* Details: The meat and two veg of the report, the "details" section is evaluated for each row in the associated query

On the left hand side of the window, we have a number of different types of widgets (labels, text/number/date fields, included resources, images, shapes and so on.

You can drag and drop elements from the query (under the Data tab) or from the left of the window into the "Details" section now and preview the report, just to see what happens. For now, let's create a "Username" label, drag & drop the "member" field beside it, a "Posts" label, with the number of posts associated, and let's add a horizontal rule underneath. Select the horizontal rule and set its x offset to 5% and width to 90%, in the "Style" tab which is visible when the element is active. Then we can preview the report, just to make sure it's working OK, with "View->Preview".

If all is going well, you should see something like this:

[[Image:Report_preview.png]]

Now we can start making the report a little prettier.

There are a few areas in the document structure which are not visible on the canvas by default (and so we can't drop elements onto them). The important ones for us are:
* Group Header/Footer: You can use "GROUP BY" in your SQL queries to aggregate data corresponding to certain criteria together - this is useful for generating statistics per mailing list, per author, per month, etc. You can associate a group with a group header/footer (the outermost group will necessarily be the first field in the "group by" clause) to have the report broken up into different groups. The group header and footer appear at the top & bottom of each group
* Details Header/Footer: "Details header" is typically used to provide the header row in a results table.

To make these visible on the canvas, enabling you to drag & drop elements onto them, select them in the "Structure" tab on the left of the window, and under "Attributes", change the attribute "hide-on-canvas" to false.

You can then cut & paste the "Username" and "Posts" labels, add a "Rank" label, and arrange them as a header in the "Details header" area. You can set all the labels in the header to bold by setting the "font"/"bold" attribute in the "Style" tab to "true" for the details header area (the value will be inherited by all labels in the area).

Now set a report title using a Message field (you can include data from a query in the text of the field using $(parameter), and add <pre>monthname(date_add(now(), INTERVAL -1 MONTH)) as monthname</pre> to the fields in the query, and let's set the report title to "Top forum posters for $(monthname) $(year)". PS, if anyone knows how I can avoid those repeated calls to <pre>date_add(now(), INTERVAL -1 MONTH)</pre> in Pentaho, I would be delighted to know how.

Just to pretty things up a touch more, let's set a background colour for the header, and alternate the row colours for odd & even rows between white & pale yellow. To do the even/odd banding, we're going to use a Pentaho "Row banding" function, which you'll find in the Format->Row-Banding menu. For the moment, choose a colour for the "Visible colour" area, and click OK. Now in the Data tab, we can see our new row banding function. All we need to do is give it a name in that area, and drag & drop it to the Details area. I just called it "band". Then, select "band" in the Structure tab, and in the "Style" tab, set the field "visible" to "false".

If everything has gone to plan, when you choose "View->Preview", you should see something like the following:

[[Image:Report_table.png]]

== Next steps ==

As with anything graphical, the possibilities are boundless. The next step will be to explain how to create graphics, and integrate many queries into a singnle report using subreports, and finally the deployment of reports so that we can generate them on demand via the Pentaho BI server, or schedule regular static report generation for publication.

Metrics/Creating a report

2011-07-07T15:12:08Z

Dneary: /* Laying out a report */

== How to create a Pentaho report ==

There are two ways to create a Pentaho report: the first is a method to create simple tabular reports using the Pentaho user console "ad-hoc reporting" capability, but as the more powerful method, we will concentrate on the [http://wiki.pentaho.com/display/Reporting/Report+Designer Pentaho Report Designer], available [http://sourceforge.net/projects/pentaho/files/Report%20Designer/ from Sourceforge].

There are four basic steps to creating a report in PRD:
1. Make a database connection available as a JNDI source for the Pentaho platform
2. Duplicate the JDNI configuration locally for PRD (since the designer will not have direct access to the Pentaho database)
3. Create queries which will be made available to the report designer
4. Lay out the report to make the results of those queries available to the user (as a graph, tabular data, etc)

=== Configuring a new JNDI connection ===

The easiest way to configure a new JNDI connection is to start the Pentaho Administration Console (found in $PENTAHO_JOME/administration_console) with "start_pac.sh". The reason we use JNDI rather than a direct JDBC connection is that when we deploy the report to a different instance, where the username, password and database host are potentially different, we don't have to change the report, we simply use the same symbolic name for the JNDI source.

In Pentaho Admin Console (PAC), click "Administration", then "Database connections", and create a new connection. For the forum database, for example, the local parameters here were:

* Name: Forum
* Driver class: com.mysql.jdbc.Driver
* User: <db_user>
* Password: <sure_ill_tell_you>
* URL: jdbc:mysql://localhost:3306/forum

Then click "Test", and if everything is fine, you should see "Connection test successful".

Potential pitfalls are if you are using c3p0 libraries to do connection pooling [http://wiki.pentaho.com/display/ServerDoc2x/Configuring+for+MySQL as described in the Pentaho wiki], but [http://forums.pentaho.com/archive/index.php/t-78383.html have not included c3p0.jar on the classpath for PAC], or if the database user does not have the appropriate permissions or access for the database. You can troubleshoot permissions issues using the MySQL command line client.

=== Configuring JNDI for PRD ===

Before you can use a "pure" JNDI connection in PRD, you need to declare it locally to make it available.

Open the file <pre>~/.pentaho/simple-jndi/default.properties</pre> and add this to the bottom:

<pre>
Forum/type=javax.sql.DataSource
Forum/driver=com.mysql.jdbc.Driver
Forum/user=<db_user>
Forum/password=<sure_ill_tell_you>
Forum/url=jdbc:mysql://localhost:3306/forum
</pre>

Hopefully the parallels between this configuration and the JNDI configuration in PAC are obvious! The prefix "Forum/" represents the symbolic name of the JNDI connection.

=== Starting Pentaho Report Designer ===

Once you have downloaded PRD, you can run report_designer.sh directly from the directory in which you uncompressed the package. PRD looks like a typical Java application (that is to say, on Linux it's not very pretty).

[[Image:PRD_screenshot.png]]

Create a new report with File->New, and you will see on the right-hand side of the screen a pane with two tabs, "Data" and "Structure". PRD is semi-WYSIWYG. Reports have a fixed structure, and each part of the report is evaluated based on a query associated with the report. The group header, for example, will appear once for every group defined in a query, what we put in the data section will be evaluated once for each row returned by the query, and the report header will be evaluated only once per report generation. Styling of elements in the report is cascading and hierarchical: if I set a font family for the report root, this will cascade throughout the report. The styling syntax is very similar to CSS.

To make a report do anything useful, we much associate a query with it. If we want to use several queries in one single document, we must declare all the queries at the top level, and then use sub-reports for each query to display.

=== Creating and testing queries ===

Click on the Data tab in PRD. We can define a number of sources of data for the report by right-clicking on "Data sets". For a database source, we use the JDBC source type.

On the screen which pops up, we can add a new connection by clicking on the + icon above the connection names, and then choose "JNDI" as our connection type, and set the name of the connection for PRD with the name of the JNDI connection we configured before (see screenshot).

[[Image:Configure_JNDI.png]]

Test the connection with the "Test" button, then click "OK" to finalise things and make the connection available for queries.

Once the connection is correctly configured, you can create and edit queries. Click the "+" icon above the "Available queries" frame, type in a name, and you can start typing directly into the Query text entry area.

There is a graphical tool to help you join tables and perform more complicated wueries, which you can access by double-clicking the pencil icon above this area. By clicking on it, you will be brought to the SQL query designer. I have found this useful in the early stages of writing a queries, for simple joins between tables and for situations where I am not using any SQL functions, but for polishing queries, I tend to use the raw text.

For now, let's create a query to list the top 20 posters for last month. Drag the "top_posters" table onto the query canvas area on the right; all of the fields will be selected by default, and that's fine. Just to test the query, start with "where month=6 and year=2011" as the condition.

The final query is:
<pre>
SELECT
month,
year,
rank,
member,
posts
FROM
forum_top_posters
WHERE
month=6
and year=2011
LIMIT 20
</pre>

and when you hit "Preview", you should get a data table like this:
{|style="border-collapse: separate; border-spacing: 0; border-width: 1px; border-style: solid; border-color: #000; padding: 0" cellpadding="5"
!month!!year!!rank!!member!!posts
|-
|6|| 2011|| 1|| qgil|| 185
|-
|6|| 2011|| 2|| sjgadsby|| 115
|-
|6|| 2011|| 3|| shmerl|| 104
|-
|6|| 2011|| 4|| texrat|| 77
|-
|6|| 2011|| 5|| jaffa|| 60
|-
|6|| 2011|| 6|| swift11|| 52
|-
|6|| 2011|| 7|| jonay|| 42
|-
|6|| 2011|| 8|| mja|| 35
|-
|6|| 2011|| 9|| vitna|| 35
|-
|6|| 2011|| 10|| timoph|| 35
|-
|6|| 2011|| 11|| hartti|| 29
|-
|6|| 2011|| 12|| lm|| 27
|-
|6|| 2011|| 13|| rubear2009|| 26
|-
|6|| 2011|| 14|| andre|| 25
|-
|6|| 2011|| 15|| profebral|| 23
|-
|6|| 2011|| 16|| mikecomputing|| 21
|-
|6|| 2011|| 17|| ritratt|| 21
|-
|6|| 2011|| 18|| helex|| 19
|-
|6|| 2011|| 19|| cckwes|| 19
|-
|6|| 2011|| 20|| pycage|| 19
|}

Hit OK, and now your query is selected for use with your report.

To make the query always refer to "last month", you need to resort to datetime functions in MySQL in the WHERE clause.
<pre>
WHERE
month = month(date_add(now(), INTERVAL -1 MONTH))
AND year = year(date_add(now(), INTERVAL -1 MONTH))
</pre>

=== Laying out a report ===

By default, there are 5 visible areas on the report canvas:
* Page Header and Footer: These are what they usually are in documents, a short text on the top/bottom of every page
* Report Header and Footer: you can think of these as the introduction and conclusion of your report, they will be evaluated once when the report. This is a good place to put an overview graph, or some introductory text, a report summary, or perhaps a sub-report (we'll get to those later)
* Details: The meat and two veg of the report, the "details" section is evaluated for each row in the associated query

On the left hand side of the window, we have a number of different types of widgets (labels, text/number/date fields, included resources, images, shapes and so on.

You can drag and drop elements from the query (under the Data tab) or from the left of the window into the "Details" section now and preview the report, just to see what happens. For now, let's create a "Username" label, drag & drop the "member" field beside it, a "Posts" label, with the number of posts associated, and let's add a horizontal rule underneath. Select the horizontal rule and set its x offset to 5% and width to 90%, in the "Style" tab which is visible when the element is active. Then we can preview the report, just to make sure it's working OK, with "View->Preview".

If all is going well, you should see something like this:

[[Image:Report_preview.png]]

Now we can start making the report a little prettier.

There are a few areas in the document structure which are not visible on the canvas by default (and so we can't drop elements onto them). The important ones for us are:
* Group Header/Footer: You can use "GROUP BY" in your SQL queries to aggregate data corresponding to certain criteria together - this is useful for generating statistics per mailing list, per author, per month, etc. You can associate a group with a group header/footer (the outermost group will necessarily be the first field in the "group by" clause) to have the report broken up into different groups. The group header and footer appear at the top & bottom of each group
* Details Header/Footer: "Details header" is typically used to provide the header row in a results table.

To make these visible on the canvas, enabling you to drag & drop elements onto them, select them in the "Structure" tab on the left of the window, and under "Attributes", change the attribute "hide-on-canvas" to false.

You can then cut & paste the "Username" and "Posts" labels, add a "Rank" label, and arrange them as a header in the "Details header" area. You can set all the labels in the header to bold by setting the "font"/"bold" attribute in the "Style" tab to "true" for the details header area (the value will be inherited by all labels in the area).

Now set a report title using a Message field (you can include data from a query in the text of the field using $(parameter), and add <pre>monthname(date_add(now(), INTERVAL -1 MONTH)) as monthname</pre> to the fields in the query, and let's set the report title to "Top forum posters for $(monthname) $(year)". PS, if anyone knows how I can avoid those repeated calls to <pre>date_add(now(), INTERVAL -1 MONTH)</pre> in Pentaho, I would be delighted to know how.

Just to pretty things up a touch more, let's set a background colour for the header, and alternate the row colours for odd & even rows between white & pale yellow. To do the even/odd banding, we're going to use a Pentaho "Row banding" function, which you'll find in the Format->Row-Banding menu. For the moment, choose a colour for the "Visible colour" area, and click OK. Now in the Data tab, we can see our new row banding function. All we need to do is give it a name in that area, and drag & drop it to the Details area. I just called it "band". Then, select "band" in the Structure tab, and in the "Style" tab, set the field "visible" to "false".

If everything has gone to plan, when you choose "View->Preview", you should see something like the following:

[[Image:Report_table.png]]

File:Report table.png

2011-07-07T15:11:31Z

Dneary:

File:Report preview.png

2011-07-07T15:11:11Z

Dneary:

Metrics/Creating a report

2011-07-07T15:10:33Z

Dneary: /* Laying out a report */

== How to create a Pentaho report ==

There are two ways to create a Pentaho report: the first is a method to create simple tabular reports using the Pentaho user console "ad-hoc reporting" capability, but as the more powerful method, we will concentrate on the [http://wiki.pentaho.com/display/Reporting/Report+Designer Pentaho Report Designer], available [http://sourceforge.net/projects/pentaho/files/Report%20Designer/ from Sourceforge].

There are four basic steps to creating a report in PRD:
1. Make a database connection available as a JNDI source for the Pentaho platform
2. Duplicate the JDNI configuration locally for PRD (since the designer will not have direct access to the Pentaho database)
3. Create queries which will be made available to the report designer
4. Lay out the report to make the results of those queries available to the user (as a graph, tabular data, etc)

=== Configuring a new JNDI connection ===

The easiest way to configure a new JNDI connection is to start the Pentaho Administration Console (found in $PENTAHO_JOME/administration_console) with "start_pac.sh". The reason we use JNDI rather than a direct JDBC connection is that when we deploy the report to a different instance, where the username, password and database host are potentially different, we don't have to change the report, we simply use the same symbolic name for the JNDI source.

In Pentaho Admin Console (PAC), click "Administration", then "Database connections", and create a new connection. For the forum database, for example, the local parameters here were:

* Name: Forum
* Driver class: com.mysql.jdbc.Driver
* User: <db_user>
* Password: <sure_ill_tell_you>
* URL: jdbc:mysql://localhost:3306/forum

Then click "Test", and if everything is fine, you should see "Connection test successful".

Potential pitfalls are if you are using c3p0 libraries to do connection pooling [http://wiki.pentaho.com/display/ServerDoc2x/Configuring+for+MySQL as described in the Pentaho wiki], but [http://forums.pentaho.com/archive/index.php/t-78383.html have not included c3p0.jar on the classpath for PAC], or if the database user does not have the appropriate permissions or access for the database. You can troubleshoot permissions issues using the MySQL command line client.

=== Configuring JNDI for PRD ===

Before you can use a "pure" JNDI connection in PRD, you need to declare it locally to make it available.

Open the file <pre>~/.pentaho/simple-jndi/default.properties</pre> and add this to the bottom:

<pre>
Forum/type=javax.sql.DataSource
Forum/driver=com.mysql.jdbc.Driver
Forum/user=<db_user>
Forum/password=<sure_ill_tell_you>
Forum/url=jdbc:mysql://localhost:3306/forum
</pre>

Hopefully the parallels between this configuration and the JNDI configuration in PAC are obvious! The prefix "Forum/" represents the symbolic name of the JNDI connection.

=== Starting Pentaho Report Designer ===

Once you have downloaded PRD, you can run report_designer.sh directly from the directory in which you uncompressed the package. PRD looks like a typical Java application (that is to say, on Linux it's not very pretty).

[[Image:PRD_screenshot.png]]

Create a new report with File->New, and you will see on the right-hand side of the screen a pane with two tabs, "Data" and "Structure". PRD is semi-WYSIWYG. Reports have a fixed structure, and each part of the report is evaluated based on a query associated with the report. The group header, for example, will appear once for every group defined in a query, what we put in the data section will be evaluated once for each row returned by the query, and the report header will be evaluated only once per report generation. Styling of elements in the report is cascading and hierarchical: if I set a font family for the report root, this will cascade throughout the report. The styling syntax is very similar to CSS.

To make a report do anything useful, we much associate a query with it. If we want to use several queries in one single document, we must declare all the queries at the top level, and then use sub-reports for each query to display.

=== Creating and testing queries ===

Click on the Data tab in PRD. We can define a number of sources of data for the report by right-clicking on "Data sets". For a database source, we use the JDBC source type.

On the screen which pops up, we can add a new connection by clicking on the + icon above the connection names, and then choose "JNDI" as our connection type, and set the name of the connection for PRD with the name of the JNDI connection we configured before (see screenshot).

[[Image:Configure_JNDI.png]]

Test the connection with the "Test" button, then click "OK" to finalise things and make the connection available for queries.

Once the connection is correctly configured, you can create and edit queries. Click the "+" icon above the "Available queries" frame, type in a name, and you can start typing directly into the Query text entry area.

There is a graphical tool to help you join tables and perform more complicated wueries, which you can access by double-clicking the pencil icon above this area. By clicking on it, you will be brought to the SQL query designer. I have found this useful in the early stages of writing a queries, for simple joins between tables and for situations where I am not using any SQL functions, but for polishing queries, I tend to use the raw text.

For now, let's create a query to list the top 20 posters for last month. Drag the "top_posters" table onto the query canvas area on the right; all of the fields will be selected by default, and that's fine. Just to test the query, start with "where month=6 and year=2011" as the condition.

The final query is:
<pre>
SELECT
month,
year,
rank,
member,
posts
FROM
forum_top_posters
WHERE
month=6
and year=2011
LIMIT 20
</pre>

and when you hit "Preview", you should get a data table like this:
{|style="border-collapse: separate; border-spacing: 0; border-width: 1px; border-style: solid; border-color: #000; padding: 0" cellpadding="5"
!month!!year!!rank!!member!!posts
|-
|6|| 2011|| 1|| qgil|| 185
|-
|6|| 2011|| 2|| sjgadsby|| 115
|-
|6|| 2011|| 3|| shmerl|| 104
|-
|6|| 2011|| 4|| texrat|| 77
|-
|6|| 2011|| 5|| jaffa|| 60
|-
|6|| 2011|| 6|| swift11|| 52
|-
|6|| 2011|| 7|| jonay|| 42
|-
|6|| 2011|| 8|| mja|| 35
|-
|6|| 2011|| 9|| vitna|| 35
|-
|6|| 2011|| 10|| timoph|| 35
|-
|6|| 2011|| 11|| hartti|| 29
|-
|6|| 2011|| 12|| lm|| 27
|-
|6|| 2011|| 13|| rubear2009|| 26
|-
|6|| 2011|| 14|| andre|| 25
|-
|6|| 2011|| 15|| profebral|| 23
|-
|6|| 2011|| 16|| mikecomputing|| 21
|-
|6|| 2011|| 17|| ritratt|| 21
|-
|6|| 2011|| 18|| helex|| 19
|-
|6|| 2011|| 19|| cckwes|| 19
|-
|6|| 2011|| 20|| pycage|| 19
|}

Hit OK, and now your query is selected for use with your report.

To make the query always refer to "last month", you need to resort to datetime functions in MySQL in the WHERE clause.
<pre>
WHERE
month = month(date_add(now(), INTERVAL -1 MONTH))
AND year = year(date_add(now(), INTERVAL -1 MONTH))
</pre>

=== Laying out a report ===

By default, there are 5 visible areas on the report canvas:
* Page Header and Footer: These are what they usually are in documents, a short text on the top/bottom of every page
* Report Header and Footer: you can think of these as the introduction and conclusion of your report, they will be evaluated once when the report. This is a good place to put an overview graph, or some introductory text, a report summary, or perhaps a sub-report (we'll get to those later)
* Details: The meat and two veg of the report, the "details" section is evaluated for each row in the associated query

On the left hand side of the window, we have a number of different types of widgets (labels, text/number/date fields, included resources, images, shapes and so on.

You can drag and drop elements from the query (under the Data tab) or from the left of the window into the "Details" section now and preview the report, just to see what happens. For now, let's create a "Username" label, drag & drop the "member" field beside it, a "Posts" label, with the number of posts associated, and let's add a horizontal rule underneath. Select the horizontal rule and set its x offset to 5% and width to 90%, in the "Style" tab which is visible when the element is active. Then we can preview the report, just to make sure it's working OK, with "View->Preview".

If all is going well, you should see something like this:
[[Image:Report_preview.png]]

Now we can start making the report a little prettier.

There are a few areas in the document structure which are not visible on the canvas by default (and so we can't drop elements onto them). The important ones for us are:
* Group Header/Footer: You can use "GROUP BY" in your SQL queries to aggregate data corresponding to certain criteria together - this is useful for generating statistics per mailing list, per author, per month, etc. You can associate a group with a group header/footer (the outermost group will necessarily be the first field in the "group by" clause) to have the report broken up into different groups. The group header and footer appear at the top & bottom of each group
* Details Header/Footer: "Details header" is typically used to provide the header row in a results table.

To make these visible on the canvas, enabling you to drag & drop elements onto them, select them in the "Structure" tab on the left of the window, and under "Attributes", change the attribute "hide-on-canvas" to false.

You can then cut & paste the "Username" and "Posts" labels, add a "Rank" label, and arrange them as a header in the "Details header" area. You can set all the labels in the header to bold by setting the "font"/"bold" attribute in the "Style" tab to "true" for the details header area (the value will be inherited by all labels in the area).

Now set a report title using a Message field (you can include data from a query in the text of the field using $(parameter), and add <pre>monthname(date_add(now(), INTERVAL -1 MONTH)) as monthname</pre> to the fields in the query, and let's set the report title to "Top forum posters for $(monthname) $(year)". PS, if anyone knows how I can avoid those repeated calls to <pre>date_add(now(), INTERVAL -1 MONTH)</pre> in Pentaho, I would be delighted to know how.

Just to pretty things up a touch more, let's set a background colour for the header, and alternate the row colours for odd & even rows between white & pale yellow. To do the even/odd banding, we're going to use a Pentaho "Row banding" function, which you'll find in the Format->Row-Banding menu. For the moment, choose a colour for the "Visible colour" area, and click OK. Now in the Data tab, we can see our new row banding function. All we need to do is give it a name in that area, and drag & drop it to the Details area. I just called it "band". Then, select "band" in the Structure tab, and in the "Style" tab, set the field "visible" to "false".

If everything has gone to plan, when you choose "View->Preview", you should see something like this:
[[Image:Report_table.png]]

Metrics/Creating a report

2011-07-07T14:20:30Z

Dneary: /* Creating and testing queries */

Metrics/Creating a report

2011-07-07T13:34:01Z

Dneary: /* Creating and testing queries */

Metrics/Creating a report

2011-07-07T13:31:58Z

Dneary: /* Creating and testing queries */

File:PRD screenshot.png

2011-07-07T11:05:11Z

Dneary:

File:Configure JNDI.png

2011-07-07T11:04:58Z

Dneary:

Metrics/Creating a report

2011-07-07T11:04:31Z

Dneary: Start documenting the creation of Pentaho reports.

Metrics/Dashboard

2011-07-07T10:15:49Z

Dneary: /* Architecture */

== Community Metrics Dashboard ==

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:
* Drupal members
* Bugzilla (bugs opened, bugs closed, active users)
* Mailing lists (members, posts, threads)
* gitorious (commits, employer details for committers) - should use Jon Corbet's scripts like are used in the LF yearly kernel data.
* Wiki (edits, new pages)
* Forums (members, posts)
* IRC (total comments, people on channel)
* Transifex (Languages, translators, strings translated)
* Community OBS (uploads, users)
* SDK downloads (potentially extrapolated from meego.com)

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:

* [http://jasperforge.org/index.php?q=project/jasperreports JasperReports]
* [http://www.pentaho.com/ Pentaho]

The following are essentially ETL engines, and do not provide reporting or dashboard functionality:

* [http://www.talend.com/index.php Talend]
* [http://petals.ow2.org/ Petals]

[http://www.mulesoft.com/ 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.

=== Business intelligence engines ===

The area of Business Intelligence is littered with acronyms. Here's a quick overview of the main ones, and how they all fit together.

; BI
: Business Intelligence - general name for any middleware which allows you to query business processes (sales, inventory, etc) and get data overviews from it
; ETL
: Extract, Transform, and Load - the process if extracting data from a data source (database, screen scraping, text file parsing, whatever), transforming it to a well understood format, and loading it in your BI engine database or data warehouse. Good ETL solutions provide a nice way for you to connect another database and have new data sucked in at regular intervals, define views into the source data store which you can then query within your BI engine, etc. Pentaho's ETL, [http://kettle.pentaho.com/ Kettle], and [http://www.jaspersoft.com/jasperetl JasperETL], used by JasperReports, both provide (kind of) straightforward ways to hook into a MySQL database.
; ESB
: [http://en.wikipedia.org/wiki/Enterprise_service_bus Enterprise Service Bus] - a middleware bus providing a unique interface to applications on the front-end and data stores on the back end. Often used to link up many front-end applications (eg. library, student registration, employee payroll, syllabus management, accounting, supply-chain, student lodgement programmes, etc in a university). Not really useful for us, as far as I can tell.
; EAI
: Enterprise Application Integration - using software to integrate different applications together. As far as I can tell, this is a meaningless catch-all phrase for anything from kludges to architected business intelligence solutions.
; DW
: Data Warehouse. Basically the same thing as a database, as far as I can tell, but bigger and more impressive sounding.
; OLAP
: On-Line Analytical Processing. Commonly used acronym for extracting data via multi-dimensional queries. Databases can be configured to provide the results of this kind of query. As far as I can tell this is mostly a buzzword - an "OLAP database" like [http://mondrian.pentaho.com/ Mondrian] is basically the same thing as a database. "speed-of-thought" response times indeed.
; Business reporting
: An application which allows a graphical view of a database, and allows you to construct queries interactively, often using drag & drop. The results of these queries can then be plugged into graphing software for presentation in a dashboard.
; Dashboard
: Organised presentation of information in a web-page or other similar format allowing an at-a-glance overview of the situation for the data being measured.

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.

=== Comparison of candidate ETL/reporting ===

Modules available:

{|
! Software !! License !! ETL !! OLAP database !! BI server !! Reporting !! Dashboard module
|-
| Pentaho || EPL || [http://kettle.pentaho.com/ Kettle] || [http://mondrian.pentaho.com/ Mondrian] || [http://community.pentaho.com/projects/bi_platform/ Pentaho BI Platform] || [http://reporting.pentaho.com/ Pentaho Reporting] || [http://wiki.pentaho.com/display/COM/Community+Dashboard+Framework Community Dashboard Framework]
|-
| [http://www.jaspersoft.com/editions 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.

== Architecture ==

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 [https://docs.google.com/Doc?docid=0AdJmocc0fj_EZDJ3YmZiZF83M2RtaHhwcmRk&hl=en 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).

* [[../Installing Pentaho]]: A guide to getting Pentaho up and running for MeeGo Dashboard
* [[../Gathering data]]: A guide to installing and using the tools to gather data for a dashboard
* [[../Creating a report]]: Given data in a database, how do we generate a report in Pentaho, and deploy it to the dashboard?

=== Extracting data ===

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 [http://forge.morfeo-project.org/projects/libresoft-tools/ 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 [http://lwn.net/Articles/290957/ 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 [http://code.google.com/p/superseriousstats/ 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 [http://meego.transifex.net/stats/ some analytics] available on Transifex, and that there is a RESTful API available to query this data.

== Data to report ==

For each of the resources, the following statistics (at a minimum) should be extracted:

=== Drupal ===
* Members of meego.com (+ evolution month over month)
* Active members (need a decent way to hook up different ways a person can be active: wiki, ML, IRC, forum, git)

=== Mailing lists ===
* Subscriber numbers (+ evolution) - from Mailman directly, not available in mlstats
* Emails sent (+ evolution) - from mlstats
* Active participants (individuals with >=2 emails during month) - from mlstats
* Hot threads - from mlstats
* Top posters - from mlstats

==== Useful queries ====

* Count posts of most popular threads:
*: <code>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;</code>
* Count the number of posts for each person
*: <code>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;</code>
* Restricting queries to a date range / month
*: The query above gives month-by-month totals, you could add an order by year(first_date) to get the year too
*: The easiest way is to add <code>where month(first_date)=3 and year(first_date)=2010</code> for March 2010. For the current month, <code>month(m.first_date)=month(NOW()) and year(first_date)=year(NOW())</code> works.

=== Forum ===
* Posts per month (+evolution)
* Active posters (2+ posts during month)
* Hot topics
* Top posters

[http://forum.meego.com/stats/ Stats] are exported from the Forum in CSV format monthly.

=== Bugzilla ===
* Bugs created (+ evolution)
* Bugs resolved (+ evolution)
* Comments on bugs this month
* Active Bugzilla contributors (2+ comments during month)

==== Useful queries ====

* [http://sourceforge.net/projects/qareports/ Software Quality Reports for Pentaho] of course
* See the [http://bugs.meego.com/query.cgi?format=report-table bugzilla tabular reports] and relative dates for more.
* Other potential resources:
** [http://www.ravenbrook.com/project/p4dti/tool/cgi/bugzilla-schema/ Bugzilla database schema by version]
** [https://landfill.bugzilla.org/bugzilla-tip/report.cgi Demo site for standard Bugzilla reports]
** [https://wiki.mozilla.org/Bugzilla:SQLCookBook The Bugzilla SQL cookbook]
** [http://www.bugzillametrics.org/ Bugzilla Metrics] - Third party add-on to Bugzilla that produces lots of metrics
** browse.cgi and weekly-summary.html extensions of GNOME Bugzilla: [https://launchpad.net/bugzilla.gnome.org Code], examples in practice: [http://bugzilla.gnome.org/browse.cgi?product=Evolution browse.cgi], [https://bugzilla.gnome.org/page.cgi?id=weekly-bug-summary.html weekly-summary.cgi]

=== Mediawiki ===
* New wiki pages (+ evolution)
* Edits this month (+ evolution)
* Pages deleted this month (+ evolution)
* Unique editors this month (+ evoluion)

==== Queries ====

A [http://www.mediawiki.org/wiki/Extension:SpecialUserScore 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;

=== 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.

=== IRC ===

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 [https://github.com/tommyrot/superseriousstats/blob/master/empty_database_v3.sql 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 [https://github.com/tommyrot/superseriousstats/blob/master/html.class.php html.class.php] which we can use to generate some reports, though.

* Total IRC activity (by hour)
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`
* Total active participants (+ evolution) - we may be able to get "number of participants per hour/day/month" (so you can see if it's 2 guys taking amongst themselves or a larger group) - I'll ask tommyrot what the query should look like.
* Top contributors (per month)
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

=== Community & Official OBS ===
* Package submissions (+ evolution)
* Active participants (+ evolution)

== Not yet in scope ==

I have not yet considered how I might get web analytics and download stats.

Metrics/Gathering data

2011-07-07T10:13:05Z

Dneary: How we get Forum stats into metrics

For each of the services we gather data for, here's a guide to getting that data:

== Mailing lists - MLStats ==

Mailman mailing lists can be downloaded, parsed and stored in a MySQL database using [https://projects.libresoft.es/projects/show/mlstats MLStats].

The general idea is to point mlstats at the list archive page, and let it do the work of figuring out what to download.

We are carrying a small local patch to mlstats to ensure that it re-downloads the current month's archives and reparses them. The patch [https://bugzilla.libresoft.es/attachment.cgi?id=137 has been submitted upstream].

Once you have downloaded and unpacked MLStats 0.4, patched it with the patch above, and installed it with <pre>setup.py install --prefix=/install/path</pre> you will need to "prime the pump", and download and import the archives to all of the Maemo mailing lists.

The format of the mlstats command line is:
/path/to/mlstats --db-user=<username> --db-password=<password> http://lists.meego.com/pipermail/meego-announce/

The command line option <pre>--no-report</pre> suppresses the creation of a report after the import, useful for a cron job.

You can list the archives for a number of mailing lists together on the command line. The list of MeeGo mailing lists is [http://lists.meego.com/mailman/listinfo here].

for list in meego-adaptation-intel-automotive meego-announce meego-architecture \
meego-commits meego-community meego-dev meego-distribution-tools \
meego-events meego-handset meego-il10n meego-inputmethods meego-it \
meego-ivi meego-kernel meego-packaging meego-pm meego-porting \
meego-python meego-qa meego-releases meego-sdk meego-security \
meego-security-discussion meego-touch-dev meego-tv;
do
/path/to/mlstats --no-report --db-user=<username> --db-password=<password> http://lists.meego.com/pipermail/${list} >> /tmp/output.txt
done;

This should be run every night through cron.

=== Mailing list graph over time ===

The SQL query (yes, a Big Hairy Beast) which can be used to create a report graphing the messages posted to each list, month by month, is this:
<pre>
SELECT
`messages`.`mailing_list_url` AS list,
year(first_date) AS y,
monthname(first_date) AS mon,
month(first_date) AS m,
date_format(first_date, '%M %Y') as monthstr,
date_format(first_date,'%Y%m') as monthnum,
count(*) AS c
FROM
`messages`
WHERE
year(first_date) > 1979 and
mailing_list_url not like '%meego-commits%' and first_date<'2011-05-01'
GROUP BY
`messages`.`mailing_list_url`,
y,m
ORDER BY
monthnum ASC,
list ASC,
c ASC
</pre>

We use $monthnum to order the results over time, and $monthstr as a more meaningful X axis label. We eliminate all emails with bad Date headers and filter meego-commits out of our analysis, and group the messages by mailing list, to give the following graph as the final result (how to create a report will follow later):

[[Image:Mlgraph.png]]

== IRC - SuperSeriousStats ==

[http://code.google.com/p/superseriousstats/ SuperSeriousStats] is a super serious IRC log analyser, developed by Jos de Ruiter. It is written in PHP.

To use SuperSeriousStats, you need to install the php-cli command line interface. Once you have this, and the [https://github.com/tommyrot/superseriousstats/wiki/Setup-guide other dependencies for sss listed here], you will be able to run sss.

Before running it for the first time, [https://github.com/tommyrot/superseriousstats/blob/master/empty_database_v4.sql initialise the database]. You will need to download all of the [http://mg.pov.lt/meego-irclog/index.html IRC logs] you wish to import first. Logs are expected in the Supybot format ([http://mg.pov.lt/meego-irclog/%23meego.2011-06-15.log example]).

=== Getting the IRC logs ===
For the initial download, the following script should help get all the logs:

<pre>
for y in 2010 2011; do
for m in `seq -f '%02g' 1 12`; do
for d in `seq -f '%02g' 1 31`; do
wget http://mg.pov.lt/meego-irclog/%23meego.$y-$m-$d.log;
done;
done;
done;
</pre>

Thereafter, you can add the following line in your cron job to get the previous day's logs:
<pre>
logdate= `date +%Y-%m-%d -d yesterday`
wget -P $logdir http://mg.pov.lt/meego-irclog/%23meego.${logdate}.log;
</pre>

=== Parsing the logs ===

To run sss, you will need to modify a number of fields in sss.conf. Notably, I had to change the filename format to "*.Y-m-d.\lo\g" to match the log file names, and set log format to supybot. I recommend creating a separate config file for each channel you want to parse. Here is the appropriate sss.conf for #meego logs:
<pre>
#
# This file contains all of sss' settings along with their defaults.
# All values must be placed between double quotes, even empty ones.
#
################################# Required ###################################

channel = "#meego" # Name of the IRC channel.
timezone = "UTC" # Timezone the logs are in. Used for time offset
# calculations and conversions.
# See http://php.net/manual/en/timezones.php
db_host = "host" # IP address or FQDN of the MySQL server.
db_port = "3306" # Port the MySQL server is listening on.
db_user = "db_user" # MySQL user.
db_pass = "db_password" # MySQL password.
db_name = "db_name" # Name of the MySQL database used for sss.
parser = "parser_supybot" # The parser to use depending on logfile format.
# e.g. "parser_irssi" or "parser_eggdrop"

# This string contains the format of the date within a logfile filename.
# Examples:
# filename: #chatroom.20030131 dateformat: *.Ymd
# filename: #chatroom.20030131 dateformat: \#c\h\atroo\m.Ymd
# filename: chatroom.log-31012003 dateformat: *.*-dmY
# filename: chatroom.log-31012003.gz dateformat: *.*-dmY.\g\z
# filename: chatroom.log-31012003.gz dateformat: *.*-dmY.*
# See http://php.net/date_create_from_format for more specific syntax options.
logfile_dateformat = "*.Y-m-d.\lo\g"
</pre>

sss has a mechanism to prevent it from re-importing log files for the same channel, by storing dates for which it has already parsed files in the table "parse_history", but you can store summary data from several channels in one database.

To do the initial import, just run
php sss.php -c meego.conf -i ${logdir} -o report.html

And to do the nightly import, put the following in a shell script, and call it from cron:
<pre>
logdate=`date +%Y-%m-%d -d yesterday`
logdir=/path/to/logfiles
sssdir=/path/to/sss-4.0

/usr/bin/wget -P $logdir http://mg.pov.lt/meego-irclog/%23meego.${logdate}.log;
/usr/bin/php ${sssdir}/sss.php -c meego.conf -i ${logdir}/\#meego.${logdate}.log
</pre>

=== Queries ===

The sss report is built from a number of queries. A number of other useful sss queries are already listed in [[Metrics/Dashboard#IRC | the Dashboard page]]

'''Activity by month'''
<pre>
select date_format(`date`, '%Y-%m') as `date`,
sum(`l_total`) as `l_total`,
sum(`l_night`) as `l_night`,
sum(`l_morning`) as `l_morning`,
sum(`l_afternoon`) as `l_afternoon`,
sum(`l_evening`) as `l_evening`
from `channel`
group by year(`date`), month(`date`);
</pre>

'''Activity by day over last 30 days'''

<pre>
select `date`, `l_total`, `l_night`, `l_morning`, `l_afternoon`, `l_evening` from `channel`
where `date` > DATE_SUB(CURDATE(),INTERVAL 30 DAY);
</pre>

== Forums ==

Forum stats are available as a series of CSV files on [http://forums.meego.com/stats forums.meego.com], supplied monthly. We need to download the .csv files every month (just the latest ones), parse the CSV files into a database and generate a report from that.

=== Downloading CSV files ===

To get started and load up all of the old stats, run the following:

<pre>
wget -nd -P <local dir for data> -r -l1 --no-parent -A.csv http://forum.meego.com/stats
</pre>

This will download all CSV files to the local directory specified.

For the monthly refresh, we use wget in a cron script as follows:
<pre>
y=`/bin/date -d "1 month ago" +%Y`
m=`/bin/date -d "1 month ago" +%m`

wget -nd -P <local dir for data> -r -l1 --no-parent -A "${y}${m}*.csv" http://forum.meego.com/stats

</pre>

That wget command line is worth explaining:
* -nd: Don't create the remote directory structure when downloading the files locally
* -P <directory>: Download files to the parent directory specified
* -r: Recursively download
* -l1: Limit to 1 level of directories (combining -r and -l1 allows us to download several files at the same time)
* --no-parent: Ignore the .. link
* -A "${y}${m}*.csv": Match filenames of the form "YYYYMM*.csv" - gives us the latest stats files only

=== Database schema ===

We created 7 tables, one for each of the statistics provided by the forum.

Here is the database schema:
<pre>--
-- Table structure for table `forum_cumulative_posts`
--

DROP TABLE IF EXISTS `forum_cumulative_posts`;
CREATE TABLE `forum_cumulative_posts` (
`month` int(11) NOT NULL,
`year` int(11) NOT NULL,
`forum` varchar(50) NOT NULL DEFAULT '',
`posts` int(11) DEFAULT NULL,
PRIMARY KEY (`month`,`year`,`forum`)
);

--
-- Table structure for table `forum_cumulative_threads`
--

DROP TABLE IF EXISTS `forum_cumulative_threads`;
CREATE TABLE `forum_cumulative_threads` (
`month` int(11) NOT NULL,
`year` int(11) NOT NULL,
`forum` varchar(50) NOT NULL DEFAULT '',
`threads` int(11) DEFAULT NULL,
PRIMARY KEY (`month`,`year`,`forum`)
);

--
-- Table structure for table `forum_hottest_threads`
--

DROP TABLE IF EXISTS `forum_hottest_threads`;
CREATE TABLE `forum_hottest_threads` (
`month` int(11) NOT NULL,
`year` int(11) NOT NULL,
`rank` int(11) NOT NULL DEFAULT '0',
`title` varchar(255) DEFAULT NULL,
`posts` int(11) DEFAULT NULL,
PRIMARY KEY (`month`,`year`,`rank`)
);

--
-- Table structure for table `forum_most_viewed_threads`
--

DROP TABLE IF EXISTS `forum_most_viewed_threads`;
CREATE TABLE `forum_most_viewed_threads` (
`month` int(11) NOT NULL,
`year` int(11) NOT NULL,
`rank` int(11) NOT NULL DEFAULT '0',
`title` varchar(255) DEFAULT NULL,
`views` int(11) DEFAULT NULL,
PRIMARY KEY (`month`,`year`,`rank`)
);

--
-- Table structure for table `forum_posts`
--

DROP TABLE IF EXISTS `forum_posts`;
CREATE TABLE `forum_posts` (
`month` int(11) NOT NULL,
`year` int(11) NOT NULL,
`forum` varchar(50) NOT NULL DEFAULT '',
`posts` int(11) DEFAULT NULL,
PRIMARY KEY (`month`,`year`,`forum`)
);

--
-- Table structure for table `forum_top_posters`
--

DROP TABLE IF EXISTS `forum_top_posters`;
CREATE TABLE `forum_top_posters` (
`month` int(11) NOT NULL,
`year` int(11) NOT NULL,
`rank` int(11) NOT NULL DEFAULT '0',
`member` varchar(50) DEFAULT NULL,
`posts` int(11) DEFAULT NULL,
PRIMARY KEY (`month`,`year`,`rank`)
);

--
-- Table structure for table `forum_top_thanked`
--

DROP TABLE IF EXISTS `forum_top_thanked`;
CREATE TABLE `forum_top_thanked` (
`month` int(11) NOT NULL,
`year` int(11) NOT NULL,
`rank` int(11) NOT NULL DEFAULT '0',
`member` varchar(50) DEFAULT NULL,
`thanks` int(11) DEFAULT NULL,
PRIMARY KEY (`month`,`year`,`rank`)
);

</pre>

We import the files into the databases via <pre>LOAD DATA LOCAL INFILE</pre>

<pre>
cd <local data directory>

if [ -f ${y}${m}_forum_cumulative_posts.csv ]; then
echo "Importing ${y}${m}_forum_cumulative_posts.csv";

# forum,posts
mysql -u<db_user> -p<db_password> -h <host> <database> -e \
"LOAD DATA LOCAL INFILE '${y}${m}_forum_cumulative_posts.csv'
INTO TABLE forum_cumulative_posts
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"'
IGNORE 1 LINES
(forum, posts)
set year=$y, month=$m"
fi;

if [ -f ${y}${m}_forum_cumulative_threads.csv ]; then
echo "Importing ${y}${m}_forum_cumulative_threads.csv";

# forum,threads
mysql -u<db_user> -p<db_password> -h <host> <database> -e \
"LOAD DATA LOCAL INFILE '${y}${m}_forum_cumulative_threads.csv'
INTO TABLE forum_cumulative_threads
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"'
IGNORE 1 LINES
(forum, threads)
set year=$y, month=$m"
fi;

if [ -f ${y}${m}_forum_posts.csv ]; then
echo "Importing ${y}${m}_forum_posts.csv";

# forum,posts
mysql -u<db_user> -p<db_password> -h <host> <database> -e \
"LOAD DATA LOCAL INFILE '${y}${m}_forum_posts.csv'
INTO TABLE forum_posts
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"'
IGNORE 1 LINES
(forum, posts)
set year=$y, month=$m"
fi;

if [ -f ${y}${m}_hottest_threads.csv ]; then
echo "Importing ${y}${m}_hottest_threads.csv";

# rank,title,posts
mysql -u<db_user> -p<db_password> -h <host> <database> -e \
"LOAD DATA LOCAL INFILE '${y}${m}_hottest_threads.csv'
INTO TABLE forum_hottest_threads
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"'
IGNORE 1 LINES
(rank, title, posts)
set year=$y, month=$m"
fi;

if [ -f ${y}${m}_most_viewed_threads.csv ]; then
echo "Importing ${y}${m}_most_viewed_threads.csv";

# rank,title,views
mysql -u<db_user> -p<db_password> -h <host> <database> -e \
"LOAD DATA LOCAL INFILE '${y}${m}_most_viewed_threads.csv'
INTO TABLE forum_most_viewed_threads
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"'
IGNORE 1 LINES
(rank, title, views)
set year=$y, month=$m"
fi;

if [ -f ${y}${m}_top_posters.csv ]; then
echo "Importing ${y}${m}_top_posters.csv";

# rank,member,posts
mysql -u<db_user> -p<db_password> -h <host> <database> -e \
"LOAD DATA LOCAL INFILE '${y}${m}_top_posters.csv'
INTO TABLE forum_top_posters
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"'
IGNORE 1 LINES
(rank, member, posts)
set year=$y, month=$m"
fi;

if [ -f ${y}${m}_top_thanked.csv ]; then
echo "Importing ${y}${m}_top_thanked.csv";

# rank,member,thanks
mysql -u<db_user> -p<db_password> -h <host> <database> -e \
"LOAD DATA LOCAL INFILE '${y}${m}_top_thanked.csv'
INTO TABLE forum_top_thanked
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"'
IGNORE 1 LINES
(rank, member, thanks)
set year=$y, month=$m"
fi;

</pre>

We run this script (which downloads and imports the .csv files froim the server) monthly through cron. Not sure when the files are put up on the 1st, so I get them on the 2nd:
<pre>
# Retrieve forum stats monthly on 2nd of month
15 2 2 * * /home/dneary/bin/forum_stats.sh
</pre>

Metrics/Gathering data

2011-07-04T16:09:04Z

Dneary: /* Queries */

Metrics/Gathering data

2011-07-04T16:08:50Z

Dneary: /* Queries */

Metrics/Gathering data

2011-07-04T16:07:55Z

Dneary: /* IRC - SuperSeriousStats */

Metrics

2011-06-29T12:32:14Z

Dneary:

== Status Update ==

Metrics for April are posted below [[User:Dawnfoster|Dawnfoster]] 23:56, 10 May 2011 (UTC)

== Monthly Metrics ==
More details than you could possibly want to know about the activity in the MeeGo community.
* [http://wiki.meego.com/images/MeeGo_Community_Metrics_April_2011.pdf April 2011 (PDF)] or read the [https://meego.com/community/blogs/dawnfoster/2011/meego-community-update-and-metrics-april blog post summary].
* [http://wiki.meego.com/images/MeeGo_Community_Metrics_March_2011.pdf March 2011 (PDF)] or read the [https://meego.com/community/blogs/dawnfoster/2011/meego-community-update-and-metrics-march blog post summary]
* [http://wiki.meego.com/images/MeeGo_Community_Metrics_February_2011.pdf February 2011 (PDF)] or read the [http://meego.com/community/blogs/dawnfoster/2011/meego-community-update-and-metrics-february blog post summary]
* [http://wiki.meego.com/images/MeeGo_Community_Metrics_January_2011.pdf January 2011 (PDF)] or read the [http://meego.com/community/blogs/dawnfoster/2011/meego-community-update-and-metrics-january blog post summary]
* [http://wiki.meego.com/images/MeeGo_Community_Metrics_December_2010.pdf December 2010 (PDF)] or read the [http://meego.com/community/blogs/dawnfoster/2011/meego-community-update-and-metrics-december blog post summary]
* [http://wiki.meego.com/images/MeeGo_Community_Metrics_November_2010.pdf November 2010 (PDF)] or read the [http://meego.com/community/blogs/dawnfoster/2010/meego-community-update-and-metrics-november blog post summary]
* [http://wiki.meego.com/images/MeeGo_Community_Metrics_October_2010.pdf October 2010 (PDF)] or read the [http://meego.com/community/blogs/dawnfoster/2010/meego-community-update-and-metrics-october blog post summary]
* [http://wiki.meego.com/images/MeeGo_Community_Metrics_September_2010.pdf September 2010 (PDF)] or read the [http://meego.com/community/blogs/dawnfoster/2010/meego-community-update-and-metrics-september blog post summary]
* [http://wiki.meego.com/images/MeeGo_Community_Metrics_August_2010.pdf August 2010 (PDF)] or read the [http://meego.com/community/blogs/dawnfoster/2010/meego-community-update-and-metrics-august blog post summary]
* [http://wiki.meego.com/images/MeeGo_Community_Metrics_July_2010.pdf July 2010 (PDF)] or read the [http://meego.com/community/blogs/dawnfoster/2010/meego-community-update-and-metrics-july blog post summary]
* [http://wiki.meego.com/images/MeeGo_Community_Metrics_June_2010.pdf June 2010 (PDF)] or read the [http://meego.com/community/blogs/dawnfoster/2010/meego-community-metrics-june blog post summary]
* [http://wiki.meego.com/images/MeeGo_Community_Metrics_May_2010.pdf May 2010 (PDF)] or read the [http://meego.com/community/blogs/dawnfoster/2010/meego-community-metrics-may blog post summary]
* [http://wiki.meego.com/images/MeeGo_Community_Metrics_April_2010.pdf April 2010 (PDF)] or read the [http://meego.com/community/blogs/dawnfoster/2010/meego-community-metrics-april blog post summary]

== Community Health Metrics ==

My approach to metrics is to focus on measuring the things that help indicate whether the community is healthy and growing and identify any issues early. I have an internal deliverable to provide a monthly report in the first week of every month to my management team, and my plan is to deliver a similar report for the community. I also try to focus on picking the things that have the most impact rather than trying to measure everything. -- [http://wiki.meego.com/User:Dawnfoster Dawn Foster]

The metrics that are currently being gathered are organized into four categories:
* '''Awareness''': Finding the community and visiting it to learn more (potential community members)
* '''Membership''': Taking the time to sign up and join the community (passive and active community members)
* '''Engagement''': Interacting with other community members (active community members)
* '''Development''': Engaging with the MeeGo project at a code level (developers)

Note: [http://wiki.meego.com/User:Dawnfoster Dawn Foster] is currently gathering the metrics listed in this section.

=== Awareness Metrics ===

* Website Visits (source Google Analytics)
* Unique Visitors (source Google Analytics)
* Page Views (source Google Analytics)
* Social Media Posts (source Radian6)

=== Membership Metrics ===

* MeeGo.com Total Members (source Drupal)
* MeeGo-Dev ML Subscribers (source mailing lists)
* MeeGo-Community ML Subscribers (source mailing lists)
* MeeGo-SDK ML Subscribers (source mailing lists)
* MeeGo L10N ML subscribers (source mailing lists)

=== Engagement Metrics ===

* MeeGo-Dev ML Messages (source mailing lists)
* MeeGo-Community ML Messages (source mailing lists)
* MeeGo-SDK ML Messages (source mailing lists)
* MeeGo-iL10n ML messages (source mailing lists)
* MeeGo-Dev People with 2+ messages (source mailing lists / mlstats)
* MeeGo-Community People with 2+ messages (source mailing lists / mlstats)
* Forum Threads (source vBulletin)
* Blog Comments (source Drupal)
* Wiki Content Pages (source MediaWiki)
* Wiki Page Edits (source MediaWiki)
* Overall MeeGo Translation in the 16 POR languages (source Margie)
* Community-sourced translations (beyond the POR 16) that are at 50% complete or above (source Margie)
* Active language teams (source Margie)
* Overall MeeGo Translation in the 37 languages (source Margie)
* IRC top contributors and common words used (source irssistats from Stskeeps)

=== Code Metrics ===
* Downloads (source Anas)
* Commits (source Gitorius)
* New bugs (source Bugzilla)
* Closed bugs (source Bugzilla)
* Total bugs (source Bugzilla)

=== Content Analysis ===
* Most popular mailing list posts across each list (source mailing lists / mlstats)
* Most popular forum threads (source vBulletin)

== Metrics Dashboard Automation Project ==

* Lead: [[User:Dneary|Dave Neary]]
* Help from: [[User:Dawnfoster|Dawn Foster]]
* See [[/Dashboard]] for details.

== Biggest Gaps We Need to Fill Now ==
* Add Community OBS Metrics - work with lbt.
* Add Transifex (localization) metrics - work with Dmitry
* Add Download numbers (including mirrors) - work with Anas / Adam

== Future Improvement Ideas ==
This is a great way to help out on the MeeGo project - anyone can contribute to the metrics!

If you are starting a task, please add your name next to it to claim your task to avoid duplication (we don't want multiple people working on the same task without coordinating).

'''Dashboard''' - high priority
* An automated dashboard that people can access at any time gathering some of these metrics.
* We can start with some of the easy ones to gather and add more over time.
* Would be great to cut this by month for easy report generation.
* Note: see the [[#Points to consider | points to consider]] section below for some additional ideas

'''Other metrics that people may want to capture'''
* Software packaging
** New packages added to repository
** Updates to existing packages
** Number of developers/maintainers uploading packages
* Server performance and maintenance metrics
** Server use levels
** Network bandwidth
** Downtime and error reports
** OS and firmware patching
* bugzilla reports
** Fixed bug reports
** Unfixed closed bugreports (dups, not a bug, etc)
** Upstreamed bug reports (MeeGo bug matched with upstream bug report)
* various open source aspects
** Activity of Intel & Nokia developers vs activity of "NIN" (Not Intel/Nokia) developers
** Number of NIN committers, number of MeeGo specific modules with NIN committers.
** Not sure what you might want to quantitatively measure here - qualitative is hard to automate. See Siobhan O'Mahony's reports for Eclipse & various Eclipse Foundation reports for inspiration [[User:Dneary|Dneary]] 10:00, 10 March 2010 (UTC)
* membership / user accounts
** member satisfaction
** member recognition
** Not sure what you might want to quantitatively measure here - qualitative is hard to automate. [[User:Dneary|Dneary]] 10:00, 10 March 2010 (UTC)
* Planet MeeGo
** Blog posts
** Voting activity
** Reactions to posts from outside meego.com

'''Alerts'''
* Planned events
** Infrastructure service outages
* Unplanned events
** Capacity shortages (monitor storage and throughput; report on threshold breaches)
** Attacks on infrastructure

== Points to consider ==

* Reporting solution
** [http://www.slideshare.net/OpenLogic/open-source-reporting-tool-comparison A comparison of open source solutions] - compares [http://www.jaspersoft.com/ Jasper Reports], [http://www.pentaho.com/ Pentaho], [http://www.eclipse.org/birt/phoenix/ BIRT] and more
** [http://cricket.sourceforge.net/ Cricket] looks promising
** Direct graphing with [http://www.graphviz.org/ graphviz] might be simplest
** [http://developmentseed.org/blog/2010/apr/20/world-bank-open-data-initiative-launched-on-drupal Drupal solution example]
* Report sharing methods
** Dashboard(s) for ongoing data reports (server health, member stats, bugs, app stats, etc)
** Voluntary subscriptions (ie, RSS) for periodic public reports
* Public vs Private data

== References ==

* [http://dash.eclipse.org/dash/commits/web-app/ Eclipse Foundation dashboards]
* [http://www.artofcommunityonline.org The Art of Community] - Chapter 7: Measuring community
* [http://www.gitorious.org/mining-tools/gitdm gitdm] - Git data miner
* [http://www.horsepigcow.com/2007/10/metrics-for-healthy-communities/ Metrics for healthy communities] - Tara Hunt
* [http://spreadloveproject.pbworks.com/CommunityMeasurements Collection of metrics]
* [http://tools.libresoft.es/ Tools] for analysing CVS/SVN, mailing lists, social networks and more. Community metrics tools mecca.

== Contributors ==

* Coordinator: [http://meego.com/users/dawnfoster Dawn Foster/dawnfoster] - Intel's MeeGo community manager.

* [http://meego.com/users/dneary Dave Neary/dneary] - Maemo docmaster, I help grow free software communities.

* [http://meego.com/users/bergie Henri Bergius/bergie] - helping to automate the metrics.

== Additional Helpful Details on Specific Metrics ==

'''mlstats queries:'''
* Count posts of most popular threads:
*: <code>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;</code>

* Count the number of posts for each person
*: <code>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;</code>
* Restricting queries to a date range / month
*: The query above gives month-by-month totals, you could add an order by year(first_date) to get the year too
*: The easiest way is to add <code>where month(first_date)=3 and year(first_date)=2010</code> for March 2010. For the current month, <code>month(m.first_date)=month(NOW()) and year(first_date)=year(NOW())</code> works.

'''Bugzilla:'''
* A Bugzilla report that shows new bugs, closed bugs, and total bugs (or instructions for creating this report)
*: Standard Bugzilla search queries & reports can give counts for this. Using relative dates in the date fields you can get all changes from one month ago (set date to -1m) or one week ago (-1w).
** [http://is.gd/bOO6E New bugs this month, by product, and their current states] (shortened link)
** [http://bugs.meego.com/report.cgi?x_axis_field=&y_axis_field=product&z_axis_field=&query_format=report-table&short_desc_type=allwordssubstr&short_desc=&longdesc_type=allwordssubstr&longdesc=&bug_file_loc_type=allwordssubstr&bug_file_loc=&deadlinefrom=&deadlineto=&bug_status=RESOLVED&bug_status=RELEASED&bug_status=VERIFIED&bug_status=CLOSED&emailassigned_to1=1&emailtype1=substring&email1=&emailassigned_to2=1&emailreporter2=1&emailqa_contact2=1&emailcc2=1&emailtype2=substring&email2=&bugidtype=include&bug_id=&chfieldfrom=-1M&chfieldto=Now&chfield=bug_status&chfieldvalue=&format=table&action=wrap&field0-0-0=noop&type0-0-0=noop&value0-0-0= Resolved & closed bugs in the past month] (Status changed in the past month, and current status is one of resolved, released, verified or closed - not sure if this is what you want)
** There are lots of variations - play with [http://bugs.meego.com/query.cgi?format=report-table bugzilla tabular reports] and relative dates for more.
** Get the SQL queries which generate these reports by manually adding "&debug=1" to the URL
*: This might be easiest to automate as a Bugzilla extension querying the BZ database directly. Some potential resources:
** [http://www.ravenbrook.com/project/p4dti/tool/cgi/bugzilla-schema/ Bugzilla database schema by version]
** [https://landfill.bugzilla.org/bugzilla-tip/report.cgi Demo site for standard Bugzilla reports]
** [https://wiki.mozilla.org/Bugzilla:SQLCookBook The Bugzilla SQL cookbook]
** [http://www.bugzillametrics.org/ Bugzilla Metrics] - Third party add-on to Bugzilla that produces lots of metrics
** browse.cgi and weekly-summary.html extensions of GNOME Bugzilla: [https://launchpad.net/bugzilla.gnome.org Code], examples in practice: [http://bugzilla.gnome.org/browse.cgi?product=Evolution browse.cgi], [https://bugzilla.gnome.org/page.cgi?id=weekly-bug-summary.html weekly-summary.cgi]

'''MediaWiki'''
* A [http://www.mediawiki.org/wiki/Extension:SpecialUserScore 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).

'''IRC'''

Stskeeps has started running monthly IRC reports:
* [http://www.daimi.au.dk/~cvm/data/irssistats.html February]
* [http://www.daimi.au.dk/~cvm/data/irssistats-march.html March]
* [http://www.daimi.au.dk/~cvm/data/irssistats-april.html April]

Quality/UpstreamBugTrackers

2011-06-23T14:15:35Z

Dneary: /* Mapping */

== Practice ==

If a bug in code or in translations exists that comes from an "upstream" project and that was not changed in ("downstream") MeeGo it is common practice to forward this bug report to the corresponding upstream bugtracker.

This makes upstream developers / translators become aware of issues (that were found in MeeGo but affect any other downstream projects too) so they can get fixed upstream. Patches are of course also welcome to be attached in an upstream report.

Fixing issues upstream reduces the efforts of maintaining downstream patches. It is welcome to query first in the upstream bugtracker for a potentially already existing report covering the issue.

In case an upstream bug has been filed or has been found, add the URL to the field "See Also: Add Bug URLs:" in the report in bugs.meego.com, and vice versa if possible. CC yourself to the upstream bug report to keep track and update the report in bugs.meego.com once it is fixed upstream.

== Mapping ==
{| border="1" + Upstream Bug Trackers
!rowspan="2"|MeeGo Components
!colspan="3"|Upstream
|-
! Project
! Bugtracker URL
! Product for Translation Issues
|-
| Alsa
| Alsa
| https://bugtrack.alsa-project.org/alsa-bug/
|
|-
| Cairo, Exempi, Xorg, PackageKit
| FreeDesktop.org
| https://bugs.freedesktop.org/
|
|-
| Giflib
| Giflib
| http://sourceforge.net/tracker/?group_id=102202
|
|-
| Banshee, librest, libsocialweb, gnome-packagekit
| Gnome
| https://bugzilla.gnome.org/
| L10N
|-
| Icu
| Icu
| http://bugs.icu-project.org
|
|-
| KDE
| KDE
| https://bugs.kde.org/
| i18n
|-
| Fennec
| Mozilla
| https://bugzilla.mozilla.org/
|
|-
| Iptables
| Netfilter/Iptables
| http://bugzilla.netfilter.org/
|
|-
| ntp
| ntp
| http://bugs.ntp.org/
|
|-
| PulseAudio
| PulseAudio
| http://pulseaudio.org/report
|
|-
| Qt
| Qt
| http://bugreports.qt.nokia.com
|
|-
| sudo
| sudo
| http://www.sudo.ws/bugs/
|
|-
| SWI-Prolog
| SWI-Prolog
| http://www.swi-prolog.org/bugzilla/
|
|-
| WebKit, WebCore, CoreJS
| WebKit
| https://bugs.webkit.org/
|
|}

If the component you're looking for isn't listed here, please add it, possible places to find further bug trackers include:
* Ubuntu: https://launchpad.net/bugs/bugtrackers/
* Gnome: http://live.gnome.org/Bugsquad/TriageGuide/NonGnome

== Other bug trackers ==

=== Components related to MeeGo ===

For components that related to MeeGo but not part of MeeGo.

{| border="1" + Other Bug Trackers
! Project
! Bugtracker URL
|-
| Peregrine
| http://bugs.peregrine-communicator.org/
|}

=== Downstream MeeGo products ===

For products that are based on MeeGo.

{| border="1" + Other Bug Trackers
! Project
! Bugtracker URL
|-
| Nokia MeeGo 1.2 Harmattan
| http://www.developer.nokia.com/bugs/
|}

[[Category:QA]]

Quality/UpstreamBugTrackers

2011-06-23T14:15:18Z

Dneary: /* Mapping */

== Practice ==

If a bug in code or in translations exists that comes from an "upstream" project and that was not changed in ("downstream") MeeGo it is common practice to forward this bug report to the corresponding upstream bugtracker.

This makes upstream developers / translators become aware of issues (that were found in MeeGo but affect any other downstream projects too) so they can get fixed upstream. Patches are of course also welcome to be attached in an upstream report.

Fixing issues upstream reduces the efforts of maintaining downstream patches. It is welcome to query first in the upstream bugtracker for a potentially already existing report covering the issue.

In case an upstream bug has been filed or has been found, add the URL to the field "See Also: Add Bug URLs:" in the report in bugs.meego.com, and vice versa if possible. CC yourself to the upstream bug report to keep track and update the report in bugs.meego.com once it is fixed upstream.

== Mapping ==
{| border="1" + Upstream Bug Trackers
!rowspan="2"|MeeGo Components
!colspan="3"|Upstream
|-
! Project
! Bugtracker URL
! Product for Translation Issues
|-
| Alsa
| Alsa
| https://bugtrack.alsa-project.org/alsa-bug/
|
|-
| Cairo, Exempi, Xorg
| FreeDesktop.org
| https://bugs.freedesktop.org/
|
|-
| Giflib
| Giflib
| http://sourceforge.net/tracker/?group_id=102202
|
|-
| Banshee, librest, libsocialweb, gnome-packagekit
| Gnome
| https://bugzilla.gnome.org/
| L10N
|-
| Icu
| Icu
| http://bugs.icu-project.org
|
|-
| KDE
| KDE
| https://bugs.kde.org/
| i18n
|-
| Fennec
| Mozilla
| https://bugzilla.mozilla.org/
|
|-
| Iptables
| Netfilter/Iptables
| http://bugzilla.netfilter.org/
|
|-
| ntp
| ntp
| http://bugs.ntp.org/
|
|-
| PulseAudio
| PulseAudio
| http://pulseaudio.org/report
|
|-
| Qt
| Qt
| http://bugreports.qt.nokia.com
|
|-
| sudo
| sudo
| http://www.sudo.ws/bugs/
|
|-
| SWI-Prolog
| SWI-Prolog
| http://www.swi-prolog.org/bugzilla/
|
|-
| WebKit, WebCore, CoreJS
| WebKit
| https://bugs.webkit.org/
|
|}

If the component you're looking for isn't listed here, please add it, possible places to find further bug trackers include:
* Ubuntu: https://launchpad.net/bugs/bugtrackers/
* Gnome: http://live.gnome.org/Bugsquad/TriageGuide/NonGnome

== Other bug trackers ==

=== Components related to MeeGo ===

For components that related to MeeGo but not part of MeeGo.

{| border="1" + Other Bug Trackers
! Project
! Bugtracker URL
|-
| Peregrine
| http://bugs.peregrine-communicator.org/
|}

=== Downstream MeeGo products ===

For products that are based on MeeGo.

{| border="1" + Other Bug Trackers
! Project
! Bugtracker URL
|-
| Nokia MeeGo 1.2 Harmattan
| http://www.developer.nokia.com/bugs/
|}

[[Category:QA]]

Metrics/Gathering data

2011-06-14T22:03:35Z

Dneary: Add SSS import & parsing to metrics "Gathering data" page. Next up: mediawiki

Metrics/Gathering data

2011-06-14T12:12:00Z

Dneary:

For each of the services we gather data for, here's a guide to getting that data:

== Mailing lists ==

=== MLStats ===

Mailman mailing lists can be downloaded, parsed and stored in a MySQL database using [https://projects.libresoft.es/projects/show/mlstats MLStats].

The general idea is to point mlstats at the list archive page, and let it do the work of figuring out what to download.

We are carrying a small local patch to mlstats to ensure that it re-downloads the current month's archives and reparses them. The patch [https://bugzilla.libresoft.es/attachment.cgi?id=137 has been submitted upstream].

Once you have downloaded and unpacked MLStats 0.4, patched it with the patch above, and installed it with <pre>setup.py install --prefix=/install/path</pre> you will need to "prime the pump", and download and import the archives to all of the Maemo mailing lists.

The format of the mlstats command line is:
/path/to/mlstats --db-user=<username> --db-password=<password> http://lists.meego.com/pipermail/meego-announce/

The command line option <pre>--no-report</pre> suppresses the creation of a report after the import, useful for a cron job.

You can list the archives for a number of mailing lists together on the command line. The list of MeeGo mailing lists is [http://lists.meego.com/mailman/listinfo here].

for list in meego-adaptation-intel-automotive meego-announce meego-architecture \
meego-commits meego-community meego-dev meego-distribution-tools \
meego-events meego-handset meego-il10n meego-inputmethods meego-it \
meego-ivi meego-kernel meego-packaging meego-pm meego-porting \
meego-python meego-qa meego-releases meego-sdk meego-security \
meego-security-discussion meego-touch-dev meego-tv;
do
/path/to/mlstats --no-report --db-user=<username> --db-password=<password> http://lists.meego.com/pipermail/${list} >> /tmp/output.txt
done;

This should be run every night through cron.

=== Mailing list graph over time ===

The SQL query (yes, a Big Hairy Beast) which can be used to create a report graphing the messages posted to each list, month by month, is this:
<pre>
SELECT
`messages`.`mailing_list_url` AS list,
year(first_date) AS y,
monthname(first_date) AS mon,
month(first_date) AS m,
date_format(first_date, '%M %Y') as monthstr,
date_format(first_date,'%Y%m') as monthnum,
count(*) AS c
FROM
`messages`
WHERE
year(first_date) > 1979 and
mailing_list_url not like '%meego-commits%' and first_date<'2011-05-01'
GROUP BY
`messages`.`mailing_list_url`,
y,m
ORDER BY
monthnum ASC,
list ASC,
c ASC
</pre>

We use $monthnum to order the results over time, and $monthstr as a more meaningful X axis label. We eliminate all emails with bad Date headers and filter meego-commits out of our analysis, and group the messages by mailing list, to give the following graph as the final result (how to create a report will follow later):

[[Image:Mlgraph.png]]

File:Mlgraph.png

2011-06-10T16:36:26Z

Dneary: Graph of mailing list messages per month up to April 2011) on all MeeGo mailing lists except meego-commits

Graph of mailing list messages per month up to April 2011) on all MeeGo mailing lists except meego-commits

Metrics/Gathering data

2011-06-10T16:35:23Z

Dneary: /* Mailing list graph over time */

Metrics/Gathering data

2011-06-10T16:35:01Z

Dneary: /* MLStats */

For each of the services we gather data for, here's a guide to getting that data:

== Mailing lists ==

=== MLStats ===

Mailman mailing lists can be downloaded, parsed and stored in a MySQL database using [https://projects.libresoft.es/projects/show/mlstats MLStats].

The general idea is to point mlstats at the list archive page, and let it do the work of figuring out what to download.

We are carrying a small local patch to mlstats to ensure that it re-downloads the current month's archives and reparses them. The patch [https://bugzilla.libresoft.es/attachment.cgi?id=137 has been submitted upstream].

Once you have downloaded and unpacked MLStats 0.4, patched it with the patch above, and installed it with <pre>setup.py install --prefix=/install/path</pre> you will need to "prime the pump", and download and import the archives to all of the Maemo mailing lists.

The format of the mlstats command line is:
/path/to/mlstats --db-user=<username> --db-password=<password> http://lists.meego.com/pipermail/meego-announce/

The command line option <pre>--no-report</pre> suppresses the creation of a report after the import, useful for a cron job.

You can list the archives for a number of mailing lists together on the command line. The list of MeeGo mailing lists is [http://lists.meego.com/mailman/listinfo here].

for list in meego-adaptation-intel-automotive meego-announce meego-architecture \
meego-commits meego-community meego-dev meego-distribution-tools \
meego-events meego-handset meego-il10n meego-inputmethods meego-it \
meego-ivi meego-kernel meego-packaging meego-pm meego-porting \
meego-python meego-qa meego-releases meego-sdk meego-security \
meego-security-discussion meego-touch-dev meego-tv;
do
/path/to/mlstats --no-report --db-user=<username> --db-password=<password> http://lists.meego.com/pipermail/${list} >> /tmp/output.txt
done;

This should be run every night through cron.

=== Mailing list graph over time ===

The SQL query (yes, a Big Hairy Beast) which can be used to create a report graphing the messages posted to each list, month by month, is this:
{{{
SELECT
`messages`.`mailing_list_url` AS list,
year(first_date) AS y,
monthname(first_date) AS mon,
month(first_date) AS m,
date_format(first_date, '%M %Y') as monthstr,
date_format(first_date,'%Y%m') as monthnum,
count(*) AS c
FROM
`messages`
WHERE
year(first_date) > 1979 and
mailing_list_url not like '%meego-commits%' and first_date<'2011-05-01'
GROUP BY
`messages`.`mailing_list_url`,
y,m
ORDER BY
monthnum ASC,
list ASC,
c ASC
}}}

We use $monthnum to order the results over time, and $monthstr as a more meaningful X axis label. We eliminate all emails with bad Date headers and filter meego-commits out of our analysis, and group the messages by mailing list, to give the following graph as the final result (how to create a report will follow later):

[[File:Mlgraph.png]]

Metrics/Gathering data

2011-06-10T16:33:57Z

Dneary: /* MLStats */

For each of the services we gather data for, here's a guide to getting that data:

== Mailing lists ==

=== MLStats ===

Mailman mailing lists can be downloaded, parsed and stored in a MySQL database using [https://projects.libresoft.es/projects/show/mlstats MLStats].

The general idea is to point mlstats at the list archive page, and let it do the work of figuring out what to download.

We are carrying a small local patch to mlstats to ensure that it re-downloads the current month's archives and reparses them. The patch [https://bugzilla.libresoft.es/attachment.cgi?id=137 has been submitted upstream].

Once you have downloaded and unpacked MLStats 0.4, patched it with the patch above, and installed it with {{{setup.py install --prefix=/install/path}}} you will need to "prime the pump", and download and import the archives to all of the Maemo mailing lists.

The format of the mlstats command line is:
{{{
/path/to/mlstats --db-user=<username> --db-password=<password> http://lists.meego.com/pipermail/meego-announce/
}}}

The command line option {{{--no-report}}} suppresses the creation of a report after the import, useful for a cron job.

You can list the archives for a number of mailing lists together on the command line. The list of MeeGo mailing lists is [http://lists.meego.com/mailman/listinfo here].

{{{
for list in meego-adaptation-intel-automotive meego-announce meego-architecture \
meego-commits meego-community meego-dev meego-distribution-tools \
meego-events meego-handset meego-il10n meego-inputmethods meego-it \
meego-ivi meego-kernel meego-packaging meego-pm meego-porting \
meego-python meego-qa meego-releases meego-sdk meego-security \
meego-security-discussion meego-touch-dev meego-tv;
do
/path/to/mlstats --no-report --db-user=<username> --db-password=<password> http://lists.meego.com/pipermail/${list} >> /tmp/output.txt
done;
}}}

This should be run every night through cron.

=== Mailing list graph over time ===

The SQL query (yes, a Big Hairy Beast) which can be used to create a report graphing the messages posted to each list, month by month, is this:
{{{
SELECT
`messages`.`mailing_list_url` AS list,
year(first_date) AS y,
monthname(first_date) AS mon,
month(first_date) AS m,
date_format(first_date, '%M %Y') as monthstr,
date_format(first_date,'%Y%m') as monthnum,
count(*) AS c
FROM
`messages`
WHERE
year(first_date) > 1979 and
mailing_list_url not like '%meego-commits%' and first_date<'2011-05-01'
GROUP BY
`messages`.`mailing_list_url`,
y,m
ORDER BY
monthnum ASC,
list ASC,
c ASC
}}}

We use $monthnum to order the results over time, and $monthstr as a more meaningful X axis label. We eliminate all emails with bad Date headers and filter meego-commits out of our analysis, and group the messages by mailing list, to give the following graph as the final result (how to create a report will follow later):

[[File:Mlgraph.png]]

Metrics/Gathering data

2011-06-10T16:33:38Z

Dneary: Document mailing list graph.

For each of the services we gather data for, here's a guide to getting that data:

== Mailing lists ==

=== MLStats ===

Mailman mailing lists can be downloaded, parsed and stored in a MySQL database using [https://projects.libresoft.es/projects/show/mlstats MLStats].

The general idea is to point mlstats at the list archive page, and let it do the work of figuring out what to download.

We are carrying a small local patch to mlstats to ensure that it re-downloads the current month's archives and reparses them. The patch [https://bugzilla.libresoft.es/attachment.cgi?id=137 has been submitted upstream].

Once you have downloaded and unpacked MLStats 0.4, patched it with the patch above, and installed it with {{{setup.py install --prefix=/install/path}}} you will need to "prime the pump", and download and import the archives to all of the Maemo mailing lists.

The format of the mlstats command line is:
{{{
/path/to/mlstats --db-user=<username> --db-password=<password> http://lists.meego.com/pipermail/meego-announce/
}}}

The command line option {{--no-report}} suppresses the creation of a report after the import, useful for a cron job.

You can list the archives for a number of mailing lists together on the command line. The list of MeeGo mailing lists is [http://lists.meego.com/mailman/listinfo here].

{{{
for list in meego-adaptation-intel-automotive meego-announce meego-architecture \
meego-commits meego-community meego-dev meego-distribution-tools \
meego-events meego-handset meego-il10n meego-inputmethods meego-it \
meego-ivi meego-kernel meego-packaging meego-pm meego-porting \
meego-python meego-qa meego-releases meego-sdk meego-security \
meego-security-discussion meego-touch-dev meego-tv;
do
/path/to/mlstats --no-report --db-user=<username> --db-password=<password> http://lists.meego.com/pipermail/${list} >> /tmp/output.txt
done;
}}}

This should be run every night through cron.

=== Mailing list graph over time ===

The SQL query (yes, a Big Hairy Beast) which can be used to create a report graphing the messages posted to each list, month by month, is this:
{{{
SELECT
`messages`.`mailing_list_url` AS list,
year(first_date) AS y,
monthname(first_date) AS mon,
month(first_date) AS m,
date_format(first_date, '%M %Y') as monthstr,
date_format(first_date,'%Y%m') as monthnum,
count(*) AS c
FROM
`messages`
WHERE
year(first_date) > 1979 and
mailing_list_url not like '%meego-commits%' and first_date<'2011-05-01'
GROUP BY
`messages`.`mailing_list_url`,
y,m
ORDER BY
monthnum ASC,
list ASC,
c ASC
}}}

We use $monthnum to order the results over time, and $monthstr as a more meaningful X axis label. We eliminate all emails with bad Date headers and filter meego-commits out of our analysis, and group the messages by mailing list, to give the following graph as the final result (how to create a report will follow later):

[[File:Mlgraph.png]]

Metrics/Gathering data

2011-06-10T10:33:35Z

Dneary: Created page with "For each of the services we gather data for, here's a guide to getting that data: == Mailing lists == Mailman mailing lists can be downloaded, parsed and stored in a MySQL data..."

For each of the services we gather data for, here's a guide to getting that data:

== Mailing lists ==

Mailman mailing lists can be downloaded, parsed and stored in a MySQL database using [https://projects.libresoft.es/projects/show/mlstats MLStats].

The general idea is to point mlstats at the list archive page, and let it do the work of figuring out what to download.

We are carrying a small local patch to mlstats to ensure that it re-downloads the current month's archives and reparses them. The patch [https://bugzilla.libresoft.es/attachment.cgi?id=137 has been submitted upstream].

Metrics/Installing Pentaho

2011-06-09T11:41:42Z

Dneary: /* Installing Pentaho */

= Installing Pentaho =

This page is part of the instructions for replicating the [[../Dashboard | MeeGo Community Dashboard]] and helping us improve community metrics.

== Installing Tomcat ==

apt-get install tomcat6 tomcat6-examples tomcat6-docs tomcat6-admin

Add manager and admin role to <code>/etc/tomcat6/tomcat-users.xml</code>, add user to manager & admin roles with password for authentification.

Tomcat instance is on http://localhost:8080 by default, there are also http://localhost:8080/examples/ and http://localhost:8080/docs/. You can check your Tomcat installation by using the examples webapp.

Tomcat will be launched at start-up via <code>/etc/init.d/tomcat6</code>

=== Overview ===

Tomcat is an important common element of Pentaho and JasperReports, and it's worth documenting how it works.

Tomcat is basically a web server which allows the server-side execution of Java code in one of a number of ways:

# Servlets are Java objects which are instantiated on the server and which can conserve state across user sessions, and handle HTTP queries
# JSP pages allow Java code to be embedded directly in web pages (similar to PHP). JSPs are precompiled into servlets by the server.
# Taglibs allow the developer to define custom tags which will be parsed on the server and will run predefined methods of specific classes when they are used. Similar to JSPs, custom tags are pre-compiled in JSPs to instantiate and call the classes which handle them.

Tomcat thus requires a way to compile JSPs into Java code into .class files - this component of Tomcat is called Jasper. Tomcat has two other important components: the Coyote HTTP server parses HTTP traffic, and Catalina, its servlet container instantiates and stores classes in memory on the server.

Recent versions of Tomcat no longer include the JSP Standard Taglib Library by default. You will need to download and install [http://tomcat.apache.org/taglibs/standard/ JSTL from Apache] to make all of the examples work correctly - these are also required for Pentaho.

=== Configuration ===

Tomcat is typically started via an init script, <code>/etc/init.d/tomcat6</code>, which initiates the following environment variables:
; CATALINA_HOME
: the directory where the Tomcat binary, class files and any common JAR files are found
; CATALINA_BASE
: The directory where Catalina will generate class files and use as the base directory for any deployed webapps
; JAVA_HOME and JAVA_OPTS
: Environment variables for the system JRE (or JDK) install

Finally, the init shell will call catalina.sh in $CATALINA_HOME/bin to start the servlet engine.

On starting Catalina, a number of config files will be read, in the following order, and in the following places:

In $CATALINA_BASE/conf, the files catalina.policy, catalina.properties, logging.properties, content.xml, server.xml, tomcat-users.xml and web.xml are parsed:

; catalina.policy
: defines the global security policy for the server
; catalina.properties
: server-wide configuration
; logging.properties
: defines various logging-related options
; content.xml
: Defines [http://www.mulesoft.com/tomcat-context Tomcat Context] information. A Context is a single web application running inside Tomcat. Context can be defined in a number of ways:
:* $CATALINA_BASE/conf/context.xml - contexts defined here have their elements loaded by all webapps
:* $CATALINA_BASE/conf/[enginename]/[hostname]/context.xml.default
:* XML files in $CATALINA_BASE/conf/[enginename]/[hostname] - each XML file defines a new context path - examples.xml defines the context path "/examples"
:* /META-INF/context.xml files inside .war files - these files *should* get copied to $CATALINA_BASE/conf/[enginename]/[hostname]
:* Finally a Context element inside a Host element in conf/server.xml - since other methods provide a means for simple per-webapp contexts, this method should be avoided.
; server.xml
: [http://www.mulesoft.com/tomcat-configuration The global configuration file] - defines hosts, contexts, and more. Analogous to httpd.conf for apache.
; tomcat-users.xml
: Contains information about users, passwords and trusted Realms on the server
; web.xml
: Options and values which will be shared by all webapps. Each webapp also has a context-specific config file in WEB-INF/web.xml

== Installing Pentaho on Tomcat ==

The best guide I found for this is here: https://docs.google.com/Doc?docid=0AdJmocc0fj_EZDJ3YmZiZF83M2RtaHhwcmRk&hl=en

# Install Sun's Java and Tomcat, and get them working. On Ubuntu, this will set CATALINA_HOME to /usr/share/tomcat6 and CATALINA_BASE to /var/lib/tomcat6, and ${CATALINA_BASE}/conf is a sym link to /etc/tomcat6.
# Download and unpack pentaho-ce-3.7.0.stable.tar.gz from [http://sourceforge.net/projects/pentaho/ sourceforge]
# Move biserver-ce and administration-console to /opt/pentaho (or /var/lib/pentaho, wherever you want to install it - for the rest of the document, we'll refer to this as PENTAHO_HOME)
# Copy (if it's not already there) mysql-connector-java-5.0.7.jar from biserver-ce/tomcat/common/lib to $CATALINA_HOME/lib
# Copy pentaho, pentaho-styles and sw-styles webapps from biserver/tomcat/webapps/ to $CATALINA_BASE/webapps
# Move biserver-ce/pentaho-solutions to $PENTAHO_HOME
# Create the required Pentaho databases (quartz, hibernate and sample_datasource) complete with database users, by downloading and executing in order [https://docs.google.com/leaf?id=0B9Jmocc0fj_EN2MyZjc4ZjEtNzFkNC00NzIzLTljZTctZjIzZWQ1NjU3MzJk&hl=en these SQL scripts]. Apparently the sample data delivered with pentaho-ce is lacking in some way, but I don't understand how. You should change the passwords for hibuser and pentaho_user in these scripts to something better than 'password' before running them.

Once you have the databases, you need to configure them as data sources for the Pentaho web application. This has several aspects:
# Setting the JDBC security to use a MySQL database instead of the packages Hypersonic database (I'll admit, I really don't understand this) - [https://docs.google.com/Doc?docid=0AdJmocc0fj_EZDJ3YmZiZF83M2RtaHhwcmRk&hl=en this document] has details for changes needed
## applicationContext-spring-security-jdbc.xml in pentaho-solutions/system/: change driver to mysql & password for hibuser
## applicationContext-spring-security-hibernate.properties in pentaho-solutions/system/:change driver to mysql & password for hibuser
## hibernate-settings.xml in pentaho-solutions/system/hibernate: change config file to point to MySQL settings
## mysql5.hibernate.cfg.xml in pentaho-solutions/system/hibernate: change password for hibuser
# Configure the hibernate and quartz databases for use with the webapp
## Update the context.xml file which should be present (if you used the biserver-ce package, not the -manual one) in $CATALINA_BASE/webapps/pentaho/META-INF/ to use the MySQL JDBC driver (and set the password correctly for hibuser and pentaho_user)
# Configure the sampledata database for use as a data source: In the hibernate database, update the DATASOURCE table to set DRIVERCLASS, URL and QUERY to the appropriate values when the NAME is SampleData. If you change the password for the pentaho_user user from 'password', you will need to do the same for the SampleData datasource, which will require you to launch the administration panel. To do this, you first need to configure the panel to set the path to the pentaho-solutions directory and the pentaho webapp in $PENTAHO_HOME/administration-console/resource/config/, then run start_pac.sh in $PENTAHO_HOME/administration-console and connect to http://localhost:8099 (username/password is 'admin'/'password' by default)

Finally, we need to configure the Pentaho application for the local set-up:
# In $CATALINA_BASE/webapps/pentaho/WEB_INF/web.xml: Set the solutions-path parameter to $PENTAHO_HOME/pentaho-solutions
# Set fully-qualified-server-url to the name of the server, port and path for the webapp (eg. http://localhost:8080/pentaho) - if you want people to be able to use Pentaho remotely, this should be an IP address or hostname, rather than localhost (since it's the URL used internally to find resources)
# Disable startup of Hypersonic by commenting the relevant sections (hsqldb-databases and the appropriate listener)
# Add trusted connecting hosts to TrustedIpAddrs (I used a netmask: 192.168.1.0/255.255.255.0)

Finally, ensure that you have set permissions correctly for the Tomcat user on $PENTAHO_HOME/pentaho-solutions and $CATALINA_BASE/webapps/pentaho - the webapp needs write access there.

Now restart Tomcat, and go to http://localhost:8080/pentaho to see if it's working.

=== Configuring email ===

To send email from reports, you can configure smtp in pentaho-solutions/system/smtp-email/email_config.xml - there is a sample config file for GMail which you can copy over, if you use gmail SMTP, in email_config_gmail.xml - all you need to do is set your username, the email address which will appear in the "From:" header, and your password.

Metrics/Installing Pentaho

2011-06-08T10:12:47Z

Dneary: Document installation of Pentaho for metrics

= Installing Pentaho =

== Installing Tomcat ==

apt-get install tomcat6 tomcat6-examples tomcat6-docs tomcat6-admin

Add manager and admin role to <code>/etc/tomcat6/tomcat-users.xml</code>, add user to manager & admin roles with password for authentification.

Tomcat instance is on http://localhost:8080 by default, there are also http://localhost:8080/examples/ and http://localhost:8080/docs/. You can check your Tomcat installation by using the examples webapp.

Tomcat will be launched at start-up via <code>/etc/init.d/tomcat6</code>

=== Overview ===

Tomcat is an important common element of Pentaho and JasperReports, and it's worth documenting how it works.

Tomcat is basically a web server which allows the server-side execution of Java code in one of a number of ways:

# Servlets are Java objects which are instantiated on the server and which can conserve state across user sessions, and handle HTTP queries
# JSP pages allow Java code to be embedded directly in web pages (similar to PHP). JSPs are precompiled into servlets by the server.
# Taglibs allow the developer to define custom tags which will be parsed on the server and will run predefined methods of specific classes when they are used. Similar to JSPs, custom tags are pre-compiled in JSPs to instantiate and call the classes which handle them.

Tomcat thus requires a way to compile JSPs into Java code into .class files - this component of Tomcat is called Jasper. Tomcat has two other important components: the Coyote HTTP server parses HTTP traffic, and Catalina, its servlet container instantiates and stores classes in memory on the server.

Recent versions of Tomcat no longer include the JSP Standard Taglib Library by default. You will need to download and install [http://tomcat.apache.org/taglibs/standard/ JSTL from Apache] to make all of the examples work correctly - these are also required for Pentaho.

=== Configuration ===

Tomcat is typically started via an init script, <code>/etc/init.d/tomcat6</code>, which initiates the following environment variables:
; CATALINA_HOME
: the directory where the Tomcat binary, class files and any common JAR files are found
; CATALINA_BASE
: The directory where Catalina will generate class files and use as the base directory for any deployed webapps
; JAVA_HOME and JAVA_OPTS
: Environment variables for the system JRE (or JDK) install

Finally, the init shell will call catalina.sh in $CATALINA_HOME/bin to start the servlet engine.

On starting Catalina, a number of config files will be read, in the following order, and in the following places:

In $CATALINA_BASE/conf, the files catalina.policy, catalina.properties, logging.properties, content.xml, server.xml, tomcat-users.xml and web.xml are parsed:

; catalina.policy
: defines the global security policy for the server
; catalina.properties
: server-wide configuration
; logging.properties
: defines various logging-related options
; content.xml
: Defines [http://www.mulesoft.com/tomcat-context Tomcat Context] information. A Context is a single web application running inside Tomcat. Context can be defined in a number of ways:
:* $CATALINA_BASE/conf/context.xml - contexts defined here have their elements loaded by all webapps
:* $CATALINA_BASE/conf/[enginename]/[hostname]/context.xml.default
:* XML files in $CATALINA_BASE/conf/[enginename]/[hostname] - each XML file defines a new context path - examples.xml defines the context path "/examples"
:* /META-INF/context.xml files inside .war files - these files *should* get copied to $CATALINA_BASE/conf/[enginename]/[hostname]
:* Finally a Context element inside a Host element in conf/server.xml - since other methods provide a means for simple per-webapp contexts, this method should be avoided.
; server.xml
: [http://www.mulesoft.com/tomcat-configuration The global configuration file] - defines hosts, contexts, and more. Analogous to httpd.conf for apache.
; tomcat-users.xml
: Contains information about users, passwords and trusted Realms on the server
; web.xml
: Options and values which will be shared by all webapps. Each webapp also has a context-specific config file in WEB-INF/web.xml

== Installing Pentaho on Tomcat ==

The best guide I found for this is here: https://docs.google.com/Doc?docid=0AdJmocc0fj_EZDJ3YmZiZF83M2RtaHhwcmRk&hl=en

# Install Sun's Java and Tomcat, and get them working. On Ubuntu, this will set CATALINA_HOME to /usr/share/tomcat6 and CATALINA_BASE to /var/lib/tomcat6, and ${CATALINA_BASE}/conf is a sym link to /etc/tomcat6.
# Download and unpack pentaho-ce-3.7.0.stable.tar.gz from [http://sourceforge.net/projects/pentaho/ sourceforge]
# Move biserver-ce and administration-console to /opt/pentaho (or /var/lib/pentaho, wherever you want to install it - for the rest of the document, we'll refer to this as PENTAHO_HOME)
# Copy (if it's not already there) mysql-connector-java-5.0.7.jar from biserver-ce/tomcat/common/lib to $CATALINA_HOME/lib
# Copy pentaho, pentaho-styles and sw-styles webapps from biserver/tomcat/webapps/ to $CATALINA_BASE/webapps
# Move biserver-ce/pentaho-solutions to $PENTAHO_HOME
# Create the required Pentaho databases (quartz, hibernate and sample_datasource) complete with database users, by downloading and executing in order [https://docs.google.com/leaf?id=0B9Jmocc0fj_EN2MyZjc4ZjEtNzFkNC00NzIzLTljZTctZjIzZWQ1NjU3MzJk&hl=en these SQL scripts]. Apparently the sample data delivered with pentaho-ce is lacking in some way, but I don't understand how. You should change the passwords for hibuser and pentaho_user in these scripts to something better than 'password' before running them.

Once you have the databases, you need to configure them as data sources for the Pentaho web application. This has several aspects:
# Setting the JDBC security to use a MySQL database instead of the packages Hypersonic database (I'll admit, I really don't understand this) - [https://docs.google.com/Doc?docid=0AdJmocc0fj_EZDJ3YmZiZF83M2RtaHhwcmRk&hl=en this document] has details for changes needed
## applicationContext-spring-security-jdbc.xml in pentaho-solutions/system/: change driver to mysql & password for hibuser
## applicationContext-spring-security-hibernate.properties in pentaho-solutions/system/:change driver to mysql & password for hibuser
## hibernate-settings.xml in pentaho-solutions/system/hibernate: change config file to point to MySQL settings
## mysql5.hibernate.cfg.xml in pentaho-solutions/system/hibernate: change password for hibuser
# Configure the hibernate and quartz databases for use with the webapp
## Update the context.xml file which should be present (if you used the biserver-ce package, not the -manual one) in $CATALINA_BASE/webapps/pentaho/META-INF/ to use the MySQL JDBC driver (and set the password correctly for hibuser and pentaho_user)
# Configure the sampledata database for use as a data source: In the hibernate database, update the DATASOURCE table to set DRIVERCLASS, URL and QUERY to the appropriate values when the NAME is SampleData. If you change the password for the pentaho_user user from 'password', you will need to do the same for the SampleData datasource, which will require you to launch the administration panel. To do this, you first need to configure the panel to set the path to the pentaho-solutions directory and the pentaho webapp in $PENTAHO_HOME/administration-console/resource/config/, then run start_pac.sh in $PENTAHO_HOME/administration-console and connect to http://localhost:8099 (username/password is 'admin'/'password' by default)

Finally, we need to configure the Pentaho application for the local set-up:
# In $CATALINA_BASE/webapps/pentaho/WEB_INF/web.xml: Set the solutions-path parameter to $PENTAHO_HOME/pentaho-solutions
# Set fully-qualified-server-url to the name of the server, port and path for the webapp (eg. http://localhost:8080/pentaho) - if you want people to be able to use Pentaho remotely, this should be an IP address or hostname, rather than localhost (since it's the URL used internally to find resources)
# Disable startup of Hypersonic by commenting the relevant sections (hsqldb-databases and the appropriate listener)
# Add trusted connecting hosts to TrustedIpAddrs (I used a netmask: 192.168.1.0/255.255.255.0)

Finally, ensure that you have set permissions correctly for the Tomcat user on $PENTAHO_HOME/pentaho-solutions and $CATALINA_BASE/webapps/pentaho - the webapp needs write access there.

Now restart Tomcat, and go to http://localhost:8080/pentaho to see if it's working.

=== Configuring email ===

To send email from reports, you can configure smtp in pentaho-solutions/system/smtp-email/email_config.xml - there is a sample config file for GMail which you can copy over, if you use gmail SMTP, in email_config_gmail.xml - all you need to do is set your username, the email address which will appear in the "From:" header, and your password.

Metrics/Dashboard

2011-06-08T10:03:56Z

Dneary: /* Architecture */

== Community Metrics Dashboard ==

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:
* Drupal members
* Bugzilla (bugs opened, bugs closed, active users)
* Mailing lists (members, posts, threads)
* gitorious (commits, employer details for committers) - should use Jon Corbet's scripts like are used in the LF yearly kernel data.
* Wiki (edits, new pages)
* Forums (members, posts)
* IRC (total comments, people on channel)
* Transifex (Languages, translators, strings translated)
* Community OBS (uploads, users)
* SDK downloads (potentially extrapolated from meego.com)

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:

* [http://jasperforge.org/index.php?q=project/jasperreports JasperReports]
* [http://www.pentaho.com/ Pentaho]

The following are essentially ETL engines, and do not provide reporting or dashboard functionality:

* [http://www.talend.com/index.php Talend]
* [http://petals.ow2.org/ Petals]

[http://www.mulesoft.com/ 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.

=== Business intelligence engines ===

The area of Business Intelligence is littered with acronyms. Here's a quick overview of the main ones, and how they all fit together.

; BI
: Business Intelligence - general name for any middleware which allows you to query business processes (sales, inventory, etc) and get data overviews from it
; ETL
: Extract, Transform, and Load - the process if extracting data from a data source (database, screen scraping, text file parsing, whatever), transforming it to a well understood format, and loading it in your BI engine database or data warehouse. Good ETL solutions provide a nice way for you to connect another database and have new data sucked in at regular intervals, define views into the source data store which you can then query within your BI engine, etc. Pentaho's ETL, [http://kettle.pentaho.com/ Kettle], and [http://www.jaspersoft.com/jasperetl JasperETL], used by JasperReports, both provide (kind of) straightforward ways to hook into a MySQL database.
; ESB
: [http://en.wikipedia.org/wiki/Enterprise_service_bus Enterprise Service Bus] - a middleware bus providing a unique interface to applications on the front-end and data stores on the back end. Often used to link up many front-end applications (eg. library, student registration, employee payroll, syllabus management, accounting, supply-chain, student lodgement programmes, etc in a university). Not really useful for us, as far as I can tell.
; EAI
: Enterprise Application Integration - using software to integrate different applications together. As far as I can tell, this is a meaningless catch-all phrase for anything from kludges to architected business intelligence solutions.
; DW
: Data Warehouse. Basically the same thing as a database, as far as I can tell, but bigger and more impressive sounding.
; OLAP
: On-Line Analytical Processing. Commonly used acronym for extracting data via multi-dimensional queries. Databases can be configured to provide the results of this kind of query. As far as I can tell this is mostly a buzzword - an "OLAP database" like [http://mondrian.pentaho.com/ Mondrian] is basically the same thing as a database. "speed-of-thought" response times indeed.
; Business reporting
: An application which allows a graphical view of a database, and allows you to construct queries interactively, often using drag & drop. The results of these queries can then be plugged into graphing software for presentation in a dashboard.
; Dashboard
: Organised presentation of information in a web-page or other similar format allowing an at-a-glance overview of the situation for the data being measured.

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.

=== Comparison of candidate ETL/reporting ===

Modules available:

{|
! Software !! License !! ETL !! OLAP database !! BI server !! Reporting !! Dashboard module
|-
| Pentaho || EPL || [http://kettle.pentaho.com/ Kettle] || [http://mondrian.pentaho.com/ Mondrian] || [http://community.pentaho.com/projects/bi_platform/ Pentaho BI Platform] || [http://reporting.pentaho.com/ Pentaho Reporting] || [http://wiki.pentaho.com/display/COM/Community+Dashboard+Framework Community Dashboard Framework]
|-
| [http://www.jaspersoft.com/editions 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.

== Architecture ==

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 [https://docs.google.com/Doc?docid=0AdJmocc0fj_EZDJ3YmZiZF83M2RtaHhwcmRk&hl=en 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).

* [[../Installing Pentaho]]: A guide to getting Pentaho up and running for MeeGo Dashboard
* [[../Gathering data]]: A guide to installing and using the tools to gather data for a dashboard

=== Extracting data ===

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 [http://forge.morfeo-project.org/projects/libresoft-tools/ 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 [http://lwn.net/Articles/290957/ 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 [http://code.google.com/p/superseriousstats/ 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 [http://meego.transifex.net/stats/ some analytics] available on Transifex, and that there is a RESTful API available to query this data.

== Data to report ==

For each of the resources, the following statistics (at a minimum) should be extracted:

=== Drupal ===
* Members of meego.com (+ evolution month over month)
* Active members (need a decent way to hook up different ways a person can be active: wiki, ML, IRC, forum, git)

=== Mailing lists ===
* Subscriber numbers (+ evolution) - from Mailman directly, not available in mlstats
* Emails sent (+ evolution) - from mlstats
* Active participants (individuals with >=2 emails during month) - from mlstats
* Hot threads - from mlstats
* Top posters - from mlstats

==== Useful queries ====

* Count posts of most popular threads:
*: <code>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;</code>
* Count the number of posts for each person
*: <code>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;</code>
* Restricting queries to a date range / month
*: The query above gives month-by-month totals, you could add an order by year(first_date) to get the year too
*: The easiest way is to add <code>where month(first_date)=3 and year(first_date)=2010</code> for March 2010. For the current month, <code>month(m.first_date)=month(NOW()) and year(first_date)=year(NOW())</code> works.

=== Forum ===
* Posts per month (+evolution)
* Active posters (2+ posts during month)
* Hot topics
* Top posters

[http://forum.meego.com/stats/ Stats] are exported from the Forum in CSV format monthly.

=== Bugzilla ===
* Bugs created (+ evolution)
* Bugs resolved (+ evolution)
* Comments on bugs this month
* Active Bugzilla contributors (2+ comments during month)

==== Useful queries ====

* [http://sourceforge.net/projects/qareports/ Software Quality Reports for Pentaho] of course
* See the [http://bugs.meego.com/query.cgi?format=report-table bugzilla tabular reports] and relative dates for more.
* Other potential resources:
** [http://www.ravenbrook.com/project/p4dti/tool/cgi/bugzilla-schema/ Bugzilla database schema by version]
** [https://landfill.bugzilla.org/bugzilla-tip/report.cgi Demo site for standard Bugzilla reports]
** [https://wiki.mozilla.org/Bugzilla:SQLCookBook The Bugzilla SQL cookbook]
** [http://www.bugzillametrics.org/ Bugzilla Metrics] - Third party add-on to Bugzilla that produces lots of metrics
** browse.cgi and weekly-summary.html extensions of GNOME Bugzilla: [https://launchpad.net/bugzilla.gnome.org Code], examples in practice: [http://bugzilla.gnome.org/browse.cgi?product=Evolution browse.cgi], [https://bugzilla.gnome.org/page.cgi?id=weekly-bug-summary.html weekly-summary.cgi]

=== Mediawiki ===
* New wiki pages (+ evolution)
* Edits this month (+ evolution)
* Pages deleted this month (+ evolution)
* Unique editors this month (+ evoluion)

==== Queries ====

A [http://www.mediawiki.org/wiki/Extension:SpecialUserScore 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;

=== 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.

=== IRC ===

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 [https://github.com/tommyrot/superseriousstats/blob/master/empty_database_v3.sql 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 [https://github.com/tommyrot/superseriousstats/blob/master/html.class.php html.class.php] which we can use to generate some reports, though.

* Total IRC activity (by hour)
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`
* Total active participants (+ evolution) - we may be able to get "number of participants per hour/day/month" (so you can see if it's 2 guys taking amongst themselves or a larger group) - I'll ask tommyrot what the query should look like.
* Top contributors (per month)
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

=== Community & Official OBS ===
* Package submissions (+ evolution)
* Active participants (+ evolution)

== Not yet in scope ==

I have not yet considered how I might get web analytics and download stats.

MeeGo Conference Spring 2011

2011-05-11T07:44:11Z

Dneary: /* Introduction to Qt */

The main Conference site is: [http://sf2011.meego.com/ San Francisco Meego Conference 2011]

== Logistics ==
May 23 - 25: San Francisco Regency Hyatt
* Annual industry event
* Announcements / Information focus
* Broader industry audience
* Call for Proposals and Registration will open early February

== Organizing Committee ==
[[File:Organizing committee.pdf]]

'''Team Lead: Amy Leeland'''

Advisory and Community Interface:
* Dawn Foster - Community Management
* Quim Gil - Marketing Management

Program Committee:
* Lead: Dirk Hohndel
* Carsten Munk
* Thiago Maciera
* Ashley Walker (Speaker Management)

Coordination:
* Lead: Brian Warner Sponsorships
* AJ Reed: Post Event management
* Mike Shaver: Web Management
* Dave Neary: Early Bird Events

== Call for proposals ==

* [http://sf2011.meego.com/program/call-session-proposals CfP]
* [[MeeGo Conference Spring 2011/CfP Drafting|Draft CfP]]

== Hyatt Space details==

Link to conference area map:[http://sanfranciscoregency.hyatt.com/hyatt/images/hotels/sfors/floorplan.pdf]

'''2nd Floor ''Street Level'' entrance'''
*Grand Ballroom A+B+C will be used as our Keynote room with redundant Screens/Sound in Market Street Foyer and Grand Ballroom Foyer (Seats 1300, with 100 standing in lobby spaces and feeds coming out)
Breakouts and room descriptions as follows:
*1 Grand Ballroom A (700 theater)
*2 Grand Ballroom B (200 cab)
*3 Grand Ballroom C (200 cab)
*Tech Showcase will take place in Market Street Foyer and Grand Ballroom Foyer. Not to be just single table tops, requested 3 different orientations to choose from. Tea and Coffee will be available in Tech showcase area
*Registration + Welcome Kit fulfillment: Market Street Foyer and Grand Ballroom Foyer. Kits will be organized based upon gender/size, not name
*Media/Blogger Room: Regency A
*Keynote Prep Room: Regency B
*Event Office: Plaza Room

'''3rd floor ''Bay Level'''''
*4 Seacliff A-B (110 cab)
*5 Seacliff C-D (110 cab)
*6 Bayview Room A-B (200 cab)
*Platinum Meeting Rooms: Marina Room and Golden Gate room on Bay Level

'''4th floor ''Atrium Lobby Level'''''
*All Catering Breakfast, Lunch, breaks and receptions will be held in Atrium of Hotel
This includes Garden Room A+B, 13 Views Lounge, and Hospitality Room can seat up to 1400 in this area.
*Nokia Boardroom: Boardroom A [atrium]
*Intel Boardroom: Boardroom B [atrium]

'''1st Floor ''Pacific Concourse Level'''''
*This will all be used as Early Bird space partitioned rooms A-O (17,000 sq ft all partitionable) Saturday until Sunday evening then it will be………
*Hacker Lounge: Pacific Concourse K—O
*Meeting rooms partitioned in Pacific Concourse: A, B, C, D, E
*Gold sponsors get A and B
*Spaces F and G will be used as a meeting room bookable space

== Attendee information ==

For flight arrival & departure times, please see [[MeeGo Conference Spring 2011/Flight Information]] - feel free to add your own arrival & departure times, contact people to share taxis or meet up at the airport.

For sponsored attendees sharing rooms, please choose your sharing partner on the [[/Accommodation | accommodation page]], or we will assign the person sharing a room with you.

== MeeGo Conference Warm-Up ==

The MeeGo Conference Warm-Up will include workshops catering to MeeGo application developers, and tutorials to help people get started developing for the platform. We also plan to have some fun extra-curricular activities involving building things. The warm-up sessions will be held at the Hyatt in San Francisco (the conference hotel/ conference facility) on May 21-22, the weekend before the conference begins.

We will have two parallel tutorial tracks, on a range of topics related to MeeGo, and one workshop track where people can get together and work on programming problems or brainstorm application designs.

And we plan some fun activities throughout the weekend, including a Maker's Contest where contestants will have to use raw materials including balsa wood, paper clips, string, paper and glue to build a machine capable of launching a projectile (Trebuchet, slingshot, catapult, whatever) over a distance of ~10m. The results will be judged with a live-action Angry Birds contest. And every evening we'll be running Werewolf sessions.

=== Schedule ===

{| class="wikitable"
! Date/Time
! Tutorial 1
! Tutorial 2
! Workshop
|-
|Sat 21, 09:00 - 12:30
| [[#Introduction_to_Qt | Introduction to Qt ]]
| [[#Harmattan_for_developers | Harmattan for developers]]
| [[#UX_workshop | UX workshop ]]
|-
|Sat 21, 14:00 - 17:30
| [[#Introduction_to_Qt | Introduction to Qt]]
| [[#Community OBS | Community OBS & Software Distribution]]
| [[#UX_workshop | UX workshop]]
|-
|Sat 21, 18:00 - 19:30
| colspan="3" align="center" | [[#Makers_contest | Siege weapon building, live action Angry Birds]]
|-
|Sat 21, 21:00 - late
| colspan="3" align="center" | [[/Werewolf | Mini Werewolf]]
|-
|Sun 22, 09:00 - 12:30
| [[#Introduction_to_MeeGo_SDK | Introduction to MeeGo SDK]] ([http://appdeveloper.intel.com/events registration required])
| [[#Linux_developer_tools | Linux developer tools]]
| [[#Qt_development_workshop | Qt development workshop]]
|-
|Sun 22, 14:00 - 17:30
| [[#Introduction_to_MeeGo_SDK | Introduction to MeeGo SDK]] ([http://appdeveloper.intel.com/events registration required])
| [[#MeeGo_localisation | MeeGo localisation]]
| [[#Qt_development_workshop | Qt development workshop]]
|-
|Sun 22, 20:00 - late
| colspan="3" align="center" | [[/Werewolf | Mass Werewolf, MeeGo Conference 2011 version]]
|}

=== Introduction to Qt ===

Training course offered by Roland Krause from ICS and MeeGo community member Thomas Perl.

Topics covered include:
* Setting up the MeeGo SDK
* Installing MeeGo on the Device
* Installing and Configurating Qt SDK
* Configuring Qt Creator
* Intro to Qt Creator
* Understanding QMake
* Building your First Application
* Introduction to PySide
* Introduction to QML
** Elements
** Properties and Types
** Signals
** Anchoring and Positioning
** States
** Transitions
** Simple Animations

=== Harmattan for developers ===
Presented by Daniel Wilms

... description pending ...

=== Community OBS ===
David Greaves, Niels Breet and Henri Bergius will take you through the basics of using Community OBS, uploading your project, getting it built and packaged automatically, fixing any build issues, and making the software available on the community software downloads site.

This will be part tutorial, part hands-on workshop, part BOF.

=== UX workshop ===

Developers are invited to turn up and present their applications to other attendees and our UX experts, and identify together areas of improvement and possible designs for presenting the same functionality to the use.

=== Makers contest ===

10 teams of 3 to 4 people will have one hour to build the best siege weapon possible for a live action Angry Birds round which will decide the winner. Trebuchet, catapult or slingshot, the weapon must be able to send an Angry Bird bean-bag into a pre-arranged structure containing evil snorting pigs. The winner will be determined by our impartial judges and comperes, Dave Neary, Alison Chaiken & Julien Fourgeaud.

Equipment available to teams will include: various balsa wood cuts, glue guns and glue sticks, twine, paper clips, plain brown paper and elastic bands. There may be some other surprise materials thrown in on the day, if we're feeling generous.

=== Introduction to MeeGo SDK ===

'''Important: You need to [http://appdeveloper.intel.com/events register in advance] if you want to attend this session'''

The Intel AppUpSM Application Lab: MeeGo series will be in San Francisco for the MeeGo Conference Warm Up! Register now to Meet Bob Spencer from the MeeGo SDK team and members of the Intel AppUpSM developer program team to learn how to create and deploy MeeGo applications using the MeeGo SDK and the Intel AppUpTM SDK for MeeGo. Discover how to create exciting user experiences with MeeGo* and the Intel AppUpSM developer program. MeeGo promotes innovation and portability across multiple device types, such as tablets, netbooks and smartphones. Developing for MeeGo presents a great opportunity to make money and deploy your applications quickly and easily. You don't have to be attending the MeeGo Conference to attend this event!

Join us at The Hyatt Regency San Francisco Embarcadero Centre on Sunday, May 22, 2011 for one of our FREE training sessions to learn how to develop applications for MeeGo and the benefits of the Intel AppUpSM developer program. Two sessions are available to choose from:

Sunday, May 22: 9:00am - 12:30pm
Sunday, May 22: 2:00pm - 5:30pm

General Agenda:
* Doors open 30 minutes before each session start for check-in (check-in closes 10 minutes after start)
* Session Content
** Overview of the Intel AppUpSM center & Intel AppUpSM developer program
** Introduction to the MeeGo SDK
** Introduction to the Intel AppUpTM SDK Suite for MeeGo
** Application packaging and submission
* Talk to Intel engineers about your specific code & questions with an information Q&A session

Seating is limited so [http://appdeveloper.intel.com/events register today] at http://appdeveloper.intel.com/events

=== Linux developer tools ===

An overview of common Linux developer tools, including git, gdb and valgrind, by timeless.

'''Using MXR'''

* For Triagers -- when you get a crash trace
** Using identifier searches to walk through a stack trace
** When you get a bug report in a foreign language, using text
searches to work from the foreign report to the codebase's native
language
* For Architects -- when you want to understand the ramifications of
changing an API
** Using text searches to find
** Using identifier searches to
* For Linguists
** Using filtered text searches to get more awareness of context
* For themers
** When you see an image in the ui and need to find its name
* Aiding MXR
** Fields packagers can use to provide directory descriptions

=== MeeGo localisation ===

Dimitris Glezos and Margie Foster will walk you through the localisation process for MeeGo:
* Extracting translatable strings from an application
* Translating strings locally using Linguist
* Uploading strings to Transifex and translating them there
* Retrieving a translation package from Transifex and integrating it into your application
* Compiling and testing your translated application

=== Qt development workshop ===

A programming problem will be presented, and attendees will have a hands-on programming lab, with amateurs and experts on hand to help you out when you get into trouble. If you'd like to help out as an assistant during this training session, please add your name here:

'''Qt/QML/UI experts'''

* Thomas Perl
* Sampo Savola

== Hacker Lounge ==
Was a very popular activity for community members. Having it stocked with beer and snacks was perfect, and we'd love to do something similar for spring.

== Ideas for fun activities ==
* [http://www.bahiker.com/northbayhikes/stinson.html Hike in Marin County on Mt. Tamalpais] just across the Golden Gate Bridge and get great views of San Francisco (when it's not foggy). Potentially led by [[User:Alison| Alison Chaiken]]. Would require transportation to the trailhead from the hotel, perhaps a 45-minute drive.
* [http://www.blazingsaddles.com/store/?catid=7 Cycle across the Golden Gate Bridge], potentially guided by [[User:Alison| Alison Chaiken]]. The route is pancake flat but is on a regular city street with low-speed traffic. [http://www.blazingsaddles.com/maps-and-rides/san-francisco-self-guided-tours.aspx Riding across the Bridge] does require some caution on foggy days due to a wet surface. (Foggy days can occur any time of year.) I have inquired about group discounts. The bike rental is walkable from the hotel.
* The [[MeeGo_Conference_2010/Werewolf]] was popular, and we're scheming on some additional [[MeeGo_Conference_Spring_2011/Werewolf]] MeeGo Werewolf variations for 2010.

[[File:Example.jpg]]

MeeGo Conference Spring 2011

2011-05-09T08:09:28Z

Dneary: /* Flight Information */

The main Conference site is: [http://sf2011.meego.com/ San Francisco Meego Conference 2011]

== Logistics ==
May 23 - 25: San Francisco Regency Hyatt
* Annual industry event
* Announcements / Information focus
* Broader industry audience
* Call for Proposals and Registration will open early February

== Organizing Committee ==
[[File:Organizing committee.pdf]]

'''Team Lead: Amy Leeland'''

Advisory and Community Interface:
* Dawn Foster - Community Management
* Quim Gil - Marketing Management

Program Committee:
* Lead: Dirk Hohndel
* Carsten Munk
* Thiago Maciera
* Ashley Walker (Speaker Management)

Coordination:
* Lead: Brian Warner Sponsorships
* AJ Reed: Post Event management
* Mike Shaver: Web Management
* Dave Neary: Early Bird Events

== Call for proposals ==

* [http://sf2011.meego.com/program/call-session-proposals CfP]
* [[MeeGo Conference Spring 2011/CfP Drafting|Draft CfP]]

== Hyatt Space details==

Link to conference area map:[http://sanfranciscoregency.hyatt.com/hyatt/images/hotels/sfors/floorplan.pdf]

'''2nd Floor ''Street Level'' entrance'''
*Grand Ballroom A+B+C will be used as our Keynote room with redundant Screens/Sound in Market Street Foyer and Grand Ballroom Foyer (Seats 1300, with 100 standing in lobby spaces and feeds coming out)
Breakouts and room descriptions as follows:
*1 Grand Ballroom A (700 theater)
*2 Grand Ballroom B (200 cab)
*3 Grand Ballroom C (200 cab)
*Tech Showcase will take place in Market Street Foyer and Grand Ballroom Foyer. Not to be just single table tops, requested 3 different orientations to choose from. Tea and Coffee will be available in Tech showcase area
*Registration + Welcome Kit fulfillment: Market Street Foyer and Grand Ballroom Foyer. Kits will be organized based upon gender/size, not name
*Media/Blogger Room: Regency A
*Keynote Prep Room: Regency B
*Event Office: Plaza Room

'''3rd floor ''Bay Level'''''
*4 Seacliff A-B (110 cab)
*5 Seacliff C-D (110 cab)
*6 Bayview Room A-B (200 cab)
*Platinum Meeting Rooms: Marina Room and Golden Gate room on Bay Level

'''4th floor ''Atrium Lobby Level'''''
*All Catering Breakfast, Lunch, breaks and receptions will be held in Atrium of Hotel
This includes Garden Room A+B, 13 Views Lounge, and Hospitality Room can seat up to 1400 in this area.
*Nokia Boardroom: Boardroom A [atrium]
*Intel Boardroom: Boardroom B [atrium]

'''1st Floor ''Pacific Concourse Level'''''
*This will all be used as Early Bird space partitioned rooms A-O (17,000 sq ft all partitionable) Saturday until Sunday evening then it will be………
*Hacker Lounge: Pacific Concourse K—O
*Meeting rooms partitioned in Pacific Concourse: A, B, C, D, E
*Gold sponsors get A and B
*Spaces F and G will be used as a meeting room bookable space

== Attendee information ==

For flight arrival & departure times, please see [[MeeGo Conference Spring 2011/Flight Information]] - feel free to add your own arrival & departure times, contact people to share taxis or meet up at the airport.

For sponsored attendees sharing rooms, please choose your sharing partner on the [[/Accommodation | accommodation page]], or we will assign the person sharing a room with you.

== MeeGo Conference Warm-Up ==

The MeeGo Conference Warm-Up will include workshops catering to MeeGo application developers, and tutorials to help people get started developing for the platform. We also plan to have some fun extra-curricular activities involving building things. The warm-up sessions will be held at the Hyatt in San Francisco (the conference hotel/ conference facility) on May 21-22, the weekend before the conference begins.

We will have two parallel tutorial tracks, on a range of topics related to MeeGo, and one workshop track where people can get together and work on programming problems or brainstorm application designs.

And we plan some fun activities throughout the weekend, including a Maker's Contest where contestants will have to use raw materials including balsa wood, paper clips, string, paper and glue to build a machine capable of launching a projectile (Trebuchet, slingshot, catapult, whatever) over a distance of ~10m. The results will be judged with a live-action Angry Birds contest. And every evening we'll be running Werewolf sessions.

=== Schedule ===

{| class="wikitable"
! Date/Time
! Tutorial 1
! Tutorial 2
! Workshop
|-
|Sat 21, 09:00 - 12:30
| [[#Introduction_to_Qt | Introduction to Qt ]]
| [[#Harmattan_for_developers | Harmattan for developers]]
| [[#UX_workshop | UX workshop ]]
|-
|Sat 21, 14:00 - 17:30
| [[#Introduction_to_Qt | Introduction to Qt]]
| [[#Community OBS | Community OBS & Software Distribution]]
| [[#UX_workshop | UX workshop]]
|-
|Sat 21, 18:00 - 19:30
| colspan="3" align="center" | [[#Makers_contest | Siege weapon building, live action Angry Birds]]
|-
|Sat 21, 21:00 - late
| colspan="3" align="center" | [[/Werewolf | Mini Werewolf]]
|-
|Sun 22, 09:00 - 12:30
| [[#Introduction_to_MeeGo_SDK | Introduction to MeeGo SDK]] ([http://appdeveloper.intel.com/events registration required])
| [[#Linux_developer_tools | Linux developer tools]]
| [[#Qt_development_workshop | Qt development workshop]]
|-
|Sun 22, 14:00 - 17:30
| [[#Introduction_to_MeeGo_SDK | Introduction to MeeGo SDK]] ([http://appdeveloper.intel.com/events registration required])
| [[#MeeGo_localisation | MeeGo localisation]]
| [[#Qt_development_workshop | Qt development workshop]]
|-
|Sun 22, 20:00 - late
| colspan="3" align="center" | [[/Werewolf | Mass Werewolf, MeeGo Conference 2011 version]]
|}

=== Introduction to Qt ===

Training course offered by Gregg Leibovitz of ICS and Thomas Perl.

Topics covered include:
* Setting up the MeeGo SDK
* Installing MeeGo on the Device
* Installing and Configurating Qt SDK
* Configuring Qt Creator
* Intro to Qt Creator
* Understanding QMake
* Building your First Application
* Introduction to PySide
* Introduction to QML
** Elements
** Properties and Types
** Signals
** Anchoring and Positioning
** States
** Transitions
** Simple Animations

=== Harmattan for developers ===
Presented by Daniel Wilms

... description pending ...

=== Community OBS ===
David Greaves, Niels Breet and Henri Bergius will take you through the basics of using Community OBS, uploading your project, getting it built and packaged automatically, fixing any build issues, and making the software available on the community software downloads site.

This will be part tutorial, part hands-on workshop, part BOF.

=== UX workshop ===

Developers are invited to turn up and present their applications to other attendees and our UX experts, and identify together areas of improvement and possible designs for presenting the same functionality to the use.

=== Makers contest ===

10 teams of 3 to 4 people will have one hour to build the best siege weapon possible for a live action Angry Birds round which will decide the winner. Trebuchet, catapult or slingshot, the weapon must be able to send an Angry Bird bean-bag into a pre-arranged structure containing evil snorting pigs. The winner will be determined by our impartial judges and comperes, Dave Neary, Alison Chaiken & Julien Fouregard.

Equipment available to teams will include: various balsa wood cuts, glue guns and glue sticks, twine, paper clips, plain brown paper and elastic bands. There may be some other surprise materials thrown in on the day, if we're feeling generous.

=== Introduction to MeeGo SDK ===

'''Important: You need to [http://appdeveloper.intel.com/events register in advance] if you want to attend this session'''

The Intel AppUpSM Application Lab: MeeGo series will be in San Francisco for the MeeGo Conference Warm Up! Register now to Meet Bob Spencer from the MeeGo SDK team and members of the Intel AppUpSM developer program team to learn how to create and deploy MeeGo applications using the MeeGo SDK and the Intel AppUpTM SDK for MeeGo. Discover how to create exciting user experiences with MeeGo* and the Intel AppUpSM developer program. MeeGo promotes innovation and portability across multiple device types, such as tablets, netbooks and smartphones. Developing for MeeGo presents a great opportunity to make money and deploy your applications quickly and easily. You don't have to be attending the MeeGo Conference to attend this event!

Join us at The Hyatt Regency San Francisco Embarcadero Centre on Sunday, May 22, 2011 for one of our FREE training sessions to learn how to develop applications for MeeGo and the benefits of the Intel AppUpSM developer program. Two sessions are available to choose from:

Sunday, May 22: 9:00am - 12:30pm
Sunday, May 22: 2:00pm - 5:30pm

General Agenda:
* Doors open 30 minutes before each session start for check-in (check-in closes 10 minutes after start)
* Session Content
** Overview of the Intel AppUpSM center & Intel AppUpSM developer program
** Introduction to the MeeGo SDK
** Introduction to the Intel AppUpTM SDK Suite for MeeGo
** Application packaging and submission
* Talk to Intel engineers about your specific code & questions with an information Q&A session

Seating is limited so [http://appdeveloper.intel.com/events register today] at http://appdeveloper.intel.com/events

=== Linux developer tools ===

An overview of common Linux developer tools, including git, gdb and valgrind, by timeless.

'''Using MXR'''

* For Triagers -- when you get a crash trace
** Using identifier searches to walk through a stack trace
** When you get a bug report in a foreign language, using text
searches to work from the foreign report to the codebase's native
language
* For Architects -- when you want to understand the ramifications of
changing an API
** Using text searches to find
** Using identifier searches to
* For Linguists
** Using filtered text searches to get more awareness of context
* For themers
** When you see an image in the ui and need to find its name
* Aiding MXR
** Fields packagers can use to provide directory descriptions

=== MeeGo localisation ===

Dimitris Glezos and Margie Foster will walk you through the localisation process for MeeGo:
* Extracting translatable strings from an application
* Translating strings locally using Linguist
* Uploading strings to Transifex and translating them there
* Retrieving a translation package from Transifex and integrating it into your application
* Compiling and testing your translated application

=== Qt development workshop ===

A programming problem will be presented, and attendees will have a hands-on programming lab, with amateurs and experts on hand to help you out when you get into trouble. If you'd like to help out as an assistant during this training session, please add your name here:

'''Qt/QML/UI experts'''

* Thomas Perl
* Sampo Savola

== Hacker Lounge ==
Was a very popular activity for community members. Having it stocked with beer and snacks was perfect, and we'd love to do something similar for spring.

== Ideas for fun activities ==
* [http://www.bahiker.com/northbayhikes/stinson.html Hike in Marin County on Mt. Tamalpais] just across the Golden Gate Bridge and get great views of San Francisco (when it's not foggy). Potentially led by [[User:Alison| Alison Chaiken]]. Would require transportation to the trailhead from the hotel, perhaps a 45-minute drive.
* [http://www.blazingsaddles.com/store/?catid=7 Cycle across the Golden Gate Bridge], potentially guided by [[User:Alison| Alison Chaiken]]. The route is pancake flat but is on a regular city street with low-speed traffic. [http://www.blazingsaddles.com/maps-and-rides/san-francisco-self-guided-tours.aspx Riding across the Bridge] does require some caution on foggy days due to a wet surface. (Foggy days can occur any time of year.) I have inquired about group discounts. The bike rental is walkable from the hotel.
* The [[MeeGo_Conference_2010/Werewolf]] was popular, and we're scheming on some additional [[MeeGo_Conference_Spring_2011/Werewolf]] MeeGo Werewolf variations for 2010.

[[File:Example.jpg]]

MeeGo Conference Spring 2011/accommodations

2011-05-09T08:06:17Z

Dneary: moved MeeGo Conference Spring 2011/accommodations to MeeGo Conference Spring 2011/Accommodation: Naming conventions (leaving redirect)

#REDIRECT [[MeeGo Conference Spring 2011/Accommodation]]