Press "Enter" to skip to content

Day: April 26, 2016

Powershell + Bash

Published 2016-04-26 by Kevin Feasel

Max Trinidad shows how to integrate Powershell with Bash in Windows 10:

If it worth knowing that in order to get Bash, it’s a feature you need to installed it first. The following is the series of steps I use to enabled and install Bash on my desktop. And, after enabling Bash, I started using it under the PowerShell Console.

Apropos of this, I read a very interesting article by Alex Clemmer yesterday on how terrible the Windows command line is.  Powershell and Bash are way, way better for pretty much any purpose, other than perhaps experiencing masochism.

Comments closed

BimlExpress

Published 2016-04-26 by Kevin Feasel

Catherine Wilhelmsen introduces us to BimlExpress:

BimlExpress is a free Visual Studio add-in for working with Biml in your SSIS projects. It allows you to add and edit Biml files, generate SSIS packages from Biml, and the code editor is fully featured with syntax coloring, error highlighting and intellisense.

If you are already using BIDS Helper, you will see that BimlExpress works the same way and includes all the same Biml features as in BIDS Helper – just with a new and improved code editor. No more squiggly red lines, yay!

For normal Biml purposes, I see some benefit.  But to me, the biggest benefit is when presenting:  a common question which comes up during Biml talks is, “Why does Intellisense say you have so many errors?”  A superior code editor is well worth the download.

Comments closed

Transforming Cursors

Published 2016-04-26 by Kevin Feasel

Mickey Stuewe has a post in which she transforms a cursor into a set-based procedure:

His approach was to use a cursor to cycle through all the columns in the provided table, analyze each column, determine the new data type, and store the information in a table variable. After the cursor was completed, the data in the table variable was written to a permanent table for the next process to use.

This approach isn’t necessarily bad. If you are only running it infrequently and you needed to write this stored procedure quickly, then it’s fine. But if this type of stored procedure needs to be run frequently, then it should be rewritten.

Set-based code tends to be easier to read and more compact than cursors, so even without the performance improvements they bring, there are benefits.

Comments closed

DISTINCT Windows

Published 2016-04-26 by Kevin Feasel

Daniel Hutmacher looks at ways to get windows of distinct elements from a table:

Probably the most common distinct aggregate is COUNT(DISTINCT). In some respects, it’s similar to the windowed function DENSE_RANK(). DENSE_RANK(), as opposed to ROW_NUMBER(), will only increment the row counter when the ordering column(s) actually change from one row to the next, meaning that we can use DENSE_RANK() as a form of windowed distinct count

This is a very interesting approach to the problem.  Read the whole thing.

Comments closed

Power BI: Dynamic Chart Titles

Published 2016-04-26 by Kevin Feasel

Chris Webb shows how to generate dynamic chart titles using Power BI:

But what about if you want the chart title to change depending on what is selected? For example, you might be using slicers or filters to allow a user to choose which days of the week they want to see data for. In that situation you might want to add a title that shows which days of the week have actually been selected; this would be particularly important if the report uses filters, or if the report is going to be printed. Unfortunately the built in Title Text property can’t be used to display dynamic values but in this blog post I’ll show you how to solve this problem using DAX.

The solution isn’t trivial, but it is cool.

Comments closed

“Broken” Left Joins

Published 2016-04-26 by Kevin Feasel

James Anderson reminds you to check those WHERE clauses:

We have said that a NULL value for s.DateOfSale is not in the range we are interested in. This means the rows with NULLs in the s.DateOfSale column (our employees yet to make a sale) will be filtered out. It will also filter out employees with sales in months other than March. We have converted the LEFT JOIN into an INNER JOIN.

James’s fix is to move the filter to the join clause, which eliminates the implicit inner join.  When I see a condition like this in a code review, the first question on my mind is whether the correct fix is James’s fix or whether the developer really meant to do an inner join.  There’s a potential performance gain from using an inner join over a left outer join (due to being able to drive from either table and thus having a larger number of potential execution plans) if it turns out you really do want to filter all rows and not just making the join criterion more specific.

Comments closed

Clustered Columnstore Index Load With SSIS

Published 2016-04-26 by Kevin Feasel

Koen Verbeeck looks at loading a clustered columnstore index using SSIS:

I stumbled upon this MSDN blog post: SQL Server 2016 SSIS Data Flow Buffer Auto Sizing capability benefits data loading on Clustered Columnstore tables (catchy title). It explains how you can set the buffer properties of the data flow to try to insert data directly into compressed row groups instead of in the delta store. They fail to achieve this using SSIS 2014 and then they explain how using the new AutoAdjustBufferSize property of SSIS 2016 works miracles and everything is loaded directly into compressed row groups. Hint: you want to avoid loading data into the delta store, as it is row storage and you need to wait for the  tuple mover to load the data to the CCI in the background.

However, it’s still possible to achieve the same using SSIS 2014 (or earlier). Niko Neugebauer (blog |twitter) shows this in his post Clustered Columnstore Indexes – part 51 (“SSIS, DataFlow & Max Buffer Memory”). It still depends on the estimated row size, but using these settings you should get better results:

This advice is a bit different from loading standard rowstore-based tables, but serves to pack as many rows into each columnstore row group as possible.

Comments closed