It’s been a while since I wrote anything here. My last post was back in mid-2024, and before that… well, let’s just say consistency wasn’t my strongest suit when it came to blogging 😊

If you’ve read any of my older posts, you probably noticed they were mostly about SQL Server — SSMS tips, query tricks, things I stumbled upon while working as a SQL Server and BI consultant. That world was my bread and butter for years, and I genuinely enjoyed sharing small discoveries that made my (and hopefully your) day-to-day work a bit easier.

PowerBI semantic models utilizing the Direct Lake storage mode read data directly from tables stored in OneLake. However, if a DAX query reaches the limitations of the Direct Lake mode, it may switch to DirectQuery mode (this behavior can be controlled, details follow). These limitations are as follows:

As of the writing of this article, the following features were unsupported:

• Single lakehouse/warehouse
• Composite models
• T-SQL based views
• Calculated columns and calculated tables
• String length > 4000 characters
• Complex/No structured delta table column types (Binary, GUID)
• Relationships based on DateTime types
• Hierarchies / Excel drillthrough
• RLS/OLS in Warehouse
• Direct Lake model resource limits

Switching from Direct Lake mode to DirectQuery mode results in reduced performance and, in some cases, query failure, as not all DAX features are supported in DirectQuery mode.

When I’m working with git, I like to compare files at the commit level. You have version X displayed in the left window, version Y in the right window, and any differences in the two versions are shown in color - extra changes are shown in green, and missing changes are shown in red. On one of my last projects I needed to compare changes in a .sql script that was not available in SSDT, but only in SSMS. That’s when I said to myself, gosh, how could I use a version of file change comparison as we know it from git… I don’t even know how but by chance I came across this article https://raresql.com/2023/04/04/sql-server-how-to-compare-files-in-sql-server-management-studio-ssms/ where this functionality is described and available directly in SSMS!

Recently I was asked by one of my friends for help with slow PowerBI report. The scenario is that the data source is view from Oracle database, built as a join from several tables and returning a lot of rows. Without parameters, this view is running forever (they cancelled it after one hour …). Because of Oracle database, only DirectQuery can be used as a storage model. The problem with DirectQuery is that it doesn’t support query parameters, so in our case all data from the source view must be read into PowerBI desktop and filters can be applied later. The challange here is how to apply filters to source view first? While considering all possible solutions, I noticed short message on Twitter from Phil Seamark mentioning PowerBI bookmarks. After little research, I realized that this feature might be used as a workaround I was looking for! So how we can use bookmarks in our scenario? The trick is that when you click on or refresh report page in PowerBI, all visible visuals are refreshed at the same time – tables, charts, slicers etc. The key word here is visible – what if we hide visuals showing data from the source view, set up the filter first and show main visual(s) with the data afterwards? Let’s have a look if this idea would work. First, I prepared the source data. I decided to use ContosoRetailDW (and yes, instead of Oracle I use SQL Server 😊) and created source view:

In my recent project I ran into situation, where I had to turn off page compression on some of the indexes. Unfortunately, indexes were dropped and re-created in stored procedures, so I had to identify those procedures and change the data_compression setting from PAGE to NONE.
To do this I’ve decided to query sys.sql_modules DMV and check the definition column, which contains stored procedure text. I just copied text into clipboard and then pasted into new query windows and … no voilà effect  :-(. The stored procedure text wasn’t formatted as I expected, it was formatted as a one line. The reason of this behaviour is improper handling of CR/LF characters by SSMS by default.

One of the techniques for query performance troubleshooting is to compare Actual and Estimated Execution Plans. In the most cases, both plan shapes are identical and the only difference is in the runtime statistics (ActualNumberOfRows, ActualExecution, MemoryGrant, DegreeOfParallelism etc.) – those are present in Actual Execution Plan only. If the difference between estimated and actual number of rows is too big, you have a cardinality estimates issue.

For demonstration, let’s enlarge AdventureWorks2017 sample database using J. Kehayias script (you can download it here).