R help - Jul 2004 - MS OLAP -- RODBC to SQL Server "Slice Server" pass-through query to MS OLAP

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]]