June 26, 2016 Leave a comment
In the previous blog post, I went through steps 1 – 6 for building a SQL Server 2016 BI development VM on Microsoft Azure:
1. Provision the Azure VM using SQL Server 2016 developer image
2. Configure the SSAS/SSRS services
3. Install development software
4. Install sample databases
5. Integrate SSRS and Power BI Service
6. Install and configure Personal Power BI Gateway
7. Install and configure Enterprise Power BI Gateway and setup SSAS connection
This blog post details the last step, installing and configuring Power BI Enterprise Gateway and setting up live connections to Azure VM hosted SSAS from online Power BI services.
In the previous blog post, the personal Gateway has been setup on the Azure VM that allows the Power BI services to refresh/load data from the data sources hosted in the Azure VM. However, there is one important feature missing from personal Gateway, the support of live connections to SSAS, which is only available in the Enterprise edition of Power BI Gateway.
Considering the importance of the live SSAS connection feature, which empowers Power BI to be the visualization front-end of an enterprise-class BI solution, the Azure-based development environment we are building now need to be able to support and test this feature.
Therefore I downloaded the Enterprise Power BI Gateway, installed on the Azure VM, and configured the data source connection and service account/credentials on Power BI services.
Everything seems simple and going pretty smoothly, however, when we I was trying to access the dataset or report in Power BI services, I got this error.
After a little bit more researching on how SSAS live connection works, I’ve learnt that the stored credentials I configured in the Data Source Settings (snapshot above) only setup the initial connection to the SSAS services, and the actual queries are executed based on individual user’s permissions. For the SSAS services to know which user the current query is from, along with the query itself, the EffectiveUserName property (in the format of UPN) of the Power BI user, who started the query through UI interaction, is also passed through Enterprise Gateway to the SSAS. The UPN passed from Power BI needs to match a windows account in the local Active Directory (which is the Power BI user’s mapping account in the on-premise Active Directory). SSAS will then check the Analysis Services roles of the matched windows account and decide whether or not to execute the query.
After understanding how SSAS live connection works, we can now list the requirements we need to meet in order to get the SSAS live connection working in our Azure VM environment:
- The Azure VM need to be in an Active Directory Domain
- For each Power BI user, a corresponding windows account need to be created in the Active Directory.
- The UPN of the Power BI user account need to be mapped to the UPN of the windows account
- The windows account need to be added into the appropriate Analysis Service role
Based on the requirements above, I gave it a try to setup the SSAS live connection in the following steps:
Firstly, I promoted the Azure VM to be a Domain Controller so that the Azure VM itself will be in the domain.
Then, I created a windows account (linxiaodev\linxiao.ma) with UPN like email@example.com (which is the mapping account for the Power BI user)
and added the account into the “Users” role in SSAS.
Next, I went to the “Map user names” panel on the Power BI portal and added a rule to map the UPN of a Power BI user account to the UPN of the windows account we created.
Now, all the settings have been done and the datasets and reports on the Power BI services started to get data through from the live SSAS data source.