Eighteenth Blog Birthday: On The Question Of “Wouldn’t It Be Great If Power BI Did This?”

Every year, on the anniversary of the first-ever post on this blog, I write a post reflecting on what has happened to me professionally in the past year or discussing a non-technical (but still work-related) subject. Two years ago I wrote a post about people who ask the question “Why don’t you add this one simple feature to Power BI?”, something which seems even more relevant now than when I first wrote it. This year I want to discuss a similar topic: people who ask “Wouldn’t it be great if Power BI did this?”, where “this” is some amazing, complicated new piece of functionality they have thought of.

First of all, let me explain why “Wouldn’t it be great if Power BI did this?” is different from “Why don’t you add this one simple feature to Power BI?”. The “one simple feature” question is asked by people who come across the same problem in their day-to-day work so frequently that they can’t understand why Microsoft hasn’t addressed it yet – which is why they are often so frustrated and angry – and where the solution seems obvious and easy. Expanding/collapsing column headers, the top-voted idea on ideas.powerbi.com, is a great example of this type of question: lots of people want it, there are no arguments about how useful it would be, but as Amanda’s comment on the item says it’s actually a lot harder for us to implement than you would think. Hopefully we’ll be able to do it soon.

The “wouldn’t it be great” question is different because it is is prompted by long years of experience of BI tools and projects and is the result of some very creative thinking: the solutions suggested are never obvious, never going to appear on ideas.powerbi.com, and are much more strategic. In the past I’ve been very much the type of person to ask the “wouldn’t it be great” question. For example, about fifteen years ago I remember writing a long email to the Analysis Services team asking for something vaguely like DirectQuery on datasets; more recently I remember trying to convince Chris Finlan that it would be good if paginated reports could render to adaptive cards. Luckily for me the people I sent these emails to were always very polite, even if nothing ever came of my galaxy-brain proposals.

Why does someone like me love asking the “wouldn’t it be great” question so much? It’s the IT equivalent of fantasy football: if you spend way too much time thinking about your favourite football team/BI tool then it’s only natural to imagine how cool it would be to be the coach/program manager of that team/tool and make important decisions about its destiny. Maybe you think you could do a better job than the people who are actually in charge. It’s harmless fun and a great way to while away a few hours with friends over a beer at a conference.

It’s important to understand why the “wouldn’t it be great” question is not a good way of requesting changes to Power BI though. My colleagues Kasper de Jonge and Matthew Roche, both of whom have many years of experience as program managers, helped me with this when I first joined the Power BI CAT team. Now, when I think of a “wouldn’t it be great” idea, I hear Kasper’s voice in my head telling me that it’s the CAT team’s job to collect and curate feedback and it’s the program managers alone whose job it is to design the product. From Matthew I learned the word “solutionize”, which according to the definition I found here means “to come up with a solution for a problem that hasn’t been defined (and might not even exist)” where “common examples are things like developing a feature because you can, iterating and building without research, or selecting a platform or pattern before knowing the required functionality”. Asking the “wouldn’t it be great” question is suggesting a solution when actually the people who design the solutions need to hear the details of the problem you’re trying to solve. If you provide enough details of the problem then it’s possible that the program manager will come to the same conclusion as you about the solution, but even then there are so many other factors to take into account (such as whether it’s even possible to implement your solution) that nothing can be guaranteed.

So, in summary, if you have a problem that you think Power BI needs to solve then you should spend as much time as possible defining that problem rather than imagining what the solution could be – that’s the best way to ensure it gets the attention it deserves from Microsoft. To be clear I’m not saying that you shouldn’t suggest a solution too, I’m just saying that solutions are not what you should be focusing on.

Power BI DirectQuery Mode And Degenerate Dimensions

A few weeks ago my colleague Dany Hoter wrote a post on the Azure Data Explorer blog about how using columns on fact tables as dimensions in DirectQuery mode can lead to errors in Power BI. You can read it here:

https://techcommunity.microsoft.com/t5/azure-data-explorer-blog/to-star-or-not-to-star-more-about-data-modeling-in-power-bi/ba-p/3689933

In the post he mentioned that he could reproduce the same behaviour in SQL Server, so I thought it would be good to show an example of this to raise awareness of the issue because I think it’s one that anyone using DirectQuery mode on any data source is likely to run into.

Consider a simple DirectQuery dataset built from the ContosoRetailDW SQL Server sample database:

There are two important things to point out here:

  1. The FactOnlineSales table has over 12 million rows
  2. The SalesOrderNumber column on the FactOnlineSales table is an example of a degenerate dimension, a dimension key in the fact table that does not have a related dimension table. This typically happens when it would make no sense to create a separate dimension table because it would be the same, or almost the same, granularity as the fact table.

