Press "Enter" to skip to content

Author: Kevin Feasel

Excel Data Cleansing

Published 2017-02-03 by Kevin Feasel

Cedric Charlier continues his series on fixing up an Excel file.  First up is turning results into an enumeration:

We’ve previously decided that a DON’T KNOW, shouldn’t influence our average of the answers. To apply this decision, we just need to filter the table Result and remove all the values equal to 0 (Enum value of DON’T KNOW). Then we calculate the average and subtract 1 to get a value between 0 and 4. Coooool, except that if we’ve no value non equal to 0, it will return -1 … not what we’re expecting. We’ll need to validate that the average is not null before subtracting 1.

The next post involves converting respondent information into a dimension:

In this table, only the results with a QuestionId equal to 111 really interest me for a merge with the existing table Respondent. If you’re familiar with the UI of Power BI Desktop then you’ll probably think to create a new table referencing this one then filter on QuestionId equals 111and finally merge. It’ll work but applying this strategy could result in many “temporary” tables. A lot of these small tables used only for a few steps before merging with other tables tend to be a nightmare for maintenance. You can use the “Advanced formula editor” to not display this kind of temporary tables and embed them in your main table.

Read on for more.

Comments closed

Viewing Partitions

Published 2017-02-02 by Kevin Feasel

Kendra Little has a query to view partitions:

This helps make sure that you’re designing your tables correctly, and it also helps you avoid goofs like merging the wrong boundary point and causing a bunch of data to move into another– which can be slow and painful.

All this information is available in TSQL, it’s just an ugly query, and it doesn’t come in any built-in reports or views.

So I’ve got an ugly query for you!

Having a script like this is very helpful if you use partitioning for anything.

Comments closed

Columnstore And Strings

Published 2017-02-02 by Kevin Feasel

Niko Neugebauer has a great knowledge dump regarding strings in columnstore indexes:

Having Strings in Fact tables is something that is quite normal, but to be honest, in the most cases – does not make a lot of sense, since we are trying to keep there the information that can be calculated and/or aggregated. Notice that I have written in the most cases and NOT in all cases, because there are some noticeable exceptions. Additionally if you are “feeding” SSAS Tabular with your table this might be much easier to do it directly (hey, there is a solution through the views for that, I was told :)).

In this blog post, I am focusing not on the exceptions but on the typical cases where its not the best option and so here is a basic solution I just wanted to present you an optimised structure, which contains a tinyint column referring to the new table with distinct data for the ShipMode.

The string experience with columnstore can be troublesome.  It’s great for numeric values, but less great for strings.

Comments closed

File Snapshot Backups

Published 2017-02-02 by Kevin Feasel

Raul Gonzalez digs into file snapshot backups in Azure:

One of the limitations for these ‘File Snapshot Backups’ (and probably the most important) is that all our databases files must be stored in the cloud, so we can take my previous post just as the preparation for what is coming now.

In order to move our files to the cloud we have different possibilities, one might be the typical approach where we’re allowed for some down time.

Check it out; you might want to give file snapshot backups a try.

Comments closed

Anti-Slicers In Power BI

Published 2017-02-02 by Kevin Feasel

Nicolo Grando shows how to create anti-slicers in Power BI:

A normal slicer can be tedious when you want to show everything apart from just one or two entries in your filtered tiles – don’t take your finger off the Ctrl key! You could always turn on Select All, then unselect the items. But you may not want Select All enabled, and it’s not available for chiclets. Or you could use Visual/Page/Report level filters, but these are not available in dashboards or publish-to-web. So you may be interested in an anti-slicer? There are many ways to do this, this is one approach.

This is an interesting problem to solve, and I think this is a nice solution.

Comments closed

Using Powershell To Shred Query Plan XML

Published 2017-02-02 by Kevin Feasel

Mike Fal shows how to use Powershell (or any .NET language) to read parts of a query plan:

Once the pattern is down, the use is pretty straightforward. There’s also more options accessible to you. If we just look at the RunTimeCountersPerThread node, we can compare other values such as Rows, Scans, and CPU time. We could really get crazy and extract all the different statements within the batch. There are numerous possibilities for analysis and review.

I’m not here to tell you that you should start using PowerShell to automate query tuning. Query performance is an art form and requires a lot of case-by-case analysis. However, like any great carpenter, it’s good to know the capabilities of your tool set. Understanding the options available to you not only helps you be more effective, but can also provide answers you may not have had access to.

It’s another tool for the belt.

Comments closed

SQL Server VMs In Google Compute Engine

Published 2017-02-02 by Kevin Feasel

Brent Ozar reports on Google cloud improvements:

Google Compute Engine is infrastructure-as-a-service (IaaS), selling virtual machines by the hour like Azure VMs and AWS EC2. You can run whatever you like in these VMs, and Google has long supported running SQL Server in GCE. You could build your own SQL Servers, or use pre-built (and licensed) instances of SQL Server 2012, 2014, or 2016 – but only Standard or Web Editions.

Today, GCE supports Enterprise Edition AND Always On Availability Groups.

We’ve got a white paper coming soon on how to build and test it, plus more cool stuff in the pipeline that DBAs will love.

We live in interesting times.

Comments closed

SQL On Linux Connectivity

Published 2017-02-01 by Kevin Feasel

Slava Murygin troubleshoots connectivity issues with SQL Server on Linux:

After you enter SA password you have to get “vNext” version of your SQL Server.
If you did not get the correct response you might have following problems:
I)   Wrong SA password. To fix it, just re-configure SQL Server.
II)  SQL Server Tools are not installed.
III) Typo. Check your syntax.

The troubleshooting process is a bit different from SQL Server on Windows, but it’s still pretty straightforward.

Comments closed