Press "Enter" to skip to content

Author: Kevin Feasel

Retrieving Text Between Delimiters

Published 2020-10-23 by Kevin Feasel

Erik Darling takes us through the seedy underbelly of T-SQL:

I have to do this a fair amount, and I always go look at this little cheat sheet that I have.

Then it occurred to me that it might be worth sharing the details here, in case anyone else runs into the same need.

The way I learned to do it is with SUBSTRING and CHARINDEX, which is a pretty common method.

That this is possible is great, but it’d be nice to have an easier approach. Thinking through that easier approach is outside the scope of this post…

Comments closed

Creating Schema-Only Database Copies

Published 2020-10-23 by Kevin Feasel

Garry Bargsley shows us three methods for creating schema-only clones of databases:

Have you ever been asked to make a schema only copy of a database? What is your preferred method to complete this request? Below we will discuss how to create a schema only database copy.

Here are three approaches that are easy to understand and perform. One approach will use PowerShell and the dbatools module. The second method will use built-in SQL Server functionality, depending on your SQL Server version. The third way will use a third-party (paid) tool from Red-Gate software.

Click through for the three options.

Comments closed

Getting Power BI to Write Your DAX

Published 2020-10-23 by Kevin Feasel

Gilbert Quevauvilliers shows off an interesting way of using a Power BI feature:

Recently I had to get some data from a Power BI Dataset. At first, I started writing the DAX using the fantastic DAX Studio.

Then a thought occurred to me, what if I could get the DAX already written and change it to my requirement. This would save me a lot of time and effort. I love the quote from Patrick in Guy in a Cube “I am not lazy, I am efficient”

Click through for the scenario. Looks like it will get you at least part of the way there.

Comments closed

Changing Power BI Data Source Credentials

Published 2020-10-23 by Kevin Feasel

Martin Schoombee walks us through changing data source credentials on deployment with Power BI:

The method we need to use here is the Patch method. Why is it Patch and not Post like we did with the parameters? Great question, and the “devil is in the details”…the method dictates how the underlying resource or attribute is modified, and the Patch method in this case means that there are partial (and in-place) modifications to an existing resource. Read more about the differences between Post, Put and Patch here.

This is one of those areas where Power BI can be quite a letdown if you only use the UI.

Comments closed

More Fun with NULL

Published 2020-10-22 by Kevin Feasel

Chris Johnson troubleshoots an issue in code:

The poster had a CASE statement and was wondering why it didn’t work as expected. Essentially they were doing something like:

CASE WHEN @a = @b OR (@a IS NULL AND @b IS NULL) THEN 1 ELSE 0
CASE WHEN NOT(@a = @b OR (@a IS NULL AND @b IS NULL)) THEN 1 ELSE 0

And they wanted to know why both were returning 0 when @a or @b were set to NULL. The issue here is that any normal predicate involving NULL returns an unknown. They had tried to compensate with the OR, which got them the result they wanted in the first statement, but didn’t understand why it did that.

Click through for the explanation.

Comments closed

Azure Elastic Queries, Jobs, and Transactions

Published 2020-10-22 by Kevin Feasel

Steve Hughes walks us through three Elastic concepts in Azure:

Elastic queries allow developers to interact with data from multiple databases supported on the Azure SQL database platform including Synapse. Elastic queries are often referred to as Polybase which is currently implemented in SQL Server 2019 and Azure Synapse. The key difference is that elastic queries only allow you to interact with other Azure SQL Databases but not Hadoop or other database implementations (e.g. Teradata or Oracle). Part of the confusion comes from the fact that the implementation looks very similar. Both toolsets use external tables in SQL Server to interact with the connected data sources. However, Polybase requires additional components to run whereas elastic queries are ready to go without additional setup.

Read on for more information, including demos.

Comments closed

Mapping New Column Names with Power Query

Published 2020-10-22 by Kevin Feasel

Soheil Bakhshi reminds me of DB/2:

So, here is my scenario. I received about 10 files, including 15 tables. Some tables are quite small, so I didn’t bother. But some of them are really wide like having between 150 to 208 columns. Nice!

Looking at the column names, they cannot be more difficult to read than they are, and I have multiple tables like that. So I have to rename those columns to something more readable, more on this side of the story later.

Fortunately, there’s a way to fix this; click through for that way.

Comments closed

Tips for Using Azure Table Storage

Published 2020-10-22 by Kevin Feasel

Adrian Hills takes us through using Azure Table Storage:

Azure Table Storage is a NoSQL key-value PaaS data store that can be a great option for highly scalable, highly available systems. It supports storing petabytes of data and a flexible data schema, meaning different entities in the same table can have different schemas. References to NoSQL databases having “flexible schema” or being “schema-less” can give the impression that database schema design is a thing of the past and that you can bypass it and focus more on the application code. The reality is, even in this NoSQL world, schema design is very important and if you don’t give it due care and attention, then it can come back to bite you.

If you have a RDBMS background and are new to Azure Table Storage, it’s common to find yourself “thinking in SQL” and trying to solve database modeling requirements with a SQL approach before then trying to translate that to a key-value mindset. In this blog post, I’ll cover some of the fundamentals of Azure Table Storage and dive into some common questions you might find yourself asking about Azure Table Storage. Where code samples or references are applicable in this blog post, we’ll be focusing on .NET and using the Azure SDK (specifically relating to the Microsoft.Azure.Cosmos.Table nuget package).

Read on for the full story.

Comments closed