If you need to write queries in SQL Server Management Studio against an SSAS cube that has its Visible property set to false, you have a problem because when you connect to your database you can’t see the cube or its metadata! Luckily you can override this and make the cube visible by setting the following connection string property when you connect:
Show Hidden Cubes=true
Connection string properties can be set in the Additional Connection Parameters tab of the connection dialog that appears when you open a new MDX query window.
Unfortunately this doesn’t make any objects in the cube that are not visible, like measures or dimensions, visible again – it just makes the cube itself visible. However, if you’re working on the Calculations tab of the Cube Editor in SSDT it is possible to make all hidden objects visible as I show here.
I was wondering in what scenarios would you hide a cube? I’ve never seen it so keen to understand when it occurs what scenarios its used in.
Good question! Sometimes I think people hide a large cube and only make the perspectives visible.