SSAS DB not visible in Management Studio but can be seen in Excel

5
votes

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?

6
sql-serverssasssms
Are you sure you connect to the right server? – Igor Borisenko
Is it possible that the data source is running on a different instance? I ask because with 2012, it's now possible to install both a multi-dimensional and a Tabular instance on the same server. – brian
@brian How can I verify? – NS.X.
In excel, you should be able to get the instance name from the data sources' connection string. Copy everything between "Data Source = " and the following ";". Then paste that into the server connection in SSMS and connect. – brian
@brian Just checked, the name after 'Data Source =' from Excel is just the server name I've already known. – NS.X.

6 Answers

6
votes

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.

6
votes

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

1
votes

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...

0
votes

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.

0
votes

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.