Enterprise Gateway: Not possible to use as data source?

Your username has to be added to the data source which has to be added to the gateway

Add a data source

You can add a data source by either selecting a gateway and click Add data source, or go to Gateway > Add data source.

You can then select the Data Source Type from the list. Select Analysis Services if you are connecting to either a Multidimensional or Tabular server.

You will then want to fill in the information for the data source which includes the Server and the Database.

The Username and Password that you enter will be used by the gateway to connect to the Analysis Services instance.

You can click Add after you have everything filled in. You can now use this data source for scheduled refresh, or live connections, against an Analysis Services instance that is on premises. You will see Connection Successful if it succeeded.

Advanced settings

You can configure the privacy level for your data source. This controls how data can be mashed up. This is only used for scheduled refresh. It does not apply to live connections. Learn more

'Get Data' experience for Analysis Services in Power BI site

A unique option for Analysis Services is to use Get Data within the Power BI service directly. You can connect to a live Analysis Services data source that is configured within the gateway without needing Power BI Desktop. Your account needs to be listed in the Users tab for the data source, under the gateway, for it to show up in the list. To connect to the data source, you can do the following.

1. Within the Power BI service, select Get Data.

2. Select Databases.

3. Select SQL Server Analysis Services > Connect.

4. Select a data source from the list. Any Analysis Services data source that you have access to will be listed here.

5. Select the model that you want to connect to. Then select Connect.

You will see a dataset show up with the name of the server. You can then select that dataset and begin to create reports on it. This will be working against live data.

Usernames with Analysis Services

Each time a user interacts with a report connected to Analysis Services, the effective username is passed to the gateway and then onto your on-premises Analysis Services server. The email address, that you sign into Power BI with, is what we will pass to Analysis Services as the effective user. This is passed in the connection property EffectiveUserName. This email address should match a defined UPN within the local Active Directory Domain. The UPN is a property of an Active Directory account. That Windows account then needs to be present in an Analysis Services role. If a match cannot be found, in Active Directory, the login will not be successful. Learn more

You can also map your Power BI sign in name with a local directory UPN.

How do I tell what my UPN is?

You may not know what your UPN is, and you may not be a domain administrator. You can use the following command from your workstation to find out the UPN for your account.

whoami /upn

The result will look similar to an email address, but this is the UPN that is on your local domain account. If you are using an Analysis Services data source for live connections, this must match what was passed to EffectiveUserName from the gateway.

What about Azure Active Directory?

Microsoft cloud services use Azure Active Directory to take care of authenticating users. Azure Active Directory is the tenant that contains usernames and security groups. Typically, the email address a user signs in with is the same as the UPN of the account.

What is my local Active Directory’s role?

For Analysis Services to determine if a user connecting to it belongs to a role with permissions to read data, the server needs to convert the effective username passed from AAD to the gateway, and onto the Analysis Services server. The Analysis Services server passes the effective username to a Windows Active Directory domain controller (DC). The Active Directory DC then validates the effective username is a valid UPN, on a local account, and returns that user’s Windows username back to the Analysis Services server.

EffectiveUserName cannot be used on a non-domain joined Analysis Services server. The Analysis Services server must be joined to a domain to avoid any login errors.

Mapping usernames for Analysis Services data sources

Power BI allows for mapping usernames for Analysis Services data sources. You can configure rules to map a username logged in with Power BI to a name that is passed for EffectiveUserName on the Analysis Services connection. The map user names feature is a great way to work around when your username in AAD doesn't match a UPN in your local Active Directory. For example, if your email address is [email protected], you could map it to [email protected], and that value would be passed to the gateway. You can learn more about how to map user names.

Synchronize an on-premises Active Directory with Azure Active Directory

You would want your local Active Directory accounts to match Azure Active Directory if you are going to be using Analysis Services live connections. As the UPN has to match between the accounts.

The cloud services only know about accounts within Azure Active Directory. It doesn’t matter if you added an account in your local Active Directory, if it doesn’t exist in AAD, it cannot be used. There are different ways that you can match your local Active Directory accounts with Azure Active Directory.

1. You can add accounts manually to Azure Active Directory.

   You can create an account on the Azure portal, or within the Office 365 Admin Portal, and the account name matches the UPN of the local Active Directory account.

2. You can use the Azure AD Connect tool to synchronize local accounts to your Azure Active Directory tenant.

   The Azure AD Connect tool provides options for directory and password synchronization. If you are not a tenant admin or a local domain administrator, you will need to contact your IT admin to get this configured.

3. You can configure Active Directory Federation Services (ADFS).

   You can associate your ADFS server to your AAD tenant with the Azure AD Connect tool. ADFS makes use of the directory synchronization discussed above but allows for a single sign-on (SSO) experience. For example, if you are within your work network, when you to a cloud service, and go to sign in, you may not be prompted to enter a username or password. You will need to discuss with your IT Admin if this is available for your organization.

Using Azure AD Connect ensures that the UPN will match between AAD and your local Active Directory.