A Fine Slice Of SQL Server
Chris Webb goes off the beaten path:
After my post on tuning Excel cube functions last week there was some (justified) moaning on Twitter about their performance. This got me thinking about other things you could do with cube functions that might improve performance and I had a crazy idea…
I can concur that this is a crazy idea, but it just might work…
Comments closedJeffrey Wang plays matchmaker:
In this final installment of our four-part mini-series on DAX window functions, we’ll be focusing on a new development. The May release of Power BI Desktop has enriched all DAX window functions – namely OFFSET, WINDOW, INDEX, RANK, and ROWNUMBER – with an additional sub-function, MATCHBY, supplementing the existing sub-functions, ORDERBY and PARTITIONBY. In this article, we’ll delve into the purpose of the MATCHBY function, along with the three specific problems it aims to resolve.
Read on to understand what the MATCHBY sub-function does and why it can be important.
Reza Rad compares and contrasts:
These two functions in DAX are often used instead of each other; ALL and ALLSELECTED. Their behavior can be similar in some contexts, but it can also be different in other contexts. In this article and video, I’ll explain the difference between these two functions and when to use each in DAX for Power BI, Analysis Services, or Power Pivot.
Reza has a video as well as a blog post to describe the differences.
Comments closedMarco Russo and Alberto Ferrari prime the pump:
When you import a table in Power BI, all the strings contained in a text column are stored in a dictionary, which improves the compression and provides excellent query performance when there is a filter with an exact match for the column value. However, reports that apply complex filters on a text column may have performance issues when the dictionary has a large number of values: depending on many other variables, a column with a few thousand unique values might already present a bottleneck, and this is definitely an issue when there are hundreds of thousands of unique strings in a column.
In October 2022, there was an internal optimization in Power BI that has improved the performance of these searches by creating an internal index. Chris Webb described this optimization in his article, Text search performance in Power BI. In this article, we explore how to evaluate whether the optimization is applied and how to measure any performance improvements. As usual, everything comes at a price: creating the index has a cost, that you will see applied to the first query hitting the column. We will also see how to detect this event and the existing limitations for this optimization.
Click through for their deep dive into the process. The final answer reminds me of the warehousing world, where you might pre-run some important queries to get those pages into the buffer pool and available for later reports.
Comments closedMarco Russo and Alberto Ferrari shave of fractions of a cent:
The first reason to choose a data type is the range of numbers supported and the precision. However, the result of a mathematical operation may produce a number that cannot be represented in the chosen data type, which requires a rounding operation. Therefore, the result of one same sequence of operations can produce different results depending on the data type and the order of execution. In this article, we discuss the typical rounding behavior for each data type and how to avoid possible issues in your DAX formulas because of any differences from the results you may have expected.
Read on to learn what granularity limits exist for integers, fixed decimal numbers, and floating point operations.
Comments closedJeffrey Wang takes us through some DAX language updates:
In the April 2023 release of Power BI Desktop, two new functions, RANK and ROWNUMBER, have been added to the DAX window functions family, along with significant enhancements to the ORDERBY sub-function. These improvements allow ORDERBY to support sorting by arbitrary DAX scalar expressions, rather than being limited to column names. This not only benefits the new functions, but also existing window functions that we have previously discussed here and here. You might be wondering, since we have had RANKX and RANK.EQ since the inception of DAX, why do we need another rank function? In today’s blog, we will address this question and explore other considerations related to using these new functionalities.
Click through for examples and caveats.
Comments closedPhil Seamark does some manufacturing analysis:
I always enjoy it when we get new DAX functions, especially so for the new set of WINDOW Functions recently added. As part of the April 2023 release of Power BI Desktop, we now have a RANK function and the ability to use a measure to control the order within the existing WINDOW function.
The first thing that sprung to my mind was to see how a Pareto calculation might leverage the new capability.
The basic idea of a Pareto calculation is to create a curve like representation of data ordered from largest to smallest.
Read on to see how.
2 CommentsEd Hansberry has no bugs, but just in case:
When trying to get your DAX measures to work correctly, there are a number of tools you can use to debug, which I will briefly mention, but not go into depth on. Instead, this post is about how to think about debugging your code. I am focusing on DAX here, but many of the methods would apply to any type of code.
Read on for a series of tips around built-in capabilities, process, and the power of conversation.
Comments closedChris Webb has an update for us:
Some good news for those of you using DirectQuery mode in Power BI: the ApproximateDistinctCount DAX function, which returns an estimate of the number of the distinct values in a column and which can be a lot faster than a true distinct count as returned by the DistinctCount function, is now available to use with BigQuery, Databricks and Snowflake sources. It only worked with Azure SQL DB and Synapse before; RedShift is coming soon. You can use it in exactly the same way that you would with the DistinctCount function except that it only works in DirectQuery mode.
As always, there’s an example. I do wonder if the DAX function uses the same HyperLogLog algorithm that SQL Server uses for its approximate count distinct.
Comments closedMarco Russo and Alberto Ferrari cover the blank row:
We dedicated a previous article to the blank row in DAX. In that article, the goal was to explain the differences between VALUES and DISTINCT. This article here focuses on how important it is to generate the blank row to guarantee that totals are always correct.
The blank row is created for regular relationships that are invalid – that is, when there is at least one row on the many-side that does not have a matching row on the one-side of the regular relationship. The same does not happen for limited relationships, which do not generate a blank row in similar conditions. Therefore, if a model contains a limited invalid relationship, developers must pay extra attention to how they create reports to avoid obtaining inaccurate results.
Read on for an example of what they mean.
Comments closed