Today I was able to give another talk at Reactor, with the theme “How to share billion-line reports with thousands of people at low cost”, a very useful and relevant topic for most professionals and companies.

The event was a complete success and packed with people. Thank you very much for the invitation Larissa Cyganski, Hugo Venturini, Gustavo Leo, Sidney Cirqueira and Victor Temple.

Speakers who made a difference at the event:

  • Rafael Mendonça
  • Fabio Santos
  • Sidney Cirqueira
  • Jones Santos
  • Juliana Maria Lopes

Also present was Power Tuning's Analytics Team Leader, Nayara Hellen and the experts from the Power Embedded support team Edilson Santos Sthéfane Lisboa João Paulo Macedo Paulo Eduardo and also my great friends Samyr Moises, Orlando Gomes, Andre Jaar and Guilherme Louzada.

I was very happy to be able to contribute a little to the more than 140 people present, increase my networking with super qualified people and participate in another Microsoft Reactor event, which are always excellent.

The Presentation

Video of my presentation:

 

Presentation Slides:

Event photo gallery:

This slideshow requires JavaScript.

How to share billion-line reports with thousands of people at low cost

The topic is a latent pain in almost every large company: how to share reports that number in the billions of lines, for thousands of users, without the Azure bill costing the price of a Ferrari.

If you think the only way out is to buy Pro licenses for everyone or raise the Fabric level to the limit, this talk is for you.

During the talk, I gave an overview of the Power BI licensing types and showed where SQL Server Analysis Services (SSAS) can help to drastically reduce the consumption of your Fabric/Power BI Embedded capacity.

The Scenario: The Myth of the Billion Lines

Before we talk about price, we need to talk about architecture. When a user asks for a 1 billion row report, the first question should be: Why?

Nobody analyzes 1 billion rows individually in Power BI. If the user wants to filter a specific invoice in the middle of an ocean of data, Power BI may not be the tool; Maybe he needs an application. In BI, we work with aggregation. However, if the scenario really requires this volume to analyze historical trends, the licensing strategy and the processing engine become critical to the financial viability of the project.

Licensing: Pro, PPU, Premium or Fabric?

The ecosystem has changed. Power BI Premium by Capacity (P SKUs) has been discontinued for new contracts, making way for Microsoft Fabric. But the basis remains the same: either you pay per user, or you pay per capacity.

License Type Cost Model Ideal for…
Power BI Pro Per User ($) Small teams and self-service basic BI.
Power BI PPU Per User ($$) Premium features (Deployment Pipelines, Datasets > 1GB) for few users.
Fabric (F SKUs) Capacity (Reservation/Hourly) Companies that need scale, OneLake and unified Data Engineering.
Power BI Embedded (A SKUs) Capacity (Hourly) SaaS and internal portals where the cost per Pro user is unfeasible.
Important Note: Fabric in Brazil becomes 60% more expensive than in the United States due to the tax burden and local infrastructure. If latency isn't the #1 factor (and for Power BI, it rarely is), consider building your capacity in regions like East US for dramatic savings.

SQL Server Analysis Services (SSAS) + Live Connection

Here's the secret to handling billions of rows at low cost.

Power BI, by default, uses the Import, where data is loaded into capacity memory (F SKUs or A SKUs). If your dataset is 50GB, you will need expensive capacity to handle processing this data model.

However, Power BI is a “child” of Analysis Services. When we use the mode Live Connection pointing to an SSAS server (either On-premises or a VM), DAX processing and memory management do not happen in Power BI, but rather on the Analysis Services server.

Impact on Capacity: In my tests, a report with 400 million lines in Import mode it consumed almost 100% of an F2 capacity during refresh and interactions. THE same report via Live Connection with SSAS resulted in ZERO consumption of Fabric capacity.

This allows you to use a minimum capacity (like an F2 or F4) just to “display” the portal, while the heavy lifting of processing billions of rows is on a server where upgrading RAM is infinitely cheaper than moving up a level in cloud licensing.

Embedded Analytics: Sharing with Thousands of People

If you have 2,000 users, paying for the Pro license for everyone would cost a fortune. The legalized and licensed solution is Power BI Embedded. You develop a simple portal (or use a ready-made solution) and “pack” the reports there.

  • Advantage: End users do not need a Pro license. They access it via the application.
  • Flexibility: You can turn capacity on in the morning and off at night, or scale from an F2 to an F64 only during late-night processing.
  • Reserved Instance: On Fabric, you can reserve capacity for 1 year and earn 40% discount.
CRITICAL ALERT: Be careful with “Publish to Web”. He NO is a saving tool for private data. Any data published there is indexed on Google. I've seen multinationals with sensitive data exposed because they tried to save licenses in the wrong way.

Performance Analysis: Competition, Smoothing, Bursting and Throttling

When using Fabric or Embedded, monitor simultaneous connection limits.

A capacity F2 only allows 2 Live Connection connections per second. If you have a spike of 100 users accessing at the same time, the report will be slow and you will see the impact on Analysis Services response time.

Be careful with the Smoothing & Bursting:: Fabric allows you to “borrow” CPU from the future so as not to crash the report now, but if you abuse it, its capacity will go into disrepair. Throttling, leaving everything slow for hours and probably even unavailable, without being able to open any report until all the credits used are returned or the capacity is restarted (generating an extra cost, which can be quite high).

Conclusion

Sharing massive data at low cost isn't magic, it's engineering.

By moving heavy processing to Analysis Services and using capacity licensing (Fabric or Embedded) strategically, you can deliver performance at a much lower cost.

Be the guardian of capacity in your company. Don't accept poorly modeled reports that use much more capacity than they should. Optimize DAX, use aggregations and, if your capacity consumption is still too high, SSAS is your best friend.