Deep Dive: Microsoft Power BI In-Depth Guide

What Is Power BI? 

Power BI is Microsoft’s cloud service and software for business intelligence (BI). Like other BI systems, it helps business professionals analyze information from production systems and discover actionable “insights.” With Power BI, users can collect and organize source data, build analytic models, and create and share interactive reports, charts, dashboards, and other content for presentation. For example, a manufacturing firm might use Power BI to pull data from its enterprise resource planning, customer relationship management, and production floor monitoring systems to build dashboards that track key performance metrics for each business unit and job role.

Power BI is available in several configurations (in the cloud and on-premises) and has numerous components and features that customers use to build a full end-to-end BI environment. (In early 2024 Microsoft announced the retirement of Power BI Premium P by early 2025. Going forward large customers will need to deploy high-end Power BI environments using Fabric licenses.)

Power BI Competitors 

Power BI can be used to replace most existing on-premises BI deployments, including ones that companies have built with Excel and SQL Server’s Analysis Services, Integration Services, and Reporting Services. Major competitors to Power BI outside of Microsoft include Alteryx, IBM Cognos, SAP Business Objects, SAS Visual Analytics, Sisense, Tableau, and Tibco.

Role at Microsoft

Power BI is also used as a reporting tool in several Azure and Microsoft 365 services. In 2023, Microsoft announced plans to make Power BI a cornerstone of its Microsoft Fabric data analytics platform, where it is more deeply integrated with a number of Microsoft’s key data integration and analytic tools. Although Power BI is one of the key components in Fabric, it is also the least changed, which means Power BI customers will see minimal changes to their existing Power BI environments, but will see additional integration with other data sources that are in Fabric’s OneLake data solution. The most notable impact will be the retirement of Power BI Premium P purchasing options in Jan. 2025. Microsoft is pushing Power BI customers to Fabric by making them relicense and redeploy Power BI capacities inside Fabric.

Microsoft announced the public preview of its Copilot AI assistant technology for Microsoft Fabric, including the general availability of Copilot in Power BI. The goal of Copilot in Power BI is to allow customers to use natural language to create reports, analyze data, and generate text summaries with less effort. The preview of Copilot in the other Fabric components is rolling out in stages and will help developers build pipelines, write Spark scripts, and query various databases.

Key Benefits of Power BI 

Consolidating BI 

Power BI enables organizations to consolidate collecting, storing, analyzing, and presenting data in a single system. Data can come from an enormous variety of Microsoft and non-Microsoft data sources, and customers have a range of cloud and on-premises options for hosting reports and data. Users can access reports through numerous client applications, including Web browsers, Teams, and mobile applications, and in custom and third-party applications that embed Power BI reports. Users and developers can design reports in Power BI’s native format or in legacy report formats from Excel and SQL Server Reporting Services.

This all-in-one-place model has advantages for management, security, performance, and reliability. Consolidating data, analysis, and presentations in a single system reduces the complexity of infrastructure that IT personnel must manage and reduces latency and network-caused outages. It also helps IT personnel to consistently control access and enforce policy.

Cloud Hosting 

Customers can host Power BI on-premises, but most customers use the Microsoft-hosted cloud service. This choice offers all the usual advantages of Microsoft cloud hosting: reduced effort to maintain infrastructure, faster scaling of storage and compute resources, an extensive global network of datacenters, and better security and availability infrastructure and people than most companies can afford themselves.

Design 

For designing analytic data models, Power BI offers the same semantic data modeling tools available in Excel’s Power Query and Power Pivot components. For creating reports, Power BI provides three key design options: native Power BI format (PBIX) reports, Excel workbooks, and SQL Server reports built with the Report Definition Language (RDLs). PBIX reports deliver interactive, animated, multipage reports using hundreds of visual formats like bar charts, waterfall charts, timelines, and word clouds. Developers can also create their own visual concepts to extend Power BI further. Although Microsoft focuses on the PBIX report format, it has not abandoned its legacy: Power BI can also host Excel workbooks with some limitations and reports created in RDL of SQL Server, enabling organizations to consolidate existing reports with new ones in Power BI and preserve their investment.

Developers normally collect and organize data and design reports in a free Windows desktop application, Power BI Desktop. However, the browser client of Power BI’s cloud service continues to add design features and could eventually take over the role of Power BI Desktop, which would simplify client management and enable Power BI design on Macs and other non-Windows clients.

Application integration 

More and more Microsoft apps and services can work with Power BI for reporting and analysis, including Dynamics 365, Project Online, and most Azure services. Excel has a special relationship with Power BI in that it can be used to access Power BI–hosted data, allowing data analysts to continue using Excel. This integration helps ensure that investment in Power BI skills and infrastructure is paid off in multiple Microsoft apps and services.

