Sakai Queries / Reporting Metrics

[Charlie Williams]

Hello everyone,

I am trying to write several queries (that would eventually be used in reports) to validate the success of our Sakai implementation. Some examples of our metrics include: the number of user interactions per time period, number of posts on various tools/sites, and the total number of uploaded resources.

It has proven difficult to write some of these queries, since I am not very familiar with how information is stored in the Sakai database. Does anyone have any suggestions on the best method of finding the information I need for each query? How do you approach writing new "report" queries?

Thanks,

Charlie Williams
University of Alabama
MIS/Enterprise Integration

[Adams, David]

Charlie,

I'm sure you'll get better answers from folks more experienced with
Sakai, but a quick word of warning: If you are trying to do reporting
solely with SQL you are going to be quickly frustrated beyond a few
basic reports. Sakai's data structures and relational persistence
patterns have been designed for use by a Java API that's developed by
dozens of disparate groups, and quite explicitly *not* for
SQL-friendliness. In fact, the message sysadmins often get in response
to similar questions is that one shouldn't be looking at--and certainly
not modifying-the database directly, as it can be misleading and
incomplete. While that's not always possible, it's definitely important
to keep in mind.

 

The more you dig in, the more you'll find that many "linked" tables are
not linked at all using any direct relational conventions, but rather
require some knowledge (or good guesses) about how the API works and
manual "joins" with some external-to-SQL logic. A simple example of this
is the relationship between the SAKAI_SITE and SAKAI_REALM tables: For a
site with sakai_site.site_id='xyz-123', the corresponding
sakai_realm.realm_id is '/site/xyz-123'. Groups and other realm
consumers have other conventions for encoding the realm_id. If there's a
global reference for this type of thing, I don't know of it. In general
you just have to learn from experience, but this is just one of many
many similar examples.

 

Another widespread convention is the practice of embedding a LOT of
really interesting metadata inside XML "entities" which are just dumped
in blob form into the corresponding row in the database. If you use
Oracle you are straight out of luck querying those columns, and in MySQL
it's no picnic. But that might be the only place a particular piece of
data is stored. Furthermore, for 2.5, content hosting has migrated to a
binary form of this entity serialization to give better performance.
Alas, it makes accessing those values via SQL that much more impossible.

 

Basically, Sakai's architecture has never placed a priority on making
the data accessible with SQL. The developers and architects on the list
have plenty of good reasons for this, which they have explained many
times before and which I don't deny. It does make the job of
troubleshooting and reporting much more difficult. But mainly it just
means we have to take a different approach that we can use with most
other systems.

 

From my perspective, it seems to me that the quickest way to develop
reporting data for some of these more hidden data is probably to get
familiar with the Sakai API and use the Axis-based web services which
allow you to quickly write and revise the Java code you use to collect
the data. Since you can add and change the code on the fly without
compiling or restarting Tomcat, iterations can go very quickly.

 

Again, I don't have a lot of experience in this area so far, but it is a
topic of discussion at Virginia Tech as well as on this list if you go
back through the archives. Good luck.

 

David Adams

Director of Learning Systems Integration and Support

Virginia Tech Learning Technologies

 

 

 

From: Charlie Williams [mailto:ccwilliams@...]
Sent: Friday, July 11, 2008 11:36 AM
To: sakai-dev@...
Subject: Sakai Queries / Reporting Metrics

 


Hello everyone,

I am trying to write several queries (that would eventually be used in
reports) to validate the success of our Sakai implementation. Some
examples
of our metrics include: the number of user interactions per time period,

number of posts on various tools/sites, and the total number of uploaded

resources.

It has proven difficult to write some of these queries, since I am not
very
familiar with how information is stored in the Sakai database. Does
anyone
have any suggestions on the best method of finding the information I
need
for each query? How do you approach writing new "report" queries?

Thanks,

