Enterprise Gateway: Not possible to use as data source?

[OneWithQuestion]

I use an Enterprise Gateway to call back to SSAS Tabular on prem.

I was looking at using Desktop to build some reports and I can't seem to work out how to connect to it as my data source?

I can DIRECT connect...but then when I deploy my report what happens then?

Do I deploy the report and then edit the reprot on the web service to repoint the data to my gateway?

Thanks!

[dkay84_PowerBI]

Have you already configured your gateway and added the data source to the gateway?

Some good info

General gateway info: 

https://powerbi.microsoft.com/en-us/documentation/powerbi-gateway-onprem/

Managing data sources (including Get Data with SSAS on Power BI site): 

https://powerbi.microsoft.com/en-us/documentation/powerbi-gateway-enterprise-manage-ssas/

In depth review:

https://powerbi.microsoft.com/en-us/documentation/powerbi-gateway-onprem-indepth/

Troubleshooting:

https://powerbi.microsoft.com/en-us/documentation/powerbi-gateway-onprem-tshoot/

[OneWithQuestion]

Yes.

The gateway works great for stuff I do in the web service only, but I can't seem how to point the Desktop to use my enterprise gateway when I go to create a new report in Desktop?

I must be missing something obvious.

With Power BI Desktop:

Can you select an existing data gateway as a data source to create a new report? 

[dkay84_PowerBI · Accepted solution]

You add the data source to the gateway and then point Power BI desktop to the data source and the gateway will use your Power BI login credentials to authenticate you against the data source

[OneWithQuestion]

So I don't point Desktop to the gateway I just point it to the exact same SSAS Tabular servername and everything that the enterprise gateway was setup to use?

Then when I publish a report Power BI service will "just know" to use the gateway I already setup?

[dkay84_PowerBI]

The gateway is a service bus that handles communication between PowerBI service and the data source.  When you publish a report that points to a data source that has been added to the gateway, the gateway will authenticate your access to the data source upon refreshing/querying the data.

Your desktop app should be able to connect to on prem data sources (provided you have access) without the gateway.  However, those login credentials you used (i.e. server name and password) to connect to the data source will be encrypted and stored so that the gateway can query data from on prem.  

Here are the steps that occur when Power BI service talks to the data source:

1. A query will be created by the cloud service, along with the encrypted credentials for the on-premises data source, and sent to the queue for the gateway to process.

2. The gateway cloud service will analyze the query and will push the request to the Azure Service Bus.

3. The on-premises data gateway polls the Azure Service Bus for pending requests.

4. The gateway gets the query, decrypts the credentials and connects to the data source(s) with those credentials.

5. The gateway sends the query to the data source for execution.

6. The results are sent from the data source, back to the gateway, and then onto the cloud service. The service then uses the results.

Power BI service "knows" to use the gateway because when you installed it you had to sign in, effectively pairing the gateway with your Power BI account.

https://powerbi.microsoft.com/en-us/documentation/powerbi-gateway-onprem-faq/

[OneWithQuestion]

OK, that's exactly how it works, cool.

I just didn't expect it would be able to take the local desktop data connection and match it to the Enterprise Gateway, cool!

[dkay84_PowerBI]

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.

Note:

The Windows account you enter must have Server Administrator permissions for the instance you are connecting to. If this account’s password is set to expire, users could get a connection error if the password isn’t updated for the data source. For more information, see the main on-premises data gateway article to learn more about how credentials are stored.

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 nancy@contoso.onmicrsoft.com, you could map it to nancy@contoso.com, 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.

Note:

Synchronizing accounts with the Azure AD Connect tool will create new accounts within your AAD tenant.