Evolution and Roadmap 

Over the years, Microsoft has steadily increased the maximum size and computing power available to Power BI deployments and improved security, management, and licensing to serve larger organizations. More recently it has been integrating data storage and data integration into Power BI. This development enables organizations to eliminate separate systems to extract, transform, and load source data for analysis directly into the service. In some cases, customers can eliminate other data marts as well, consolidating all BI and support functions in Power BI.

Microsoft is also investing in the Power BI portal (a browser application) so that users can fully edit and update reports in the portal without requiring Power BI Desktop. The company continues to surface Power BI in more apps and services. There are plans for Power BI in Outlook Online and Office Hub and more capabilities in the Power BI app for Teams and Excel Online. Microsoft also has made it a priority to integrate Power BI with OneDrive for Business and SharePoint Online. (These integrations are all with the browser-based online apps and probably won’t come to the desktop applications..)

How It Works: Power BI Components

The Power BI platform consists of several components that work together. These include the following:

Back-end Hosting Platforms

The back-end platforms enable storing and sharing of organized data models, processing data integration pipelines, pulling data from various sources, processing the data to produce (render) reports, publishing and distribution of reports and dashboards, and managing user access. Microsoft offers customers several tiers of Power BI cloud services and one on-premises Power BI Report Server. They vary somewhat in client tools, features, performance, scaling, and security.

Choosing the correct back-end platforms is the single most important decision a customer will make in Power BI adoption or major expansions. The back-end platforms are key to meeting business requirements and are the primary determinant of licensing costs.

Data Management Tools

Power BI data management features are used in combination with other Power BI components as a full-service reporting solution, in some cases replacing existing data integration and reporting products. Source data is loaded into Power BI datasets (also called data models), which are created using Power BI Desktop, Power BI portal, and SQL Server Data Tools.

Most data can reside within Power BI storage, but larger datasets can be offloaded to Fabric OneLake or other Azure services like Azure SQL Database.

Data Integration is provided via Power BI Dataflows, a built-in extract, transform, and load (ETL) component that allows developers to create refreshable data pipelines to bring data into Power BI. Microsoft built this dataflows feature using Power Query, so it’s familiar to users of Power BI Desktop and Excel.

Data Connections

Power BI also provides connectors, or gateways, for querying data used in datasets, dataflows, and reports that are loaded from source systems such as financial software and production logs. The data can be stored in the Power BI service or remain in the external source and be queried as needed. There are connectors for Azure data services, like OneLake and Azure SQL Database, and data gateways that connect to on-premises data sources.

Report Design Tools

Power BI content is created and shared using a variety of tools. Content can include datasets, data integration pipelines, reports, dashboards, metrics, Excel files, and traditional SQL Server Report Definition Language (RDL) tabular reports. Content creators, who may be business analysts or software developers, typically use Power BI Desktop, SQL Server Report Builder, or Excel, depending on the task, to design the content. Increasingly, content creators use the Power BI portal browser interface. Software developers can also use SQL Server Data Tools in Visual Studio for RDL reports.

Content creators can also create Power BI apps that bundle certain content together for specific groups of users. For example, a Power BI app for the financial forecast team can contain (point to) just the relevant reports from the hundreds of financial, sales, and production reports that an organization may have, helping users find the reports they need.

Power BI Desktop

Power BI Desktop is the primary tool for building Power BI (PBIX) reports. It is a free Windows desktop application that contains data modeling capabilities and a report design canvas. Authors create reports (saved locally as .pbix files) using various visuals like bar charts, waterfall charts, timelines, and word clouds.

The product includes several visual types, but authors can purchase third-party solutions or build their own visuals to extend the service. Authors use other Desktop features to allow users to filter and slice data for performing data analysis and to guide users through pages of data. The tool also provides links to other Microsoft-hosted services, like Azure Machine Learning and Power Apps, that authors can use to perform advanced and custom data processes. Authors typically use Power BI Desktop to publish reports directly to a Power BI back-end platform.

Power BI Portal

The portal provides a growing subset of Power BI Desktop’s dataset and report-building features for creating PBIX reports. The portal lacks some of the canvas design tools and the advanced data query and data modeling features found in Power BI Desktop, which limits the ability to query and transform data from multiple sources. Consequently, the portal’s design features are often used to create basic reports in a Web interface without using the desktop tool, although Microsoft is investing heavily to build more functionality into the portal. A more robust portal application could remove the need to use Power BI Desktop, a benefit for MacOS and Linux users.

Excel

Microsoft’s Excel spreadsheet is used to create Excel workbooks. It is a familiar tool to most data analysts and has been used for many years as a reporting tool to build workbooks with tables, charts, and data models. Most Power BI back-end platforms can host Excel workbooks (.xlsx files) from current versions of Excel.

