R help - Jan 2010 - How do people use Sweave / R / Databases

I'm currently using r scripts in sweave to grab some data via ODBC, 
process it then generate some tables.  I'd like to be able to give 
someone the files and let them reproduce what I've done.  Is there some 
way to store the data that is gathered by ODBC so that the second person 
can recreate the work without the database (apart from just writing it 
to a file or into the document)

Thanks

Paul.

On 29/01/2010 2:28 PM, Paul wrote:
> I'm currently using r scripts in sweave to grab some data via ODBC, 
> process it then generate some tables.  I'd like to be able to give 
> someone the files and let them reproduce what I've done.  Is there some
> way to store the data that is gathered by ODBC so that the second person 
> can recreate the work without the database (apart from just writing it 
> to a file or into the document)

What I've done in a case like that is to store the data in a .Rdata file 
using save(), then have the Sweave code call load() to load it at the 
beginning of the script.
There's also the cacheSweave package (and I think another one whose name 
I can't remember) to cache the value of some code blocks; you might be 
able to bundle up the document with the cache to send to someone who 
doesn't want to re-run the retrieval code.

Duncan Murdoch

On Jan 29, 2010, at 1:28 PM, Paul wrote:
> I'm currently using r scripts in sweave to grab some data via ODBC,
process it then generate some tables.  I'd like to be able to give someone
the files and let them reproduce what I've done.  Is there some way to store
the data that is gathered by ODBC so that the second person can recreate the
work without the database (apart from just writing it to a file or into the
document)
> 
> Thanks
> 
> Paul.
There are likely to be several approaches, as there always is with R, but here
is mine.

I have a function that I wrote to (via RODBC) obtain the entire content of a
series of Oracle views from our server. Basically a loop of "select * from
VIEWNAME" queries, where the changing parts of the query are paste()d
together. The function takes a viewname prefix to define a common set of views,
gets the names of the views that match the prefix pattern from a table that
stores all viewnames and then creates a series of data frames in the R global
environment via assign(), with one data frame per Oracle view.

The function is called from within a code chunk in a .Rnw file, which by default
is set to "<<results=hide,eval=FALSE>>=".

I do this so that I can run the code chunk once manually to secure the data.
Note that I use ESS for all of this, so I just highlight that code and send it
to the R buffer (session).

I then save the data frames (Oracle views) to a .RData file using save() to
preserve the "clean" source data.

I can then run the rest of the .Rnw file as much as I want, changing code as I
need to, but always using the same set of data, since the code chunk containing
the function that gets the Oracle data is not evaluated on subsequent runs.

I also use subversion to version control changes made to the .Rnw file as
required and to store the original data set. Since I will perform updated
retrievals over time, I have the flexibility of running the .Rnw file (or a
revised version) on old versions of the data retrieval as I need.

So, using this approach, you can pass the .RData file off to your colleague,
along with the .Rnw file and they should be good to go.

HTH,

Marc Schwartz

On Fri, 29 Jan 2010, Paul wrote:

P > I'm currently using r scripts in sweave to grab some data via ODBC,
process
P > it then generate some tables.  I'd like to be able to give someone
the files
P > and let them reproduce what I've done.  Is there some way to store
the data
P > that is gathered by ODBC so that the second person can recreate the work
P > without the database (apart from just writing it to a file or into the
P > document)
P > 
P > Thanks
P > 
P > Paul.
P > 
P > 

Another idea is to build a package. I used to think that a 
package was just for building a set of functions for my use 
or to contribute to cran. But, another use is to "package" 
up an analysis with all the data, functions, etc, including 
sweave scripts. Your work can then be repeated by someone 
else who has installed R.

Anne