R help - Jun 2004 - R client connection OLAP cube (SQL Analysis Services / PivotTable Service)

I have been doing data analysis/modeling in R, connecting to SQL databases 
with RODBC (winXP client with R1.9.0 and win2k SQL server 2000).

I am now trying to leverage some of the OLAP features to keep the data 
intensive tasks on the DB server side and only keep the analytical tasks 
within R (optimize use of memory). Is there any package that would allow to 
connect to OLAP cubes (as a client to SQL Analysis Services PivotTable 
Service) from an R session (similar to the RODBC package)?

Can this be done directly with special connection parameters (from R) 
through the RODBC package or do I have to setup an intermediary XL table 
(pivottable linked to the OLAP cube) and then connect to the XL data source 
from R?

I would appreciate any reference / pointer to OLAP/R configuration 
instructions. Thanks.

Olivier Collignon

Dear Olivier,

I believe your best bet may be to connect to the database through
some kind of R-COM connection (either Thomas Baier and
Erich Neuwirth's R-(D)COM in CRAN or Duncan Temple Lang's at
http://www.omegahat.org/RDCOMClient).  For instance, the 
ADO MD (ActiveX Data Objects Multi-dimensional) COM object/library
allows you to connect to the OLAP database and query multiple cubes,
their axes and hierarchies, etc.  See the Microsoft Developer Network
(MSDN) for the gory details.

Hope this helps,

--
David

Olivier Collignon wrote:
> I have been doing data analysis/modeling in R, connecting to SQL databases 
> with RODBC (winXP client with R1.9.0 and win2k SQL server 2000).
> 
> I am now trying to leverage some of the OLAP features to keep the data 
> intensive tasks on the DB server side and only keep the analytical tasks 
> within R (optimize use of memory). Is there any package that would allow to
> connect to OLAP cubes (as a client to SQL Analysis Services PivotTable 
> Service) from an R session (similar to the RODBC package)?
> 
> Can this be done directly with special connection parameters (from R) 
> through the RODBC package or do I have to setup an intermediary XL table 
> (pivottable linked to the OLAP cube) and then connect to the XL data source
> from R?
> 
> I would appreciate any reference / pointer to OLAP/R configuration 
> instructions. Thanks.
> 
> Olivier Collignon
> 
> ______________________________________________
> R-help at stat.math.ethz.ch mailing list
> https://www.stat.math.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide!
http://www.R-project.org/posting-guide.html