PRB: Some Database Tables Are Not Available for OLAP Cubes (199872)

SYMPTOMS

Some tables from multi-user databases may not be listed among available tables in the Cube Wizard and the Dimension Wizard, and you may receive an error that resembles the following:

Cannot retrieve the list of columns for the 'SomeTable' table.

CAUSE

When using Microsoft SQL Server or an Oracle database as a datasource, you cannot reference tables created by user IDs other than the user ID specified in the current OLAP datasource.

All tables in these databases are prefixed with the creator's user ID. Ignoring these prefixes could lead to ambiguity of object names. Microsoft SQL Server OLAP Services is unable to work with this prefix, and limits available tables and views to the current user ID in the Dimension Wizard, the Cube Wizard, and in Decision Support Objects (DSO) programs.

WORKAROUND

Use the user ID and password from the datasource, login to the source database, and create a view to the desired table. The table's owner may need to grant the datasource's user ID permission to create a view. The view will then be available to the OLAP dimensions and cubes.

MORE INFORMATION

The third-party products discussed here are manufactured by vendors independent of Microsoft; we make no warranty, implied or otherwise, regarding these products' performance or reliability.