You might have seen error message like this from a SQL job that pulls data from an OLAP cube using OPENQUERY:
The OLE DB provider “MSOLAP” for linked server “CUBE_LINK” indicates that either the object has no columns or the current user does not have permissions on that object.
When using linked server or writing MDX queries in SSRS reports to pull data from OLAP cube, consider removing the NON EMPTY key word on COLUMNS.
When there is no data in the result set, the NON EMPTY key word on COLUMNS will not return any columns. The linked server will send “object has no columns” error and the OPEMQUERY will fail.
Unless you truly want the OPEMQUERY to fail, consider removing the NON EMPTY key word from the COLUMNS axis.
Removing the NON EMPTY key word from the COLUMNS axis will allow the MDX to return the columns even when the query contains no data.
SELECT --NON EMPTY
{[Measures].[The Minutes]} ON COLUMNS
,NON EMPTY {
[SomeDimension].[EMPID].CHILDREN *
[Date].[Dates].[Month].&[201603] } ON ROWS
FROM TheCube
WHERE …
There are many tips in the book MDX with SSAS2012 Cookbook about how the NON EMPTY key word behaves. Since the book is organized not by MDX functions, but rather by subject areas, the best way to find these tips is to use the Index at the back of the book to find examples and tips in the book.
