Press "Enter" to skip to content

Author: Kevin Feasel

Semantic Search in SQL Server

Published 2020-08-24 by Kevin Feasel

Haroon Ashraf wraps up a series:

Being the final part of the article, it is going to take you to the next level of analyzing word documents stored in Windows folders, managed by File Table, and consumed by Semantic Search.

Additionally, the readers are going to gain more understanding of Semantic Search and how to make it work with MS Word documents for analysis.

This article provides a name-based analysis of the documents with equal attention to both theory and practice.

Click through for the culmination of all of this filestream work.

Comments closed

ADF Data Flows and Joins Failing During Debugging

Published 2020-08-20 by Kevin Feasel

Mark Kromer clears up some issues around debugging in Azure Data Factory:

One of the important features built into ADF is the ability to quickly preview your data while designing your data flows and to execute the finished product against a sampling of data prior to finalizing and operationalizing your pipelines.

However, there are a few fundamentals relative to working with Joins that you should keep in mind and a few details below are important to understand at design time and while debugging / testing.

The answer makes sense but it would not have been the first thing to come to mind for me.

Comments closed

Using Perspectives in Power BI

Published 2020-08-20 by Kevin Feasel

Mark Lelijveld walks us through something new in Power BI Desktop’s August 2020 update:

If you work or used to work with Analysis Services, you might know the perspectives functionality. It is a feature inside tabular modelling that allows you to define viewable subsets of a data model.

Each tabular model can include multiple perspectives, where each perspective can include a subset of tables, columns a measures. Especially with large enterprise models, perspectives can be very useful.

With perspectives, you can define specific perspectives to be defines for a specific target audience. For example, the author can create logical subsets of the model for each audience of the dataset. (e.g. Sales, Finance, Marketing, etc.) One thing must clear, perspectives are not object level security or any other kind of security! It is just a better way to view it.

Read on to see how you can create and work with these in Power BI Desktop.

Comments closed

When Altering a Table Blocks the Log Reader

Published 2020-08-20 by Kevin Feasel

Aaron Bertrand walks us through a painful scenario:

We recently performed a DDL operation against a SQL Server table – simply increasing the size of a varchar column – which should have been instantaneous. Instead, we killed the SQL Server process after observing 20 minutes of HARD_SYNC_COMMIT waits and a blocked replication log reader. Could this issue have been avoided? What went wrong?

I spotted the issue pretty quickly, but it’s easy to miss in a code review. Read the whole thing.

Comments closed

Using the Ring Buffer for Monitoring

Published 2020-08-20 by Kevin Feasel

Eitan Blumin uses Extended Events to track activity:

Extended events provides a solution similar to client side trace. It basically can capture all events that a trace can capture (and more), and it also supports a wider choice of target types. And that, is where its true power lies.

It just so happens that Extended Events has a target type called “ring buffer“, and it gives us exactly what we need.

The ring buffer is easy to set up and if you don’t need permanence, works great.

Comments closed

Fun with Benford’s Law

Published 2020-08-19 by Kevin Feasel

Nagdev Amruthnath covers a topic which brings me joy:

Benford’s Law is one of the most underrated and widely used techniques that are commonly used in various applications. United States IRS neither confirms nor denies their use of Benford’s law to detect any number of manipulations in income tax filing. Across the Atlantic, the EU is very open and proudly claims its use of Benford’s law. Today, this is widely used in accounting to detect any fraud. Nigrini, a professor at the University of Cape Town, also used this law to identify financial discrepancies in Enron’s financial statement. In another case, Jennifer Golbeck, a professor at the University of Maryland, was able to identify bot accounts on twitter using Benford’s law. Xiaoyu Wang from the University of Winnipeg even published a report on how to use Benford’s law on images. In the rest of this article, we will take about Benford’s law and how it can be applied using R.

The applications to images and music were new to me. Very cool. H/T R-Bloggers

Comments closed

The Row Count Spool Operator

Published 2020-08-19 by Kevin Feasel

Hugo Kornelis dives into another operator:

The Row Count Spool operator is one of the four spool operators that SQL Server supports. It counts the number of rows in its input data, and can then later return that same amount of rows, without having to call its child operators to produce the input again.

The Row Count Spool can be viewed as similar to Table Spool, but optimized for cases where the amount of rows is relevant but their content is not. Because the content of the rows is not relevant, the operator does not need to use tempdb to store its input in a worktable; it only has to keep a running count as it reads the input. The other two spool operators have different use cases: Index Spool is used to enable the spool operator to return specific subsets of the input multiple times, and the Window Spool operator is used to support the ROWS and RANGE specifications of windowing functions.

Read on to see where this might be useful and when it may appear.

Comments closed

Managing User Input and Creating Menus in Powershell

Published 2020-08-19 by Kevin Feasel

Mark Wilkinson wants a bit of user interaction:

Fully automated hands-off PowerShell scripts can be extermely useful for the DBA or System Administrator, but what if you need to get input from the user, or maybe you want to implement a menu system? Like most things related to PowerShell, you have a few options:

– Read-Host
– [Console] object methods

Most use cases are covered by Read-Host, but if you need something a little more flexible, the [Console] methods might be the way to go.

Read on to see how these work, as well as one way to create a menu.

Comments closed