Modernizing Power BI Development: A Project with PBIP, TMDL, CI/CD, and More

For years, developing Power BI solutions often felt like a solitary endeavor, with limited options for version control and streamlined deployment. However, the introduction of the Power BI Project (.pbip) file format marks a significant shift, opening the door to modern development workflows. When combined with tools like Tabular Model Definition Language (TMDL), Tabular Model Scripting Language (TMSL), Continuous Integration/Continuous Deployment (CI/CD) pipelines in Azure DevOps, the pbi-sm PowerShell module, and Git for version control, we can create a powerful and collaborative development experience.

This article will guide you through setting up and managing a Power BI Desktop project using these technologies, culminating in an automated deployment process using PowerShell.

Laying the Foundation: The Power BI Project (PBIP)

The .pbip format is a container that holds all the elements of your Power BI Desktop project in a structured way. Unlike the monolithic .pbix file, a .pbip project breaks down into individual files, making it ideal for version control and collaborative development.

Key benefits of using .pbip:

◾Version Control Friendly: The decomposed structure allows Git to track changes at a granular level, enabling effective collaboration and history management.

◾Modularity: Separating metadata (TMDL), reports (JSON), and other assets enhances organization and maintainability.

◾Extensibility: The open format paves the way for future tooling and automation.

Getting Started with .pbip:

1. Enable the Preview Feature: In Power BI Desktop, navigate to File > Options and settings > Options > Preview features and enable "Power BI Project save format". Restart Power BI Desktop.

2. Save as PBIP: Open your existing .pbix file (or create a new one) and go to File > Save as. Choose "Power BI Project files (*.pbip)" as the save type.

This will create a folder containing your project files, including:

◾<Project Name>.SematicModel: While .pbip aims for TMDL, initially, the model might be stored in the traditional.bim format. Power BI Desktop will handle the serialization and deserialization.

◾<Project Name>.Report: Contains the visual layout and configuration in JSON format.

◾<Project Name>.pbip

◾.gitignore – Allows you to define exclusions.

Defining Your Data Model with TMDL

Tabular Model Definition Language (TMDL) is a declarative language for defining tabular models. It aims to replace the binary.bim format with a human-readable and version-control-friendly text-based representation.

Benefits of TMDL:

◾Human-Readable: Easier to understand and review model definitions.

◾Version Control Integration: Text-based format integrates seamlessly with Git for tracking changes to your data model.

◾Automation-Friendly: Simplifies programmatic manipulation and deployment of tabular models.

Working with TMDL:

Currently, direct editing of TMDL files within Power BI Desktop isn't fully implemented. However, Power BI Desktop will serialize the model into a .bim file within the .pbip structure. Future updates are expected to bring more direct TMDL support. Tools like Tabular Editor 3 offer excellent capabilities for viewing, editing, and scripting TMDL.

Scripting Model Changes with TMSL

Tabular Model Scripting Language (TMSL) is a JSON-based scripting language for managing and querying tabular models in Analysis Services, including the Power BI semantic model. While TMDL defines the structure of the model, TMSL is used to perform actions on it, such as refreshing data, processing objects, or deploying metadata.

Use Cases for TMSL in a .pbip Project:

◾Automating Refreshes: Incorporate TMSL scripts into your CI/CD pipeline to automatically refresh the semantic model after deployment.

◾Partition Management: Script the creation, modification, or deletion of partitions.

◾Role and Permission Management: Automate the assignment of roles and permissions.

You can execute TMSL scripts using tools like SQL Server Management Studio (SSMS) connected to your Power BI Premium capacity or through PowerShell using the Invoke-ASCmd cmdlet from the SQL Server module.

 

Version Control with Git

Git is the industry-standard distributed version control system. Integrating your .pbip project with Git is crucial for:

◾Collaboration: Multiple developers can work on the same project simultaneously without conflicts.

◾Tracking Changes: Every modification is recorded, allowing you to revert to previous versions if needed.

◾Branching and Merging: Enables the creation of isolated environments for developing new features or fixing bugs.

 

Setting up Git for your .pbip Project:

Initialize a Repository: Navigate to your .pbip project folder in your terminal or Git client and run git init.i. Open your project 

◾Set up a terminal

1. Git Terminal can be downloaded from Install and set up Git - Azure DevOps | Microsoft Learn

2. Install Visual Studio Code Development Environment – if you already have it proceed to next steps https://code.visualstudio.com/

◾Once done with installation, access VS Code and set up a repository

i. Open your project 

1. 1.  

1.  

 

2. Create a .gitignore File: Add a .gitignore file to exclude temporary files and sensitive information (like connection strings if not managed securely elsewhere). A typical .gitignore for a Power BI project might include entries like *.pbi\*, bin/, obj/.
3. Commit Your Changes: Stage your project files using git add . and commit them with a descriptive message using git commit -m "Initial commit of Power BI project"
4. Connect to a Remote Repository: Link your local repository to a remote repository on platforms like GitHub, GitLab, or Azure DevOps

 

