Hi Joshua,

I was mainly looking at your program, not for the code, but as a way to
bring myself up to speed about current practices in modeling the COUNTER
data. I'm trying to avoid reinventing something that has already been well
thought through. I apologize for calling out your model. You have gotten
much further than I have. Some of the other respondents in this thread have
set me straight on some things I was very fuzzy on going in.

How go about I collecting and storing the data is still something I haven't
resolved yet. I personally would prefer a Python solution, but there forces
here at MPOW that suggest I should build a data repository in SharePoint.
Assuming that is the case, Serial Solution's open source SUSHI harvester
written in .NET might actually be the way for me to go. So, my next step is
to look at their data model and see what reports they collect and store.

As an aside, I'm also now wondering if de-duping is strictly necessary as
long as there is a field to record the date the report was generated.
 De-duping (or maybe just deprecating duplicate data) could be separate
from the collection process.

Best,
Tom

On Mon, Feb 4, 2013 at 10:07 AM, Joshua Welker <[log in to unmask]> wrote:

> Tom,
>
> I am the guy who wrote sushi.py around this time last year. My apologies
> for the shabbiness of the code. It was meant to be primarily a proof of
> concept. It's definitely incomplete. I only completed the DB3 and JR1
> report logic up to this point, but it would be easy enough to add other
> report types. You're also right that sushi.py doesn't do anything to dedupe
> data, but it would be very simple to write a script that reads through the
> SQL records and deletes dupes. You could also use the built-in UNIQUE flag
> in MySQL when creating your table so that duplicate records just don't get
> saved. If you use the CSV export functionality of sushi.py, Excel has some
> built-in dedupe features that would help as well.
>
> Let me know if you'd like some help modifying sushi.py. I sort of gave up
> on it last spring. SUSHI implementation among vendors is still pretty
> shabby, and there are still some weaknesses in the SUSHI standard (I wrote
> about them in the Nov 2012 issue of Computers in Libraries). The
> productivity gains I was seeing from using SUSHI ended up being pretty low.
>
> Josh Welker
>
>
> -----Original Message-----
> From: Code for Libraries [mailto:[log in to unmask]] On Behalf Of
> Tom Keays
> Sent: Friday, January 25, 2013 8:40 AM
> To: [log in to unmask]
> Subject: Re: [CODE4LIB] Anyone have a SUSHI client?
>
> I've been looking briefly at sushi.py, as a way to orient myself to
> collecting stats this way. I'm not intending to single out sushi.py, but
> looking at it (mainly the data structure at this point, and not the code
> itself), raises some questions about the best approach for collecting SUSHI
> data.
>
> sushi.py seems to have a small number of routines; mainly to retrieve the
> XML file from a vendor and ingest the data in that file into a MySQL
> database. There are only MySQL tables for COUNTER JR1, DR1, DR2, and DR2
> reports and they mirror, to a degree, the structure of the item records
> returned in the SUSHI xml. Here are the skeletons of 2 of the sushi.py SQL
> tables:
>
> counter_jr1
>   id int,
>   print_issn varchar,
>   online_issn varchar,
>   platform varchar,
>   item_name text,
>   data_type varchar,
>   date_begin datetime,
>   date_end datetime,
>   ft_pdf int,
>   ft_html int,
>   ft_total varchar
>
> counter_db3
>   id int,
>   platform varchar,
>   item_name text,
>   data_type varchar,
>   date_begin datetime,
>   date_end datetime,
>   searches int,
>   sessions int
>
> On the face of it, this seems like a pretty good data structure (although
> I have a couple of concerns, that I will get to) but my main question is
> whether there is any agreement about how to collect this data? If I were to
> dig into some of the other SUSHI packages mentioned in this thread, what
> would I find there? Excel-formatted COUNTER reports are simply a table of
> columns, representing various fields, such as title (for JR1), platform,
> publisher (for JR1), ISSN (for JR1), etc., followed by columns for up to 12
> months of the collected year, and then summary data.  JR1 reports have
> fulltext HTML, PDF, and Total columns. DR1 has two rows, one for searches
> and one for sesssions, with YTD totals in the final column. Similar data
> structures exist for other COUNTER reports. They rely on the user to
> interpret them and probably ought not to inform a decision for structuring
> the data in a database. Is there been any best practice for how COUNTER
> data is modeled in a database?
>
> There are other COUNTER reports besides those four. For instance, some
> journal vendors do indeed report searches and sessions using the DR3
> report, but others use the equivalent JR4 report, so I would have expected
> sushi.py to have a mechanism to collect these. Does SUSHI only deliver JR1,
> DR1, DR2, and DR2 reports, or is this a problem with sushi.py?
>
> Now, one of the selling points for SUSHI is that if a vendor ever advises
> that you should re-collect data for a given time period, the xml you
> receive is structured such that the act of collecting OUGHT TO update,
> rather than duplicate, data previously collected. However in sushi.py's SQL
> structure, which gives every row a unique (auto-incremented) ID number,
> there would have to be logic applied during the ingest to prevent multiple
> instances of data collected from the same vendor for the same time period.
> So, that's a concern.
>
> I'm also concerned about what is represented in the ft_pdf, ft_html, and
> ft_total fields. In the Excel COUNTER reports, the ft_pdf, ft_html, and
> ft_total columns simply tabulate the YTD totals and the only way you would
> be able to derive a monthly breakdown would be to collect 12 monthly
> reports and analyze the differences from month to month -- something that
> most libraries don't do. I have to go back and confirm this, but I don't
> think the SUSHI reports are giving a month-only breakdown for those fields,
> so I wonder about their inclusion in that table. I guess my question is
> what is returned in the SUSHI xml report: monthly or yearly figures for the
> ft_pdf, ft_html, and ft_total fields?
>
> Tom
>