Charlie Williams
University of Alabama
MIS/Enterprise Integration
--
View this message in context:
http://www.nabble.com/Sakai-Queries---Reporting-Metrics-tp18406633p18406
633.html
Sent from the Sakai - Development mailing list archive at Nabble.com.

________________________________


This automatic notification message was sent by Sakai Collab
(https://collab.sakaiproject.org//portal
<https://collab.sakaiproject.org/portal> ) from the DG: Development
(a.k.a. sakai-dev) site.
You can modify how you receive notifications at My Workspace >
Preferences.


----------------------
This automatic notification message was sent by Sakai Collab (https://collab.sakaiproject.org//portal) from the DG: Development (a.k.a. sakai-dev) site.
You can modify how you receive notifications at My Workspace > Preferences.

[John Leasia]

Charlie,
I generally take a look at the tables, check out what columns are there,
do a query returning 20 or so rows to get a feel for what is stored in
them and then go from there. But I think others are using web services
to get at data via APIs to better effect.

John

These are for Oracle. Not guaranteed to be the most efficient...

-- Number of tool related events in  a time period (times are in gmt so
if you really want a day in your time zone you'd need to adjust the
days/times)
select event, count(event)
from  sakai_event
where event not in ('pres.end', 'pres.begin')
and event_date between
to_date('20070418 22:00','YYYYMMDD HH24:MI') and
to_date('20070418 22:45','YYYYMMDD HH24:MI')
group by event

-- Number of resources added in the last 10 days
select count(event)
from  sakai_event
where event = 'content.new'
and event_date > sysdate - 10

-- Who made new announcements in last 5 days
select  smap.eid,  se.event_date, se.event, se.ref
from sakai_session ss,  sakai_event se, sakai_user_id_map smap
where ss.session_id = se.session_id
and se.event = 'annc.new'
and event_date > sysdate - 5
and ss.session_user=smap.user_id

-- Number of replies to each topic in a site for the Forums tool
select count(mmt.id) as num_messages, MFR_OPEN_FORUM_T.title as Forum,  
mfr_topic_t.title as Topic
from MFR_OPEN_FORUM_T, mfr_topic_t, mfr_area_t, mfr_message_t mmt
where mfr_area_t.context_id like '<put the site id here>'
and mfr_area_t.id=mfr_open_forum_t.surrogatekey
and mfr_open_forum_t.id =mfr_topic_t.of_surrogatekey
and mmt.surrogatekey(+)=mfr_topic_t.id
group by MFR_OPEN_FORUM_T.title,  mfr_topic_t.title
order by MFR_OPEN_FORUM_T.title



Charlie Williams wrote:

>
> Hello everyone,
>
> I am trying to write several queries (that would eventually be used in
> reports) to validate the success of our Sakai implementation. Some
> examples
> of our metrics include: the number of user interactions per time period,
> number of posts on various tools/sites, and the total number of uploaded
> resources.
>
> It has proven difficult to write some of these queries, since I am not
> very
> familiar with how information is stored in the Sakai database. Does
> anyone
> have any suggestions on the best method of finding the information I need
> for each query? How do you approach writing new "report" queries?
>
> Thanks,
>
> Charlie Williams
> University of Alabama
> MIS/Enterprise Integration
> --
> View this message in context:
> http://www.nabble.com/Sakai-Queries---Reporting-Metrics-tp18406633p18406633.html 
>
> Sent from the Sakai - Development mailing list archive at Nabble.com.
>
> ------------------------------------------------------------------------
>
> This automatic notification message was sent by Sakai Collab
> (https://collab.sakaiproject.org//portal) from the DG: Development
> (a.k.a. sakai-dev) site.
> You can modify how you receive notifications at My Workspace >
> Preferences.

----------------------
This automatic notification message was sent by Sakai Collab (https://collab.sakaiproject.org//portal) from the DG: Development (a.k.a. sakai-dev) site.
You can modify how you receive notifications at My Workspace > Preferences.

[Charlie Williams]

I was not aware that it was common practice to use Axis. Can anyone link me to any (simplified/getting started with) Axis articles or resources? I have been reading the Apache Axis site/guide to get started. I have never used Axis before, and I am unsure how it would be used with the Sakai API. Can anyone provide simplified examples of how you've used Axis with Sakai?

I've had decent success so far with MySQL queries, but I can already tell that I will be limited in the types of information I can pull. Here are some example MySQL queries that I have written so far (unoptimized/preliminary, but I certainly welcome the community's feedback!):

#How many unique messages were sent from within the message tool on each site?
SELECT count(distinct msg.ID), ss.TITLE
FROM MFR_MESSAGE_T msg
INNER JOIN SAKAI_USER su
INNER JOIN MFR_PVT_MSG_USR_T as pvt
INNER JOIN SAKAI_SITE as ss
on su.USER_ID = msg.CREATED_BY
AND msg.ID = pvt.messageSurrogateKey
AND ss.SITE_ID = pvt.CONTEXT_ID
GROUP BY CONTEXT_ID

#How many messages were sent in a given month/year?
#Displays count of messages sent in every month-year pair.
SELECT DATE_FORMAT(msg.CREATED, '%Y-%m') ym, COUNT(*)
FROM MFR_MESSAGE_T msg
GROUP BY ym

#Display the number of resources stored on each site
SELECT ui.EID, site.TITLE, count(*)
FROM CONTENT_RESOURCE cr
inner join SAKAI_SITE site
inner join SAKAI_USER_ID_MAP ui
on (site.SITE_ID = cr.CONTEXT) AND (site.CREATEDBY = ui.USER_ID)
group by cr.CONTEXT

Thanks,
Charlie

Charlie Williams wrote:

Hello everyone,

I am trying to write several queries (that would eventually be used in reports) to validate the success of our Sakai implementation. Some examples of our metrics include: the number of user interactions per time period, number of posts on various tools/sites, and the total number of uploaded resources.

It has proven difficult to write some of these queries, since I am not very familiar with how information is stored in the Sakai database. Does anyone have any suggestions on the best method of finding the information I need for each query? How do you approach writing new "report" queries?

Thanks,

Charlie Williams
University of Alabama
MIS/Enterprise Integration

[Goodyear, Duran]

Charlie (and anyone else) I wrote some very simple SQL Queries and php  
scripts that might be useful to you if you're interested in them.

(see attached files)

Weekly I take a SQL dump of the whole DB (about 500mB in our case),  
and run a sequence of PHP scripts over them to create some snapshot  
reports.   Its kinda manual, but it gets us some useful numbers.

You'll need to create the "sakaiReport" database.
here are the schemas for the two databases I use in this process

sakai.sql is... actually, THE SAKAI database... since I take a sql  
dump snapshot of our production to run it against.
the "sakaiReport.sql" is the other database i write records to.


Run the importX.php (where X is 1 through 4) files first.


You'll see an include at the top of each file
here's what you need for that.

(sanitized for username/password for the DB)
Put these in the directory the include statement is looking in...  
"Connections/..."  I think.


Then you can browse the index.php file and see the simple numbers it  
gives you.




Its clunky, I kinda threw it together in a few days...   its not  
optimized really, and since its all php you browse through everything  
via a browser, and the import scripts can be left to run over lunch ;)

Hopefully this gives you somewhere to start.






] duran goodyear
] web developer
] administrative computing // office of information services // the  
university of the arts
] dgoodyear@...
] 215.717.6068
] skype://duran.goodyear



