Press "Enter" to skip to content

Curated SQL Posts

Getting Started with Azure Synapse Analytics

Published 2020-10-23 by Kevin Feasel

John Macintyre shares some Azure Synapse Analytics samples:

To further accelerate time to insight in Microsoft Azure Synapse Analytics, we are introducing the Knowledge center to simplify access to pre-loaded sample data and to streamline the getting started process for data professionals. You can now create or use existing Spark and SQL pools, connect to and query Azure Open Datasets, load sample scripts and notebooks, access pipeline templates, and tour the Azure Synapse Studio—all from one place.

Click through for details on the samples.

Comments closed

A New Financial Sample Dataset with Power BI

Published 2020-10-23 by Kevin Feasel

David Eldersveld takes a look at a new sample data set in Power BI Desktop:

With the October 2020 release of Power BI Desktop, Microsoft has incorporated a sample dataset directly into the product. While there are a few commonly used training and demo datasets already, there are benefits to having a basic starter Financials dataset included.

This data can accelerate the learning experience for new users, particularly those who may only need to focus on authoring reports on existing datasets at their organization and don’t need to immediately know much about data modeling.

Click through for David’s mixed review.

Comments closed

Finding Row Totals in Powershell

Published 2020-10-23 by Kevin Feasel

Shane O’Neill needs a better data model:

Once you have the total per row, you throw in a SUM(that total) OVER () and you have a grand total. Thank you to Kevin Wilkie ( blog | twitter ) for re-igniting my curiosity about Window Functions again.

Click through to see a result in SQL Server and then trying to replicate it in Powershell. It’d be a lot easier, though, with a properly normalized data model which includes date, attempt number, and push-ups in that attempt. Pivot those results at the end if you want this sort of report, but SQL is designed to work best with tables in first normal form or higher.

Comments closed

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