Press "Enter" to skip to content

Day: October 11, 2022

Case Operations in KQL

Robert Cain needs more than two paths for branching logic:

In my previous post Fun With KQL – IIF, we saw how to use the Kusto iif function to check for a condition then perform an action based on the result of a condition.

What if you had multiple conditions you need to check? While you could string multiple iif functions together there’s better solution: the KQL case function.

Robert includes several examples, as well as a check of whether KQL does short circuiting or not.

Comments closed

Implicit Conversion of DATEDIFF

Daniel Hutmacher noticed a problem:

As I was performance tuning a query, I found that a number of date calculation functions in SQL Server appear to be forcing a conversion of their date parameters to a specific datatype, adding computational work to a query that uses them. In programming terms, it seems that these functions do not have “overloads”, i.e. different code paths depending on the incoming datatype.

So let’s take a closer look at how this manifests itself.

Some of these results are pretty annoying, especially because I like to use DATETIME2(0) for the large majority of development work

Comments closed

Inserting Data into MySQL

Robert Sheldon takes us through data insertion in MySQL:

In most cases, adding a single row of data to table is a reasonably straightforward process. You define the INSERT clause and VALUES clause and usually specify the column list in between. The column list should include only those columns for which you provide values. The list can include the primary key column, generated columns, or columns defined with default constraints, but you must be careful how you handle them, as you’ll see later in the article.

This is definitely aimed at people new to MySQL and SQL in general.

Comments closed

Column Lookups and Dynamic SQL

Rob Farley does a double-check:

I’ve written before about what I consider the golden rule with SQL Injection. And that is, to understand that DATA should not be executed like CODE. A parameter is fine – that’s not part of what gets executed – but you do not embed user-entered values into queries. You never create a WHERE clause like “WHERE t.col1 = 3”, if that value 3 is being passed in. Instead, you use “WHERE t.col1 = @param”. Because you can’t assume that “3” is safe to run.

But there are things in queries that can’t be parameterised. Table names, linked server names, column names, for example.

Read on to learn what Rob does in those cases.

Comments closed

Auto-Aggregation on Dimension Tables: Gone

Matt Allington made the Power BI world a slightly better place:

Have you ever loaded a calendar table with lots of numeric columns (such as Year, Month Number, etc), loaded it into Power BI, and then had to manually go an turn off the default aggregation on these columns?  If you build Power BI reports, then I bet you have done this more than once. Jeffrey’s idea was that Power BI should be smart enough to detect a dimension table (one side of the relationship) and to disable these default aggregations.  There’s nothing worse than adding a Year column to a table and having Power BI add the years together. If I add a year to a table, I want to slice and dice, not SUM the years.

Matt added a request and see where that gets you.

Comments closed

When Batching Breaks Down

Erik Darling has some thoughts on batching data modifications:

The whole point is to go a little bit easier on your server:

– Smaller transactions sent to the log file

– Shorter locking durations

– Less build up of the version store (optimistic isolation levels, triggers, etc.)

But there are thing that make batching impossible, or downright tricky.

Erik provides more details after the jump. This is all “stuff you should think about” rather than “reasons why you shouldn’t batch.”

Comments closed

Creating Power BI Scorecards from Excel

Imke Feldmann has a goal:

I like Power BI scorecards a lot, as they give a really nice interface and look to your and your teams goals. However, entering all goals manually can become a bit tedious, especially if you have your goals already sitting in an Excel file. This is often the case for example with Budget figures. In this article I will share a method using Power Automate to create a scorecard automatically from an Excel file using standard Power Automate connectors. Please note that these connectors are still a bit limited, so connections to Power BI reports for example cannot be created currently.

Click through to see how.

Comments closed