/pbi-dataops-template

Starting with Part 5 of "Bringing DataOps to Power BI" this repository serves to provides templates for applying DataOps principles.

Primary LanguagePowerShellMIT LicenseMIT

Part 5 of "Bringing DataOps to Power BI" this branch serves to provides templates for applying DataOps principles.

These instructions are a continuation from Part 5 of Bringing DataOps to Power BI. The steps below describe how to setup a DevOps project with a pipeline that tests and deploys a Power BI report.

Important Note #1: This guide is customized to Power BI for U.S. Commercial environment. If you are trying to set this up for another Microsoft cloud environment (like U.S. Gov Cloud), please check Microsoft's documentation for the appropriate URLs. They will be different from the U.S. Commercial environment.

Important Note #2: This guide uses scripts that I built and tested on environments I have access to. Please review all scripts if you plan for production use, as you are ultimately response for the code that runs in your environment.

Table of Contents

  1. Prerequisites
  2. Installation Steps
  3. Priming the Pipeline
  4. Running the Pipeline
  5. Failed Pipeline

Prerequisites

Power BI

  • Power BI Premium Per User license assigned to a service account. If you do not have a Premium Per User license, use the "Buy Now" feature on Microsoft's site or if you don't have access to do that, please contact your administrator (be nice!).

Desktop

  • Azure CLI installed.

  • PowerShell 7 installed. If you are using Windows 10 or 11, this should be installed already. For the purposes of the instructions I'm going to use PowerShell ISE to run a PowerShell script.

  • GitHub desktop installed.

  • Power BI Desktop installed on device executing these steps.

Azure DevOps

  • Signed up for Azure DevOps.

  • For Azure DevOps you must be a member of the Project Collection Administrators group, the Organization Owner, or have the Create new projects permission set to Allow.

Installation Steps

Create Power BI Workspaces and Create Azure DevOps project

  1. Open PowerShell Version 7 and enter the following script:

    Invoke-WebRequest -Uri "https://raw.githubusercontent.com/kerski/pbi-dataops-template/part5/SetupScripts/PremiumPerUser/Setup-PPU.ps1" -OutFile "./Setup-PPU.ps1"

  2. This will download the setup scripts to the current folder. Run ".\Setup-PPU.ps1" in PowerShell.

  3. During the install process you will be prompted to enter the following information:

    • The name of the workspaces you wish to create in the Power BI Service.
    • The name of the development workspace you wish to create in the Power BI Service.
    • The name (UPN/email) of the Service account you created in the Prerequisites section.
    • The password for the (UPN/email).
    • The name of the project you wish to create in Azure DevOps.

    Prompt for information in install script

  4. During the course of the install you will be prompted to enter your Microsoft 365 credentials. Depending on your environment you may have a browser tab appear to sign-in. After signing in you can return to the PowerShell window. In addition, if you don't have the Power BI Management Shell or Azure DevOps CLI package installed, you will be asked to install. Please affirm you wish to install those packages if prompted.

    Prompt to install azure devops cli

  5. If the script runs successfully you will be presented with a message similar in the image below.

    Example of successful install

Priming the Pipeline

As stated in Part 5 of Bringing DataOps to Power BI the data source credentials have to be manually set in order for the pipeline to be automated. This is based on Power BI's architecture as of August 2021. Please take the following steps to setup the credentials for the SampleModel.pbix file found within this project.

  1. Navigate to app.powerbi.com and go the workspace you named in step 5 on the Installation Steps. Find the dataset and select the 'Settings' option (example outlined in orange in the image below).

    Dataset Settings screenshot

  2. Expand the 'Data source credentials' section and select 'Edit Credentials' link (outlined in orange in the image below). Dataset Settings screenshot

  3. Select the Authentication method as "Anonymous" and Privacy level to "None" and then press the "Sign in" button.

    Set the credentials

Running the Pipeline

With the Installation Steps and Priming the Pipeline steps complete, you will need to follow the steps below to work with the project locally, make a change to a Power BI file, commit it to the repository in Azure DevOps, and see the pipeline in action.

  1. Copy the URL and navigate the project in Azure DevOps. Click on the Repos section and select the Clone button (outlined in orange in the image below).

Example to Clone Project

  1. Copy the textbox under the 'Command line' label. I suggest copying to Notepad temporarily as you'll have two other text fields to copy (outlined in orange in the image below).

Copy HTTPs url for git

  1. Press the "Generate Git Credentials" button.

Press the 'Generate Git Credentials' button

  1. Copy the Username and Password to Notepad temporarily.

  2. Open GitHub Desktop and select clone reposistory (outlined in orange in the image below).

Clone the repository.

  1. Paste the URL copied in step 2.

Enter URL to clone repository.

  1. You will then be prompted to enter the username and password credentials you copied in Step 4.

Prompt to enter credentials to clone repository.

  1. Within GitHub Desktop switch the branch from main to 'origin/part5'. I ask you to do this because in subsequent blog series, I'll have separate branches that will introduce new features that follow DataOps priniciples.

Switch branch

  1. Within File Explorer (for Windows) navigate to the project folder that was cloned and within that folder navigate to Pbi->SampleModel->SampleModel.pbix and open the pbix file.

Example of File Explorer

  1. Navigate to the "Number of Characters" metric (outlined in orange in the image below) and remove "+ 0" from the measure (outlined in purple in the image below). Then save the changes. This demonstrates a change made to the Power BI file by a developer.

Example to Update Model

  1. Navigate back to GitHub Desktop and press "Commit to part5" (outlined in orange in the image below).

Example of Committing changes in GitHub Desktop

  1. Then select the "Push origin" button. This will push the changes to Azure DevOps and kick-off the pipeline.

Example of pushing changes to Azure DevOps repository

  1. Navigate back to Azure DevOps and you should see the pipeline in progress. This is typically donated by the a blue clock icon. Press the pipeline link (outlined in orange in the image below).

Example of Pipeline in Azure Devops

  1. This page will show you the latest status of the pipeline. The example image below shows the commit you pushed to Azure DevOps and that the pipeline is in progress.

Example of Pipeline in Progress

  1. Once the pipeline completes you should get a green checkmark icon. You may also receive an email stating the pipeline successfully completed.

Example of Pipeline Success

Failed Pipeline Example

If a test case fails in the pipeline you will see a red x icon appear in the Azure DevOps Pipeline.

Example of Pipeline Success

If you click on the failed entry (outlined in orange in the image below) you will be presented with a screen providing details of the failed tests.

Example of Pipeline Success

For example, in the image below you can see an example of the failed test.

Example of Pipeline Success