Database Name Not Optional (SSAS Tabular Direct Connect)

[leonardmurphy]

Note: this question is similar to this (older) thread, but the functionality doesn't seem to be working exactly as designed, preventing me from doing what I need: http://community.powerbi.com/t5/Desktop/Changing-Connection-in-SSAS-Live-mode/m-p/1216#M198

When I do a live connection to a tabular SSAS cube, I am prompted for the Server and Database (Optional). However, if I don't type in a database name, I get told that the server doesn't have any tabular models. I have to type in the database name in order to connect (despite it saying the database name is optional).

So that's fine. I type in the Database name and go on my merry way.

However, later, I want to change the server I'm connecting to (from a development server to a production server). This is where I run into problems.

When I click 'Edit Queries' and type in the server name, there is no box for Database, so I can't type it in even if I want to. And if I try to connect without a database name, it again says there are no tabular models on the server. The Database is the same on both servers, so it's not even changing.

I've tried changing the .pbix file to a .zip, pulling out the Connections file, changing the server name there, then putting the .pbix file back together again, but then the file won't open in Power BI Desktop at all.

Does anyone else have this problem (where the database name is not as optional as claimed)? Is there something I can do to my SSAS server so that it recognizes there are tabular models when I don't put in a database name? Otherwise, how do I change a tabular live connection from one server to another in PBI Desktop given that I can't specify the Database directly when I edit the query?

[boje]

Hi,

I have the exact same problem.

We have been developing a big report in one perspective and want to change to another. This is not possible because we need to enter the database. 

Have you found any solution?

[leonardmurphy]

I never did find a solution, but it is server specific - it's only one of my SSAS tabular servers that I can't switch to (or connect to without typing in a database name). Other SSAS tabular servers work just fine, so it is definitely something to do with the SSAS server. I just don't know what the difference is.

It could potentially be Kerberos delegation related, but that's a Pandora's box to open, so I chose not to.

Perhaps submit a frown, and please do update the thread if you learn anything useful? I never frowned.

[leonardmurphy]

If I select import data, I am not required to enter a database name. Instead, I am prompted for my credentials. I select windows credentials, and then the Navigator appears. There is only one database on the server in question, but in the Navigator to import data it shows several drill downs under the database.

I can then choose my tables and import.

The servers are all on the same domain, but the enterprise gateway is installed on a different server than either SSAS instance.

SSAS is SQL 2014 Enterprise SP1: 12.0.4100.1

Note: Edited to include image rather than description and add server version. (I wasn't signed in so the upload image option wasn't there when I first replied) 

[pqian]

@leonardmurphy hmmm...that's not what I'm expecting. I was hoping that maybe there is some DB with no model that could possibly explain the part that you can connect by specifying the right DB. 

In any case, to unblock your scenario, in the last update (December) we shipped a preview feature to lift the tabular model restriction on AS live connect. You can goto File->Options->Preview Features and enable this. Can you give this a try and see if you can now connect to the server (and maybe post a screenshot of the server under live connect to see if there are any clues)?

[leonardmurphy]

I enabled the preview feature 'Explore live with SQL Server Analysis Services multidimensional models' as well as 'Use KPIs from Power Pivot or SSAS Tabular' - which are the two preview features that related to cubes.

It still shows that there are no tabular models if I type in the server name without specifiying a database.

If I do type in the database name, then this is what I see:

[Seth_C_Bauer]

@leonardmurphy I can't seem to replicate the issue your having. (current desktop Version: 2.30.4246.181 64-bit (December 2015))

I see all my tabular models when inputting the instance name... Couple additional thoughts:

Have you tried to fully qualify the name?

Have you checked to make sure the SSAS instance is installed correctly? Set up as Tabular not Multidimensional? Or is everything set up right and you've already determined the problem exists in PBI?

[leonardmurphy]

It's good to know it's unique to me. It's likely not a Power BI Desktop issue then...probably something with my server.

I'm using PBI Desktop 2.30.4246.281 64-bit (December, 2015)

The name is fully qualified. The cube is definitely tabular 🙂

BUT...I tried connecting to a different server and that worked without me specifying the database name. So that also points to it being a difference between the servers. (The one that works has a database engine, SSIS and SSAS tabular - the one that doesn't work has SSRS and SSAS tabular).

That makes me feel a lot better.

[pqian]

@leonardmurphy, Something is definitely unique about your server. Would it be possible to give me access to that server (assuming it's on the cloud)?

[leonardmurphy]

It's an on-prem server.

I'll have to run remote access by some folks. I'll get back to you (probably next week).

I've tried a few different things (syncing a 2nd cube to the server, deploying a cube directly to the server) to see if that triggers recognition of tabular models. No luck. The message about there being no tabular models is instantaneous too...it's not trying to connect and timing out or anything like that. 

The impact to me is small right now (I can develop my reports against the server in question from the get-go).

Thanks for your help narrowing this down so far @pqian and @Seth_C_Bauer

[Seth_C_Bauer]

I updated to the latest desktop version (as I saw a slight difference) - I still have no issue.

Does the initial connection rely on the gateways.. I forget? What gateway did you install? Is it still the Tabular Gateway or is it the new Enterprise Gateway? My set up is still using the Tabular... so that could be a difference as well.

[Seth_C_Bauer]

@leonardmurphy Are the two SSAS instances in different domains? And if so, do you have a AS Gateway installed on that server?

[pqian]

@leonardmurphy

Do you know the AS server version? If you select "Import data ...", do you see a list of databases there? Could the first database be empty by any chance?

[pqian]

@leonardmurphy That is really weird...I'm not aware of such limitations. I can dig further on this.