Microsoft Fabric: Capacity Options and Cost Management, Part 1; The Basics

Microsoft Fabric: Capacity Options and Cost Management, Part 1

Microsoft Fabric is a SaaS platform that allows users to get, create, share, and visualise data using a wide set of tools. It provides a unified solution for all our data and analytics workloads, from data ingestion and transformation to data engineering, data science, data warehouse, real-time analytics, and data visualisation. In a previous blog post, I explained the basics of the Microsoft Fabric data platform. In a separate blog post, I explained some Microsoft Fabric terminologies and personas where I explained what Tenant and Capacities are.

In this blog post, we will explore the different types of Fabric capacities, how they affect the performance and cost of our Fabric projects, and how you can control the capacity costs by pausing the capacity in Azure when it is not in use.

Fabric capacity types

Fabric capacities are the compute resources that power all the experiences in Fabric. They are available in different sizes and prices, depending on our needs and budget. We can currently obtain Fabric capacities in one of the following options:

If we want to purchase Microsoft Fabric capacities on Azure, they come in SKUs (Stock Keeping Units) sized from F2 – F2048, representing 2 – 2048 CU (Capacity Units). A CU is a unit of measure representing the resource power available for a Fabric capacity. The higher the CU, the more resources we get on our Fabric projects. For example, an F8 capacity has 8 CUs, which means it is four times more powerful than an F2 capacity, which has 2 CUs.

When purchasing Azure SKUs with a pay-as-you-go subscription, we are billed for compute power (which is the size of the capacity we choose) and for OneLake storage, which is charged for the data stored in OneLake per gigabyte per month (approximately $0.043 (New Zealand Dollar) per GB). OneLake is the unified storage layer for all the Fabric workloads. It allows users to store and access our data in a secure, scalable and cost-effective way.

Azure Fabric capacities are priced uniquely across regions. The pay-as-you-go pricing for a Fabric capacity at Australia East region is $0.3605 (NZD) per CU per hour, which translates to a monthly price of $526.217 (NZD) for an F2 ($0.3605 * 2 * 730 hours).

Microsoft Fabric pricing overview
Microsoft Fabric pricing overview

It is important to note that billing is per second with a one-minute minimum. Therefore, we will be billed for when the capacity is not in use. Here is a full list of prices available at the Azure portal by selecting our Fabric capacity region.

Now that we have an indication of the costs of owning Microsoft Fabric capacities let’s explore the methods to control the cost.

Nuances of Fabric’s Cost of Ownership

It is important to note that all the math we have gone through in the previous section is just about the capacity itself. But are there any other costs that may apply? The answer is it depends. If we obtain any SKUs lower than F64, we must buy Power BI Pro licenses per user on top of the capacity costs. For the tiers above F64, we get unlimited free users but, BUT, we still have to purchase Power BI Pro licenses for all developers on top of the cost of the capacity itself.

Another gotcha is that the Fabric experiences are unavailable to either Power BI Premium (PPU) users or the Power BI Embedded capacities. Just be mindful of that.

The good news for organisations owning Power BI Premium capacities is that you do not need to do anything to leverage Fabric capabilities. As a matter of fact, you already own a Fabric capacity, you just need to enable it on your tenant.

Continue reading “Microsoft Fabric: Capacity Options and Cost Management, Part 1; The Basics”

Integrating Power BI with Azure DevOps (Git), part 2: Local Machine Integration

Integrating Power BI with Azure DevOps (Git), part 2: Local Machine Integration

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:

Continue reading “Integrating Power BI with Azure DevOps (Git), part 2: Local Machine Integration”

Datatype Conversion in Power Query Affects Data Modeling in Power BI

Datatype Conversion in Power Query Affects Data Modeling in Power BI

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.
  • Poor performing data model.
  • Bloated model size.
  • Difficulties in configuring user-defined aggregations (agg awareness).
  • Difficulties in setting up incremental data refresh.
  • 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
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.

Continue reading “Datatype Conversion in Power Query Affects Data Modeling in Power BI”

Endorsement in Power BI, Part 2, How to Endorse?

Endorsement in Power BI, Part 2, How to Endorse?

In the previous post I explained the basic concepts around endorsement in Power BI. We discussed that users’ ability to collaborate in creating and sharing artifacts is one of the key aspects of users’ experience in Power BI. But it would be hard, if not impossible, to identify the quality of the artifact without a mechanism to identify the artifact’s quality in large organisations. Endorsement is the answer to this challenge. We discussed the following in the previous post:

In this post, I explain the following:

How do Power BI administrators enable certification and grant rights to security groups?

In the previous post, we discussed that a Power BI administrator must enable certification and grant sufficient rights to the security groups. Therefore, all members of the specified security group are authorised to certify the artifacts. If you are a Power BI administrator, follow these steps to do so:

  1. After logging into Power BI Service, click the Settings button
  2. Click Admin Portal
  3. From the Tenant settings, scroll down to find the Export and sharing settings
  4. Find and expand the Certification setting
  5. Enable certification
  6. Put the certification process documentation URL (if any)
  7. It is not recommended to enable this feature for the entire organisation. So, select the Specific security groups option
  8. Type the security group name and select it from the list
  9. Click the Apply button

The following image shows the above steps:

Enabling certification from the Admin Portal in Power BI Service
Enabling certification from the Admin Portal in Power BI Service

It may take up to 15 minutes for the changes to go through. After that, all the members of the specified security can certify the artifacts. In the next section, we see how to certify the supported artifacts.

Note

Everyone who has “write” permission on the Workspace containing the artifact can promote it. Therefore, the users or security groups with one of the AdminMember, or Contributor roles in the Workspace can promote the artifacts.

However, one should not promote the artifacts just because he/she can. The organisations usually have a promotion process to follow, but the boundaries around promoting are often much more relaxed than certifying it.

Continue reading “Endorsement in Power BI, Part 2, How to Endorse?”