So, you are looking to build some home-grown tooling for FinOps (or anything else) and have been eyeing Power BI or Fabric. However, you are wondering which exact product to choose, as the features, licenses, and pricing are a bit complex. You have come to the right place.

I will help you with a walkthrough of the entire offering, starting from vanilla Excel, stepping through the different options for Power BI, and finishing with Fabric.

These steps will include:

  • Vanilla Excel
  • Excel with Power Query and Power Pivot
  • Power BI Desktop
  • Power BI Pro
  • Power BI Premium
  • Fabric Capacity

In addition, I will cover some special flavors:

  • Power BI Embedded
  • Power BI Report Server (local service deployment)

To boost your understanding, I will also put things in context: introducing the product history of Power BI and Fabric will help you understand today’s offerings from an evolutionary standpoint. But hey, if you feel that’s boring and you want just the juice, skip the history. No significant risk is involved. Therefore, I have split this writing into two parts:

  • Part 1 is the previous post, and it covers the product history,
  • Part 2 is this post, and it examines the product offerings step by step.

Let’s go!

Part 2 - The Juice - Examining the Options for Building FinOps (or Any) Home-grown Tooling from Excel, through Power BI, to Fabric

Level 0 - Vanilla Excel

Ok, you have exported some cloud bills (or other data) in a human-readable format like csv. This is often underrated and accompanied by a cynical smile. But hey, dinosaurs have lived for about 200 million years, and it took a teraton grade asteroid to take them out. I bet csv will stick around until the next asteroid and keep delivering unmatched simplicity and portability, but forget about performance or any other fancy stuff.

You can do a lot with Excel, but your major limit will be the number of rows in a sheet which has a hard stop at roughly one million rows. Cloud bills can quickly grow beyond this limit and even if they don’t, the processing power (CPU, memory) of your machine will still limit capabilities. By experience, it works well with tens of thousands of rows, but performance and reliability will suffer at hundreds of thousands.

Excel is still very valuable for some use cases in FinOps. First, if you are just starting out and have a small bill or just a day’s worth of data, it is perfect to examine it and do the first analyses by dropping pivot tables and charts on top of it. Second, for ad-hoc analyses and bill debugging with a subset of your more extensive data. Third, it can work well with aggregated data, downloaded from your favorite built-in cloud cost management tool or using Power Query on top of a larger dataset. Using these ‘Power tools’, will take you to the next level.

Level 1 - Excel with Power Query and Power Pivot

One step up, you can get around some of Excel’s limitations by connecting to an external data source via Power Query. This allows you to connect to 100s of types of data sources to consume your cloud bill, wherever you please to store or query them. Common options for cloud bills, like Azure Data Lake, Azure Synapse, Amazon Athena, Google BigQuery are all included, along with popular relational databases like MySQL, MariaDB, PostgreSQL, SQL Server, RedShift, etc.

With PowerQuery you can fetch data from external data sources with a lot more flexibility and performance: choosing the fields or columns you need, aggregating, and filtering are your best friends. You can outsource a part of the heavy lifting from the Excel sheet to Power Query and even to your data source, and you are less constrained by the size of your bill. You can go above one million rows at the data source. However, you can still only fetch up to one million result rows into your Excel sheet, so it is best to aggregate and filter your data before.

There are a few caveats. For some data sources, you have limitations on what is possible to compute remotely on the data source side, or you may need to use advanced mode to achieve what you want (e.g. to define an SQL query to select only the columns you need). In these cases, you may need to load more data to your computer, and the computation (selection, aggregation, filtering, etc.) may happen locally. This will kill a part of your advantage, and you will be constrained by the performance (CPU, memory) of your machine.

Level 2 - Power BI Desktop

You have arrived to the world of Power BI. The product range starts with the free-of-charge Power BI Desktop application as an entry point. As it handles data in tabular format and works with files that typically encapsulate both the data and the logic, just like Excel, it also feels similar. However, it is pretty different in how it approaches working with data.

