Press "Enter" to skip to content

Month: March 2022

Dealing with Shift Times

Published 2022-03-17 by Kevin Feasel

Kenneth Fisher knows what time it is:

One of the more interesting jobs I’ve had over the years was for a company that created emergency room software. It was pretty cool software and I learned a lot, both about writing queries in SQL Server and about how a software company can be run. One of the more interesting things in the various reports we created was the concept of shift calculations. In other words, what happened during a given shift.

I’ve had to do something similar (though it was for nurse scheduling rather than emergency rooms). Things get really tricky when you start dealing with 12-hour and 16-hour shifts, tracking overtime, and the like.

Comments closed

From Cosmos DB to the Serverless SQL Pool

Published 2022-03-17 by Kevin Feasel

Jovan Popovic shows off Synapse Link:

The serverless SQL pools enable you to implement near-real-time analytics solutions on top of your Cosmos DB data. Serverless SQL pools with the Synapse Link provide a cost-effective analytics solution for analyzing NoSQL data stored in Cosmos DB, which is not affecting or spending the resource units on your Cosmos DB transactional store. You can run heavy analytics on the serverless SQL pools that will not affect your workload or price of the main Cosmos DB transactional store. The serverless SQL pools enable you to use the T-SQL query language for analytics that enables you to connect the reporting & analytics tools (such as Power BI, Analytics Services) from a large ecosystem that works with SQL Server or Azure SQL database.

When you are integrating the serverless SQL pools in your solution, you need to apply some best practices. There are general best practices for the serverless SQL pools in the Synapse Analytics workspace, but some of these settings are not applicable to the Cosmos DB scenario. Probably you will use only a subset of the best practices that you can find here. In this post, you will find only the best practices that you should apply in the Cosmos DB solution and some additional hints that could help you to optimize your solution.

Click through to see how the process works and a few recommendations.

Comments closed

Views in MySQL

Published 2022-03-17 by Kevin Feasel

Robert Sheldon continues a series on getting started with MySQL:

Like other database management systems, MySQL lets you create views that enable users and applications to retrieve data without providing them direct access to the underlying tables. You can think of a view as a predefined query that MySQL runs when the view is invoked. MySQL stores the view definition as a database object, similar to a table object.

Read on for plenty of detail around views. Even if you know how views work in another RDBMS, there are nuances to each of them you’ll want to understand.

Comments closed

PSProjectStatus

Published 2022-03-17 by Kevin Feasel

Jeffery Hicks wants to check Git status:

I write a lot of PowerShell modules. And probably like you, I am working on more than one project at a time. I was finding it difficult to keep track of what I was working on and what I might be neglecting. So I turned to PowerShell and created a tool that I use to keep on top of my projects. The PowerShell module is called PSProjectStatus and you can install it from the PowerShell Gallery. You can find the project on GitHub, but I thought I’d provide an introduction here.

Read on to see how it works.

Comments closed

Quoted and Unquoted Parameters in the Tidyverse

Published 2022-03-16 by Kevin Feasel

Sebastian Sauer shows two ways to dereference a parameter:

Using the tidyverse ecosystem, programming – instead of interactive use – may be something different or unusual and it may take some time to wrap your head around it.

In this post, I’ll show how to deal with a standard situation (using tidyvserse’ nonstandard evaluation). More precisely, there are two (complementary) situations we’ll address:

Read on for those techniques.

Comments closed

Defining Data Quality

Published 2022-03-16 by Kevin Feasel

Ust Oldfield notes the importance of data quality:

We can safely assume this because a lot of organisations do not have data quality at the top of the priority lists. Why might this be the case? Because monitoring data quality and correcting poor quality data is hard. If it was easy, every organisation would have a strategy and method for tracking and improving data quality.

Often, inertia – driven by an overwhelming amount of information to the risks – sets in as it’s difficult to know where to start. So, where do you start?

I’d say there’s an incentive alignment problem with data quality: organizations want it but not enough that they’d trade anything else for it. And agents within the organization consider data quality a chore, so they’re looking for the minimum viable path. Then, for end users, we consider it even more of a chore (or a nuisance). Furthermore, I’m one of those end users who will put in fake data if I can get away with it on the principle that I don’t want you to have my personal information because you’re probably going to sell it or lose it.

Comments closed

Creating a Trust between On-Prem AD and AWS Directory Service

Published 2022-03-16 by Kevin Feasel

Tom Collins makes a connection:

Most SQL Servers use a large portion of the authentication as Windows Authentication – utilising Kerberos and NTLM protocols via Active Directory. So when it comes to considering moving on-prem SQL Server resources to Cloud Providers – Active Directory is a foundational question.    There are other methods than Microsoft Directory – which I’ll discuss in future posts.

Utilising AWS RDS SQL Server with Windows Authentication methods is only possible using the AWS Directory Service.  i.e The AWS RDS SQL Server is created and added as a resource to the AWS Directory Service . If on-prem users require access to the AWS RDS SQL Server via Kerberos , a forest trust is required between the AWS Directory Service and the on-prem AD. 

For this post – the focus is on an existing on-premises SQL Server inventory using Microsoft Active Directory Services.

Read on to see what you’d need to do to implement this.

Comments closed

The Power of QUOTENAME

Published 2022-03-16 by Kevin Feasel

Kevin Wilkie unlocks the power of QUOTENAME():

When I first heard about QUOTENAME, I was like “This is rather useless. It just puts brackets around whatever. I can do it just as easily hard-coding the strings.”

Truly, I’m not completely wrong, but it’s a heck of a lot more fun to knock things out with the QUOTENAME function!

But there’s more that you can do with this function, as Kevin notes.

Comments closed

Materializing Views on Materialized Views

Published 2022-03-16 by Kevin Feasel

Drew Furgiuele is asking for it:

Consider this: you’ve developed a data ingestion strategy that is taking in remote thermostat readings. Usually, the devices report in on a set frequency and you’re able to calculate aggregate readings an hourly interval. A materialized view could be created that does this calculation and stores the results out for querying. But what if something causes some of this data to become duplicated? You’d first have to eliminate these duplicates, re-ingest the data, and then do your calculations again.

This is where we can leverage creating a materialized view over a materialized view. Our first materialized view will handle the deduplication, and our second can handle the aggregation of the deduplicated data.

Yo dawg, I heard you like materialized views, so I put some materialized views in your materialized views so you can materialize views while you materialize views.

Comments closed

The Cost is a Lie

Published 2022-03-16 by Kevin Feasel

Erik Darling doesn’t need your costs:

Costs are okay for figuring out why SQL Server’s cost-based optimizer:

– Chose a particular query plan

– Chose a particular operator

Costs are not okay for figuring out:

– Which queries are the slowest

– Which queries you should tune first

– Which missing index requests are the most important

– Which part of a query plan was the slowest

Yep. And read on for more information.

Comments closed