I’ve spent quite a bit of time the last few days trying to get Power BI preview to work for accessing a tabular model so am sharing my experience. Based on that experience, I am making some assertions that are not even in the Microsoft documentation, albeit most of this is found on various other blogs of users who have tried to get this working.
Below is a screen snapshot of a demo that my adult son, Blake built in his spare time. My thanks to Blake for his work to do the tabular model and report.
My conclusion is that the Power BI configuration is extremely complex, difficult to troubleshoot and requires Active Directory infrastructure configuration. However, once it is working it is seamless to develop reports from a single Power BI site that access tabular models exposed by other organizations without the need to store the data locally.
Here are the steps that I had to do – your mileage may vary and I’m happy if you didn’t have to go through all of this:
1) Power BI can only access a tabular model on a machine joined to a domain.
2) The service account registered with Power BI must be defined in Active Directory with a full UPN exactly matching the Power BI logon. For example, if the logon account is email@example.com, then there must be an account in Active Directory registered with firstname.lastname@example.org This is because Power BI sends all requests for tabular data using the “effectiveusername” technique (see profiler trace at end of this post). This requires the following steps:
a. Use Active Directory Domains and Trusts tool and select properties at the root level to get the UPN Suffixes dialogue as shown below and add the UPN suffix that matches to the Power BI domain name (i.e. company.onmicrosoft.com)
Note that multiple UPNs may be added, but only one can be registered to a specific A/D account.
b. Use Active Directory Users and Computers to identify or create users is mapped to the UPN suffix. Use caution when creating Power BI accounts that are the same as those of the company domain accounts unless the Power BI domain is the same as the standard UPN suffix. Best practice is to register for Power BI using the company’s Email domain rather than relying on the company-name.onmicrosoft.com. Below is an example of using a different UPN than the corporate domain:
The only article I’ve found on this is https://www.artisconsulting.com/blogs/greggalloway/Lists/Posts/Post.aspx?ID=27
Power BI uses a semantic model connection technique with effectiveusername – see https://msdn.microsoft.com/en-us/library/gg471575.aspx for an explanation.
c. This needs to be done for all the Power BI user accounts that consume the model not just the one under which the Analysis Services Connector is configured.
3) Depending on the firewall restrictions, port 8051 (HTTP transport) or 8050 (HTTPS) may need be opened. The documentation states that this is only needed for OData, but I have found it needed at least in my environment even for tabular only. (http://blogs.technet.com/b/powerbisupport/archive/2014/12/19/failed-to-register-the-gateway-key.aspx) For Azure use the endpoint functionality of either 8051 for HTTP and 8050 for HTTPS so that the Analysis Services Connector will work. If using 8051 all data will be transmitted in plain text. However, the credentials themselves appear to be tokenized between the connector and the Power BI site such that username/password theft does not appear to be a risk. I’ve not yet done extensive network sniffing to evaluate the risks with not using SSL.
Only after the above steps are done, will a connector be able to be defined that will actually allow the tabular data to be piped back to Power BI.
4) Configure the Analysis Services Connector using instructions from http://support.powerbi.com/knowledgebase/articles/471577-configure-a-power-bi-analysis-services-connector. When configuring the connector, you are asked to login to Power BI again. Use the Power BI account configured in step 2 that has the required UPN suffix.
5) At this point, Power BI should be able to be connected directly to the tabular model and Excel reports that use the tabular model can be uploaded and should function.
Note the profiler trace of SSAS tabular connection via the gateway – the gateway was actually configured using email@example.com, yet the connection is made on behalf of the specific Power BI user. This means that all users must be granted read access using their full UPN mapping to the Power BI account.
…<PropertyList xmlns=”urn:schemas-microsoft-com:xml-analysis” xmlns:soap=”http://schemas.xmlsoap.org/soap/envelope/”><Catalog>DemoSales_Tabular</Catalog><LocaleIdentifier>1033</LocaleIdentifier>