In Excel, the norm is often manual data entry, freestyle copy-paste, arbitrary placement of tables, and an abundance of formulas spreading around sheets. From this angle, Power BI looks much more constrained than Excel. It is really a Business intelligence tool. It focuses on larger data size, bulk transformations, querying speed, powerful interactive visualizations, and AI extensions. When it comes to analytics and business intelligence it is way more powerful than Excel.

With Power BI (Desktop) you can connect to much more extensive data sources with multiple millions of rows and beyond. Perfect for cloud billing data. You can also choose how you do that. You can Import the data. This will load it to your machine’s memory into a local in-memory analytical model and you can run transformations and lightning-fast queries on top of it. Your data will also be saved to your Power BI file (this uses the *.pbi file extension, but behind it is a compressed zip file). You can also directly query (DirectQuery) remote data sources by sending only the query statement there, letting the remote end process it, and fetching only the results. This saves your machine’s resources and storage and allows connecting to more extensive data sources beyond your machine’s constraints. There are further connection modes available too, but it is beyond our scope here.

At the same, with the Desktop edition of Power BI, you have some severe limitations on automation, collaboration and scaling. You can only work with it as files (*.pbi) and refresh the data manually. The files can be shared similarly to Excel files. You can put them on a shared drive (OneDrive, Google Drive, Dropbox, etc.) and assign permissions to people. You can also send them as attachments via email or in a chat app (with size limitations). The drawback of this approach is that the data gets copied, sent around, and can evolve into different versions, as there is no centrally controlled way to update the data or to make changes on all files at once. Scaling quickly becomes a mess and limitation.

Enter Power BI Service (next levels)

This is the point where Power BI Service (explained in the next levels) starts to make sense. You have made your data processing and visualization work locally with Power BI Desktop. Good. But its constraints will keep you from evolving your report into a live system, that:

  • serves your report in the browser as a web app or a mobile app,
  • provides a single point of access (via a web link) for everyone,
  • ensures that users look at the same version of the report with the same data,
  • keeps your data up to date with automatic refreshes,
  • allows you to manage sharing and access with ease.

Power BI Service gives you all these advantages. You can use it under different service plans, offering various capacity levels, features, licensing, and pricing. The gradually more capable plans are a Free account, Power BI Pro, and Power BI Premium.

Let’s explore how these are providing increasingly capable services. I skipped adding a separate section for the Free account option because it does not offer many capabilities above Power BI Desktop. It only adds a web workspace called My Workspace that allows you to consume your reports there. However, you still cannot consume reports from other workspaces, publish and share your reports with others, or automate data refreshes. In the following sections, let’s see what Power BI Pro and Power BI Premium offer.

Level 3 - Power BI Pro

So, you have upgraded yourself to be a pro. So far, everything has been free of charge. But now your pocket starts to hurt. The Power BI Pro plan is licensed on a per-user basis, and you pay a monthly fee of 10 USD for each user. This applies not only to those who want to create reports, but also to those who want to consume (* other license options)

What do you get in exchange? You can now publish your reports, display them in a browser, and start to share and collaborate from the same source with your colleagues. You can store 10 GB of data per user license, and you can create reports with a maximum in-memory model size of 1 GB. When dealing with cloud billing data, this is not large at all, but pragmatically, it can suffice if the billing data you want to work with does not exceed about 500 MB. You will need that other 500 MB of memory when refreshing your data.

Frankly, with an average laptop today, which typically has at least 8 GB of memory, you can load more data locally in Power BI Desktop. So Power BI Pro will not take you super far with data volume. The real deal is the ability to automate data refreshes, run them up to 8 times per day (e.g., once every 3 hours), and publish and share your reports with colleagues.

As both the creators and consumers of the reports need a license, this setup can work best for a smaller FinOps team who work with a few other folks from management on the cloud cost or do experimentation and want to learn the Power BI Service’s workings. But it will hardly be a good choice when you want to share reports with a large, hundreds of users audience.

