SSAS DB not visible in Management Studio but can b

问题:

There is a SQL 2012 Analysis Server with some cubes.

When I connect to it using Excel 2013 (DATA menu > From Other Sources > From Analysis Services), I can see the DB names in the dropdown.

But when I connect to it with SQL Server Management Studio 2012 (with Analysis Service Client installed, I've used it against other Analysis Servers successfully), I can connect to the Analysis Server, but cannot see any DB - the Databases folder is empty.

Did I miss anything? Is it possible that it's intentionally configured to be so?

回答1:

SSMS is a administration tool. You will only see databases in there to which you have admin access. So if you are not a server admin or are not in a role in any of the databases which has admin rights - you will not see any databases in the object explorer.

回答2:

Try to open SSMS using 'Run As Administrator' and you will see the database.

回答3:

For my case the reason was somehow the Cube Visible property is set to False. Cube will be visible for Excel and other client applications only if this property is set to True. But if the cube has large volume of data, it will take more time to deploy and process the cube after changing the visible property. Therefore it will not be practical and will waste time and resources.

Therefore in such scenario we can use XMLA Query to alter the cube visibility property value. We can easily get the XMLA Query by right clicking the cube and selecting Script Cube as, then ALTER element value to true (just before MeasureGroups). Like below: true

After doing this press F5 to run the query.

回答4:

Create a Role with Full control(Administrator)+ Process database + Read definition permissions and add you user/domain user to that role. Disconnect and reconnect and you should be able to see the Cubes.

回答5:

I do not fully agree with Darren's answer, I am not service administrator but I can see some tabular databases in SSMS on my instance, and there is no role inside those databases giving me administrive access... I am using last release of SSMS, but my SSAS tabular is 2012 sp1. I have roles defining my group as process & read on 4 tabular dbs, but from SSMS, me & my colleagues are seeing only 2 of them. From a role definition perspective we have the same definition for the fours... Of course SSMS is a admin tool, but when I am granted as "process" capacity, I believe that I should see the db inside this tool even when I am not db admin...

回答6:

In this case you should set read definition permissions (Grant read definition permissions on object metadata (Analysis Services))