Now if you try to build a simple table visual in a report connected to this dataset that has the SalesOrderNumber column and a measure that sums up the value of the SalesAmount column (ie with a definition of SUM(FactOnlineSales[SalesAmount] ) ) you’ll get the following error:

The resultset of a query to external data source has exceeded the maximum allowed size of ‘1000000’ rows.

This isn’t really surprising: you’ll get this error in DirectQuery mode any time Power BI generates a query against a data source that returns more than one million rows. You can increase this threshold using the Max Intermediate Row Set Count property on a Premium capacity but to be honest, if Power BI is trying to get this much data, you could still run into other performance or memory problems so the best thing to do is to redesign your report to avoid this problem. In this particular example you should question why you need a report with a table that has more than one million rows in it (I suspect the answer from the business will be “to export it to Excel”, which is another argument…).

In many cases you can avoid the error by applying a filter to the visual. In my example filtering the visual to display just the data for January 1st 2007, by applying the filter on the Datekey column of the DimDate table, means you don’t get an error because no SQL query will be generated that returns more than one million rows:

However, the bad news is that if you use slightly more complex DAX in your measure you are still likely run into the same error. For example, if you create a new measure with the following definition:

Error Demo = 
var s = [Sales Amount]
return if(s>0, s)

[Yes, I know the measure definition doesn’t make much sense but it’s just a way of reproducing the problem]

….you’ll get the error even with the filter applied:

Why is this? Looking at the various SQL queries generated by Power BI for this visual it’s easy to find the cause of the error:

SELECT 
TOP (1000001) [t1].[SalesOrderNumber]
FROM 
(
(
select [$Table].[OnlineSalesKey] as [OnlineSalesKey],
    [$Table].[DateKey] as [DateKey],
    [$Table].[StoreKey] as [StoreKey],
    [$Table].[ProductKey] as [ProductKey],
    [$Table].[PromotionKey] as [PromotionKey],
    [$Table].[CurrencyKey] as [CurrencyKey],
    [$Table].[CustomerKey] as [CustomerKey],
    [$Table].[SalesOrderNumber] as [SalesOrderNumber],
    [$Table].[SalesOrderLineNumber] as [SalesOrderLineNumber],
    [$Table].[SalesQuantity] as [SalesQuantity],
    [$Table].[SalesAmount] as [SalesAmount],
    [$Table].[ReturnQuantity] as [ReturnQuantity],
    [$Table].[ReturnAmount] as [ReturnAmount],
    [$Table].[DiscountQuantity] as [DiscountQuantity],
    [$Table].[DiscountAmount] as [DiscountAmount],
    [$Table].[TotalCost] as [TotalCost],
    [$Table].[UnitCost] as [UnitCost],
    [$Table].[UnitPrice] as [UnitPrice],
    [$Table].[ETLLoadID] as [ETLLoadID],
    [$Table].[LoadDate] as [LoadDate],
    [$Table].[UpdateDate] as [UpdateDate]
from [dbo].[FactOnlineSales] as [$Table]
)
)
 AS [t1]
GROUP BY [t1].[SalesOrderNumber]

Why is this query generated? Why is there no WHERE clause applying the date filter? When a DAX query is run Power BI does the following:

  1. Send dimension queries per unique dimension table. Only filters directly placed on the dimension table apply in this case.
  2. Send measure queries for each measure. All filters may apply in this case.
  3. Join the results from 1 and 2.

Sometimes, as in the example where just the SUM measure is used, it can optimise the dimension queries and apply the filter from the dimension table too. Sometimes, as in the last example above, it can’t and it runs a dimension query asking for all the distinct values from SalesOrderNumber in the fact table. You can try to work around it by rewriting your DAX, by filtering on the fact table and not the dimension table – applying a filter on the Datekey column of FactOnlineSales for 1/1/2007, rather than on the Datekey column of DimDate, results in no error – or using a dynamic M parameter to get more control over the queries generated. However the best course of action is to avoid using any column from a large fact table as a dimension, whether it’s a legitimate degenerate dimension or a column that should really be modelled in a separate dimension table.

[Thanks to Dany Hoter and Jeffrey Wang for the information in this post]

UPDATE February 2023: in some cases you may be able to avoid this error by building an aggregation table https://blog.crossjoin.co.uk/2023/02/08/avoiding-the-maximum-allowed-size-error-in-power-bi-directquery-mode-with-aggregations-on-degenerate-dimensions/

Excel Small Multiples And Sparklines: Fun With Dynamic Arrays, Let, Lambda And Image