With Power BI Pro, you also miss out on advanced Dataflows, Datamarts, and AI features and will not have a read/write XMLA endpoint for custom automation, expert model manipulation, performance tuning, and debugging. These come only in Power BI Premium and paid Fabric Capacities.

Level 4 - Power BI Premium

If you have grown out the relatively small in-memory model size (1 GB) and storage size (100 GB) that Power BI Pro offers, or you need more advanced features, but still do not have hundreds of people user base, you can upgrade to Power BI Premium for twice the price of Pro at 20 USD per user per month.

This will allow you to grow your in-memory model compared to Pro by 100 times to 100 GB (more practically 50 GB as you will need about half of the memory to do the data refreshes). This is already a great size for most FinOps operations. You will also be able to run refreshes 48 times a day (e.g. once every 30 minutes) and grow your storage to 100 TB. Huge. For the full benefit, do not forget to enable Large semantic models (not available in Pro). These are already ample resources to run a regular FinOps practice managing cloud bills in the range of millions of dollars per month spent in the cloud.

You will also benefit from a set of more advanced features in the Premium package, which are not available in Pro.

Datamarts and Dataflows will enable departmental self-service scenarios. This is useful if your FinOps team has strong analytical minds and power users, but not necessarily strong data engineers. Your folks are not afraid to construct their own data mart from various data sources to enjoy the benefits of independence and full control over their data. In turn, they can fire custom SQL queries or use a fully web-based, visual, no-code querying experience to self-answer their business questions. Personally, I am not a great fan of the approach, but I can totally see that there are teams where this makes absolute sense. Follow your guts.

The XMLA endpoint read/write capability, on the other hand, opens an expert’s toolbox. Not for the faint of heart. A strong, engineering level knowledge of Analysis Services internals is recommended: an understanding of the tabular model aka. semantic model, partitioning, incremental refresh and more. This is the ‘do not try this at home’ level, you have been warned. Do not mess with this in production unless you know exactly what you are doing. In exchange, you get more tools and superpowers for:

And there is even more in the Premium box. With Deployment Pipelines, you can manage the lifecycle of your Power BI reports across different environments (e.g. development, test, and production). You can Bring your own key (BYOK) to encrypt your data. You can do Multi-geographical deployments. And the list continues, visit the Power BI Premium features page to get a fuller view.

The downside? The sharing model is really the same as the Pro. You need a Premium license not just for content creators, but also for the viewers. Neither Power BI Pro licensed users, nor Fabric Capacity licensed users can look at your content either. To manage a better, broader sharing experience and achieve cost efficiency with a more extensive user base, you will need to check out a Fabric Capacity that matches your use case.

Level 5 - Fabric Capacities

Now you go all-in. You get all the features of Power BI Premium within a capacity size of your choice, and probably the most complete platform offering that exists today for data and analytics. This entails all end-to-end services that any kind of data persona would ever need, from business users, thorough analysts, to engineers, scientists and more. Plus, all of these are built on top of a unified, virtualized data lake that can encompass all data at an organization.

