Press "Enter" to skip to content

Month: March 2018

Getting Started With ssisUnit

Published 2018-03-29 by Kevin Feasel

Bartosz Ratajczyk builds a few SQL Server Integration Services unit tests with ssisUnit:

The result shows 1 test run, 1 test passed, 2 asserts run, 2 asserts passed.

Wait, what? We have prepared only one assert, why does it show two?

The second assert is: “Task Completed: Actual result (Success) was equal to the expected result (Success).“. Great. Where does it come from? Let’s find out.

This is a nice introduction to the topic; if you fuss about with SSIS packages, you should check this out.

Comments closed

Adding KPI Indicators To Power BI

Published 2018-03-29 by Kevin Feasel

Paul Turley goes on a rant:

Makes perfect sense, right?  This is a basic use case and a good application for simple KPIs; with the one minor caveat that POWER BI DOESN’T SUPPORT THIS!

This topic has become a bit of a soapbox topic for me because it’s a capability that, in my opinion, is a very obvious gap in the Power BI feature set.  After unleashing my rant, I’ll demonstrate a solution a little further down in this post.

Both the rant and the workaround are interesting enough to read, so check them out.

Comments closed

Showing Dataset Parameters In Power BI Reports

Published 2018-03-29 by Kevin Feasel

Wolfgang Strasser shows how to display the parameters selected for a Power BI report on the report itself:

Dataset Parameters are a way to bring some sort of dynamic into your Power Query datamodelling in Power BI. For my blog post about  Modifying Parameter values in powerbi.com  I was looking for a way to display the value of a parameter inside a Power BI report.

In this blog post I would like to show you the steps that are required to bring your parameters to your field list  – and as a consequence – into your Power BI report.

Read on to see how to do this.

Comments closed

SQL Operations Studio March Release

Published 2018-03-29 by Kevin Feasel

Alan Yu announces a new version of SQL Operations Studio:

Take a moment to breathe, this is a loaded update.

The March Public Preview release is focused on improving our Extensibility story and continuing to address top GitHub issues. This includes enabling Extension Manager, improving the Manage Dashboard experience and providing a couple Insights extensions. Please see the following details.

  • Enhance the Manage Dashboard extensibility model to support tabbed Insights and Configuration panes

  • Dashboard Insights extensions for sp_whoisactive from whoisactive.com and a Server Reports example

  • Extension Manager enables simple acquisition of 1st-party and 3rd-party extensions

  • Add additional Extensibility APIs for connection and objectexplorer management

  • Community Localization open for 10 languages

  • Continue to fix important customer impacting GitHub issues

There’s some nice stuff in this release, but the big story is around extensibility.

Comments closed

Using DATETIMEOFFSET

Published 2018-03-29 by Kevin Feasel

Randolph West continues his date and time data type series:

DATETIMEOFFSET works the same way as the DATETIME2 data type, except that it is also time zone aware. It is formatted as 'YYYY-MM-DD HH:mm:ss[.nnnnnnn][{+|-}hh:mm]'.

Got all that? YYYY represents a four-digit year, MM is a two-digit month between 1 and 12, DD is a two-digit day between 1 and 31 depending on the month, HH represents a two-digit hour between 0 and 23, mm is the minutes between 0 and 59, while ss is the number of seconds between 0 and 59. Once again, n represents between zero and seven decimal places in a fraction of a second.

The main difference from DATETIME2 is the time zone offset at the end, which is the number of hours and minutes as an offset from UTC time.

Read on for more.  I generally don’t use this date type much, preferring to stick with DATETIME2 and saving data as UTC.

Comments closed

Why Does Empirical Variance Use n-1 Instead Of n?

Published 2018-03-28 by Kevin Feasel

Sebastian Sauer gives us a simulation showing why we use n-1 instead of n as the denominator when calculating the variance of a sample:

Our results show that the variance of the sample is smaller than the empirical variance; however even the empirical variance too is a little too small compared with the population variance (which is 1). Note that sample size was n=10 in each draw of the simulation. With sample size increasing, both should get closer to the “real” (population) sample size (although the bias is negligible for the empirical variance). Let’s check that.

This is an R-heavy post and does a great job of showing that it’s necessary, and ends with  recommended reading if you want to understand the why.

Comments closed

Testing Disk Speed With diskspd

Published 2018-03-28 by Kevin Feasel

Marek Masko shows how to test I/O performance using the diskspd utility:

What is Diskspd?

Diskspd is a storage testing tool created by Microsoft Windows, Windows Server and Cloud Server Infrastructure Engineering teams. It combines robust and granular IO workload definition with flexible runtime and output options. That makes it a perfect tool for storage performance testing, validation and benchmarking.

Where to find Diskspd?

Diskspd is a free and open source utility. Its source code can be found on GitHub. The repository also hosts other frameworks which use Diskspd. You can find them under ‘Frameworks’ directory. A binary release is hosted by Microsoft at the following location: http://aka.ms/diskspd.

Click through for more details, including an example of a poorly-performing I/O solution.

Comments closed

Finding SQL Server Instances With dbatools

Published 2018-03-28 by Kevin Feasel

Chrissy LeMaire shows off a very helpful command in dbatools:

Nearly every time I inherit a SQL Server environment, I’m only given a partial list of SQL Servers that exist on the network. It’s my usual routine to get permission to sniff the network then run about five different programs including Idera’s SQL Discovery and Microsoft’s SQL Server Assessment and Planning Toolkit.

I always thought it’d be cool to have one comprehensive PowerShell command that could do the work of all the above and was ecstatic to see NetSPI’s Scott Sutherland had written a few commands to do just that in his awesome PowerShell module PowerUpSQL.

When I saw Scott’s multi-pronged approach (including some UDP magic 🎩), I asked if he’d be interested in contributing to dbatools and he said yes! He submitted a gorgeous mock-up and I was so excited. Then came the PR, complete with great documentation and multithreading.

Click through for a lot more information on the command.

Comments closed

Resuming Azure SQL Data Warehouse With Powershell

Published 2018-03-28 by Kevin Feasel

Arun Sirpal shows how to unpause an Azure SQL Data Warehouse instance using Powershell:

I totally forgot that with Azure SQL DWH you can pause and resume compute, to save money because it is expensive. Question is how do you go about resuming compute? TSQL is not possible and sure you can do the change via Azure portal but what about PowerShell?

This makes it easy to script out an overnight data load and then pausing the Azure Data Warehouse until the morning when those analysts come in, so that you can save a bit of cash (or a lot, depending upon your DWU utilization).

Comments closed

Comparing Distinctness

Published 2018-03-28 by Kevin Feasel

Michael J. Swart shows several options for comparing whether an attribute’s value is distinct from a parameter:

Check it:

DECLARE @TeamId bigint = NULL,
    @SubTeamId bigint = NULL;
 
SELECT TOP 1 TaskId
FROM tasks
WHERE assignedTeamId IS NOT DISTINCT FROM @TeamId
  AND assignedSubTeamId IS NOT DISTINCT FROM @SubTeamId

Talk about elegant! That’s what we wanted from the beginning. It’s part of ANSI’s SQL 1999 standard. Paul White tells us it’s implemented internally as part of the query processor, but it’s not part of T-SQL! There’s a connect item for it… err. Or whatever they’re calling it these days. Go read all the comments and then give it a vote. There are lots of examples of problems that this feature would solve.

PROS: Super-elegant!
CONS: Invalid syntax (vote to have it included).

This would be nice to have.  In the meantime, Michael shows several options which are currently valid syntax.

Comments closed