Power BI Report Builder

Report Builder is a free Windows desktop client used to create paginated reports based on SQL Server Reporting Services Report Definition Language (RDL). RDLs are traditional data-driven business and operational reports, such as monthly cost reports and annual financial statements, that were the dominant reporting option at Microsoft for years until the introduction of Power BI. The tool is based on SQL Server Report Builder and is updated frequently (sometimes monthly) to stay in sync with Power BI services and to add features that are supported only when the RDL is used in a Power BI cloud service.

SQL Server Data Tools

This is a toolset in Visual Studio where developers create databases, data models, and RDL reports. RDLs created with SQL Server Data Tools can be manually copied to a hosted Power BI service, providing a migration path to the hosted services.

Clients

Content consumers, who just view and interact with Power BI content, can use a variety of clients, including the Power BI portal, mobile applications, a Teams-based app, Microsoft 365 services, embedded Web sites, and custom applications. The clients provide interactive features like filtering and drill down. The portal enables basic report creation and provides additional organization, collaboration, and analytic features.

What is the Cost of Power BI?

Power BI licensing costs are determined by the back-end platforms that the customer chooses to host data and reports and the number of user licenses required, which depends on the back-end platform.

Back-End Platforms

The back-end platforms include four tiers of cloud services and one server software product for deployment in customer or third-party data centers.

Power BI Free is mainly for individual users and is generally unsuitable for business use because of its lack of security controls and collaboration, limited storage capacity, and other significant shortcomings.

Power BI Pro offers most Power BI features and is designed for organizations that do not need high-end security or global distribution and generally have relatively few users, typically fewer than 500. It is also used by an organization that licenses users with Microsoft 365 E5, which includes a Power BI Pro User Subscription License (SL).

Fabric licensing is replacing the existing Power BI Premium P, which retires on Jan. 1, 2025. Fabric licenses Power BI usage differently, depending on the Fabric performance level:

• The lower Fabric SKUs (F32 and below) do not include Power BI rights. However, users can still work in a Fabric workspace using Power BI Pro, which requires separate Power BI Pro licensing.
• The higher Fabric SKUs (F64 and above) include a traditional Power BI Premium P deployment with all the advanced geo-distribution, security, and large data models that were in a Premium P SKU. It also includes licensing for read-only (consumer) users with only content creators requiring separate Power BI Pro user licenses.

Power BI Premium Per-User is a user-based service that provides most of the features of the high-end solution not available in Power BI Pro. The platform is typically for deployments with fewer than 250 users, like a regional office or development team. Above this amount, higher-end Fabric subscriptions typically find a lower per-user cost.

Power BI Premium EM Azure Power BI Embedded is designed for organizations that want to share Power BI reports with internal users through their own custom applications and Web sites. It includes most high-end features of the previous Premium P environment but does not provide access to the Power BI portal or mobile apps. The service is licensed by capacity with tiers ranging from EM1 to EM3 that differ by the number of virtual cores.

Azure Power BI Embedded provides the same features as Premium EM but is designed for organizations that want to share Power BI reports with external users through their own custom applications and Web sites. Sharing externally requires the customer to provide their own user authentication solution. The service is licensed by capacity with tiers ranging from A1 to A8 that differ by the number of virtual cores.

Power BI Report Server is a special edition of SQL Server Reporting Services that can host Power BI (.pbix) reports. It can be licensed with SQL Server Enterprise edition with active Software Assurance or through dual-use rights provided by high-end Fabric SKUs (F64 and higher) purchased through capacity reservations. The dual-use rights allow customers to have a Power BI cloud deployment and deploy Power BI Report Server on-premises using the same number of equivalent cores included in the Fabric capacity concurrently.

Purchasing Options

The Power BI branded back-end platforms are purchased primarily in monthly increments with a discount for larger customers willing to make annual commitments. However, the Fabric subscription can be purchased either through capacity reservations that mimic the older Premium P subscriptions in price or through pay-as-you-go licensing that has a higher rate, but is more flexible and does not have an annual commitment.

Platforms and License Models

Power BI provides several Per-User and capacity-based licensing models. The precise licensing requirements depend on the back-end platforms being used and—in some cases—on the features the organization requires.

Per-User Only

Power BI Pro and Power BI Premium Per User platforms require a User Subscription License (User SL) for every user accessing content hosted on them. These platforms will make financial sense for smaller deployments: up to 500 users for Pro and 275 users for Power BI Premium Per User, because beyond those levels the high-end Fabric options offer a lower cost per user for Power BI usage.

Capacity and Per-User

