FIX: Drill-Through Fails if Dimension Table Has an Alias in the Cube (293528)

SYMPTOMS

If your dimension tables are aliased (obtained by right-clicking the title bar of a dimension table in the Cube Editor and selecting Change Alias) and you select columns from the alias for the drill-through results, the drill-through may fail.

Depending on the data source you are using (Access or SQL Server), an error message that resembles one of the following may be seen:

Unable to drill through.
Data source provider error. Statement(s) could not be prepared.;42000; ....

Unable to drill through.
Data source provider error. [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 12.;07001

CAUSE

The SQL statement that is generated upon drill-through seems to reference the alias names without generating the DimTable AS Alias clause, and as a result, the statement fails. For example, the following statement may be generated:

SELECT Alias.column from Alias

instead of:

SELECT Alias.column from DimTable AS Alias

RESOLUTION

To resolve this problem, obtain the latest service pack for the Microsoft SQL Server 2000 (the Analysis Services Components - Sql2kasp1.exe). For additional information, click the following article number to view the article in the Microsoft Knowledge Base:

290211 INF: How to Obtain the Latest SQL Server 2000 Service Pack

STATUS

Microsoft has confirmed that this is a problem in SQL Server 2000 Analysis Services version 8.0. This problem was first corrected in the Analysis Services Components of Microsoft SQL Server 2000 Service Pack 1.