• What are SQL Server Index Fragmentation and Index Fill Factor?

    What is index fragmentation and how does it occur? It is important to know that SQL Server data is stored in data pages, with each page holding 8KB of data. There are two types of fragmentation, both are a result of these pages not being used as efficiently as possible. When you UPDATE or INSERT data on a page that is already full, SQL Server creates a new page. The information from the original page will be split 50/50 with half being added to…

    » Read more
  • Query Store Forced Plan Failures

    Query Store is a fantastic feature of both SQL Server and Azure SQL DB. It allows you to monitor how queries execute against the database which is invaluable for troubleshooting performance issues. More than that though, it gives you the option to force an erratic query to use a particular execution plan, this helps avoid queries from running with inefficient plans and provides predictability and stability on the server. Here we can see an…

    » Read more
  • The SQL Query Alias Conundrum – Order of Execution

    So, you have just written a query, hit execute and you have encountered an error: Invalid column name ‘[column name]‘. The column you’ve used in your WHERE clause cannot be identified by its alias. You’ve defined it at the top of the query and used it fine previously as your ORDER BY condition, so why can’t the engine recognise it? If you’ve ever written a SQL query, this should look familiar: SELECT DISTINCT FROM JOIN ON…

    » Read more
  • Combining Queries from Multiple Sources in Power BI using Merge and Append

    It is always good practice to do as much data preparation as close to the sources as you can before importing or connecting them to your Power BI reports, but what if there are circumstances where this isn’t possible? I had an issue recently where a third-party application had been updated and both the new and legacy versions were being used side-by-side. Logging data from both versions was being written to two separate Azure SQL databases.…

    » Read more
  • Making the DAX Engine Work for You

    In my last blog post which can be found here, I demonstrated that it’s important to limit your DAX filters to specific columns rather than entire tables, now we know that we can and should do it, let’s explore why and how it works. There are numerous ways to write an expression that will give the same result set, but why are some ways more efficient than others? To answer that we need to know a little about what happens under the hood,…

    » Read more
  • Increasing DAX Filter Speed and Efficiency

    To understand how a filter can affect performance, first we need to understand what a filter is. A filter is a temporary table that is created on demand, held in memory, and then used as a reference for the function being filtered. The rows of the temporary filter table are all DISTINCT. This is because for the purposes of filtering, the engine doesn’t care whether there are 1, 2, 3 or 250 occurrences of a value in table, only whether it…

    » Read more
  • How the UNICHAR() DAX Function Enhances Power BI Reports

    Enhance your Power BI reports and heighten your user's experience with icons and emojis

    » Read more
  • Dynamic Date Formats in Power BI

    Which date format styles should we use if we are building a report that is being consumed internationally? Remember, 01/12/2021 is December 1st or January 12th depending in which part of the world it is being read. The decision may be taken from our hands if there is a company policy in place. If the company is based in the USA, for example, they may choose to use US formatted date fields as a standard for reporting across the entire business,…

    » Read more
  • Power BI Drill Through using Multiple Data Points

    A drill through in Power BI allows the reader to see secondary data related to the original page with the context of a specific data point applied, for example, drilling through on sales data can display the demographic information of the relevant customers for those sales. One limitation of the drill through functionality is that it only allows users to drill through on a single data point. If more than one is selected, the drill through…

    » Read more