Invisible Cubes

I had a head-scratcher the other day. All of a sudden, SQL Server Management Studio stopped showing the cubes in the dropdown of the MDX Query tab although MDX queries and reports would execute just fine. The Reporting Services MDX Query Designer would complain with “No cubes found” error when I attempted to launch it from the dataset properties. This strange behavior coincided with installing SQL Server 2008 SP1 CTP, so I was quick to find a culprit. My suspicion was that the SSAS Windows authentication was failing was some reason although the SQL Server Profiler would show that the right Windows identity was connecting to the database.

To troubleshoot this further, I issued the following schema rowset query when connected to the SSAS database in SSMS:

select * from $system.mdschema_cubes

Interestingly, the query would return a list of the database dimensions (SSAS treats dimensions as cubes and prefix the dimension name with $) but it wouldn’t return the cube name itself. Then, I had a Eureka moment. I went to the Cube Structure tab in the BIDS Cube Designer, and I realized that that somehow I managed to set the cube Visible property to False. Not sure why you would ever want to make the cube invisible and why this property is there to start with but there it was. Flipping it back to True and redeploying the cube fixed the mysterious No Cubes Found error.