Analysis : Sharing alfresco data with other applications

[zomurn]

Hi,

I need to do some stats with alfresco : who, when and what need to be stored somewhere and then I should be able to request this information given a date range for example : "How much Mr stuff processed document during the month of march in year 2007".
My VERY GLOBAL question are :

1) How to store such data ? (the history aspect is required) …. "audit" is maybe the good way.

2) I need to store this history into a database because we want to share all these statistic with a business intelligence datawarehouse….so Alfresco write in database and another BI application read the database.

I'am open to every suggestions 

[zaizi]

You need to turn on audit service and configure it so it tracks all the activities you require.

You can then write a custom view in your database to list the things you are interested in. Alternatively, you can use your BI tool to query the Alfresco audit data.

This can then be reported on.

See http://wiki.alfresco.com/wiki/Auditing_Design_and_Implementation for some sample queries. NB: These queries might not work out of the box depending on the version of Alfresco you are using. You might need to update. But they provide a start.

[zomurn]

Thanks you very much for this first answer .

I just have a quick look at your link. Well informative !
I just need to know how to create a view as you said (through independent SQL script…append it to alfresco database init scripts ? Simple "SQL" not portable query inside MY alfresco dao layer (which contains only lucene queries at the moment ? HQL portable query inside MY alfresco dao layer ?)
Is auditing service expecting to audit for custom metadata ?
Some calculation based on custom metadata should be stored in the database directly (not though alfresco).
There will be no HTML view displayed : the statistic will go inside database…the BI tool will do the synthesis of all the available information.

Thanks again and I'am going to hardly investigate how to do and starting from audit service.

[zaizi]

I just need to know how to create a view as you said (through independent SQL script…append it to alfresco database init scripts ? Simple "SQL" not portable query inside MY alfresco dao layer (which contains only lucene queries at the moment ? HQL portable query inside MY alfresco dao layer ?)

Keep it simple. Just use independent SQL script depending on which database you are using. Alternatively, you can just use your BI tool to copy the relevant audit tables across and build the view or custom tables in your reporting database.

Is auditing service expecting to audit for custom metadata ?

Yes it should.

[zomurn]

I saw in database that workspace spacestore noderef are  hardcoded.
So, inside alfresco if a delete the node (a cm:folder for example) and recreate it, all the queries are false then ….
Queries can't be "dynamic" ?