PRB: SQL SELECT Query to OLAP Does Not Return Results for Certain WHERE Clauses (275235)

SYMPTOMS

A SQL SELECT query to a Microsoft OLAP 7.0 Server, using the Microsoft OLE DB Provider for OLAP, might not return results when the number of members is greater than the large level threshold and the unique member name is not specified.

For example, if you query against the OLAP 7.0 FoodMart Sales database and you specify a WHERE clause with the Customers:Name level, a result set may not be produced for some queries.

RESOLUTION

Use a unique name in the WHERE clause of the Multidimensional Expression (MDX) query:

   SELECT DISTINCT [Customers:Country], 
	[Customers:State Province], [Customers:City], 
	[Customers:Name] 
	FROM [Sales] 
	WHERE  [Customers:Name] =  
       '[Customers].[All Customers].[USA].[WA].[Bremerton].[Larry Weeks]'

MORE INFORMATION

You can use an ad-hoc query tool such as the OLE DB QueryDemo sample or a Microsoft ActiveX Data Objects (ADO) application to pass a SQL query to the OLAP server and get the results.

Here are a few examples:

Case 1

SELECT DISTINCT [Customers:Country], 
           [Customers:State Province], [Customers:City], 
           [Customers:Name] 
           FROM [Sales] 
           WHERE [Customers:City] = 'Bremerton'

The preceding SELECT statement returns 90 rows with the first row Customers:Name of Larry Weeks

Case 2

SELECT DISTINCT [Customers:Country], 
           [Customers:State Province], [Customers:City], 
           [Customers:Name] 
           FROM [Sales] 
           WHERE [Customers:City] = 'Bremerton' 
           AND [Customers:Name] = 'Larry Weeks'

The preceding SELECT statement returns 1 row with Customers:Name of Larry Weeks

Case 3

SELECT DISTINCT [Customers:Country], 
           [Customers:State Province], [Customers:City], 
           [Customers:Name] 
           FROM [Sales]  
           WHERE  [Customers:Name] = 'Larry Weeks'

The preceding SELECT statement returns 0 rows instead of 1 row like the preceding case 2.