Skip to content

Infrastructure setup in Azure for Self-Hosted BI: Analysis Models - Power BI (Scenario 3)

This section guides you through setting up the infrastructure for your own tenant (customer tenant) and how to obtain the configuration details to set up the environment.

Refer to Analysis Models - Power BI architecture overview from here.

Disclaimer

  • Please consider the provided installation script as a sample for creating Azure Resources.
  • Please note that this is a guideline, and the setup will still need to be verified by your company security board to ensure your company standards and policies have been adhered to. IFS takes no responsibility for any data breach for any artefact hosted outside of the IFS domain.
  • Therefore, please ensure that all the created Azure Resources are reviewed on your end. IFS takes no responsibility regarding resource security and cost.

Time Estimation

The installation process could take about 1-2 hours.

Prerequisites

  • An Azure subscription
  • Windows PowerShell
  • Service Principal with the following credentials:
    1. Access Power BI API
    2. Create Workspaces

1. Export the installation script

You can download the Installation script that facilitates the setup from here.

2. Infrastructure setup/ Installation

Once the script is downloaded, use PowerShell to run the installation setup.

You need to login with the credentials. Once done, it will show the subscriptions to which you have access.

Later on in the setup, you will be prompted to enter the subscription ID. And in the Azure Portal, Resource Groups can be viewed based on the subscription.

Below are the options available and all the steps need to be completed (0 to 9) to fetch the details in option 10 (Get Customer Configuration).

Note

The first three steps need to be followed in order. Once done, following the exact sequence is not strictly recommended, as these steps reference the Key Vault. As you go along, certain steps need to be completed in a logical sequence. (i.e., Creating Data Lake Directories can be done after Creating the ADLS.)

  • Option 1: Create a Resource Group
  • Option 2: Create and Populate Key Vault
  • Option 3: Set up Azure Artefacts
  • Option 4: Create customer ADLS
  • Option 5: Create Data Lake Directories
  • Option 6: Create Fabric Capacity
  • Option 7: Configure Remote VM and PBI gateway
  • Option 8: Update Power BI Gateway Name
  • Option 9: Create Power BI Workspaces
  • Option 10: Get Customer Configuration
  • Option 11: Update Key Vault Secret
  • Option 12: Show Pre-Requisites

The Show Pre-Requisites (Option 12) displays what needs to be prepared to complete the installation process smoothly.

Option 0 is a part of the Pre-Requisites.

Please note that during this installation process, the below details should be kept prepared as you will need them to be entered into the build environment.

Option 0: Create a Service Principal

Type 0 to proceed and initiate the Service Principal creation. Provide the details as requested through the script (Subscription ID, Resource Group Name, Service Principal Name) to proceed.

Based on the output you can,

1.Provide the Service Principal with these credentials (Access Power BI API, Create Workspaces).

or

2.Create a Service Principal.

To create the Service Principal, run the below-highlighted script in PowerShell (note that you need to have admin privileges to proceed with the same), and allocate the permissions- Access Power BI API, Create Workspaces.

Once done, the Service Principal credentials will be stored.

Script for Creating SP:

To create a Service Principal, run the below highlighted code.

Option 1: Create a Resource Group

Type 1 to proceed and initiate the Resource Group creation. To complete this step, the below details are required.

  • Subscription ID
  • Customer Code
  • Location

Once the necessary details are entered and completed, navigate to Azure Portal's created Resource Group. This will be created based on the given customer code. This resource group will be populated with all the artefacts that will be built.

Below are the items that will be created in the resource group.

  • Key vault
  • Network Interface
  • Network security Group
  • Public IP address
  • Virtual network
  • Storage Account
  • Fabric capacity

Option 2: Create and Populate Key Vault

Type 2 to proceed and initiate the Key Vault creation. To complete this step, the below details are required (as highlighted in the image).

Once the details are entered, the Key Vault building starts. Key Vault will be saved within the same resource group. The admin will have access to Fabric Capacity and Data Lake.

Note

When you choose options from 3 to 10 as listed to create artefacts after the Key Vault is populated, it is not required to re-enter the credentials as the Key Vault will be referred through the script to obtain necessary details.

As the Key Vault is being created, it assigns the correct access control to the Service Principal.

To check and verify the current role assignments, you can go to the created Key Vault in the resource group. In the Access Control (IAM) tab, select the Check Access option.

Current role assignments display that it's been assigned as a Key Vault Secret Officer role.

Then select the Secrets option from the Objects drop-down tab, you can see that the secrets are already populated, which will be referenced during installation.

Option 3: Set up Azure Artefacts

Type 3 to proceed and initiate creating all the required artefacts. This will build the Azure environment for the VM, Public IP and all the necessary items. Once done, you can view the Resource Group in the Azure Portal to see all the created artefacts.

Option 4: Create customer ADLS

Type 4 to proceed and initiate creating the Data Lake. During this process, the details will be fetched from the Key Vault and it will build the storage account.

As the storage account is being created, it assigns the correct access control to the Service Principal.

