Analysis server is defined as a cube AND tabular data
provider.

Can a legacy tool that only has ODBC (not OLE or ADO)
access a 2-D slice in Analysis/OLAP Server?

I want to analyze data in Analysis/OLAP server using the
Windows(tm) version of the open source statistical
program "R."

"R" has ODBC, but not the newer (OLE, OLE for MD, ADO,
ADO.NET) technologies.

Can I define 2-D views for access by ODBC (an "R" client)
within Analysis server for access by ODBC or do I have to
export the 2-d slices [back] to a relational database such
as SQL Server?

I would like to use Analysis/OLAP server as a
economic/accounting time series data base similar to the
World Bank case study, but use the awesome statistical
power of "R" rather than a pretty EIS briefing book client.

Jim Callahan

Hi Jim,

I am afraid that we do not provide ODBC API to access Analysis Services.
According to my research, I also cannot find any other third-party tool
that does this.


This posting is provided "AS IS" with no warranties, and confers no rights.

Regards,

Bill Cheng
Microsoft Support Engineer
--------------------

Alternatively, SQL Server could do an OPENQUERY() pass-
through to an OLE DB data source as described in
MSDN article, "Passing Queries from SQL Server to a Linked
Analysis Server."

If the OPENQUERY() could be embedded in a SQL Server VIEW
it would accomplish much the same purpose, but with the
overhead of SQL Server (or some other data base with pass-
through capabilities -- such as MS Access).

Jim Callahan

Hi Jim,

Yes, we can access Analysis Services indirectly. However, the problem is
that we need to create views to wrap OPENQUERY function. To configure a
linked analysis server, we need to:
In SQL Server Enterprise Manager, connect to the instance of SQL Server
that will host the link.

In the tree pane, expand the Security folder to view the Linked Servers.

Right-click Linked Servers, and then click New Linked Server.

In the Linked Server Properties dialog box, type information about the
linked server. The required fields are:
Linked Server: This is the name of the Analysis server as it is referenced
in SQL Server. This name is used to identify the linked server in queries.

Provider Name: Select Microsoft OLE DB Provider for Olap Services 8.0.

Data Source: This is the host name of the computer hosting Analysis
Services.

Catalog: This is the name of the OLAP database provided through this link.
Click Provider Options. In the Provider Options dialog box, select the
Allow InProcess check box.

Note The Allow InProcess option affects all linked servers that use the
OLE DB for Analysis Services provider. This option can be changed only in
SQL Server Enterprise Manager.

I think you need to use "Passing Queries from SQL Server to a Linked
Analysis Server" when using OPENQUERY function. However, I think you need
to create some views for this purpose.


This posting is provided "AS IS" with no warranties, and confers no rights.

Regards,

Bill Cheng
Microsoft Support Engineer
--------------------