7

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?

NS.X.
  • 2,072
  • 5
  • 28
  • 55
  • Are you sure you connect to the right server? – Igor Borisenko Jul 07 '13 at 08:54
  • 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 Jul 08 '13 at 01:42
  • @brian How can I verify? – NS.X. Jul 08 '13 at 01:54
  • 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 Jul 08 '13 at 02:10
  • @brian Just checked, the name after 'Data Source =' from Excel is just the server name I've already known. – NS.X. Jul 08 '13 at 02:23
  • I also have the same issue. Can connect via other tools but not SSMS. After connecting it does not show if the server is running or not... and shows no databases. – Tiago Andrade e Silva Jan 20 '17 at 22:23

6 Answers6

6

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

anonym
  • 69
  • 1
6

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.

Darren Gosbell
  • 1,941
  • 13
  • 17
  • Via excel I am able to list the databases ... why wouldn't I be able to list the databases using SSMS !? makes no sense to me. – Tiago Andrade e Silva Jan 20 '17 at 22:24
  • 2
    SSMS is a administration tool. The object browser in SSMS will only list databases for which you have admin rights. However if you open an MDX window in SSMS it will show you a list of all databases for which you have "read access" – Darren Gosbell Feb 15 '17 at 00:48
1

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

Marcello Miorelli
  • 3,368
  • 4
  • 44
  • 67
user2058966
  • 11
  • 1
  • 1
    Answers need to help in solving the actual problem specified in the question. If you want to express your opinions in someone else's answer, it would be a better idea to post it in the specific answer. – Sanip Mar 28 '19 at 08:39
0

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.

Muhammad Yaseen
  • 661
  • 1
  • 5
  • 6
0

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.

lucian
  • 3
  • 2
-1

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

Mikhail Kostylev
  • 1
  • 2
  • Welcome. Please see [answer], specifically _Links to external resources are encouraged, but please add context around the link so your fellow users will have some idea what it is and why it’s there. Always quote the most relevant part of an important link, in case the target site is unreachable or goes permanently offline._ Also consider taking the [tour]. – Bugs May 02 '17 at 10:05