Last week I presented at the Nashville Modern Excel user group and before my session started Will Harvey, the organiser, did a quick run-through of what’s new in the Power Platform. He highlighted a service that piqued my interest called QuickChart which has a new Power Automate connector. QuickChart have an API that you can pass your data to and which will return an image of that data in a chart; they also have a free tier so you can play with it easily. It’s pretty cool (although this isn’t a formal endorsement) – try opening the following URL in your browser:

https://quickchart.io/chart?c={type:’bar’,data:{labels:[‘Q1′,’Q2′,’Q3′,’Q4′], datasets:[{label:’Users’,data:[50,60,70,180]},{label:’Revenue’,data:[100,200,300,400]}]}}

It made me think – can you use this with the new Excel Image function? And indeed you can. For example, here’s the URL above used from the Image function:

=IMAGE("https://quickchart.io/chart?c={type:%27bar%27,data:{labels:[%27Q1%27,%27Q2%27,%27Q3%27,%27Q4%27],%20datasets:[{label:%27Users%27,data:[50,60,70,180]},{label:%27Revenue%27,data:[100,200,300,400]}]}}",,1)

What could this be used for? Excel has had sparklines, mini charts that fit inside a cell, for a while but they’re not dynamic: the number of sparklines on a worksheet is fixed. I realised that by using QuickChart with dynamic array functions you can create dynamic arrays that return sparklines, giving you sparkline small multiples. Helpfully QuickChart explicitly support sparklines, for example:

https://quickchart.io/chart?c={type:’sparkline’,data:{datasets:[{data:[140,60,274,370,199]}]}}

Here’s a simple worked example. Let’s say you have a table on your worksheet called Sales that looks like this:

Here’s an Excel function, defined using the Lambda function, that displays a sparkline for a particular country and product combination:

=
LAMBDA(
product,
country,
IFERROR(
LET(
filterrows, FILTER(Sales, (Sales[Product] = product) * (Sales[Country] = country)), 
choosesales, CHOOSECOLS(filterrows, 4), 
concat, TEXTJOIN(",", FALSE, choosesales), 
IMAGE("https://quickchart.io/chart?c={type:'sparkline',data:{datasets:[{data:[" & concat & "]}]}}", , 1)), 
"")
)

By the way, this is exactly the kind of formula that the Advanced Formula Environment makes much easier to write; Mynda Treacy has a great video on it here.

This is what the function does:

  • It takes two parameters, product and country
  • The function definition is broken down into steps using the Let function
  • The filterrows step filters the Sales table using the Filter function to get just the rows for the specified product and country
  • The choosesales step uses the ChooseCols function to get the column that contains the sales values
  • The concat step uses the TextJoin function to turn these sales values into a comma delimited list of values
  • Finally, this list of values is injected into a call to the QuickChart API using the Image function
  • The whole thing is wrapped in IfError to prevent any error values appearing, for example if a product or country value was passed to the function that didn’t appear in the Sales table

This function can now be called from a formula like so:

=GetGraph("Apples", "UK")

But we want something more dynamic. Consider the following:

Cell B3 contains a dynamic array formula using the Unique function to return all the distinct country names in the Sales table:

=UNIQUE(Sales[Country])

Cell C2 contains a similar formula but gets the distinct product names and uses the ToRow function to return these names in different rows:

=TOROW(UNIQUE(Sales[Product], FALSE))

Finally cell C3 uses the MakeArray function to call the GetGraph for each combination of country and product:

=MAKEARRAY(
ROWS(B3#),
COLUMNS(C2#),
LAMBDA(r,c,
GetGraph(INDEX(C2#,1,c),INDEX(B3#,r))
)
)

Here’s the output:

The good thing about this is that if new products or countries are added to the Sales table, like so:

…new columns and rows automatically appear to display them:

You’d probably need to get a paid subscription to QuickChart if you wanted to do this in the real world, but for me the point of all this is how much more the amazing new functionality added to Excel in the past few years (dynamic arrays, Image, Let, Lambda, the Advanced Formula Environment etc) allows you to do. Excel is almost as exciting as Power BI these days!

Is Power BI A Semantic Layer?

The short answer: yes. And it’s a metrics layer and a metrics store and it’s headless BI too for what it’s worth.

I’ve been meaning to blog about this question for a long time now, for two reasons:

  1. Customers migrating to Power BI often ask me if Microsoft also has a product or service that can act as a semantic layer, so I need to explain that Power BI is already a semantic layer.
  2. I read the deliberations of the cool kids of analytics (Benn Stancil for example, who I enjoy a lot) and get the feeling that Microsoft and Power BI inhabit a separate universe that is invisible to them. This exchange on Twitter is a prime example.

The reason I haven’t blogged about this question yet is that earlier this year Aurimas Račas wrote a truly outstanding blog post on this subject, which I strongly recommend you read:

https://aurimas.eu/blog/2022/08/metrics-layers-and-power-bi/

What more could I say? Well Aurimas’s blog post has the virtue of being impartial and I thought it would be useful adding a few thoughts from the perspective of a Microsoft insider. These opinions are my own, not official Microsoft opinions, but I don’t think any of my colleagues would disagree with them.

So is Power BI a semantic layer?

Back to the main question. Whether or not we do a good job of promoting Power BI as a semantic layer to customers we certainly think of it as one internally; I see it referred to as one internally all the time. Indeed we always thought of Power BI’s ancestors Azure Analysis Services and SQL Server Analysis Services, all through their 20+ year history, as semantic layers too – who remembers the terms BI Semantic Model or UDM?. One of the points Aurimas makes is that this is an awareness problem more than anything else: because Power BI can be used as a self-service BI tool as well as an enterprise BI tool and because more people use it as a self-service tool, the perception of it as such prevents some people from seeing it as an enterprise BI tool. On the Power BI CAT team we certainly work with a lot of large customers that use Power BI as an enterprise BI tool and semantic layer successfully: Walmart’s finance team is a greate example and their recent case study here (this older video is good too) explicitly mentions that they use Power BI as a “semantic model library” on billions of rows of data.

Preference for thin(ness)

Another great point that Aurimas makes is that the current preference in BI tools is for them to be thin layers that “delegate the computational workloads to the (cloud) databases/warehouses where the data is stored”. Back when I first started in BI the debate was between MOLAP and ROLAP and while the pendulum has swung in different directions over the years we’re still arguing over the same points with Import mode versus DirectQuery. My personal opinion is the currently unfashionable one: Import mode and the Vertipaq engine will always outperform an approach that involves generating SQL against an external database, however fast and scalable that database claims to be, for anything more than basic BI requirements (I’m watching Google Malloy with great interest though, along with whatever SQL additions Julian Hyde is working on). The official Microsoft guidance is that Import mode should be your default choice and at present, as this video by Alberto Ferrari shows, the performance differences between Import mode and DirectQuery mode are significant. As the Walmart case study referenced above mentions, you can always mix Import mode and DirectQuery mode in composite models and build aggregations if you’re working with data volumes tha are too large for Import mode alone. We are continuing to invest in improvements to DirectQuery such as Horizontal Fusion and I think that will close the gap between Import and DirectQuery a lot.

DAX or SQL?

In the same way the MOLAP vs ROLAP debate has dragged on for twenty-plus years, people have always argued whether SQL is the right language for expressing BI queries and calculations or if another language – MDX when I started, DAX today – is necessary. To be honest I think if SQL was the right choice the argument would be settled by now and we’d already have a whole ecosystem of BI products allowing you to define complex measures in SQL in a way that developers found easy to understand. Even if you accept that another language is necessary (and the people working on Google Malloy agree on that point) then there’s the question of whether DAX is a good solution to the problem or whether a different approach would be better. DAX is certainly hard to learn but that’s more because of the concepts involved rather than the syntax itself; Marco Russo’s post here is a great explanation of why DAX is simple but not easy. Since the concepts are the issue I strongly suspect that any other language that was as powerful as DAX would be just as difficult to learn. What’s more we’re working on making DAX debugging easier, for example with the EvaluateAndLog function, and making writing calculations easier with the upcoming visual calcs feature, and there are a lot of other similar improvements we could and should implement in the future.

Conclusion

Will these points change anyone’s mind? Probably not, especially since these questions are religious more than anything else: you’re either a Microsoft fan or someone who would never, ever consider using a Microsoft product; you’re either a SQL die-hard or you aren’t. Does this matter? I’ve seen Power BI’s all-conquering usage figures and I’m not sure it does. I love theoretical questions about semantic layers as much as anyone else but what really matters is whether a tool is used and providing value to businesses.

Sharing Excel Reports Connected To Power BI Datasets Or The Excel Data Model/Power Pivot Using OneDrive For Business

I love Excel as an alternative to Power BI reports, especially now that Excel Online allows you to interact with browser-based Excel reports that use Power BI datasets or the Excel Data Model (aka Power Pivot) as a source. After all, why try to recreate a PivotTable in a Power BI report when you can give your users an actual PivotTable? You can publish Excel reports to Power BI and view them there; however if you want to share these reports using OneDrive for Business, outside Power BI, you’ll need to think carefully about how you do so before you allow other people to view the report.

To illustrate the problem I used Power Pivot to load some data into the Excel Data Model, built a simple report with a PivotTable and PivotChart, saved it to OneDrive for Business and then opened it in the browser with Excel Online. As you can see it’s possible to interact with the report in the browser, changing slicers and moving fields around in the PivotTable and PivotChart field lists:

So far so good. However, an Excel file saved to OneDrive for Business is a shared document – and any changes that one user makes are immediately visible to all other users who have access with full Edit permissions. This means that if you change a slicer and someone else is viewing my report at the same time, they will see the slicer change too in real-time. This may be useful in scenarios where you are collaborating with someone on the same report and want them to see your changes, but in most cases this is not desirable behaviour for a report. Here’s an example with the same Excel report open side by side in Chrome and Edge:

How can you stop this happening? One option is to use View mode. When an Excel workbook is in View mode a user can interact with slicers and move fields around in the PivotTable without these changes affecting other users and without these changes being saved; they cannot make most other changes such as entering values in cells. If users do have full Edit permissions they can always switch to View mode using the dropdown menu in the top right corner of the screen:

You can also use the Protect Workbook option to make sure users open the workbook in View mode by default. However there is always the danger that if a user has Edit permissions they will switch to Edit mode and make unwanted changes, so as a result it’s better to share the workbook without Edit permissions using the “Can View” option described here so they can only open the workbook in View mode:

There is another option: using an Embed link. To get this, click on the File menu in Excel Online and then on the backstage view select Share and then Embed:

As the UI suggests, this option is intended for scenarios where you want to embed your workbook in another application; it’s similar to View mode but a bit more restrictive. In the Embed dialog that opens you can choose which parts of the workbook to share, whether grid lines are shown and other useful options:

Here you need to select the “Let people sort and filter” option otherwise your users won’t be able to use the slicers. Next, copy the code from the “Embed code” box and paste into your favourite text editor so you can extract the URL in the src tag – this is the URL you should give to your users. When a user opens an Excel report using a link generated in this way they won’t see the Excel ribbon at all; they can interact with slicers but can’t get access to the PivotTable fields pane and so won’t be able to move fields around, for example changing what is on rows or columns. I think this gives the best experience for end users who just want to view an Excel report in the browser.

Note that this does not replace normal OneDrive for Business sharing and permissions though: you’ll still need to share the report with your users, ideally with “Can View” permissions as described above. If your Excel report connects back to a Power BI dataset you also need to ensure that your users have the appropriate permissions on that dataset, which means they’ll either need to be at least a Member in the workspace the dataset is stored in or have Build permissions.

What’s New In Analysis Services 2022?

There was a time when a new release of SQL Server – and therefore a new release of SQL Server Analysis Services – was the most exciting thing in the world for me. New functionality! New things to blog about! Not so now that my focus, and Microsoft’s, is on Power BI and we get cool new functionality there every month. All the same there are still a lot of people running SSAS on-premises and SQL Server 2022 has just been released, so what’s new and is it worth upgrading?

There’s nothing about Analysis Services in the SQL Server 2022 GA announcement blog post, but you can find a list of what’s new here:

https://learn.microsoft.com/en-us/analysis-services/what-s-new-in-sql-server-analysis-services?view=asallproducts-allversions

Most of the items listed here are performance optimisations, most of which have been available in Power BI and Azure Analysis Services for some time now (although we haven’t got parallel execution plans for DirectQuery in Power BI just yet 😉). Probably the most important in my opinion is MDX Fusion, the main effect of which is to improve the performance of Excel PivotTables and cube-function-based reports connected to SSAS Tabular – I saw some cases where MDX queries ran a lot faster when this rolled out for Power BI. All the features are applicable to SSAS Tabular although some are applicable to SSAS Multidimensional too; there are also a few other minor optimisations that aren’t listed. The new cloud-billing model announced here is only applicable to the SQL core engine and not to SSAS, SSRS or SSIS.

There are no deprecated features but Multidimensional’s data mining features and PowerPivot for SharePoint are now officially discontinued (which means that they are now no longer supported – see the definition of “discontinued” here).

As a Microsoft employee, obviously I’m going to say you should upgrade to SQL Server 2022. As a member of the Power BI product group I would add that you should also consider migrating all your on-prem SSAS Tabular models to Power BI Premium if you can: Power BI Premium is the strategic direction for enterprise BI as well as self-service BI and that’s where all the investment is going from now on. Don’t think about migrating to Azure Analysis Services instead – we’re already encouraging people to migrate from AAS to Premium! My colleague Dan English just posted a great walkthrough of the new AAS to Premium migration experience here, which is worth checking out.

Migration from SSAS Multidimensional to Power BI is a much more difficult task. You’ll need to rebuild your existing cubes and calculations from scratch manually in Power BI (there are no tools to automate migration because it isn’t possible to build them). Simple cubes should be easy to rebuild; more complex cubes, for example those with parent/child hierarchies, custom rollups or SCOPE statements for example, will be much more difficult to migrate and you may need to accept that you can’t reproduce some functionality exactly. You can always run SSAS Multidimensional in a virtual machine in Azure if you need to move to the cloud and there are VM images to make that easy.

Web.Contents, Text Concatenation And Dataset Refresh Errors In Power BI/Power Query

One of the most popular topics I’ve ever written about here is the dataset refresh errors you get with dynamic data sources and the Web.Contents function in Power BI (see here and here for example). Recently I learned about an interesting new detail regarding this problem that I thought was worth blogging about, and which may help someone trying to troubleshoot it.

Consider the following M query, used as the source of a table in a Power Query dataset:

let
  Term = "apples",
  Source = Json.Document(
    Web.Contents(
      Text.Combine(
        {
          "https://data.gov.uk/api/3/action/package_search?q=",
          Term
        }
      )
    )
  ),
  CallSucceeded = #table(
    type table [CallSucceeded = logical],
    {{Source[success]}}
  )
in
  CallSucceeded

[The API used here is public and doesn’t require authentication so you can run this query yourself]

This query refreshes successfully in Power BI Desktop but when you publish to the Power BI Service and go to the dataset Settings page you see the following error:

This dataset includes a dynamic data source. Since dynamic data sources aren’t refreshed in the Power BI service, this dataset won’t be refreshed. Learn more: https://aka.ms/dynamic-data-sources. Data source for Query1

Why is this a case of a dynamic data source? Unlike the examples in my previous post there is only one, known, value being passed to the API, but nevertheless the use of the Text.Combine M function is enough to prevent the Power Query engine from doing the static analysis it needs to do to allow refresh to take place. As documented here, the Power Query engine needs to be able to work out the URL it’s going to call before the query is run and in this case it can’t.

One way of solving this problem, the way I knew would work, is to use the Query option with Web.Contents like so:

let
  Term = "apples",
  Source = Json.Document(
    Web.Contents(
      "https://data.gov.uk/api/3/action/package_search",
      [Query = [q = Term]]
    )
  ),
  CallSucceeded = #table(
    type table [CallSucceeded = logical],
    {{Source[success]}}
  )
in
  CallSucceeded

What I didn’t know – or rather I did, but didn’t really understand – is that if all you want to do is concatenate text to build your URL you can use the & operator rather than Text.Combine and it will work because Power Query is smart enough to understand that during static analysis. So for example:

let
  Term = "apples",
  Source = Json.Document(
    Web.Contents(
      "https://data.gov.uk/api/3/action/package_search?q="
        & Term
    )
  ),
  CallSucceeded = #table(
    type table [CallSucceeded = logical],
    {{Source[success]}}
  )
in
  CallSucceeded

…will also successfully refresh in the Power BI Service because it uses the & operator. This is the way I would naturally write the code and I’m sure I’ve done this hundreds of times in the past without realising that concatenating text any other way will lead to errors.

[Thanks to Curt Hagenlocher for this information]

How Much Data Can You Load Into Power BI?

Power BI can handle large data volumes, but just how much data can you load into Power BI? Can Power BI handle big data? How big is “big” anyway? These are questions you may have when you’re starting out on a new Power BI project and the answers can be hard to find. Indeed the answer in most cases is “it depends”, which isn’t very helpful. In this post I will try to explain the various limits on Power BI dataset size and how you can know if you’re likely to hit them.

Power BI storage modes

First of all, what does it even mean to “load data into” Power BI? There are two fundamental ways that Power BI can work with data: Import mode and DirectQuery mode. Different tables in your Power BI dataset can have different storage modes; there’s also an option called Dual mode where a table can switch between Import mode and DirectQuery mode depending on the circumstances.

In DirectQuery mode no data is stored in your Power BI dataset and when your report renders and queries are sent to the dataset, the dataset sends queries back to the data source to get data on demand. This means the only limits on the amount of data you can work with are the limits set by your datasource.

In Import mode Power BI stores a copy of your data inside the dataset in its own internal database engine, known as either the Vertipaq engine or the Analysis Services engine. Import mode is the default storage mode and for good reason – it will almost always give you the best performance for your reports and it allows you to use all Power BI functionality. The “how much data?” question is only relevant to Import mode because when you use it Power BI imposes various limits on the amount of data that can be stored inside a dataset.

How big is your dataset?

In order to answer the question of “how much data can you load into Power BI?” you need to know how to measure the size of your dataset. There are various different ways of doing this but the best way is to install a free, community-developed tool called DAX Studio: its Model Metrics feature shows you the total size of your dataset on the Summary tab:

This figure is good enough for most purposes. It’s not quite that simple though, and this video by Marco Russo and Patrick Leblanc has a great explanation of all the different ways of measuring dataset size and what the figures really represent.

Can you predict how large your dataset is if you know how much data you have in your data source?

You have to import all your data into your dataset to find out how large your dataset is. But can you work out how large your dataset will be before you do this? The answer is no. You can be pretty sure that the size of your dataset will be smaller than the size your data is in your data source but it could only be a bit smaller or it could be a lot smaller, maybe even just 10-20% of the original size. This is because Power BI compresses your data very efficiently when you import it. What’s more, how you model your data can have a big impact on how well the compression works and making a few changes can result in a much smaller dataset as, for example, this post by Nikola Ilic shows.

How much data can you load into Power BI Desktop?

The only practical limit on the amount of data you can load into Power BI Desktop is the amount of memory you have on your PC; you’ll need at least 16GB of RAM, ideally 32GB, to get the best experience. However Power BI Desktop is just a development tool – you’ll need to publish to the Power BI Service for other people to view your report and that’s where the limits take effect. What’s more there’s a 10GB limit on the size of a dataset you can publish to the Power BI Service from Power BI Desktop, although you can have datasets much larger than that in Power BI Premium.

To be honest you should never be working with anything like 10GB of data in Power BI Desktop anyway: the file size will be huge, saving will be slow and you’ll spend a long time waiting for data to import while you’re development. What you should do is work with a small subset of your data in Desktop and only load the full volume after you’ve published. You can do this in a few different ways, for example using incremental refresh or by using M parameters to limit the amount of data you load in Desktop and then changing the parameter after publishing, as I showed here.

How much data can you load in the Power BI Service if you’re using Shared capacity (aka “Power BI Pro”)?

If you are not using Power BI Premium or Premium Per User, when you publish to the Power BI Service you are using Shared capacity (often called “Power BI Pro” by Power BI users because you only need a Power BI Pro licence to use it). The maximum size of a dataset in Shared capacity is 1GB; if you hit that limit you’ll get the “Unable to save the changes” error I blogged about here. There is one exception though: as mentioned here, if you use the rather obscure option in the Power BI Service to upload an Excel workbook with data in the Excel Data Model/Power Pivot, then the resulting dataset is limited to 250MB.

How much data can you load in the Power BI Service if you’re using Power BI Premium or Premium Per User (PPU)?

The default maximum dataset size in a Power BI Premium capacity or PPU is 10GB, but if you turn on the Large Dataset storage format you can have datasets larger than that and the maximum size depends on the amount of memory available in your Premium capacity. The “Max memory per dataset” column in the table here shows the maximum amount of memory available in each Premium or Power BI Embedded capacity SKU to an individual dataset; the maximum amount of memory available per dataset in PPU is 100GB. However the maximum amount of memory available to a dataset is not the maximum size of a dataset: apart from the memory used to store the data for a dataset, more memory will be needed when the dataset is queried or refreshed. If you do a full refresh of a dataset it may require almost double the amount of memory needed to store the dataset itself; incremental refresh may require less memory. You can use the Premium Capacity Metrics App to see how your dataset’s memory usage changes over time.

Other Import mode limits

There are a few other limitations of Import mode that should be mentioned. As documented here you can only have 1,999,999,997 distinct values in a single column and there is a limit of 16,000 on the combined total number of tables and columns in those tables – but if you have that many tables and columns you have definitely made a mistake with how you model your data. Also, some DAX functions such as Median() only work on tables with less than 2 billion rows in them, as I blogged here.

Power BI can load all my data, but will my reports be fast enough to use?

Loading all your data into Power BI is one thing but what really matters is whether the reports you build are fast enough to use. In my experience performance problems relating to data volume are not as common as performance problems caused by inefficient DAX in measures or poor modelling decisions, although they do occur – for example distinct counts on columns with a very large number of unique values can be slow. If you’re following best practices you’re unlikely to encounter them unless you’re working with the kind of data volumes that require Premium. DAX Studio’s Server Timings feature can help you understand why your queries are slow and whether data volumes are an issue.

This is a lot to take in – what does it all mean?

Let me finish up by making a few broad generalisations about how much data you can load into Power BI Import mode. Assuming you have followed all the best practices around modelling data you should be able to work with tables with up to a few million rows, probably tens of millions of rows, in Shared capacity and tables with up to a few billion rows in Premium. If your data source is Excel then Power BI can definitely handle the amount of data you have; if you are working with a relational database like SQL Server then it’s still very likely Import mode will work; even if you’re working with hundreds of gigabytes of data or more in a source like Azure Synapse, Snowflake or BigQuery then Import mode may still be ok. You will need to know Power BI very well to get good performance in Import mode with the largest data volumes but it is certainly possible and I know of several customers that are doing it.

Calling The Power BI REST API From TSQL In Azure SQL DB

Something cool has just been announced for Azure SQL DB: the ability to call a limited number of REST APIs direct from TSQL. The announcement is here:

https://devblogs.microsoft.com/azure-sql/azure-sql-database-external-rest-endpoints-integration-public-preview/

There are code samples here:

https://github.com/Azure-Samples/azure-sql-db-invoke-external-rest-endpoints

And guess what, the Power BI REST API is one of the APIs you can call! This means, for example, you can run DAX queries via the Execute Queries endpoint (bearing in mind all the limitations) from TSQL:

https://github.com/Azure-Samples/azure-sql-db-invoke-external-rest-endpoints/blob/main/power-bi.ipynb

I think this opens up a lot of possibilities. What do you think? Leave a comment!

Why You Should Defragment Your Fact Tables If You’re Using Incremental Refresh In Power BI Premium

A lot of people are building large datasets in Power BI Premium nowadays, and if you’re building a large dataset you’re very likely to be using Power BI’s incremental refresh functionality with your fact tables or manually creating and refreshing partitions in them. If so, you should occasionally do a special kind of refresh to shrink the dictionaries on your fact tables.

To illustrate why you need to do this I created a Power BI dataset with one table containing one column, where each value was a GUID (ie a unique text value). Here’s the M I used to generate it:

let
    Source = {1..10000000},
    Output = #table(type table [MyGUID=text], 
        List.Transform(
            Source, 
            each {Text.NewGuid()})
    )