To check and verify the current role assignments, you can go to the created storage account in the resource group. In the Access Control (IAM) tab, select the Check Access option.

Current role assignments display that the Service Principal is granted the necessary roles.

Storage Security

To control the level of access to the storage account, it is needed to configure the Virtual Networks and Firewall rules accordingly.

1.Navigate to the Networking tab in the storage account.

2.In the Firewalls and Virtual networks tab, select the second option as indicated for the Public network access.

3.In the Virtual networks section, confirm that the created Virtual Network in the resource group is assigned.

The Data Lake storage is included on the same V-net that the gateway is connected to (the VM that will be connected to the V-net as well).

5.In the Firewall section, add the required IPs.

  • Item 1- Personal machine IP as admin - For the admin to access the Data Lake, Firewall rules need to be set and granted
  • Item 2- IFS Public IP that needs to be whitelisted

To obtain the IFS Public IP value, please refer to the Outbound IP Whitelisting section in your Cloud Operations Guide to obtain the relevant IP, depending on the environment that is being used.

  • Item 3- Set the IP of the VM that hosts the gateway

To obtain the machine's Public IP, go to the Public IP address item in the resource group, and copy the IP address from the Overview section.

Option 5: Create Data Lake Directories

Type 5 to proceed and initiate populating the storage account. The data will be retrieved from the Key Vault and the container and folders will be populated. You can follow the steps below to view the folders.

  1. In Azure Portal's resource group, select the created storage account.

  2. Select the Storage Browser tab.

  3. Select Blob Containers.

  4. View populated folders.

Option 6: Create Fabric Capacity

Type 6 to proceed and initiate creating the Fabric Capacity. In this step, it will allocate the Service Principal as Fabric Administrator. Also, another important manual step to follow with this option is to set the XMLA endpoint in Fabric.

During this creation, the Fabric Capacity ID will be stored in the Key Vault which will be referenced in step 9 (Create Power BI Workspaces).

To check and verify the Capacity Admin status, go to the created Fabric Capacity in the resource group. Select Capacity Administrator from the Settings drop-down menu and view that the Service Principal is being set as a Capacity Administrator.

To set the XMLA endpoint, follow the steps below (note: admin privileges are required).

  1. Visit app.powerbi.com and navigate to the Fabric Admin Portal.

  2. Select Capacity Settings.

  3. Go to the Fabric Capacity tab.

  4. Select the relevant Capacity Name and in the details section, expand the Power BI Workloads option.

  5. Set the XMLA endpoint to Read-Write and apply the changes.

    If this XMLA Endpoint option is not set, Uploading models cannot be done in the Power BI workspaces.

xmlaendpoint

Option 7: Configure Remote VM and PBI Gateway

Type 7 to proceed and initiate configuring the VM. The below options will be displayed in the script.

  • Option 1- Show Admin credentials - This option fetches the VM IP address as well as the login credentials from the keyvault

  • Option 2- Continue without showing credentials.

1.To login to the VM, select option 1. This will return the VM IP, User name, Password and the rest of the script that is required to run on the VM.

2.Open Remote Desktop connection.

