Connecting to SSAS tabular model

[PeterGadsby]

I have an SSAS server in Azure running SQL server 2016 with analysis services

When I login to the server I can connect to the SSAS tabular model all fine.

I have installed a Power BI gateway on the server, and configured a gateway in Power BI.

when I run "test connections" all works fine and Iwhen I run SSAS profile on the server I see the login correctly and everything is fine.

I then published my power bi desktop report to the web. I then get a warning triangle saying it can't connect to the data source.

I looked at the profiler in SSAS and the user name is passed as per the previous test, but the profile shows an error as follows:

"The following system error occurred: The name provided is not a properly formed account name. "

I then tried to map user names from power bi to the account being used in the test connection.

Still the same error....

NOTE: The SSAS server is not in a domain I am using a local admin account  (testing) 

HELP !!!

Thanks

Peter Gadsby

[v-qiuyu-msft]

Hi @PeterGadsby,

As stated by Adam in this article:  Guy in a Cube - How authentication works with Analysis Services live connections.

"The email address that is passed into EffectiveUserName has to match a UPN property on a local Active Directory account. For this reason, the Analysis Services machine has to be joined to a domain. The domain of the email address has to match a UPN Suffix within your local Active Directory."

If the SSAS server doesn't exist in a domain, Power BI Service will not able to connect to it use data gateway. So in your scenario, what you get is expected.

To work around the issue, you can connect to SSAS tabular model in Import mode, and install a personal gateway on the same server which hosts SSAS. Then configure the dataset use personal gateway to refresh.

Best Regards,
Qiuyun Yu

[eskyline · Accepted solution]

Pretty sure SSAS must be in a domain in order for its authentication to work.  What you have done thus far is likley connecting because you are local admin on the box.  So your options might be to make the Azure VM a domain controller, or sync your local AD to Azure AD and utilize Azure Active Directory Domain services to create a virtual domain controller to which you could add the SSAS instance, or forgo the SSAS instance in the VM altogether.  Seems like everyone wants to install SSAS in a virtual machine in Azure just to test it before setting things up on premises.  Not sure if that is your scenario but based off SSAS authentication it is not trivial.  

One thing to remember is that that any Power BI Dataset deployed to the Power BI Service is itself already a SSAS tabular model so possibly the seperate stand alone instance might not be necessary.  The Power BI Service allows you to schedule refreshes of dataset (i.e. SSAS processing), connect to relational databases to retrieve hte data (also processing), and it allows users to connect directly to Datasets with its Analyze in Excel feature so they can get the standard Excel Pivot table analysis if desired.  Additionally row level security is supported in the service and the price is pretty good (i.e. free since in your case it sounds like you are using Pro features already since you have implmeented the gateway) vs. the price for Azure VMs running SQL Server. 

Just something to think about.  You would need to determine what features you are needing vs. what the service already provides.  If you must have MDM vs. Tabular then its likely not the correct fit.

[PeterGadsby]

Thanks makes sense... Re: models in Power BI I have noticed that when a change is made (remove a column change a name etc....) this seems to break the reports that are associated with the model.

I have recently had this issue when removing two columns one from a fact table and one from a dimension linked to the fact.....

Resource name and Location need to match.
Resource name: Event Summary Fact. Location: Event Summary Fact

At present my thoughts are that I can create a centralised data model in SSAS (tabular) and schedule this to refresh this once rathe than having multiple data models within Power BI.

Pete