Press "Enter" to skip to content

Curated SQL Posts

Dataverse and Microsoft Fabric Gotchas

Published 2024-06-12 by Kevin Feasel

Marc Lelijveld shares some advice:

Recently, I architected a solution for a client for their Microsoft Fabric data platform. The client works with Dynamics Finance & Operations as one of their main ERP system. Fabric offers easy ways to bring data from various standard Microsoft services into the platform, however it is not always as easy as it looks like. In this blog I will elaborate on the gotcha’s encountered in architecting this solution.

Read on for the challenges that Marc ran into along the way.

Comments closed

Choosing between Data Warehouses, Lakes, and Lakehouses

Published 2024-06-11 by Kevin Feasel

Den Smyrnov talks architecture:

Historically, the two most popular approaches to storing and managing data are Data Warehouse and Data Lake. The choice between them usually depends on business objectives and needs. While Data Lakes are ideal for preserving large volumes of diverse data, warehouses are more favorable for business intelligence and reporting. Sometimes, organizations try to have the best of both worlds and mix Data Lake & Data Warehouse architectures. This, however, can be a time and cost-consuming process.

Against this backdrop, a new hybrid approach—Data Lakehouse—has emerged. It combines features of a Data Lake and a Data Warehouse, allowing companies to store and analyze data in the same repository and eliminating the Data Warehouse vs. Data Lake dilemma. Data Lakehouse mixes the scalability and flexibility of a Data Lake with the ability to extract insights from data easily. Ever so compelling, this approach still has certain limitations. It should not be treated as a “one-size-fits-all” solution.

Read on for an explanation of each of these three styles, including their pros and cons.

Comments closed

Converting Numbers into Roman Numerals with C#

Published 2024-06-11 by Kevin Feasel

Adron Hall changes numbering schemes:

Today, I’ll dive into a fascinating challenge: converting modern numbers into their ancient Roman numeral counterparts. The task is straightforward but intricate, involving a programming challenge that takes any positive integer from 1 to 3999 and converts it into the corresponding Roman numeral.

To convert regular decimal numbers into Roman numerals, one must follow a set of rules based on the values and combinations of specific Roman numeral characters. Here’s a brief summary of the conversion process:

Click through for the translation rules and how to do this in C#. Now I’m thinking about how to do this in F# and thinking tail-call recursion. I might give that a try on my own and blog about it if I come up with something neat.

Comments closed

Bucket Operations in Oracle Object Storage

Published 2024-06-11 by Kevin Feasel

Brendan Tierney continues a series on working with Oracle Object Storage:

In a previous post, I showed what you need to do to setup your local PC/laptop to be able to connect to OCI. I also showed how to perform some simple queries on your Object Storage environment. Go check out that post before proceeding with the examples in this blog.

In this post, I’ll build upon my previous post by giving some Python functions to:

  • Check if Bucket exists
  • Create a Buckets
  • Delete a Bucket
  • Upload an individual file
  • Upload an entire directory

Read on for those examples.

Comments closed

Test Data Generation Concepts and Challenges

Published 2024-06-11 by Kevin Feasel

Phil Factor talks about data generation:

Data generation is the science and art of providing data for database development work that is as realistic and controllable as possible. The skills of generating realistic data are an essential part of being a database developer. It is important: you need plenty of data of exactly the right type, size and verisimilitude in order to develop and test a database. It has got to look right. If you are launching a corporate, data-driven application, you’ll need it for training as well as testing and development.

Where things get really tough is when you also want to get the distribution of data right. Is a uniform distribution (e.g., outputs from a random number generator) okay? Or do you have a few large accounts that take up the majority of your data? That difference could affect query plans and if you have nice and uniform data, you might never see some of the cardinality estimation problems until they pop up in production.

Comments closed

Cross-Database Ownership Chaining in SQL Server

Published 2024-06-11 by Kevin Feasel

David Seis explains and warns:

In SQL Server, an ownership chain is created when one object (like a stored procedure) accesses another object (like a table) in the same database. If both objects have the same owner, SQL Server only checks permissions for the first object, not the second. This is known as ownership chaining.

Cross-database ownership chaining extends this concept across databases. If a stored procedure in Database A accesses a table in Database B, and both objects have the same owner, SQL Server will not check permissions for the table in Database B.

For what I consider the definitive explanation of why you shouldn’t use cross-database ownership chaining (or other techniques like setting TRUSTWORTHY on), Solomon Rutzky’s article is it. Module signing is the answer and the rest are traps.

Comments closed

Exporting SQL Server Query Results to an HTML Table

Published 2024-06-11 by Kevin Feasel

Vlad Drumea builds a table:

In a previous post, I’ve covered how to import and query HTML table data in SQL Server, so I figured it makes sense to also show ho to export SQL Server query results to an HTML table using PowerShell and dbatools.

This is really similar to how the HTML report from PSBlitz is generated. The differences being that PSBlitz doesn’t rely on dbatools and there’s a bit more formatting and some JavaScript involved.

Click through for a piece-by-piece breakdown of the script and Vlad’s commentary.

Comments closed

Checking for Patterns in Text with R

Published 2024-06-10 by Kevin Feasel

Steven Sanderson looks for a sub-string:

When working with text data in R, one common task is to check if a character or substring is present within a larger string. R offers multiple ways to accomplish this, ranging from base R functions to packages like stringr and stringi. In this post, we’ll explore how to use grepl() from base R, str_detect() from stringr, and stri_detect_fixed() from stringi to achieve this.

Click through for three separate ways of doing this.

Comments closed

Making the Point Clear in a Visual

Published 2024-06-10 by Kevin Feasel

Amy Esselman offers advice:

Have you ever found yourself staring at a graph or slide, wondering what the creator was trying to convey? Perhaps you’ve sat through a presentation, only to be left scratching your head, unsure of what to do next. Don’t put your audience in this same uncomfortable position. Instead, connect the dots for them to make it clear what the point is and what action they should take. When you fail to explicitly state the purpose of your communication, you run the risk of the important insight being lost, or someone arriving at the wrong conclusion.

Read on for an example of comparing resource plans to actual allocations and moving from showing data to telling a story.

Comments closed

Third Party Vendors and Missing tempdb Space

Published 2024-06-10 by Kevin Feasel

Tanayankar Chakraborty troubleshoots a strange issue:

Issue

An issue was brought to our attention recently where an azure SQL DB was throwing TempDB related errors although the customer felt that the TempDB usage never came close to the value published in the official Microsoft document. Here’s the error the customer had complained about:

Error

Here is a more detailed error text :

The database ‘tempdb’ has reached its size quota. Partition or delete data, drop indexes, or consult the documentation for possible resolutions.’. Possible failure reasons: Problems with the query, ‘ResultSet’ property not set correctly, parameters not set correctly, or connection not established correctly.

This was an interesting problem and, admittedly, I didn’t predict the twist.

Comments closed