Press "Enter" to skip to content

Curated SQL Posts

Scraping the Microsoft Fabric Road Map with Microsoft Fabric

Published 2023-11-28 by Kevin Feasel

Prathy Kamasani wants a report, not a webpage:

Like many I am also playing with Fabric, many of my clients are also excited about Fabric and want to know more about it. Being a solution architect in the consulting world one of the most common questions I get asked is: “When certain features will be available, Where are they in the roadmap?”. That’s what sparked the idea of scraping the Microsoft Fabric Roadmap and creating this Power BI report. It is based on a Direct Lake connection, so it has been a bit temperamental.

So, what did I do it? If you are not interested in the whole story. Here is Python code you can run to get a road map. If you are interested in my process carry on reading 

Click through for the process and explanation.

Comments closed

An Overview of Docker Security Principles

Published 2023-11-28 by Kevin Feasel

Jagdish Mohite talks security:

Docker incorporates several inherent security features that contribute to its overall security posture. When you use Docker to quickly create an environment and test some code, security is important enough (especially if you execute any , but when using Docker for production, multi-user environments, it is essential to treat the container as you would any other server environment.

The following is a list of some of the basic security principles that are baked into Docker.

This includes some of the things Docker does for your automatically, limitations around securing containers, and common attack modes. It’s a high-level overview but interesting to read.

Comments closed

Controlling Fallback Behavior in Direct Lake

Published 2023-11-28 by Kevin Feasel

Sandeep Pawar talks about fallback options:

When you create a Direct Lake semantic model, by default it is in Direct Lake mode, i.e. you will directly query the delta table from the lakehouse/warehouse. This is what we want because the query performance will be very much comparable to the import mode. However, under certain circumstances, the DAX query can fallback to DirectQuery if Direct Lake limitations are hit.

Read on to learn more about circumstances in which this could happen and ways to change the default behavior.

Comments closed

Exposing KQL Data in OneLake

Published 2023-11-28 by Kevin Feasel

Brian Bønk gets in on the Microsoft Fabric fun:

Microsoft has released the final piece of the current puzzle around the OneLake as a one-stop-shopping service for dat in Fabric. Until now we had only access to the KQL data in the KQL database.

With this addition, we can now finally say that OneLake is the one place for your data in Fabric.

Read on to see how you can make data in an existing KQL database usable in OneLake.

Comments closed

Local Regression (LOESS) in R

Published 2023-11-27 by Kevin Feasel

Steven Sanderson takes us through a powerful regression technique:

LOESS, which stands for LOcal regrESSion, is a versatile and powerful technique for fitting a curve to a set of data points. Unlike traditional linear regression, LOESS adapts to the local behavior of the data, making it perfect for capturing intricate patterns in noisy datasets.

Click through for examples. LOESS works best with quadratic data, like in Steven’s last example image. The downside to it as a technique is that you can find spurious movement that may seem interesting but is just following the noise.

Comments closed

Data Warehouse ETL Patterns

Published 2023-11-27 by Kevin Feasel

Ben Johnston starts a new series:

No matter the ETL tool used, there are some basic patterns to follow when transferring data between systems. There are many data tools and platforms, but the basic patterns remain the same. This focuses on SQL Server, but most of these methods work in any data platform. Even if you are using a virtualization layer, you likely need to prepare the data before exposing it to that engine, which means ETL and data transfers.

Warehouse is very loosely a data warehouse, but the same process applies to other systems. This includes virtualization layers, and to a smaller degree, bulk transfers between transactional systems.

Read on for a few things Ben recommends you have in place before beginning the project, as well as several warehouse loading patterns.

Comments closed

Tracking Inaccessible Azure SQL DB Databases and Customer Key Cycling

Published 2023-11-27 by Kevin Feasel

Rod Edwards is watching:

This is the first follow up post from: Azure SQL TDE and Customer Keys (BYOK). Microsoft?…your name isn’t down, so you aren’t coming in. (sqlrod.com) , which explained how to use Customer Keys with Azure SQL DB (and Managed instance), and some of the dangerous pitfalls that you can face. We need to know when there may be trouble on the horizon, so key (pun fully intended) to this is monitoring.

Yes, i’m rambling on again about monitoring…but I like monitoring.

Be seeing you, Number Six.

Comments closed

Self-Join Optimizations and Index Intersection

Published 2023-11-27 by Kevin Feasel

Daniel Hutmacher shows off a possibility:

This blog post started as a “what if” contemplation in my head: Suppose you have a reasonably large table with a clustered index and a number of non-clustered indexes. If your WHERE clause filters by multiple columns covered by those non-clustered indexes, could it potentially be faster to rewrite that WHERE clause to use those non-clustered indexes?

The answer might surprise you.

To be honest, when I learned about the concept originally, I expected that there would be a great deal of use cases for it. But SQL Server rarely comes up with this answer on its own and I think that’s because in most scenarios, we’d need to do additional sorting or other expensive operations to get the multiple indexes aligned just right to make this the faster option.

Comments closed