SQL Server Savvy - 01 Jul 1999

I’ve begun to experiment with Microsoft OLAP Services, but my current report writer can’t access OLAP Service data. Is there any way I can trick my ODBC-based report writer into using the powerful summarization capabilities of OLAP Services?

The good news is that it's not difficult to give any ODBC report writer limited abilities to access data stored in OLAP Services.

Rather than focusing on the details of a specific report writer, we’ll assume you’re using Joe’s Cool Reports to access your existing SQL Server data. Like most ODBC report writers, Joe’s Cool Reports lets you define a report based on a SQL Server query or on the result set of a stored procedure. The new heterogeneous query capability of SQL Server lets you access any OLE DB data source as if it were a native SQL Server source. Joe’s Cool Reports knows how to talk to SQL Server, which knows how to talk to other OLE DB providers—which, coincidentally, include Microsoft OLAP Services. Now, Joe’s Cool Reports can talk to OLAP Services as long as you've defined a linked server from SQL Server to the target OLAP Server. (For more information on heterogeneous queries, refer to Brian Moran, "Writing Distributed Heterogeneous Queries in SQL Server 7.0," SQL Server Magazine, premiere issue.)

Here’s an example of how to access data in OLAP Services. We’ll also correct some wrong information in Books Online (BOL).

First, use this command to add a linked server on your SQL Server that points to an instance of OLAP Services:

EXEC sp_addlinkedserver
	@server='olap_server’,
	@srvproduct='',
	@provider='MSOLAP',
	@datasrc='server',
	@catalog='foodmart'

OLAP Services BOL contains a section called "Passing Queries from SQL Server to a Linked OLAP Server," which includes the following sample query:

SELECT * FROM
OPENQUERY(olap_server, 'SELECT \[customer\],
\[quantity\] FROM sales')

Unfortunately, this query doesn’t work and it returns the following error:

Server: Msg 7399, Level 16, State 1, Line 1

OLE DB provider 'MSOLAP' reported an error.

\[OLE/DB provider returned message: Column name 'customer' is invalid. Only level or measure names can be specified.\]

The syntax in BOL is wrong. When you use the SQL language to access OLAP Services, a cube is represented as a single denormalized flat table with one column for each dimension level and one column for each measure. Individual columns in the denormalized cube/table are referenced using the following syntax:

Dimension:Level or Measures:MeasureName

Here’s a simple query that works:

select * from openquery(olap_server(no_nittany), 
"
SELECT 
	\[Store:Store Country\],
	Store:Store State\],
	\[Store:Store City\], 
	SUM(\[Measures:Units Ordered\]) 
FROM 	warehouse
GROUP BY 
	\[Store:Store Country\],
	\[Store:Store State\],
	\[Store:Store City\]
"

This query will generate a result set summarizing the value of Units Ordered for every store in the warehouse cube. At this point, you should be able to configure almost any ODBC-based report writer to create reports based on the SQL statement you’re passing to the linked OLAP Server.