Press "Enter" to skip to content

Curated SQL Posts

Forcing Color Scheme by Data Element

Published 2022-05-23 by Kevin Feasel

Reza Rad forces a particular color scheme in Power BI:

You can set the color in every visual in Power BI easily. You can also set the color of multiple visuals at the same time using Themes. However, what if you want to set the same color for the same data point? For example, You want the Gender Female to be always colored Orange in all the charts and visuals. In Power BI, as of now, you cannot set a data point color. However, there is an easy solution for that, which I explained in this article and video.

Click through for the answer. Generally I’d say something along the lines of “instead of doing this, just have one color and take advantage of cross-filtering to highlight the element people care about.” But if you do have a multi-measure categorical set with a small number of categories, color can be a differentiator and at least this helps you keep consistent colors across visuals.

Comments closed

Savepoints in Transactions

Published 2022-05-23 by Kevin Feasel

Kevin Wilkie continues a series on transactions in SQL Server:

All right, now that everyone’s back with us, we’ll talk more about everyone’s favorite – transactions. When they deal with transactions, most people only know how to begin one, then either commit it or roll it back. But there’s so much more you can do with a transaction!

This time I want to focus on savepoints for transactions. Yes, the same term you’ve been using in games for years can be used in the workplace!

I think I have actually made use of savepoints in production code…maybe twice? It always seems like whenever I might actually make use of one (rather than simply rolling it all back and starting over) that there’s some limitation which makes them not useful.

Comments closed

Memory Fractions in SQL Server

Published 2022-05-23 by Kevin Feasel

Hugo Kornelis explains the notion of memory fractions:

Some time ago a reader reached out to me with a request for help. He showed me a query and accompanying execution plan, and asked if I could help reduce (or, better yet, eliminate) the many hash spills that were killing his performance.

While helping him work through the plan, I was once more reminded of one of my pet peeves with execution plans: we get to see the requested memory for the plan (the Memory Grant and MemoryGrantInfo properties), which is of course based on the estimated total memory usage of operators that are active at the same time. We also get to see the actual memory used by each individual operator (in the Memory Usage property). But there is no way to see how much memory the optimizer estimates for each individual operator.

Read on for a detailed explanation.

Comments closed

Finding Indexing Metrics in Cosmos DB

Published 2022-05-20 by Kevin Feasel

Hasan Savran looks at the numbers:

You might need Composite Indexes to make your queries more efficient, Cosmos DB does not create any Composite Indexes for you. You need to figure out which properties should have composite indexes then you need to change the indexing policy file to create them. 

    Indexing Metrics comes to your help when you need help with indexing policy. It tells you which indexes the current query uses and it gives you hints about what other indexes you should create to make the query work faster/cheaper. Like many other features of Cosmos DB, you need to write code by using SDK to see Indexing Metrics. The following example shows how to enable Indexing Metrics for your queries.

Click through for a code sample which shows how to collect index metrics.

Comments closed

Web Accessibility and Shiny

Published 2022-05-20 by Kevin Feasel

Jamie Owen has a two-parter. First up, why web accessibility standards are important:

An accessible website is more than putting content online. Making a website accessible means ensuring that it can be used by as many people as possible. Accessibility standards such as the Web Content Accessibility Guidelines (WCAG) help to standardise the way in which a website can interact with assistive technologies. Allowing developers to incorporate instructions into their web applications which can be interpreted by technologies such as screen readers helps to maintain a consistent user experience for all.

Second, how Shiny apps tend to stack up:

The great thing about {shiny} is that it allows data practitioners a relatively simple, quick approach to providing an intuitive user interface to their R code via a web application. So effective is {shiny} at this job that it can be done with little to no traditional web development knowledge on the part of the developer. {shiny} and associated packages provide collections of R functions that return HTML, CSS and JavaScript which is then shipped to a browser. The variety of packages giving trivial access to styled front end components and widgets is already large and constantly growing. What this means is that R programmers can achieve a huge amount in the way of building complex, visually attractive web applications without needing to care very much about the underlying generated content that is interpreted by the browser.

As a quick spoiler, not so well. Read on for the full report.

Comments closed

Cost Savings with Azure Data Factory

Published 2022-05-20 by Kevin Feasel

Koen Verbeeck maximizes the savings:

As you might’ve noticed, pricing in ADF is not the same as it was in SSIS for example. In SSIS, you pay your SQL Server license and you’re done (well, and you buy a server to run it on). It doesn’t matter what you do with SSIS, the cost is the same. If you run 1 package or 1000 packages, there’s no difference except in your electricity bill. However, in ADF you pay more if you use it more. You pay for each action you do, you pay for each activity you use and for how long things are running. There are a couple of guidelines you can follow to try to minimize costs:

Read on for those guidelines and some specific helpful items.

Comments closed

Troubleshooting Firewall Issues with Azure SQL MI

Published 2022-05-20 by Kevin Feasel

Emanuele Meazzo sees a problem pop up regularly:

Here is something that will save you lots of time and headaches when trying to connect to Azure SQL Managed Instances, especially from onprem servers or from other clouds; I had to repeat this multiple times to multiple actors, so I know it will happen to someone else too.

In most cases, “Connect Timeout” and/or “Cannot open server xxx requested by the login; Login failed” errors are caused by the firewall configuration and a lack of understanding the SQLMI networking model, let me explain:

Read on for that explanation.

Comments closed

Running Dynamic SQL in a Different Database

Published 2022-05-20 by Kevin Feasel

Kenneth Fisher doesn’t like this database and wants to move to a new one:

This is one of those things that on hindsight was a stupid problem, but still cost me hours and a major headache. So I thought I would share the story of my headache with you. 

A few weeks ago I was working on some dynamic SQL that hit multiple databases. Not a huge issue. I do lots of dynamic SQL.

But it didn’t quite work out the way Kenneth initially envisioned. Read on for the full story.

Comments closed

Searching SSRS Reports for Keywords

Published 2022-05-20 by Kevin Feasel

Hannah Vernon does a search:

Microsoft’s SQL Server Reporting Services, colloquially known as SSRS, provides a great way to expose data from SQL Server in human-consumable form, typically via a web site. Generally, it’s a good idea to use a stored procedure as the source of data for SSRS Reports. In a large SSRS environment, it can be challenging to determine which reports use a specific stored procedure.

This code provides details about SSRS Reports where the definition of the report includes any specific keyword, such as the name of a stored procedure.

Click through for a query against ReportServer.

Comments closed

MySQL Security

Published 2022-05-20 by Kevin Feasel

Lukas Vileikis starts a series on security in MySQL:

Security is a critical part of any infrastructure – it’s even more so in the database world, where one step in the wrong direction can be the cause of system disruptions and downtime, customer dissatisfaction, and in the worst-case scenario – deaths.

As MySQL is one of the primary relational database management systems being used in the world today, it’s critical to understand how to go about properly the database infrastructure to keep data breaches away from the organization’s websites or, if they’ve happened already, to become a thing of the past.

The first article covers some of the basics of security in MySQL. The second promises to have more.

Comments closed