Press "Enter" to skip to content

Day: April 12, 2021

Predicting Insurance Prices with ML.NET

Published 2021-04-12 by Kevin Feasel

Chandra Kudumula shows off ML.NET:

There are three ways to begin with ML.NET

– API Model: You can start ML.NET through a Framework API and write code in C# or F#
– GUI Model: Use ML.NET Model builder in Visual Studio.
– CLI Model: For cross-platform development like Mac and Linux, use ML.NET CLI.

Let’s get started with API Model for predicting the insurance premium using ML.NET Framework.

I’m using Microsoft (MS) Visual Studio 2019 and creating a Console Application. Be sure that you have the latest version of VS and that .NET 5 SDK is installed.

Click through for the demo in Visual Studio using C#.

Comments closed

Simulating Prediction Intervals

Published 2021-04-12 by Kevin Feasel

Bryan Shalloway continues a series:

Part 1 of my series of posts on building prediction intervals used data held-out from model training to evaluate the characteristics of prediction intervals. In this post I will use hold-out data to estimate the width of the prediction intervals directly. Doing such can provide more reasonable and flexible intervals compared to analytic approaches.

Click through for the article, and be sure to check out part 1 if you haven’t already.

Comments closed

Bulk Copying Lots of Rows into SQL Server

Published 2021-04-12 by Kevin Feasel

Esat Erkec shows us how to use the Bulk Copy Program (BCP) to bulk load data into SQL Server:

If the installed version is older than the last version, we can download and install the latest version from the Microsoft website. The main capability of the SQL Server BCP is not much complex because it can only run with several arguments. The syntax of the BCP is like below:

bcp {table|view|”query”} {out|queryout|in|format} {data_file|nul} {[optional_argument]…}

For example, if we want to export any data of a table to a text file, we have to specify the table name, the out option, and the data file. The following command will export the Production table into the specified text file.

bcp AdventureWorks2017.Production.Product out C:\ExportedData\Product.txt -S localhost -T –w

I don’t know if I’m the only person for which this is true, but the data file format has always been a royal pain for me to get right, to the point where I’d happily build an SSIS package to perform bulk loading over having to use BCP myself.

2 Comments

Finding and Clearing the Recycle Bin with Powershell

Published 2021-04-12 by Kevin Feasel

Jack Vamvas answers a question:

I use Powershell extensively to manage SQL Server and the Windows OS. A common problem is to identify location of the  Windows Recycle Bin and clear the contents down – particuarly if there is a space issue. 

How can I locate the Windows Recycle Bin and clear it down?

Read on for the Powershell v5 solution as well as the solution which works for earlier versions.

Comments closed

Equalizing Matrix Column Widths in Power BI

Published 2021-04-12 by Kevin Feasel

Nick Edwards has bad news and a workaround:

Have you ever come across an issue where your Power BI matrix column widths just aren’t the same width and visually just don’t look right?

Unfortunately (as of April 21’) there is no easy way to make all column widths equal in the format pane of a matrix visual.

However there is a hack to set the width of your all columns in a matrix so that they are all equal and pixel perfect with DAX!

Click through to see how. It’s a hack in all the finest ways: clever, unexpected, and a bit unwieldy.

Comments closed

Semi-Join Plan Weirdness

Published 2021-04-12 by Kevin Feasel

Erik Darling has an interesting scenario for us:

This post isn’t meant to dissuade you from using EXISTS or NOT EXISTS when writing queries. In fact, most of the time I think they make a lot of sense.

But weird things can happen along the way, especially if you don’t have supporting indexes, or if supporting indexes aren’t chosen by the optimizer for various reasons.

In this post, I’m going to show you a query plan pattern that can occur in semi-join plans, and what you can do about it.

Click through for the problem and the solution. Me? I don’t like semi-joins on principle. Either join or don’t join; give me none of these cowardly half-measures. I’m not sure what to think about anti-semi-joins because I’m apparently anti semi-join for the purposes of this belabored joke, but I’m a bit suspicious of them as well.

Comments closed

Page and Data Row Structure in SQL Server

Published 2021-04-12 by Kevin Feasel

Deepthi Goguri digs into data page internals:

Each byte you see in the picture has a purpose. The first two blocks containing a byte, Status Bit A and Status Bit B contains the bitmap information about the row, like if the row is logically been deleted/ghosted, row type information, versioning tag, if the row contains any NULL values, Variable length columns. The next 2 bytes is used for storing the length of the fixed length data. The next n bytes are for storing the fixed length data itself. There is a null bitmap after that which will have both the 2-byte column count in the row and null bitmap array. Regardless of if the column in null or not, each and every column will have one bit per every column.

Read on to see how those 8kb pages fill up so quickly.

Comments closed

Securing the sa Login

Published 2021-04-12 by Kevin Feasel

Chad Callihan has a few tips for making that sa login safer:

Every SQL Server install includes the sa login as a sysadmin. This can be good for consistency; however, that also makes it a prime target for attackers trying to get into your SQL Server. That is one of many reasons why you should make the following changes to protect your sa login from being used in an attack.

Click through for the list. Troll me says the best answer is to rename sa (like Chad mentions), create a new account called sa, and write a script to add a rule to your firewall banning the IP of anybody who logs in with this account.

Comments closed