Press "Enter" to skip to content

Day: October 8, 2020

Projecting Defensive Back Trajectories with Sagemaker

Lin Lee Cheong, et al, relay some interesting research:

NFL’s Next Gen Stats (NGS) powered by AWS accurately captures player and ball data in real time for every play and every NFL game—over 300 million data points per season—through the extensive use of sensors in players’ pads and the ball. With this rich set of tracking data, NGS uses AWS machine learning (ML) technology to uncover deeper insights and develop a better understanding of various aspects and trends of the game. To date, NGS metrics have focused on helping fans better appreciate and understand the offense and defense in gameplay through the application of advanced analytics, particularly in the passing game. Thanks to tracking data, it’s possible to quantify the difficulty of passes, model expected yards after catch, and determine the value of various play outcomes. A logical next step with this analytical information is to evaluate quarterback decision-making, such as whether the quarterback has considered all eligible receivers and evaluated tradeoffs accurately.

To effectively model quarterback decision-making, we considered a few key metrics—mainly the probability of different events occurring on a pass, and the value of said events. A pass can result in three outcomes: completion, incompletion, or interception. NGS has already created models that provide probabilities of these outcomes, but these events rely on information that’s available at only two points during the play: when the ball is thrown (termed as pass-forward), and when the ball arrives to a receiver (pass-arrived). Because of this, creating accurate probabilities requires modeling the trajectory of players between those two points in time.

For these probabilities, the quarterback’s decision is heavily influenced by the quality of defensive coverage on various receivers, because a receiver with a closely covered defender has a lower likelihood of pass completion compared to a receiver who is wide open due to blown coverage. Furthermore, defenders are inherently reactive to how the play progresses. Defenses move in completely different ways depending on which receiver is targeted on the pass. This means that a trajectory model for defenders has to similarly be reactive to the specified targeted receiver in a believable manner.

Click through for details on the study.

Comments closed

Building a CRUD Application with Cloudera Operational DB and Flask

Shlomi Tubul puts together a proof of concept app:

In this blog, I will demonstrate how COD can easily be used as a backend system to store data and images for a simple web application. To build this application, we will be using Phoenix, one of the underlying components of COD, along with Flask. For storing images, we will be using an HBase (Apache Phoenix backend storage) capability called MOB (medium objects). MOB allows us to read/write values from 100k-10MB quickly. 

*For development ease of use, you can also use the Phoenix query server instead of COD. The query server is a small build of phoenix that is meant for development purposes only, and data is deleted in each build. 

Click through for the demo and for a link to the GitHub repo.

Comments closed

Read Those Error Messages

Randolph West has a public service announcement:

My boss got upset with us one day on The Project From Hell. Tempers were frayed, tensions ran high, and other euphemisms were euphemisming. In short, we were all grumpy, and as expected on a project of this nature we kept making obvious mistakes and wasting our energy chasing our tails.

Obvious? Well, yes. It turns out that the answer to a particularly common issue we were running into was explained in the first line of the stack trace of the code that kept crashing. I’m not exaggerating for the sake of this story. The actual problem was explained in the first sentence of the error, in the very first line.

Microsoft products are fairly notorious about poorly-crafted error messages, but as Randolph mentions, often the solution is there if you take care to read the message.

Comments closed

Diving Into the Window Spool Operator

Hugo Kornelis continues a series on execution plan operators:

The Window Spool operator is one of the four spool operators that SQL Server supports. Like other spool operators, it retains a copy of data it receives and can then return those rows as often as needed. The specific functionality of the Window Spool operator allows it to replay rows within a window, as defined in a ROWS or RANGE specification of an OVER clause.

Read on to see how these work, as well as a few differences from their spool brethren.

Comments closed

Exporting Environment Variables from the SSIS Catalog

Koen Verbeeck shows how we can pull environment variables out from the SSIS catalog:

Sometimes when you’ve created a SSIS catalog, along with folders, environments and environment variables, you want this content on another server. Unfortunately, you can only script out these objects in SSMS at the moment you’re creating them (thus right before you clikc ‘OK’ to confirm). Once the objects are created, you can no longer script them out using the GUI.

Luckily, there’s a whole slew of stored procedures and views in the catalog that allow us to extract information and use that information to re-create the objects. Here are two scripts that I found useful.

Click through for the scripts.

Comments closed

Clone Logins and Users with dbatools

Jana Sattainathan takes us through a couple of DBA scenarios:

One of the more frequent requests that a SQL Server DBA receives is to “Clone a login” with all its permissions. For example a request could be

– Clone BILL_BLACK’s login and create a new login JACK_JOHNSON with exactly the same privileges in all databases.
– Clone AD group login BILLING_APP_ADMINS and create a login for new AD group BILLING_APP_CONSULTANTS with the same permissions as BILLING_APP_ADMINS in all databases

Read on to see how.

Comments closed

Azure SQL Championship

Mala Mahadevan announces a contest:

Learning can be drudgery, it can also be fun. One of the fun ways to learn Azure is to take part in Azure SQL Championship – a joint attempt by Microsoft and PASS to promote Azure learning. From October 12-30, there will be daily quizzes/simple challenges to solve. If you do it right you have a chance to win some fabulous prizes as below:

Read on to learn more, including the prizes on offer.

Comments closed

Making Use of Sort Rewinds: Closest Match

Paul White follows up on an article:

In When Do SQL Server Sorts Rewind? I described how most sorts can only rewind when they contain at most one row. The exception is in-memory sorts, which can rewind at most 500 rows and 16KB of data.

These are certainly tight restrictions, but we can still make use of them on occasion.

To illustrate, I am going reuse a demo Itzik Ben-Gan provided in part one of his Closest Match series, specifically solution 2 (modified value range and indexing).

Click through for the explanation.

Comments closed