Fabric and Power BI Report Server use a mix of Per-User and capacity licensing. Organizations using these platforms pay for a specific capacity level (much like virtual processor cores). Fabric offers two purchasing methods, pay-as-you-go (PAYG) and Reserved Capacity. PAYG has hirer prices than Reserved Capacity but may be a lower cost choice for development teams and groups that can pause the capacity overnight when not in use.

User SLs are always required for content creators – report authors, data specialists, and administrators who build and share content through a Power BI back-end platform. User SLs may not be required for content consumers (usually the largest group of users), who view and interact with reports or create reports for their own use, although it depends on the deployment size. In general, consumers require a Power BI User SL, except in cases where the content is shared from a high-end Fabric capacity (denoted as an F64 or higher).

User License Compliance Notes

Power BI cloud services enforce User SL requirements, simplifying license compliance for customers.

For example, if a Power BI report is shared from a Pro or Premium Per User workspace, then the content consumer needs a Pro or Premium Per User subscription because those platforms require User SLs. If the content is shared from a high-end Fabric platform (F64 and higher), then the content consumer does not need a User SL.

However, Power BI licensing rules also follow where the data is hosted, which can complicate matters further. For example, if a Power BI report is shared from a high-end Fabric platform, but the data the report reads is on a Power BI Pro platform (a common occurrence), then the content consumer needs a User SL even though the report is shared from a high-end Fabric deployment.

Bundled Licenses

Users get rights to access Power BI Pro with a Microsoft 365 Enterprise E5 User SL. These rights can be valuable if a company has bought E5 User SLs for security, compliance, or other reasons and intends to deploy Power BI Pro. However, if a company buys Microsoft 365 E5 enterprise wide and then deploys Power BI through Fabric with capacity-based licensing, it will likely end up with many unused Power BI Pro User SLs because only content creators, not content consumers, require Power BI User SLs for content hosted in the high-end Fabric deployments.

Top Tips and Tricks for Power BI 

1. Back-end first

Choosing the correct combination of back-end platforms is key to meeting business requirements and controlling costs. The back-end platforms dictate features, capacity, performance, scalability, security, and licensing. Before committing to Power BI or greenlighting a major expansion, customers should review the options for back-end platforms, particularly in the following areas:

• Capacities and performance (high-end requirements typically lead to more cost)
• Advanced features (like tabular data models and geo-distribution)
• Data hosting (more storage is available with high-end Fabric deployments)
• Licensing

2. Consider data integration and storage, not just reporting.

Power BI started life as a reporting system, and that is still its most important role. But it has steadily added storage and data integration options. Each high-end Fabric instance (which includes a Power BI Premium component) receives 100TB of storage and can process data models up to 400GB, which means customers can host some decent-sized data in Power BI. Additionally, the data can be accessed by outside tools, like Tableau, so it’s not walled off from other applications. Customers can put that data in Azure SQL Database or somewhere else, but with high-end Power BI deployments the storage cost is already included. Additionally, hosting and querying data do not require a separate SQL Server license. Customers may be able to retire legacy data marts and supporting data integration systems on-premises by moving them to Power BI, improving security, performance, and cost.

3. Buying PAYG Fabric F SKUs can be more cost effective.

Choosing PAYG Fabric F SKUs can be more cost effective than capacity reservations. Fabric, unlike previous Power BI options, is available through two purchasing methods: pay-as-you-go (PAYG) and capacity reservations. Generally, consistent long-running deployments should be purchased with a capacity reservation, which provides a lower-cost in exchange for a one- or three-year commitment. However, PAYG might be a better purchasing option for deployments that can be scaled down or paused on a regular basis. For situations like dev/test or single location deployments, where a capacity runs for less than 14 hours per day (and can be paused), it will be less expensive to purchase through Fabric PAYG options, at any performance level.

4. Use dual-use rights to lower on-premises costs.

There is a licensing benefit that will most likely appeal to organizations planning a hybrid deployment, with some reports hosted in high-end Fabric SKUs (F64 and higher) and other reports hosted on-premises, using Power BI Report Server. Power BI Report Server can be licensed using SQL Server Enterprise edition core licenses with active Software Assurance or with dual-use rights provided by a high-end Fabric subscriptions purchased via capacity reservations. These subscriptions provide on-premises rights that allow customers to deploy Power BI Report Server on local hardware with the same number of cores included in the Premium P subscription, which may help reduce on-premises licensing costs.

5. Don’t overestimate Microsoft 365’s bundled Power BI.

Microsoft 365 Enterprise E5 delivers rights to Power BI Pro that can be valuable for smaller firms. But larger ones will often opt for high-end Fabric deployments that leave those rights on the shelf. Large organizations that have bought Microsoft 365 E5 for security and compliance reasons should use the included Power BI Pro User SL rights they get where the rights are useful, but Power BI Pro rights should not be a factor in deciding to buy Microsoft 365 in the first place.