A Fine Slice Of SQL Server
Mara Pereira does a bit of conditional formatting:
In my example, I created a report where you could switch the measure the charts were showing, and with that, the color of the charts and KPI cards changed to.
The first thing you need to do is create your base measures.
Click through for a step-by-step walkthrough with plenty of GIFs to provide a visual explanation.
Comments closedItzik Ben-Gan twiddles some bits:
The need to manipulate data at the bit level with bitwise operations isn’t common in T-SQL, but you might stumble into such a need in some specialized scenarios. Some implementations store a set of flags (yes/no, on/off, true/false) in a single integer or binary-typed column, where each bit represents a different flag. One example is using a bitwise representation of a set of user/role permissions. Another example is using a bitwise representation of a set of settings turned on or off in a given environment. Even SQL Server stores some flag-based data using bitwise representation.
Here’s the deal. I don’t mind that this new syntax exists, particularly because—as Itzik points out—there are areas built into SQL Server which use integers to store bit flags. In application code, however, this gets a sharp “No!” from me in any code review. If you need to decompose values in your table as a matter of course, your table is not in first normal form. Having a table not be in 1NF isn’t the end of the world but at that point, I think the onus is on the developer to defend the violation at that point.
Comments closedOlivier van Streenlandt hit a deployment problem:
A couple of weeks ago my team & I ran into an issue with SQL Server Analysis Services (SSAS), due to a network split between companies, We weren’t able anymore to manage our SSAS access into our SSAS Tabular Model. Since deploying a Tabular Model using Visual Studio is also overwriting members & roles, we needed to find a valid alternative to execute our deployments. Manually at first and automated in the end.
Read on to see how they used Azure DevOps pipelines to solve the issue.
Comments closedMichael J. Swart follows up on an older post:
When I wrote Take Care When Scripting Batches, I wanted to guard against a common pitfall when implementing a batching solution (n-squared performance). I suggested a way to be careful. But I knew that my solution was not going to be universally applicable to everyone else’s situation. So I wrote that post with a focus on how to evaluate candidate solutions.
But we developers love recipes for problem solving. I wish it was the case that for whatever kind of problem you got, you just stick the right formula in and problem solved. But unfortunately everyone’s situation is different and the majority of questions I get are of the form “What about my situation?” I’m afraid that without extra details, the best advice remains to do the work to set up the tests and find out for yourself.
Definitely read the original article first. My normal approach is the naive + index method, so I’ll have to try out Michael’s method as well next time I need to delete a chunk of records.
Comments closedKevin Chant looks at a useful Powershell module:
Before covering the dbops PowerShell module I want to quickly cover DbUp.
DbUp is a .NET library that you can use to do migration-based deployments. It is open-source and is licensed under the MIT license, which you can read about in the DbUp license file.
According to the official list of supported databases, it allows you to do migration-based deployments to various databases. Such as SQL Server and MySQL. As you will discover later in this post it also works with a newer Azure service as well.
DbUp has been on my to-learn list for a little while, though I haven’t had a chance to dig into it yet.
Comments closedMarc Lelijveld looks back on things:
Over the past few days, I attended the Power BI Next Step conference in Lego land – Denmark. During the keynote, Will Thompson – PM on the Power BI team, showed a new DAX function that is available to all of us already, but was very well hidden in the latest builds of Power BI Desktop. This new function, called OFFSET, allows us to do in context comparisons between two values, without writing extremely lengthy and complex DAX.
I gave it a go and in this post I share my first experiences with this new function and how I think this will make our life easier!
This looks a bit like the combination of LAG() and LEAD() in SQL Marc shows off what’s available now and notes what appears to be forthcoming.
Andrew Collier does some linting and styling:
In the previous post we looked at how to apply a linter and styler to a Python Project. Now we’re going to do the same for an R project. We’ll use the
{precommit}R package to make the setup a breeze.
Read on to see how the combination of precommit, lintr, and styler can track deviations from expected development style. H/T R-Bloggers.
Mike Cisneros cleans up a complex visual made up of less complex visuals:
There are opportunities to improve this visual, but I appreciate that the graph is appropriately titled, that the legend is clear and easy to find, and that the most recent data point is the only one that is labeled. On its own, it’s an acceptable view of the data, albeit one that could be strengthened.
When this visual is considered in its greater context, however, the need to improve the legibility of this graph becomes obvious.
These charts are generated at multiple levels of geographic and thematic detail. Each one is then shared as part of one large report, with almost no visual variation from region to region, level to level, or week to week:
There’s a lot going for that redesign.
Comments closedKenneth Fisher sorts out data types:
With the expectation that you would get back a decimal. Did you wonder why it converted the integers to decimals rather than the other way around? If not you probably should have. I’ll give you a hint. It’s not because it’s the first value in the equation, although that does make a difference.
Thought about it? In case you didn’t figure it out T-SQL (and again, I’m going to say all programing languages where implicit conversions are allowed) has a conversion precedence order. In other words in any calculation where an implicit conversion is required the datatype with a value higher on the list is converted to the datatype with the lower value on the list.
The parenthetical statement above is correct. This makes things really convenient until you have an implicit conversion you weren’t expecting. There’s an advantage to explicit-only languages, though those do involve more typing.
Comments closedJohn Sterrett does some DNS work:
Azure Managed Instances are provisioned by default with a yourname.uniqueid.databases.windows.net DNS fully qualified domain name (FQDN). Even on your private virtual network you will still have to use this FQDN.
If you want your Azure Managed Instance to connect with your DNS prefix like yourname.domain.com this blog post is for you.
Read on for the process.
Comments closed