3.Add the VM IP (highlighted in the above screen as #1).

4.Add the Username and Password (highlighted in the above screen as #2).

5.Once you are logged into the server, open PowerShell.

6.In the VM, run the given script in step 1(copy from the script), which will install .net (this is explained in the script). This may take around 5-10 minutes. After which the VM will reboot.

7.Once rebooted, login to the server again and run step 2 of the given script, to install PBI Gateway.

8.When the installation is completed, add the email address to use with the gateway and select Sign In. In this case, you can use the administrator email that is being used during this installation process.

9.From the below prompt, select option 1 (Register a new gateway on this computer) and proceed to the next step.

10.Add the details in the below view (New on-premise data gateway name, Recovery key) and select Configure.

In this example, the Resource group name +GW suffix (ifspot3demogw) is used as the New on-premise data gateway name.

11.Once the setup is completed, the below confirmation will be displayed.

Note

Important: Please ensure to update the Key Vault with the Power BI Gateway name. This is required to get the Gateway Object ID in Option 10 (Get Customer Configuration).

Option 8: Update Power BI Gateway Name

Type 8 to Update the Power BI gateway Name. Once you completed Option 7: Configure Remote VM and PBI gateway, this step will write the obtained Gateway details into the Key Vault. Option 8 is an important step to follow, which will ensure that Option 10 will return the Gateway Object ID along with the rest of the configurations.

Option 9: Create Power BI Workspaces

Type 9 to proceed and initiate creating the Power BI Workspaces. In this step, after fetching the Fabric ID from the Key Vault, it is required to add a workspace prefix. This is done to group all the workspaces together. Once completed, the workspace IDs and the prefix will be saved in the Key Vault so that it can be referenced at Option 10.

Example prefix: OPT3DEMO

During this step, the workspaces are created and the workspace administrator is assigned to each of the workspaces. Also, it will set the Fabric Capacity as well.

workspace_Creation

Follow the below steps to view the workspaces.

1.Visit app.powerbi.com.

2.Go to Workspaces.

3.You should be able to see the created workspaces.

To check the Service Principal access status,

4.Open a workspace, and go to Manage Access.

5.View the Service Principal has been set as Administrator.

workspace_options

workspace_manageaccess

To check the Fabric capacity status,

6.Open a workspace, and go to Workspace settings.

7.Select the License info section.

8.View that Fabric Capacity is set as the current license.

To assign the Service Principal as admin,

9.Go to app.powerbi.com.

10.Navigate to Manage Connections and Gateways and select the On-premise data gateways tab.

11.You should be able to see the created Gateway. Select the Gateway and go to Manage Users.

12.Type in the Service Principal.

13.Select the Service Principal and assign Admin.

Option 10: Get Customer Configuration

Type 10 to proceed and obtain all the configuration details. Through this step, the credentials will be fetched from the Key Vault, and it will go through all the artefacts to populate the output in the below format.

Option 11: Update Key Vault Secret

If it is required to update /change client secrets, type 11 to initiate the process.

  1. Select the Key Vault.

  2. You can view the below options for the secret to change.

  3. Current details will be returned based on the selection. You can choose to update and write back into the Key Vault by selecting the Y/N option.

    Example: If you need to change the Power BI Gateway Name, selecting option 8 will return the current details. Choose Y/N to proceed with the action accordingly.

Advantages:

  • Once the first 3 steps are completed according to the logical sequence, the installation option selection can be done as required.
  • Due to the above, the whole installation process should not be done in a single stretch. You can always re-run from the stopped point.
  • In case if you have to exit the first PowerShell instance, it is possible to open a new command instance and run the setup again (login again is required).

3. Update Setup Parameters in IFS Cloud Web

Once the installation process is completed successfully, follow the below steps.

1.Login to IFS Cloud Web.

2.Navigate to the Setup Parameters page under Analysis Models - Power BI.

3.Use the obtained values from option 10 to enter against each relevant field. Output from the PowerShell script is ordered in the same way as the fields available on this page.

4.Use the Edit icon to enable each field.

5.Enter the value and Save.

Parameter Name Description
Datalake Account Name This is the Account name related to the Data Lake.
Datalake Container This specifies the Data Lake Container Name.
Tenant ID This is the Azure Tenant ID.
Client ID This is the Azure Client ID.
Target Workspaces This specifies the Target workspace list mapped to relevant areas.
Excluded Workspaces This is the Comma separated list of strings to exclude from workspaces being returned for embedding and report editing via Power BI Lobby Element Designer. Multiple comma separated values can be added and it is case insensitive.
Example: Excluded Workspaces = golden. - In this case the applicable workspaces which includes the word 'golden' are excluded (typically, Golden Workspace which includes published IFS Delivered core models). This prevents the access to the respective workspaces from the Power BI Lobby Element Designer and Power BI Report Editor pages.
Golden Workspace ID This specifies the Golden workspace which will be used to publish IFS delivered core models.
Datalake Gateway ID This is the ID of the relevant Data Lake gateway.
Azure Client Secret This is the Client secret value.
Service Principal Object ID This is the Object ID of the Service Principal.
Datalake AppKey App Key from the Data Lake account, which is used to bind the gateway.
Datapump Flush Size This specifies the count of rows that will be read into memory before being written to disk by datapump.
If an out of memory exception occurs during loading data, this value can be decreased.
(Optimal value = 20,000. If it is less, it will have an impact on the performance)
Embed User Name Claim This is the Name of the claim to be used as EffectiveIdentity when embedding RLS enabled Power BI reports connected to SQL Server Analysis Services.
(EffectiveIdentity defines the user identity and roles).
Claim types example: email address, UPN
http://schemas.xmlsoap.org/ws/2005/05/identity/claims/upn
http://schemas.xmlsoap.org/ws/2005/05/identity/claims/emailaddress
Embed Custom Data Claim This is the Name of the claim to be used as CustomData when embedding RLS enabled Power BI reports connected to Azure Analysis Services.
Claim types example: email address, UPN
http://schemas.xmlsoap.org/ws/2005/05/identity/claims/upn
http://schemas.xmlsoap.org/ws/2005/05/identity/claims/emailaddress
Write Large Numbers As Zero When reading a number causes an overflow exception (larger than 28), and when this parameter is enabled, the value will be written to the parquet file as 0.
Enable Decimal Rounding When a number has more than the max allowed no of decimals (18), the value will be rounded.

6.When adding the secrets (Azure Client Secret, Datalake AppKey), select the field name and click the Set Azure Client Secret/ Set Datalake AppKey accordingly and enter the secrets.

7.Once all details are updated, the credentials will be taken into consideration during the next minute.

Then you can proceed to use Analysis Models- Power BI services.

4. Start using the Analysis Models - Power BI service

After completing the environment setup, refer below guide to start Publishing/Uploading Analysis Models, schedule model refresh, define Parquet Data Sources, and pump data into the datalake.

Ensure that the user permissions are granted accordingly and the Golden Workspace is populated.

Analysis Models - Power BI.