Continuous Integration and Continuous Deployment (CI/CD) with Azure DevOps

Azure DevOps provides a suite of services for managing the software development lifecycle, including Azure Pipelines for CI/CD. Implementing a CI/CD pipeline for your Power BI project automates the build, test, and deployment processes.

CI/CD Workflow for a .pbip Project:

1. Trigger: The pipeline can be triggered by code commits to your Git repository.
2. Build Stage:

◾Validation: Potentially validate the project structure or run basic checks.

◾Artifact Creation: Package the necessary files from your .pbip project for deployment. This might involve zipping the contents of the .pbip folder.

1. Release Stage (CD):

◾Environment Configuration: Define different environments (e.g., Development, Test, Production).

◾Deployment Tasks: Utilize PowerShell scripts and the pbi-sm module (explained below) to deploy the Power BI artifacts to the appropriate Power BI service workspace. This could involve publishing the semantic model and reports.

◾Testing: Run automated tests against the deployed solution.

 

Empowering Deployment with pbi-sm PowerShell Module

The pbi-sm PowerShell module is specifically designed for managing Power BI semantic models (formerly known as datasets) and other Power BI Service artifacts programmatically. It provides cmdlets for various tasks, making it invaluable for automation.²³

Key pbi-sm Cmdlets for Deployment:

◾Connect-PowerBIServiceAccount: Connects to your Power BI Service tenant.

◾Import-PowerBIReport: Imports a .pbix file (or the relevant artifacts from your .pbip project) as a report and semantic model.

◾Publish-PowerBIDataflow: Publishes a dataflow.

◾Update-PowerBIReport: Updates an existing report.

◾Invoke-PowerBIRefresh: Triggers a refresh of a semantic model.

◾Get-PowerBIWorkspace, Get-PowerBIDataset, etc.: Cmdlets for retrieving information about your Power BI Service environment.

 

Automating Deployment with PowerShell and Azure DevOps

Combining the pbi-sm module with Azure Pipelines allows for fully automated deployment of your .pbip project.

Example PowerShell Deployment Script (Simplified):

PowerShell

param(

    [Parameter(Mandatory=$true)]

    [string]$PBIWorkspaceName,

    [Parameter(Mandatory=$true)]

    [string]$PBIPProjectPath

)

# Connect to Power BI Service

Connect-PowerBIServiceAccount

 

# Construct the full path to the .pbix file (assuming a simple conversion for this example)

$PBIXFilePath = Join-Path $PBIPProjectPath "model.bim" # Or potentially create a .pbix from the .pbip

 

# Get the target workspace

$Workspace = Get-PowerBIWorkspace -Name $PBIWorkspaceName

 

if (-not $Workspace) {

    Write-Error "Workspace '$PBIWorkspaceName' not found."

    exit 1

}

# Publish the PBIX (or the relevant artifacts)

Import-PowerBIReport -Path $PBIXFilePath -WorkspaceId $Workspace.Id -Name "YourReportName" -Overwrite

 

Write-Host "Power BI report and semantic model deployed to '$PBIWorkspaceName'."

 

# Optional: Trigger a refresh

# $Dataset = Get-PowerBIDataset -WorkspaceId $Workspace.Id -Name "YourReportName"

# if ($Dataset) {

#     Invoke-PowerBIRefresh -DatasetId $Dataset.Id

#     Write-Host "Semantic model refresh initiated."

# }

 

 

Integrating this script into an Azure DevOps Pipeline:

1. Create a new Pipeline in your Azure DevOps project.
2. Connect to your Git repository.
3. Define stages and jobs. In your Release pipeline, add a stage for your target environment.
4. Add a PowerShell task within the stage.
5. Configure the PowerShell task:

◾Set the Script Type to "Inline".

◾Paste your PowerShell deployment script into the Inline Script field.

◾Define variables for $PBIWorkspaceName and $PBIPProjectPath (you can configure these in the pipeline variables).

◾Ensure the Azure DevOps agent has the necessary permissions to connect to the Power BI Service (usually through a Service Principal).

 

Conclusion

Embracing the .pbip format and integrating it with TMDL (as it matures), TMSL, Git, Azure DevOps, and the pbi-sm PowerShell module represents a significant step forward in modernizing Power BI development. This approach fosters collaboration, enhances version control, and enables robust automation of your deployment processes. By adopting these practices, you can build more reliable, maintainable, and scalable Power BI solutions, ultimately empowering your organization with timely and accurate insights. As the Power BI ecosystem continues to evolve, staying ahead of these modern development paradigms will be key to maximizing your team's efficiency and the quality of your BI deliverables.