Press "Enter" to skip to content

Month: July 2016

Diagnosing Virtual Machine Cloning Issues

Published 2016-07-07 by Kevin Feasel

Jack Li walks through a few common problems when creating Azure VMs based off of captured images:

When you create VM from a captured image, the drive letters for data disks may not preserved.  For example if you have system database files on E: drive, it may get swapped to H: drive.  If this is the case, SQL Server can’t find system database files and will not start.  If the driver letter mismatch occurs on user database files, then the user databases will not recover.   After VM is created, you just need to go to disk management to change the drive letters to match your original configuration.

Read the whole thing if you’re thinking about copying your on-premise server to an Azure VM.

Comments closed

Comments As Documentation

Published 2016-07-07 by Kevin Feasel

Chris Webb shows that comments in the Advanced Editor become step property tooltips:

The June release of Power BI Desktop has what seems to be a fairly unremarkable new feature in that it allows you to add descriptions to each step in a query in the Query Editor window. However the implementation turns out to be a lot more interesting than you might expect: the step descriptions become comments in the M code, and even better if you write M code in the Advanced Editor window your comments appear as descriptions in the Applied Steps pane.

I think this is a smart move, although it does mean that you have to keep those comments up to date…

Comments closed

Building Extension Methods With Biml

Published 2016-07-07 by Kevin Feasel

Ben Weissman shows how to write extension methods in Biml:

An AstTableNode requires a schema to be valid, which is the only information that we can’t get from the AstFlatFileFormatNode so we’re defining a variable called UseSchema and pass it to our ToAstTableNode extension method.

But… how does that extension method work? MUCH easier than you might think.

Writing an extension method in C# isn’t tough either.

Comments closed

Unpivoting With APPLY

Published 2016-07-07 by Kevin Feasel

I have a post on using the APPLY operator to unpivot data:

This code works, but if you have dozens of years, it gets messy writing those case statements and you’re a bit more likely to make a mistake when refactoring code. Here’s a simpler version using CROSS APPLY:

1
2
3
4
5
6
7
8
9
10
11
SELECT
    s.Product,
    y.[Year],
    y.Quantity,
    y.[Value]
FROM #Sales s
    CROSS APPLY (VALUES
        (2013, [Qty2013], [Val2013]),
        (2014, [Qty2014], [Val2014]),
        (2015, [Qty2015], [Val2015])
    ) y([Year], Quantity, [Value]);

It’s a little easier to read than the other version, and adding additional years is pretty straightforward.  That makes for a great tip when you’re trying to refactor poorly-thought-out tables or bring into your system potentially well-thought-out flat files.

APPLY is an elegant solution to so many different classes of problem.

Comments closed

Removing Spaces From Columns

Published 2016-07-07 by Kevin Feasel

Andy Leonard shows us how to use Biml to remove spaces from flat file column names:

This Biml declares an AstFlatFileFormatNode named ffformat and sets it to the FileFormat named “FFCM Provider Data” found the RootNode’s FileFormats collection. I next loop through each column in the ffformat AstFlatFileFormatNode object. I use Biml to generate the <Column> object, replacing the spaces with an empty string for the Name attribute. The results in Mist appear as shown below

I loved the “I had 5 minutes, so I decided to fix it with Biml” line.  Both funny and true.

Comments closed

Power BI KPIs

Published 2016-07-07 by Kevin Feasel

Patrick LeBlanc explains a confusing part of KPIs in Power BI:

Now, take a look at the KPI visual.  What happened?  First, you should notice a trend line on the KPI that depicts Sales Amount for each month.  This is cool and a great feature of the visual, but wait.  Why doesn’t the Indicator value and the goal match the values in the Card?  Now I see the confusion.

I appreciate that Patrick put in several embedded reports to show us exactly what’s going on.

Comments closed

Nested Views

Published 2016-07-07 by Kevin Feasel

Ginger Grant explains several problems with nested views:

In addition to the performance reasons, there are other reasons not to use nested views. Supporting and maintaining a nested views can be a nightmare. If there is an issue with the accuracy of the data, finding the problem is just that much harder. And what about when you go to fix the problem? Then you need to test all of the places the view is called. I worked with some code recently where a view was created on a set of data just to create 3 new values based up 3 case statements. That view was nested many levels below where the field was actually called. I know people from a development background look at nested views as modular development. Don’t. TSQL is not the same as object oriented code.

Read the whole thing.  She also has helpful links digging further into the topic.

Comments closed

SSDT Supports Always Encrypted

Published 2016-07-07 by Kevin Feasel

Jakub Szymaszek reports that you can now build SQL Server database projects which support Always Encrypted columns:

Always Encrypted uses two types of cryptographic keys: column encryption keys (CEKs) and column master keys (CMKs). A column encryption key is used to encrypt data in an encrypted column. A column master key is a key-protecting key that encrypts one or more column encryption keys. A column master key is stored in a key store that is external the database and is accessible to a client application, for example Windows Certificate Store or Azure Key Vault. The database server does not store the keys of either type in plaintext – it only contains metadata about the keys in column master key and column encryption key metadata objects.

Given that they’re supporting database projects, I wonder if Integration Services is far behind.

Comments closed

Forcing Predicate Pushdown

Published 2016-07-06 by Kevin Feasel

I have a blog post on some troubles I’ve had with the FORCE EXTERNALPUSHDOWN hint:

As soon as I kick this off, I get an error:

Msg 7320, Level 16, State 110, Line 1
Cannot execute the query “Remote Query” against OLE DB provider “SQLNCLI11” for linked server “(null)”. Query processor could not produce a query plan because of the hints defined in this query. Resubmit the query without specifying any hints.

Well, that’s not good…  Checking sys.dm_exec_compute_node_errors gives me four error rows with stack trace results, none of which seems very useful to me (as opposed to a Microsoft support tech).

I don’t have any good answers in this blog post, so I’m hoping to learn more and report back later.

Comments closed

SOS_WaitableAddress

Published 2016-07-06 by Kevin Feasel

Ewald Cress looks at the SOS_WaitableAddress class next in his series on internals:

All the synchronisation mechanisms I have discussed so far have one things in common: in order to be globally visible, they involve synchronisation objects embedded within the things they protect. So for instance, if we want to protect the global scheduler list with a spinlock, that spinlock lives within the global scheduler list, and allocating the spinlock’s storage (lightweight as it is) is the responsibility of whoever creates that list. But what if there were millions of things we might occasionally want to lock, and we don’t want to embed a lock within each such item due to the hassle and/or overhead involved? What if we just wanted a publicly visible corkboard upon which we can pin notes describing the items which are currently locked?

This is a rather different locking structure than we’ve seen so far.  Read on for details, including magic within the Signal method.

Comments closed