On Jul 11, 2008, at 11:35 AM, Charlie Williams wrote:

>
> Hello everyone,
>
> I am trying to write several queries (that would eventually be used in
> reports) to validate the success of our Sakai implementation. Some  
> examples
> of our metrics include: the number of user interactions per time  
> period,
> number of posts on various tools/sites, and the total number of  
> uploaded
> resources.
>
> It has proven difficult to write some of these queries, since I am  
> not very
> familiar with how information is stored in the Sakai database. Does  
> anyone
> have any suggestions on the best method of finding the information I  
> need
> for each query? How do you approach writing new "report" queries?
>
> Thanks,
>
> Charlie Williams
> University of Alabama
> MIS/Enterprise Integration
> --
> View this message in context: http://www.nabble.com/Sakai-Queries---Reporting-Metrics-tp18406633p18406633.html
> Sent from the Sakai - Development mailing list archive at Nabble.com.
>
>
> This automatic notification message was sent by Sakai Collab (https://collab.sakaiproject.org//portal 
> ) from the DG: Development (a.k.a. sakai-dev) site.
> You can modify how you receive notifications at My Workspace >  
> Preferences.

[see attachment: "sakai.sql", size: 198429 bytes]

[see attachment: "sakaiReport.sql", size: 2839 bytes]

