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
OLAP = On-line Analytical Processing == Using a cube / hypercube of data
for decision support
(usually extracted from an transaction processing system)
Direct connection to Microsoft OLAP server ("Analysis Services") requires
and OLE DB provider
rather than an ODBC connection -- so RODBC cannot be used directly
(querying an OLAP cube rather than a SQL table requires MDX instead of SQL
queries).
One way to use RODBC to query MS OLAP cube is to use SQL Server as a
"Slice Server."
You could slice three different planes (tables) out of a 3-D cube. For
example, if you had
a cube that "Financial Data" by "Company" by "Year." The three planes
(tables) would be:
1. Financial statements -- financial line items for one company (Balance
Sheet, Income Statement)
2. Time Series -- One or more line items over time (forecasting)
3. Cross-sectional -- One line item for all companies (useful for ranking)
Microsoft OLAP "Analysis Services" is bundled with (on the same CD-ROM as)
MS SQL server,
so licensing should not be an issue....
Although I have figured out this is possible (and implemented a similar
system many years ago
in a long forgotten language), I haven't built an MS OLAP cube yet -- so
I haven't tested it.
The following is summarized from Mary Chipman's and Andy Baron's,
"Microsoft Access Guide to SQL Server," chapter 12, pages 644-654.
SQL Server is capable of sending "pass through" queries for execution on
another data base
(using the foreign data base's syntax). That way "MDX is executed on the
OLAP server, and not locally
in SQL Server." Chipman & Baron, p. 645 -- which would accomplish your
objective of "[Keeping] the data
intensive tasks on the DB server side."
Although you can use the MS SQL OPENROWSET() function to set up an ad hoc
connection, the
recommended method would be two steps:
1. Set up a Linked Server using either MS SQL Enterprise Manager or
MS SQL system stored procedure EXEC sp_addlinkedserver.
2. Use an OPENQUERY() function (which works with any OLE DB data source)
to pass the MDX query and
return a resultset. The OPENQUERY function can be used in the "FROM
clause" of a SQL query,
a "stored procedure" or in MS SQL 2000 a "User Defined Function (UDF)."
Also note,
"A view created using the OPENQUERY syntax cannot be used [without
renaming (aliasing) the columns using 'AS']
as the RecordSource for a report" Chipman & Baron, op. cit. page 647. MDX
concatenates the dimension names with
periods -- and that does not conform to the column naming conventions of
either MS SQL Server or MS Access.
Unfortunately, that's too much information for the R list and not enough
for you,
so I strongly recommend the full explanation and examples in Chapter 12 of
Chipman & Baron.
Ideally, one would like an R interface to map an multidimensional array
directly from OLAP to
an R data structure such as an R matrix or an R data frame similar to
RPgSQL -- until then, use a slice server...
Jim Callahan, MBA & MCDBA
Management, Budget & Accounting
City of Orlando
(407) 246-3039 office
[[alternative HTML version deleted]]
______________________________________________
[EMAIL PROTECTED] 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
