Press "Enter" to skip to content

Day: February 9, 2016

Credit Card Fraud Detection Using R

Published 2016-02-09 by Kevin Feasel

David Smith gives us a tutorial on credit card fraud detection:

If you have a database of credit-card transactions with a small percentage tagged as fraudulent, how can you create a process that automatically flags likely fraudulent transactions in the future? That’s the premise behind the latest Data Science Deep Dive on MSDN. This tutorial provides a step by step to using the R language and the big-data statistical models of the RevoScaleR package of SQL Server 2016 R Services to build and use a predictive model to detect fraud.

This looks to be a follow-up from the fraud detection series.

Comments closed

Plan Cache Spelunking

Published 2016-02-09 by Kevin Feasel

Ed Pollack digs into the plan cache:

The data in the plan cache is not static, and will change over time. Execution plans, along with their associated query and resource metrics will remain in memory for as long as they are deemed relevant. Plans can be removed from cache when there is memory pressure, when they age out (they get stale), or when a new plan is created, rendering the old one obsolete. Keep this in mind as we are looking around: The info we find in the plan cache is transient and indicative of a server’s current and recent activity and does not reflect a long-term history. As a result, be sure to do thorough research on the plan cache prior to making any significant decisions based on that data.

The plan cache is one of the best ways of figuring out what’s going on in your SQL Server instances, but there’s a little bit of complexity to it.

Comments closed

Sorting Power Pivot Data On Load

Published 2016-02-09 by Kevin Feasel

Matt Allington suggests pre-sorting results to reduce load in Power Pivot:

Imagine you have 50,000 products in your data table and you have 50,000,000 rows of data.  Power Pivot will take the first 1 million rows it comes to (1 segment worth), work out how to sort and compress the columns, and then compress the data into a single segment before moving to the next 1 million rows it comes to (in the order they are loaded).  When it does this, it is highly likely that every product number will appear in every single segment – all 50 segments.  If we assume an equal number of product records for each product (unlikely but OK for this discussion), then there would be 1,000 records for each product spread throughout the entire data table,and each and every segment is likely to contain all 50,000 product IDs.  This is not good for compression.

This is an interesting result and not something I would have thought intuitive.

Comments closed

Understanding Logins Versus Users

Published 2016-02-09 by Kevin Feasel

Kenneth Fisher has a user which should have rights but is unable to access the database in question:

The other day I ran across an interesting problem. A user was logging in but didn’t have access to a database they were certain they used to access to. We checked and there they were. Not only was there a database principal (a user) but it was a member of db_owner. But still no go. The user could not connect. I went to the database and impersonated them and then checked sys.fn_my_permissions. They were definitely a member of db_owner. I tested and yes, I could read the tables they needed, and yes they could execute the stored procedures they needed to execute. So what was wrong?

Keep those principals in alignment.

Comments closed

Dynamic Data Masking In Detail

Published 2016-02-09 by Kevin Feasel

Louis Davidson has a two-part series on dynamic data masking in SQL Server 2016.

Part 1:

An interesting feature that is being added to SQL Server 2016 is Dynamic Data Masking. What it does is, allow you to show a user a column, but instead of showing them the actual data, it masks it from their view. Like if you have a table that has email addresses, you might want to mask the data so most users can’t see the actual data when they are querying the data. It falls under the head of security features in Books Online (https://msdn.microsoft.com/en-us/library/mt130841.aspx), but as we will see, it doesn’t behave like classic security features, as you will be adding some code to the DDL of the table, and (as of this writing in CTP3.2, the ability to fine tune who can and cannot see unmasked data isn’t really there.)

Part 2:

The moral here is that need to be careful about how you use this feature. It is not as strict as column level security (or as Row Level Security will turn out to be, which is the next series of blogs to follow), so if a user has ad-hoc access to your db, they could figure out the data with some simple queries.

Louis’s second part is particularly interesting, as he delves into the various ways in which you can back into answers (some of which, like casting values to other types, have been fixed).

Comments closed