This is the second part of the series of blog posts showing how to integrate Power BI with Azure DevOps, a cloud platform for software development. The previous post gave a brief history of source control systems, which help developers manage code changes. It also explained what Git is, a fast and flexible distributed source control system, and why it is useful. It introduced the initial configurations required in Azure DevOps and explained how to integrate Power BI (Fabric) Service with Azure DevOps.
This blog post explains how to synchronise an Azure DevOps repository with your local machine to integrate your Power BI Projects with Azure DevOps. Before we start, we need to know what a Power BI Project is and how we can create it.
What is Power BI Project (Developer Mode)
Power BI Project (*.PBIP) is a new file format for Power BI Desktop that was announced in May 2023 and made available for public preview in June 2023. It allows us to save our work as a project, which consists of a folder structure containing individual text files that define the report and dataset artefacts. This enables us to use source control systems, such as Git, to track changes, compare revisions, resolve conflicts, and review changes. It also enables us to use text editors, such as Visual Studio Code, to edit the artefact definitions more productively and programmatically. Additionally, it supports CI/CD (continuous integration and continuous delivery), where we submit changes to a series of quality gates before applying them to the production system.
PBIP files differ from the regular Power BI Desktop files (PBIX), which store the report and dataset artefacts as a single binary file. This made integrating with source control systems, text editors, and CI/CD systems difficult. PBIP aims to overcome these limitations and provide a more developer-friendly experience for Power BI Desktop users.
Since this feature is still in public preview when writing this blog post, we have to enable it from the Power BI Desktop Options and Settings.
Enable Power BI Project (Developer Mode) (Currently in Preview)
As mentioned, we first need to enable the Power BI Project (Developer Mode) feature, introduced for public preview in the June 2023 release of Power BI Desktop. Power BI Project files allow us to save our Power BI files as *.PBIP files deconstruct the legacy Power BI report files (*.PBIX) into well-organised folders and files. With this feature, we can:
Edit individual components of our Power BI file, such as data sources, queries, data model, visuals, etc.
Use any text editor or IDE to edit our Power BI file
Compare and merge changes
Collaborate with other developers on the same Power BI file
To enable Power BI Project (Developer Mode), follow these steps in Power BI Desktop:
Power BI is a powerful tool for creating and sharing interactive data visualizations. But how can you collaborate with other developers on your Power BI projects and ensure quality and consistency across your reports? In this series of blog posts, I will show you how to integrate Power BI with Azure DevOps, a cloud-based software development and delivery platform. We can integrate Azure DevOps with Power BI Service (Fabric) as well as Power BI Desktop. The current post explains how to set up Azure DevOps and connect a Power BI Workspace. The next blog post will explain how to use it on your local machine to integrate your Power BI Desktop projects with Azure DevOps.
A brief history of source control systems
Before we dive into the details of Power BI and Azure DevOps integration, let’s take a moment to understand what source control systems are and why they are essential for any software project.
Source control systems, also known as version control systems or revision control systems, are tools that help developers manage the changes made to their code over time. They allow developers to track, compare, and roll back changes when necessary and collaborate with other developers on the same project.
There are two main types of source control systems: centralised and distributed. Centralised source control systems use Client-server approach to store all the code and its history in a single server, and developers need to connect to that server to access or modify the code. Examples of centralised source control systems are Microsoft’s Team Foundation Server (TFS) which rebranded to Azure DevOps Server in 2018, IBM’s ClearCase, and Apache’s Subversion.
On the other hand, distributed source control systems use a peer-to-peer approach, allowing each developer to have a local copy of the entire code repository, including its history. Developers can work offline and sync their changes with other developers through a remote server. Examples of distributed source control systems are Git Software and Mercurial, which takes us to the next section. Let’s see what Git is.
What is Git, and why use it?
Git is one of the world’s most popular and widely used distributed source control systems. It was created by Linus Torvalds, the creator of Linux, in 2005. Git has many advantages over centralised source control systems, such as:
Speed: Git is fast and efficient, performing most operations locally without network access.
Scalability: Git can easily handle large and complex projects, as it does not depend on a single server.
Flexibility: Git supports various workflows and branching strategies, allowing developers to choose how they want to organise their code and collaborate with others.
Security: Git uses cryptographic hashes to ensure the integrity and authenticity of the code.
Open-source: Git is free and open-source, meaning anyone can use it, modify it, or contribute to it.
While Git is pretty good, it has some disadvantages compared with a centralised source control system. Here are some:
Complexity: Git has a steep learning curve, especially for users who are new to distributed version control systems. Understanding concepts such as branching, merging, rebasing, and resolving conflicts can be challenging for beginners and sometimes even seasoned Git users.
Collaboration challenges: While distributed version control systems like Git enable easy collaboration, they can also lead to collaboration issues. Multiple developers working on the same branch simultaneously may encounter conflicts that need to be resolved, which can introduce complexities and require extra effort.
Performance with large repositories: While Git performs pretty well on most operations, it can get abortive when working with large repositories containing many files or a long history of commits. Operations such as cloning or checking out large repositories can be time-consuming.
What is Azure DevOps, and what does it relate to Git?
Azure Boards: A tool for planning, tracking, and managing work items, such as user stories, tasks, bugs, etc.
Azure Repos: A tool for hosting Git repositories online, which is the main focus of this blog post.
Azure Pipelines: A tool for automating builds, tests, and deployments.
Azure Test Plans: A tool for creating and running manual and automated tests.
Azure Artifacts: A tool for managing packages and dependencies.
Azure DevOps also integrates with other tools and platforms, such as GitHub, Visual Studio Code, and now, Power BI. This takes us to the next section of this blog post, Integrating Power BI with Azure DevOps.
How to integrate Power BI with Azure DevOps
Now that we understand what Git and Azure DevOps are let’s see how we can integrate Power BI with Azure DevOps.
Integrating Power BI with Azure DevOps has two different integrations. Cloud integration and local machine integration have the following requirements.
Prerequisites
To follow along with this tutorial, you will need:
In the cloud:
An Azure DevOps Service
A Power BI account with one of the following licenses to enable Power BI Workspace integration with Azure DevOps.:
Power BI PPU (Premium Per User)
Premium Capacity
Embedded Capacity (EM/A)
Fabric Capacity
On your local machine:
The latest version of Power BI Desktop (June 2023 or later)
Either Visual Studio or VS Code
As stated earlier, this post explains the Cloud integration partTherefore, we require to have an Azure DevOps Service and a Power BI account with a Premium licencing plan in order to integrate Power BI with Azure DevOps.
In the following few sections, we look into more details and go through them together step-by-step.
Row-level security (RLS) is a feature of Power BI that allows you to restrict data access for different users based on filters you define within roles. For example, you can create a role for Sales Managers and apply a filter that only shows them the sales data for their region. This way, you can ensure that each user only sees the data that is relevant and appropriate for them.
However, managing RLS roles can be challenging if you have a large number of users or if your user base changes frequently. You need to manually assign each user account to one or more roles, which can be time-consuming and error-prone. Moreover, if a user changes their position or leaves the organisation, you must update their role membership accordingly.
This is where Security Groups become handy. Security groups are collections of user accounts that share common characteristics or permissions. You can create security groups on your Azure Active Directory (AAD) or Microsoft 365 Admin Centre and add users based on their roles or responsibilities. For instance, you can create a security group for each sales region and add all the sales managers who belong to that region.
By using security groups in Power BI RLS role mapping, you can simplify and, somehow, automate the process of RLS management. Instead of adding individual user accounts to roles, you can add security groups as members of roles. This way, you only need to maintain the membership of security groups once, and Power BI will automatically apply the RLS filters to all the users within those groups.
Using security groups in role mapping has several benefits. The following are the top four:
In my consulting experience working with customers using Power BI, many challenges that Power BI developers face are due to negligence to data types. Here are some common challenges that are the direct or indirect results of inappropriate data types and data type conversion:
Getting incorrect results while all calculations in your data model are correct.
Getting blank visuals after the first data refresh in Power BI service.
In this blogpost, I explain the common pitfalls to prevent future challenges that can be time-consuming to identify and fix.
Background
Before we dive into the topic of this blog post, I would like to start with a bit of background. We all know that Power BI is not only a reporting tool. It is indeed a data platform supporting various aspects of business intelligence, data engineering, and data science. There are two languages we must learn to be able to work with Power BI: Power Query (M) and DAX. The purpose of the two languages is quite different. We use Power Query for data transformation and data preparation, while DAX is used for data analysis in the Tabular data model. Here is the point, the two languages in Power BI have different data types.
The most common Power BI development scenarios start with connecting to the data source(s). Power BI supports hundreds of data sources. Most data source connections happen in Power Query (the data preparation layer in a Power BI solution) unless we connect live to a semantic layer such as an SSAS instance or a Power BI dataset. Many supported data sources have their own data types, and some don’t. For instance, SQL Server has its own data types, but CSV doesn’t. When the data source has data types, the mashup engine tries to identify data types to the closest data type available in Power Query. Even though the source system has data types, the data types might not be compatible with Power Query data types. For the data sources that do not support data types, the matchup engine tries to detect the data types based on the sample data loaded into the data preview pane in the Power Query Editor window. But, there is no guarantee that the detected data types are correct. So, it is best practice to validate the detected data types anyway.
Power BI uses the Tabular model data types when it loads the data into the data model. The data types in the data model may or may not be compatible with the data types defined in Power Query. For instance, Power Query has a Binary data type, but the Tabular model does not.
The following table shows Power Query’s datatypes, their representations in the Power Query Editor’s UI, their mapping data types in the data model (DAX), and the internal data types in the xVelocity (Tabular model) engine:
Power Query and DAX (data model) data type mapping
As the above table shows, in Power Query’s UI, Whole Number, Decimal, Fixed Decimal and Percentage are all in type number in the Power Query engine. The type names in the Power BI UI also differ from their equivalents in the xVelocity engine. Let us dig deeper.