in
    Output

After publishing the dataset I copied the original partition so there were two partitions in the table, each of which contained ten million rows. When you use incremental refresh in a table Power BI automatically generates and manages partitions for you in the background – and there isn’t much difference between doing this and creating partitions manually like I’m doing here.

Here’s what DAX Studio’s Model Metrics functionality showed when I first refreshed the dataset:

The Dictionary column is the column to focus on in this table. Dictionaries are the data structures created by Power BI’s storage engine, the Vertipaq engine, to hold all the distinct values in a column – the more distinct values there are, the larger the dictionary. As Marco points out here the DMVs that DAX Studio uses to measure dictionary size don’t always return consistent or accurate results but the values it returns are good enough for the purposes of this test.

I then did several Full refreshes on just one of the two partitions. Here’s what DAX Studio showed at this point:

As you can see, the size of the dictionary for the MyGUID column (and therefore the overall size of the dataset) has grown a lot – about 1GB – even though the cardinality of the column has stayed the same. This is because dictionaries are held at the table level, not at the partition level, so by refreshing just one partition I was adding 10 million new text values to the dictionary without deleting any of the old values that has been added to the dictionary by previous refreshes and which were no longer there in the table.

Finally, I ran the following Refresh command against the workspace’s XMLA Endpoint from SQL Server Management Studio:

{
  "refresh": {
    "type": "defragment",
    "objects": [
      {
        "database": "TableWithManyDistinctValues",
        "table": "MyTable"
      }
    ]
  }
}

[Note that XMLA Endpoints are only available if you’re using Premium or PPU]

This refresh is of type defragment and it is documented here. This is the explanation of what it does from the docs:

Defragment the data in the specified table. As data is added to or removed from a table, the dictionaries of each column can become polluted with values that no longer exist in the actual column values. The defragment option will clean up the values in the dictionaries that are no longer used.

After that you can see the dictionary for the table, and therefore the size of the dataset, was back to around its original size:

This is an extreme example of course, but I can imagine something nearly this bad happening in the real world if you use incremental refresh on a table with a text column containing addresses or user comments and if you don’t keep much history. This isn’t new behaviour or functionality: this happens in Analysis Services Tabular models too and is discussed in books like Marco and Alberto’s “Tabular Modelling in Microsoft SQL Server Analysis Services” but not many people are aware it still applies to Power BI datasets. This type of refresh can be quite slow but it is likely to be faster than the only other option (which is the only option if you’re not using Premium) which is to run a full refresh on the dataset.

So why should you run a refresh of type defragment on your dataset? There are two reasons:

  1. If you’re getting close to the size limits imposed by the Premium capacity SKU you’re using, and you want to make sure that you don’t hit them.
  2. Large datasets generally result in slower query performance, so reducing the size of your dataset may result in faster queries.

How often should you do this? In most cases not very often; it depends on how much your dataset shrinks when you do defragment. Once a year may be sufficient and I doubt anyone would need to do it more than once a month. One way to automate this would be using Power Automate to call the Enhanced Refresh API, as I showed in this series of posts.