Press "Enter" to skip to content

Day: March 12, 2024

Incremental Refresh on Large Power BI Semantic Models

Published 2024-03-12 by Kevin Feasel

Soheil Bakhshi needs to refresh a lot of data:

Implementing incremental refresh on Power BI is usually straightforward if we carefully follow the implementation steps. However in some real-world scenarios, following the implementation steps is not enough. In different parts of my latest book, Expert Data Modeling with Power BI, 2’nd Edition, I emphasis the fact that understanding business requirements is the key to every single development project and data modelling is no different. Let me explain it more in the context of incremental data refresh implementation.

Read on for that explanation, as well as a few tips to make things work a bit more smoothly.

Comments closed

Durability and Hekaton

Published 2024-03-12 by Kevin Feasel

Rob Farley ponders a pair of potential performance improvements and their effects on durability:

Durability in SQL is handled by making sure that data changes are written to disk before the calling application is informed that the transaction is complete. We don’t walk out of a shop with our goods before the cashier has confirmed that the credit card payment has worked. If we did, and the payment bounced, the cashier would be calling us back pretty quickly. In SQL this is about confirming that the transaction log entry has been written, and it’s why you shouldn’t use disks with write-cache for databases

And yet, in-memory features of SQL, commonly called “Hekaton” handles transactions without writing to disk. The durability is delayed. This month, Todd Kleinhans invites us to write about Hekaton.

In-Memory OLTP is one of those features that I wish worked better for most use cases or didn’t have as many limitations around only working within the context of a single database. In practice, instead of using In-Memory OLTP for most tables, you’re usually better off just jamming more RAM on the box and limiting how many scans of large tables flush your buffer pool.

Comments closed

Tips for Using Powershell in Azure

Published 2024-03-12 by Kevin Feasel

Paul Harrison shares a few tips with us:

When I’m working with a new object in Azure I often don’t know where the information I care about is actually found in output. PowerShell makes it easy to navigate through objects, however it isn’t easy to get an overview of all properties available if they’re nested 5 levels deep. I like to use ConvertTo-JSON to help me get a general understanding for a new object and which properties are available and how to find them.

Read on for more information about converting to JSON and four other tips.

Comments closed

Proper Logging of SQL Agent Job Outputs

Published 2024-03-12 by Kevin Feasel

Aaron Bertrand hits on an annoyance of mine:

If you haven’t migrated your workloads to a managed database platform yet, you’re probably still relying on SQL Server Agent for various maintenance and other scheduled tasks. Most of the time, these processes just work. But when it’s time to troubleshoot, it can be cumbersome to get to the root of some problems. In this post, I’ll share some ideas to help you minimize the level of annoyance and tedium when you have to figure out what went wrong with the execution of a job.

Recently, I was investigating a job that ran a stored procedure against multiple databases in sequence, and ultimately failed. Here was the output of the job step as seen in Management Studio’s View Job History dialog:

Read on for a few tips to help with SQL Agent job logging.

Comments closed

VISUAL SHAPE and Visual Calculations

Published 2024-03-12 by Kevin Feasel

Marco Russo and Alberto Ferrari fit the square pegs into the square holes:

Visual calculations, introduced as a preview feature with the February 2024 release of Power BI, aim to simplify the creation of calculations tied to a specific visual. Using visual calculations for simple calculations is straightforward.

However, as soon as developers create more complex calculations, they should understand the technical details of visual calculation implementation. This requires understanding the hierarchical structure of the virtual table, the new visual context, the semantics of ROWS and COLUMNS, the behavior of CALCULATE, and the new visual context modifiers EXPAND and COLLAPSE.

In this first article about visual calculations, we introduce VISUAL SHAPE and the basics of visual calculation implementation, leaving the remaining topics to future articles. A complete whitepaper with a detailed explanation of all these topics will be available soon to SQLBI+ subscribers.

Even without a complete whitepaper, this serves as a useful primer on the topic.

Comments closed

Dropping Extra tempdb Files

Published 2024-03-12 by Kevin Feasel

Vlad Drumea doesn’t want to restart SQL Server:

This is a brief post containing a script you can use to delete extra tempdb data files and avoid the “file not empty” (Msg 5042) error.

I’ve occasionally ran into situations where an instance is configured with more than the recommended number of tempdb data files.

If you’re dealing with more than a couple of extra files it can get annoying, especially on a busy instance.

Read on for the script and how you can use it on busy and not-so-busy instances.

Comments closed