The major feats are:

  • A data lake storage, called OneLake, which is meant to be a single lake for the entire organization, much like cloud drives work these days. Its guiding principle is to have a single copy of the data with distributed ownership, that can be processed with multiple engines depending on the use case. The enabler of this is the open-source Delta Lake format, which is compatible with various processing engines like T-SQL, Spark, Notebooks or Analysis Services. OneLake also supports shortcuts (known as symlinks in the Linux world), that allow data to be visible and referenced across domains without actually moving it, even across cloud providers.

  • For the computational processing of different analytical workloads, it offers a set of tools and technologies grouped by use case or persona:

    • for the integration of various data sources at the cloud scale, it offers Data Factory with Connectors and Pipelines,
    • for Data Engineering, it extends Pipelines with Lakehouse creation, and Spark and Notebook-focused data processing,
    • for Data Science, it offers ML Modeling and Experimentation beyond Notebooks,
    • for Data Warehousing, it brings a petabyte-scale SQL Warehouse built on the open Delta Lake format, completely separating storage and compute for independent scaling,
    • for Business Intelligence, it offers Power BI with a new, Direct Lake connection mode that allows speeds similar to in-memory models but directly connected to Delta Lake storage, eliminating the need for data imports and refreshes,
    • for Real-Time Intelligence, it works with Even Streams, KQL (Kusto Query Language), Activator, and Real-Time Visualisation capabilities,
    • and in addition to this, it also supports traditional Databases for transactional (OLTP) workloads and out-of-the-box Industry Solutions.

  • As further overarching concepts beyond OneLake, it includes Purview to cover data security, governance, and compliance, plus Copilot, which gets integrated at all levels of the tooling.

You can consume Fabic in a Pay-As-You-Go model, priced by the hour, or commit to a reserved capacity for one year in exchange for a hefty ~40% discount. But there is a catch in either case. The Fabric Capacity price does not include all the things you will need to pay for.

The Fabric Capacity price covers the compute services for all capabilities. Storage will be billed to you on top of that according to your usage, as well as networking in the future (coming soon with a 90-days notice). You will also need to add some Power BI Pro (per user) licenses to enable Power BI content creation and, for smaller capacities, even consumption. Let me explain.

For capacity sizes F2-F32 (up to 10 GB memory), you will still need Power BI Pro licenses for all users who wish to create or view Power BI content. Above that, for capacities F64 (formerly P1) or greater (from 25GB to 100s of GB memory), only Power BI report creators will need a Pro license, and viewers will already be included with a Free license. This is where you should look if you have a large user base with the majority of users being viewers. The provider puts the breakeven point at roughly 250 users, but do your own math for a great decision.

Before Fabric, a similar, but much less smooth and unified technology setup could be sourced from Power BI and Azure components. This also had the same separation of the main pricing elements: compute capacity, storage, network and Power BI user licenses. Instead of a Fabric Capacity, customers could buy a Power BI Premium Capacity. However, it was available from the F64 Fabric equivalent size, called P1, not smaller. Storage, processing, and transfer of the data were done outside this capacity too in Azure, for example, Azure Data Lake Storage and querying and processing with Azure Synapse. The compute capacity was solely serving Power BI and one had to pay extra for the Azure Synapse querying (compute) as an example. With the rise of Fabric, this model has been deprecated, and it is mentioned here only for completeness and comparison, as some customers may still be using this model.

Other Levels - Power BI Embedded and On-Premises Power BI Report Server

Let’s add some spice, some specialty stuff in here.

Not all of us are the regular BI (bee eye) folks. Some of us buy Power BI to sell it. Like Independent Software Vendors (ISVs) do, who put some serious analytics and charts into their applications and put it up for sale as their own. For them, there is Power BI Embedded, which enables this model legally and technically. However, to get the viewer users for free, you will still need to opt for the larger capacities, F64 and above.

Then, there is the classic, legacy on-premises Power BI Report Server for folks who are not yet ready to move to the cloud for whatever reason: compliance, price, oldschoolism. But it is future proof in the sense that the larger reserved Fabric Capacities from F64 (and former P1) also include the Power BI Report Server license. This is not the case for Pay-As-You-Go. There are other licensing options, too, with SQL Server. Read more on this in Licensing Power BI Report Server. Happy migration!

This concludes our walk-through.

If you have not read Part 1 yet and are interested in growing your knowledge of the Power BI and Fabric product history, check it out!

I hope you learned something and enjoyed it! That’s my pleasure! Keep learning and subscribe to the FinOps Builders blogletter!

I cover an eclectic mix of FinOps, engineering, business intelligence and management topics. All things useful to build a strong practice and sharpen your profile.