[see attachment: "sakai_stats.zip", size: 134263 bytes]

[see attachment: "sakaistats.php", size: 357 bytes]

[see attachment: "sakaiReport.php", size: 371 bytes]



Attachments:

sakai.sql
https://collab.sakaiproject.org//access/content/attachment/9a49ef30-4867-4f3e-9296-0db6da97cd92/sakai.sql

sakaiReport.sql
https://collab.sakaiproject.org//access/content/attachment/a45d3370-969a-4197-956e-3e55f17ca612/sakaiReport.sql

sakai_stats.zip
https://collab.sakaiproject.org//access/content/attachment/9b766388-7e34-4dbe-b19e-81e67a69981d/sakai_stats.zip

sakaistats.php
https://collab.sakaiproject.org//access/content/attachment/67d80848-07ed-4250-b9e9-db4928671ce9/sakaistats.php

sakaiReport.php
https://collab.sakaiproject.org//access/content/attachment/31b18377-899b-4d6e-8545-0aebe8235738/sakaiReport.php

----------------------
This automatic notification message was sent by Sakai Collab (https://collab.sakaiproject.org//portal) from the DG: Development (a.k.a. sakai-dev) site.
You can modify how you receive notifications at My Workspace > Preferences.

[R.P. Aditya]

On Fri, Jul 11, 2008 at 08:35:44AM -0700, Charlie Williams wrote:
> I am trying to write several queries (that would eventually be used in
> reports) to validate the success of our Sakai implementation. Some examples
> of our metrics include: the number of user interactions per time period,
> number of posts on various tools/sites, and the total number of uploaded
> resources.

using queries similar to what John L. sent, and a few other generic ones, we
have:

  https://ctstats.ds.itd.umich.edu/stats-bin/drraw.cgi?Mode=view;Dashboard=1190913860.2872;View=2;Filter=

  https://ctstats.ds.itd.umich.edu/stats-bin/drraw.cgi?Mode=view&Dashboard=1194322225.25790

  https://ctstats.ds.itd.umich.edu/stats-bin/drraw.cgi?Mode=view&Dashboard=1160533679.18970

which might not be as application specific as you want, but if there are any
you want to know the queries used, I'd be happy to send them.

Adi
----------------------
This automatic notification message was sent by Sakai Collab (https://collab.sakaiproject.org//portal) from the DG: Development (a.k.a. sakai-dev) site.
You can modify how you receive notifications at My Workspace > Preferences.

[Adams, David]

Charlie,

The best place to start for Sakai web services is on the wiki[1],
particularly the FAQ[2] and My First Webservice[3] pages. There's not a
lot of content there (much of the web service stuff has been pretty
static for a couple of years), but enough to get you started.

 

[1] http://confluence.sakaiproject.org/confluence/display/WEBSVCS/Home

[2] http://confluence.sakaiproject.org/confluence/display/WEBSVCS/FAQ

[3]
http://confluence.sakaiproject.org/confluence/display/WEBSVCS/My+first+W
ebservice

 

The quick summary:

 

(1)    Sakai provides web services using the Apache Axis library.

(2)    Axis allows you to write Java code in .jws files that are
compiled on the fly and can access most of the Sakai API quite easily

(3)    There are several jws files providing web services built into the
standard build that you can use to get your data or at the very least as
examples

(4)    Axis exposes the public interface of the Java classes you define
in the jws files as a SOAP web service

(5)    You have to explicitly enable web services in sakai.properties

 

Basically, if you use a language that can call SOAP methods, you are in
good shape.  I get the impression that most current web services use is
for automating adding users or pre-loading data for load testing, or
scripting actions that are just difficult or repetitive to perform in
the UI. But it seems to me that reporting is another area where there's
just no other option besides writing some full blown tools (there is a
reports tool, but it's limited to tools that support its API and you
must define the reports with XML and XSLT).

 

As for client-side, I know a lot of people use Perl with great success
to call the web services and there's likely a lot of examples on the
list archives or possibly elsewhere in the wiki. PHP is also a popular
choice. I've been using Ruby, which has some pretty clean SOAP support
built in out of the box. So the client tools work great. The limit so
far seems to be what calls are available with the existing JWS files.

 

David Adams

 

 

From: Charlie Williams [mailto:ccwilliams@...]
Sent: Friday, July 11, 2008 12:43 PM
To: sakai-dev@...
Subject: Re: Sakai Queries / Reporting Metrics

 


I was not aware that it was common practice to use Axis. Can anyone link
me
to any (simplified/getting started with) Axis articles or resources? I
have
been reading the Apache Axis site/guide to get started. I have never
used
Axis before, and I am unsure how it would be used with the Sakai API.
Can
anyone provide simplified examples of how you've used Axis with Sakai?

I've had decent success so far with MySQL queries, but I can already
tell
that I will be limited in the types of information I can pull. Here are
some
example MySQL queries that I have written so far
(unoptimized/preliminary,
but I certainly welcome the community's feedback!):

#How many unique messages were sent from within the message tool on each

site?
SELECT count(distinct msg.ID), ss.TITLE
FROM MFR_MESSAGE_T msg
INNER JOIN SAKAI_USER su
INNER JOIN MFR_PVT_MSG_USR_T as pvt
INNER JOIN SAKAI_SITE as ss
on su.USER_ID = msg.CREATED_BY
AND msg.ID = pvt.messageSurrogateKey
AND ss.SITE_ID = pvt.CONTEXT_ID
GROUP BY CONTEXT_ID

#How many messages were sent in a given month/year?
#Displays count of messages sent in every month-year pair.
SELECT DATE_FORMAT(msg.CREATED, '%Y-%m') ym, COUNT(*)
FROM MFR_MESSAGE_T msg
GROUP BY ym

#Display the number of resources stored on each site
SELECT ui.EID, site.TITLE, count(*)
FROM CONTENT_RESOURCE cr
inner join SAKAI_SITE site
inner join SAKAI_USER_ID_MAP ui
on (site.SITE_ID = cr.CONTEXT) AND (site.CREATEDBY = ui.USER_ID)
group by cr.CONTEXT

Thanks,
Charlie


Charlie Williams wrote:
>
> Hello everyone,
>
> I am trying to write several queries (that would eventually be used in

> reports) to validate the success of our Sakai implementation. Some
> examples of our metrics include: the number of user interactions per
time
> period, number of posts on various tools/sites, and the total number
of
> uploaded resources.
>
> It has proven difficult to write some of these queries, since I am not

> very familiar with how information is stored in the Sakai database.
Does
> anyone have any suggestions on the best method of finding the
information
> I need for each query? How do you approach writing new "report"
queries?
>
> Thanks,
>
> Charlie Williams
> University of Alabama
> MIS/Enterprise Integration
>

--
View this message in context:
http://www.nabble.com/Sakai-Queries---Reporting-Metrics-tp18406633p18407
478.html
Sent from the Sakai - Development mailing list archive at Nabble.com.

________________________________


This automatic notification message was sent by Sakai Collab
(https://collab.sakaiproject.org//portal
<https://collab.sakaiproject.org/portal> ) from the DG: Development
(a.k.a. sakai-dev) site.
You can modify how you receive notifications at My Workspace >
Preferences.


----------------------
This automatic notification message was sent by Sakai Collab (https://collab.sakaiproject.org//portal) from the DG: Development (a.k.a. sakai-dev) site.
You can modify how you receive notifications at My Workspace > Preferences.

[Mike Wenk]

R.P. Aditya wrote:

> On Fri, Jul 11, 2008 at 08:35:44AM -0700, Charlie Williams wrote:
>  > I am trying to write several queries (that would eventually be used in
>  > reports) to validate the success of our Sakai implementation. Some
> examples
>  > of our metrics include: the number of user interactions per time period,
>  > number of posts on various tools/sites, and the total number of uploaded
>  > resources.
>
> using queries similar to what John L. sent, and a few other generic
> ones, we
> have:
>
> https://ctstats.ds.itd.umich.edu/stats-bin/drraw.cgi?Mode=view;Dashboard=1190913860.2872;View=2;Filter= 
>
>
> https://ctstats.ds.itd.umich.edu/stats-bin/drraw.cgi?Mode=view&Dashboard=1194322225.25790 
> <https://ctstats.ds.itd.umich.edu/stats-bin/drraw.cgi?Mode=view&Dashboard=1194322225.25790>
>
>
> https://ctstats.ds.itd.umich.edu/stats-bin/drraw.cgi?Mode=view&Dashboard=1160533679.18970 
> <https://ctstats.ds.itd.umich.edu/stats-bin/drraw.cgi?Mode=view&Dashboard=1160533679.18970>
>
>
> which might not be as application specific as you want, but if there are
> any
> you want to know the queries used, I'd be happy to send them.
>
> Adi
> ------------------------------------------------------------------------
>
> This automatic notification message was sent by Sakai Collab
> (https://collab.sakaiproject.org//portal) from the DG: Development
> (a.k.a. sakai-dev) site.
> You can modify how you receive notifications at My Workspace > Preferences.

Adi,

I would love to see the queries that generated this page.  Especially
the user and sessions graph.

Mike
----------------------
This automatic notification message was sent by Sakai Collab (https://collab.sakaiproject.org//portal) from the DG: Development (a.k.a. sakai-dev) site.
You can modify how you receive notifications at My Workspace > Preferences.

[Kevin P. Foote]

User and Session queries I use against mysql.
Adi's are probably much better .. as their graphs are much more impressive :-)

# unique today $today is a var in a perl script
select count(1) as unique_users_today from (select distinct SESSION_USER from SAKAI_SESSION where SESSION_START > '$today' and SESSION_START < DATE_ADD('$today', INTERVAL 1 DAY)) as unique_users_today

# sessions today $today is a var in a perl script
select count(SESSION_ID) as sessions_today from SAKAI_SESSION where SESSION_START >= '$today'

# online users
select count(SESSION_ID) as current from SAKAI_SESSION where SESSION_ACTIVE='1'";


------
thanks
   kevin.foote

On Tue, 22 Jul 2008, Michael Wenk wrote:

>
> R.P. Aditya wrote:
>> On Fri, Jul 11, 2008 at 08:35:44AM -0700, Charlie Williams wrote:
>> > I am trying to write several queries (that would eventually be used in
>> > reports) to validate the success of our Sakai implementation. Some
>> examples
>> > of our metrics include: the number of user interactions per time period,
>> > number of posts on various tools/sites, and the total number of uploaded
>> > resources.
>>
>> using queries similar to what John L. sent, and a few other generic
>> ones, we
>> have:
>>
>> https://ctstats.ds.itd.umich.edu/stats-bin/drraw.cgi?Mode=view;Dashboard=1190913860.2872;View=2;Filter=
>>
>>
>> https://ctstats.ds.itd.umich.edu/stats-bin/drraw.cgi?Mode=view&Dashboard=1194322225.25790
>> <https://ctstats.ds.itd.umich.edu/stats-bin/drraw.cgi?Mode=view&Dashboard=1194322225.25790>
>>
>>
>> https://ctstats.ds.itd.umich.edu/stats-bin/drraw.cgi?Mode=view&Dashboard=1160533679.18970
>> <https://ctstats.ds.itd.umich.edu/stats-bin/drraw.cgi?Mode=view&Dashboard=1160533679.18970>
>>
>>
>> which might not be as application specific as you want, but if there are
>> any
>> you want to know the queries used, I'd be happy to send them.
>>
>> Adi
>> ------------------------------------------------------------------------
>>
>> This automatic notification message was sent by Sakai Collab
>> (https://collab.sakaiproject.org//portal) from the DG: Development
>> (a.k.a. sakai-dev) site.
>> You can modify how you receive notifications at My Workspace > Preferences.
>
> Adi,
>
> I would love to see the queries that generated this page.  Especially
> the user and sessions graph.
>
> Mike
> ----------------------
> This automatic notification message was sent by Sakai Collab (https://collab.sakaiproject.org//portal) from the DG: Development (a.k.a. sakai-dev) site.
> You can modify how you receive notifications at My Workspace > Preferences.
>

----------------------
This automatic notification message was sent by Sakai Collab (https://collab.sakaiproject.org//portal) from the DG: Development (a.k.a. sakai-dev) site.
You can modify how you receive notifications at My Workspace > Preferences.

[R.P. Aditya]

On Tue, Jul 22, 2008 at 12:21:28PM -0700, Michael Wenk wrote:
> I would love to see the queries that generated this page.  Especially
> the user and sessions graph.

This is using Oracle, and is run every 5 minutes, and we keep stats per server
and graph them together:

select
 session_server,
 count(distinct(session_user)) as num_users,
 count(session_id) as num_sessions
from sakai_session
where
 session_start >= SYSDATE - 1
 and session_start = session_end
group by
 session_server;

but remember, users and sessions are only useful in a comparative/trending
sense in Sakai since the notion of session and users is unique to Sakai (ie.
you can't independently verify it (without some heroic effort AFAIK) via a
non-Sakai method).

More useful to knowing how hard your Sakai installation is working is plain
HTTP requests per second.

Adi
----------------------
This automatic notification message was sent by Sakai Collab (https://collab.sakaiproject.org//portal) from the DG: Development (a.k.a. sakai-dev) site.
You can modify how you receive notifications at My Workspace > Preferences.

[John Bush-2]

While it might not be necessary for your needs, sakai has a reporting  
tool and warehouse service that can be used to create reports in the  
system.  The warehouse is responsible for "warehousing" the sakai data  
into SQL-"friendliness".  It runs as a cron job that exports the data  
into models that are more natural for reporting.  It can handle the  
problems David is bringing up, and was specifically designed to  
address this problem.

This might be a good starting place for more information:
http://bugs.sakaiproject.org/confluence/display/OSPDOC/Reports

On Jul 11, 2008, at 9:03 AM, Adams, David wrote:

> Charlie,
> I’m sure you’ll get better answers from folks more experienced with  
> Sakai, but a quick word of warning: If you are trying to do  
> reporting solely with SQL you are going to be quickly frustrated  
> beyond a few basic reports. Sakai’s data structures and relational  
> persistence patterns have been designed for use by a Java API that’s  
> developed by dozens of disparate groups, and quite explicitly *not*  
> for SQL-friendliness. In fact, the message sysadmins often get in  
> response to similar questions is that one shouldn’t be looking at--
> and certainly not modifying—the database directly, as it can be  
> misleading and incomplete. While that’s not always possible, it’s  
> definitely important to keep in mind.
>
> The more you dig in, the more you’ll find that many “linked” tables  
> are not linked at all using any direct relational conventions, but  
> rather require some knowledge (or good guesses) about how the API  
> works and manual “joins” with some external-to-SQL logic. A simple  
> example of this is the relationship between the SAKAI_SITE and  
> SAKAI_REALM tables: For a site with sakai_site.site_id=’xyz-123’,  
> the corresponding sakai_realm.realm_id is ‘/site/xyz-123’. Groups  
> and other realm consumers have other conventions for encoding the  
> realm_id. If there’s a global reference for this type of thing, I  
> don’t know of it. In general you just have to learn from experience,  
> but this is just one of many many similar examples.
>
> Another widespread convention is the practice of embedding a LOT of  
> really interesting metadata inside XML “entities” which are just  
> dumped in blob form into the corresponding row in the database. If  
> you use Oracle you are straight out of luck querying those columns,  
> and in MySQL it’s no picnic. But that might be the only place a  
> particular piece of data is stored. Furthermore, for 2.5, content  
> hosting has migrated to a binary form of this entity serialization  
> to give better performance. Alas, it makes accessing those values  
> via SQL that much more impossible.
>
> Basically, Sakai’s architecture has never placed a priority on  
> making the data accessible with SQL. The developers and architects  
> on the list have plenty of good reasons for this, which they have  
> explained many times before and which I don’t deny. It does make the  
> job of troubleshooting and reporting much more difficult. But mainly  
> it just means we have to take a different approach that we can use  
> with most other systems.
>
> From my perspective, it seems to me that the quickest way to develop  
> reporting data for some of these more hidden data is probably to get  
> familiar with the Sakai API and use the Axis-based web services  
> which allow you to quickly write and revise the Java code you use to  
> collect the data. Since you can add and change the code on the fly  
> without compiling or restarting Tomcat, iterations can go very  
> quickly.
>
> Again, I don’t have a lot of experience in this area so far, but it  
> is a topic of discussion at Virginia Tech as well as on this list if  
> you go back through the archives. Good luck.
>
> David Adams
> Director of Learning Systems Integration and Support
> Virginia Tech Learning Technologies
>
>
>
> From: Charlie Williams [mailto:ccwilliams@...]
> Sent: Friday, July 11, 2008 11:36 AM
> To: sakai-dev@...
> Subject: Sakai Queries / Reporting Metrics
>
>
> Hello everyone,
>
> I am trying to write several queries (that would eventually be used in
> reports) to validate the success of our Sakai implementation. Some  
> examples
> of our metrics include: the number of user interactions per time  
> period,
> number of posts on various tools/sites, and the total number of  
> uploaded
> resources.
>
> It has proven difficult to write some of these queries, since I am  
> not very
> familiar with how information is stored in the Sakai database. Does  
> anyone
> have any suggestions on the best method of finding the information I  
> need
> for each query? How do you approach writing new "report" queries?
>
> Thanks,
>
> Charlie Williams
> University of Alabama
> MIS/Enterprise Integration
> --
> View this message in context: http://www.nabble.com/Sakai-Queries---Reporting-Metrics-tp18406633p18406633.html
> Sent from the Sakai - Development mailing list archive at Nabble.com.
>
> This automatic notification message was sent by Sakai Collab (https://collab.sakaiproject.org//portal 
> ) from the DG: Development (a.k.a. sakai-dev) site.
> You can modify how you receive notifications at My Workspace >  
> Preferences.
>
>
> This automatic notification message was sent by Sakai Collab (https://collab.sakaiproject.org//portal 
> ) from the DG: Development (a.k.a. sakai-dev) site.
> You can modify how you receive notifications at My Workspace >  
> Preferences.

----------------------
This automatic notification message was sent by Sakai Collab (https://collab.sakaiproject.org//portal) from the DG: Development (a.k.a. sakai-dev) site.
You can modify how you receive notifications at My Workspace > Preferences.