TABLE OF CONTENTS

• Introduction
• Steps of PowerBI
• Workflow:
• Cryptography
• Power BI Add a client secret approach

Introduction

PowerBI reports can now be seen in Znode. 

Steps of PowerBI

• Tools Installations:
  • PowerBI Desktop (Used for designing the reports and it should be installed on the developer's machine).
  • On-Premises Gateway (This is Used for pushing the data from SQL to Power Bi Service/Azure, and it should be installed on the Database server that we will be using).
  • A PowerBI Pro account (Used for publishing content to the Power BI Report Server).
  • A Microsoft Azure subscription (Used for authentication as with that access token user can call the PowerBI Apis).
• Below are the details of the tools:
  • PowerBI Desktop
    • This tool is mainly used for designing and publishing the report in the PowerBI workspace. The developer will install this tool in his system and design the report by connecting it to the SQL database. For publishing the report users will need a PowerBI Pro account which is covered in the third point. For more details regarding the PowerBI Desktop tool please go through this document. Below is the image of how it looks
      
      
  • On-Premises Gateway
    • The reports we generate from the PowerBI Desktop tool need to push the data to Azure. So the pull and push operation of data is done via this tool. This tool should be installed in the database server that we are using in the PowerBI Desktop tool. 

      Please refer to the diagram below for more clarification
      

    • Also, we have to configure this gateway in the PowerBI workspace setting by adding the database username and password.

      
      

  • PowerBI Pro account
    • This is a web application where users can create a workspace so that the user who designs a report can publish it into that particular workspace. 

      Below is the image of how it looks
      
      

  • Microsoft Azure subscription

    • We are using Azure for authentication purposes, as we will be using the authentication token to call the PowerBI API. Also, PowerBI APIs can only be accessed through Azure.
• Process Diagram
  • On-Premise Gateway & PowerBI Desktop tool’s integration with PowerBI Service:
    

    So as we can see in the above image, The PowerBI desktop tool will connect to the SQL server database for creating the report. Now this data should be pushed to the cloud, for that we use the on-premises gateway.

    The on-premises data gateway acts as a bridge to provide quick and secure data transfer between on-premises data (In our case, Znode SQL Database) and several Microsoft cloud services.

  • Znode - PowerBI Report integration
    
    So to integrate PowerBI, Znode will pass the required data to Azure for authentication token. Once Znode gets the authentication token it will invoke the PowerBI Apis and from that, we will get the response in the IFrame URL.

• Below are the detailed steps that are required to generate a report in PowerBI,
  • Login into a PowerBI Pro account and create a workspace.
    
  • Open the Power BI Desktop tool to design the report. To design the report, users have to connect this tool to the SQL server database. It will fetch the data and from that user can design the report simply by drag-drop option. Once the report is generated, Click on the publish menu to publish the report into the particular workspace that the user created in his PowerBI Pro account. 

    Now here comes the concept of On-Premises Gateway

    • This gateway is used to pull and push data from the SQL database to the cloud.

    • It acts as a bridge to provide quick and secure data transfer between on-premises data and several Microsoft cloud services.

    • This gateway should be installed in the database server that we are using to make the reports.

    • After publishing the report, We have to configure the gateway on the workspace setting with database username and password.
      
      

  • Now users have to register this Power BI application to Azure Portal. There are two ways to register the PowerBI Application to Azure Portal

    • Using PowerBI Portal - Go to dev.PowerBI.com/apps 

      Register with the required data and take note of the Application ID.

      Using Azure AD - Go to https://portal.azure.com 

      Login here and make sure the PowerBI service account and this azure account should have the same username. Also after registration take note of the application ID.

  • Here's how to register the application with the PowerBI App Registration Tool,

    • Go to dev.PowerBI.com/apps.

    • Select Sign in with an existing account then select Next.

    • Provide an Application Name.

    • Provide an Application Type.

    • There are two application types, Native and Server-Side web applications.

    • Here are the differences as to why you choose Native over Server-side web application for an application type

      • Native Application:

        • Creating an application that is designed for customers using a master user account (a PowerBI Pro license used for signing in to PowerBI) to authenticate.

        • In this approach, customers do not need to know about the PowerBI, they will just use this feature to see reports.

        • This master account will be stored on the server side itself. This is the proxy account where everyone has to go from this to get authenticated.

      • Server-side Web Application:

        • This is the user account where each individual user will go to authenticate to the Azure active directory for an authentication token.
          

  • Znode will use the Native application as there will be only one master account and also with this approach, customers do not need to know about the PowerBI, they will just use this feature to see reports.

    • What is the use of application ID - 

      Users register their application with Azure AD to allow application access to the PowerBI rest API. Once the user registers his application, he can establish an identity for his application and specify permissions to PowerBI rest resources.

      The application is used by the application to identify itself to the users from which you're requesting permissions.

    • Refer to this URL: https://docs.microsoft.com/en-us/power-bi/developer/register-app

• Access and Permission Guidelines
  • Now the user has to Assign Permission to the apps that he has created. Please look at this video for the steps on how to grant permission.
  • Users will not be able to give access to some permission using their portal Azure account. So to grant access permission the user has to ask the organization admin Azure account which will have control over these permissions and he will grant these permissions.
• The above steps are required for configuring PowerBI. Once these steps are done, developers can update the report from the PowerBI Desktop Tool and simply publish it. The new, updated report changes will automatically reflect on the client's report.
• Now for generating a new report, the developer will create a new report and publish the report in a different workspace, so that only GroupId and ReportId will change else remain the same to load that particular report. Or If we use the same workspace which was used previously then only ReportId will change.

Workflow:

To view the PowerBI reports in the Znode Admin application the following changes were implemented with two approaches:

In the ADMIN application web. config key settings-

<add key="IsGeneratePowerBIReportUsingSecureSecretValue" value="true"/>

• When its value is set as true then it will show the report using a secure secret key.
• When its value is set as false then it will show the report using a username and password with cryptography technique.
  
  

Cryptography

With this approach, we encrypt the password using base64 encryption when saving Power BI Configuration Settings from the Global setting. While fetching the report we decrypt the password using the same base64 decryption and show the report.

Power BI Add a client secret approach

Power BI Add a client secret approach is taken to overcome the security 

Concern.

Add a client secret -  Also called an application password, a client secret is a

 string value your app can use in place of a certificate to identify itself.

Using Power BI embedded analytics which allows you to embed your Power BI 

items such as reports, dashboards, and tiles, in a web application or in a 

Website

Using Embed for your customers' solution we achieve this approach.

The embed for your customers' solution allows you to build an app that uses 

non-interactive authentication against Power BI.

Cryptography

A new tab is added in the Global Settings named PowerBI Settings which contains text fields as per the fig. attached below. 

Below are the key values that are used to  connect the PowerBI api with the Znode admin application,

• Group ID: It is in the form of a guide as this group ID is the power bi workspace ID.
• Report ID: It is also in the form of a guide, we get this id from the Power BI report.
• Application Id: We have to register our application with Azure AD to allow the application access to the Power BI rest APIs, so when we register we get the application ID.
• Tenant ID: It is also in the form of guid but this is different than the tenant name or domain. A tenant is a boundary that represents the organization in azure. It is also called DirectoryId.
• Username & Password: This will be required for authentication. Here we save the password in encrypted format for security purposes.
• API, Authority, and Resource Url are pre-defined which are also needed for the application to access the PowerBI Apis.
  These fields will be used to connect to Azure for authentication and with the authentication token it will call the PowerBI Rest Apis.

Power BI Add a client secret approach

Client secret detail-

To get the client's secret, follow these steps:

• Log into Microsoft Azure.
• Search for App registrations and select the App registrations link.
• Select the Azure AD app you're using for embedding your Power BI content.
• Under Manage, select Certificates & secrets.
• Under Client Secrets, select New Client Secret.
• In the Add a client secret pop-up window, provide a description for your application secret, select when the application secret expires, and select Add.
• From the Client secrets section, copy the string in the Value column of the newly created application secret.
  Note : Make sure you copy the client's secret value when it first appears. After navigating away from this page, the client secret will be hidden and you'll not be able to retrieve its value.

Along with the client secret ID and value, the Znode admin user must have all the values listed below-

• Group ID: It is in the form of a guide as this group ID is the power bi workspace ID.
• Report Id: It is also in the form of a guide, we get this id from the Power BI report.
• Application Id: We have to register our application with Azure AD to allow the application access to the Power BI rest APIs, so when we register we get the application ID.
• Tenant ID: It is also in the form of guid but this is different than the tenant name or domain. A tenant is a boundary that represents the organization in azure. It is also called DirectoryId.

A new menu named PowerBI is added in the Znode Admin application under the Reports menu.