Press "Enter" to skip to content

Author: Kevin Feasel

The structure() Function in R

Published 2019-05-28 by Kevin Feasel

Tomaz Kastrun takes us through the structure() function in R:

Structure() function is a simple, yet powerful function that describes a given object with given attributes. It is part of base R language library, so there is no need to load any additional library. And also, since the function was part of S-Language, it is in the base library from the earlier versions, making it backward or forward compatible.

Read on to see how you can create a matrix or data frame using this function and additional details you can save.

Comments closed

Self-Joins Versus Key Lookups

Published 2019-05-28 by Kevin Feasel

Erik Darling takes us through an interesting scenario:

Like most tricks, this has a specific use case, but can be quite effective when you spot it.

I’m going to assume you have a vague understanding of parameter sniffing with stored procedures going into this. If you don’t, the post may not make a lot of sense.

Or, heck, maybe it’ll give you a vague understanding of parameter sniffing in stored procedures.

This was new to me.

Comments closed

Power BI Performance Analyzer

Published 2019-05-28 by Kevin Feasel

Marco Russo takes us through the Power BI Performance Analyzer:

The Power BI Performance Analyzer is a feature included in the May 2019 release of Power BI Desktop that simplifies the way you can collect the DAX queries generated by Power BI. You can use DAX Studio to capture them (as described in Capturing Power BI queries using DAX Studio), but the Performance Analyzer integrated in Power BI is simpler and provides a few insights about the time consumed in other activities, such as the rendering time of any visuals.

You can enable the Power BI Performance Analyzer by clicking the Performance Analyzer checkbox in the View ribbon of Power BI Desktop.

Read the whole thing.

Comments closed

Constructing Virtual Tables with VALUES

Published 2019-05-28 by Kevin Feasel

Kenneth Fisher shows how to use the VALUES clause to construct a virtual table:

This has come up a few times recently, I find it rather fascinating and I can never seem to remember how to do it properly .. so in other words, it’s a perfect subject for a blog post.

Basically, you can use VALUES to create a table within a query. I’ve seen it done in a number of places. Mostly when demoing something and you want some data, but don’t want to actually create a table. I’ve also seen it used to create a numbers table, create test data, etc. Really, any case where you want a short list of values but don’t want to create an actual (or even temp) table to store them.

Click through for examples on how to construct and use this virtual table as a quick replacement for creating a temporary table or table variable.

Comments closed

Customizing a Docker Container

Published 2019-05-28 by Kevin Feasel

Grant Fritchey shows how you can take a Docker container and save modifications:

There are much more sophisticated ways to get this done using Docker Files. However, this illustrates the point quite simply. You can customize your servers and then use those customizations. You don’t have to re-customize every time. Again, this is just a small slice of why containers are so powerful.

This method is great when you want to build out a sample data set, like when you’re running through automated testing and want to start from the same known point each time.

Comments closed

Max Dispatch Latency in Extended Events

Published 2019-05-28 by Kevin Feasel

Dave Bland takes us through the Max_Dispatch_Latency property for an Extended Event:

You would logically think that the minimum setting would be zero seconds.  If you think that way, you are correct.  However, 0 does not mean 0 seconds.  When this is set to 0 it means the event will stay in the buffer until the buffer becomes full.  This is the same set setting the “Unlimited” option you see below. Given this, the true minimum is one second.

Read on to see what it does and why it can be important.

Comments closed

SQL Server Settings Blade in Azure

Published 2019-05-28 by Kevin Feasel

Dave Bermingham notes a recent change to the Azure Portal when creating a new VM with SQL Server pre-installed:

As you slide the IOPS slider to the right you will see the number of data disks increase, the Storage Size increase, and the Throughput increase. You will be limited to the max number of IOPS and disks supported by that instance size. You see in the screenshot below I am able to go as high as 80,000 IOPS when provisioning storage for a Standard E64-16s_v3 instance.

It sounds like they did a pretty good job of things there.

Comments closed

Dynamic Top N in Power BI

Published 2019-05-27 by Kevin Feasel

Gerhard Brueckl shows how to create a Top N slicer in Power BI, as well as some of the problems you might need to work through:

As I said, this pretty much depends on the business requirements and after discussing that in length with the users, the solution is usually to simply add an “Others” row that sums up all values which are not part of the TopN items. For regular business users this requirement sounds really trivial because in Excel the could just add a new row and subtract the values of the TopN items from the Grand Total.

However, they usually will not understand the complexity behind this requirement for Power BI. In Power BI we cannot simply add a new “Others” row on the fly. It has to be part of the data model and as the TopN calculations is already dynamic, also the calculation for “Others” has to be dynamic. As you probably expected, also this has been covered already:
Oraylis – Show TopN and rest in Power BI
Power BI community – Dynamic Top N and Others category

This is a pain point that ideally I’d like the Power BI team to address. Gerhard does a good job showing how to do it, but Tableau has that (and more) built in.

Comments closed

Memory-Optimized TempDB Metadata Tables

Published 2019-05-27 by Kevin Feasel

Ned Otter shows how to configure SQL Server to use memory-optimized metadata tables in TempDB:

Like other changes to TempDB, in order for the new memory-optimization to take effect a restart of the SQL Server service is required. Once the service is restarted, system tables in TempDB are now memory-optimized (it should be that way for RTM, but in CTP 3.0, it could be the case that not all system tables have been converted to Hekaton). You can reverse this setting with the following command, and again restarting the SQL Server service:

There are a couple of important notes that Ned gives us around accessing these metadata tables in scripts.

Comments closed