I have developed some Power BI dashboards that use Microsoft SQL Server databases as source which i have published to the power BI service.
I have also installed an "On-premises data gateway" such that i can schedule them for automatic refresh.
The automatic refresh for all the Dashboards that are using the SQL server relational databases as source is working fine with out any problems.
PROBLEM:
One dashboard though is using an Analysis Services cube as source and its automatic refresh has simply refused to work. I have added its data source to the gateway and the connection from the gateway to the source cube looks to be successful.
The problem is that when the dataset refresh is initiated, it always fails. When i check the refresh history in power bi service, i see this error
Unable to connect to the data source undefined.
If i go to the machine where the gateway is installed and i export the logs, i see the text below in the log file.
System.AggregateException: One or more errors occurred. ---> Microsoft.PowerBI.DataMovement.Pipeline.Diagnostics.MashupDataAccessValueException: Mashup expression evaluation error. Reason: . ---> Microsoft.PowerBI.DataMovement.Pipeline.Diagnostics.GatewayPipelineWrapperException: AnalysisServices: The following system error occurred: The security database on the server does not have a computer account for this workstation trust relationship.
Does any one have an idea of what might be causing this. This happening only when the dashboard source is SQL Server analysis services. All the other dashboards are refreshing with out any problems.
If I understood thrown error correctly,
The security database on the server does not have a computer account for this workstation trust relationship
You should check your UPN (User Principal Name) settings,
Power BI allows for mapping user names for Analysis Services data sources. You can configure rules to map a user name signed in with Power BI to a name that's passed for EffectiveUserName on the Analysis Services connection. The map user names feature is a great way to work around when your user name in Azure Active Directory (Azure AD) doesn't match a UPN in your local Active Directory instance. For example, if your email address is nancy@contoso.onmicrsoft.com, you map it to nancy@contoso.com and that value is passed to the gateway.
The email address must match a defined user principal name (UPN) within the local Active Directory domain. The UPN is a property of an Active Directory account. The Windows account must be present in an Analysis Services role. If a match can't be found in Active Directory, the sign-in isn't successful.
Nice article from RadCad