[R] MS OLAP -- RODBC to SQL Server "Slice Server" pass-through query to MS OLAP

From: <James.Callahan_at_CityofOrlando.net>
Date: Thu 01 Jul 2004 - 10:08:45 EST


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



R-help@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 Received on Thu Jul 01 10:16:47 2004

This archive was generated by hypermail 2.1.8 : Wed 03 Nov 2004 - 22:54:38 EST