Why Getting Data Right Matters

An InfoWorld article from 2017 suggests that 80 percent of a data scientist’s job is cleaning and transforming data, and I believe this is probably only true for organizations that spend at least an average effort in designing and implementing their data storage. These persons who have trained to analyze data using complex math formulas could be spending their time producing important insights into how to make their company more profitable. Yet instead, assuming they don't have other meetings and administrative tasks during a week, they are spending on average 32 hours each week mucking through data trying to figure out what data is useful, what it means, and then reorganizing it for analyzing. They have only eight hours a week left to provide insight into the data, assuming they were right in what they thought the data meant.

We can do better. Organizations can reduce the amount of time deciphering data by paying more attention to getting database structures designed as right as reasonably possible the first time. It is crucial when you start a project that involves data, no matter whether a completely new system or altering an existing system, to understand the reason a company stores data. Clearly, the first reason is the obvious one: to manage operations. Take money in, ship products out.

What follows is generally where the power of data comes in. Why did we get that money? How quickly did we ship the product? For people who received their product quickly, were they more likely to purchase more? Did the offer that was included on the receipt help to bring in more sales? How did we not know that people who bought peanut butter on a Tuesday ordered more milk on Friday?

Structure data properly for whatever data platform you are employing. Name attributes the same from version to version of a system, so you know what the ProductStatus means in v1, and v10, even though structures and even platform may have changed. Perhaps even more importantly, that when you needed to store the ProductStatus, you didn't use the LastName attribute in v3-3.2 because it was "easier" than adding a new column.

Just getting structure right is just the beginning. Too many databases are like basic buckets. They will allow any data in that the customer wants. This leads to situations like having a product order appear to be shipped years before it was even ordered; phone calls that appear to take less than 0 seconds; invoices that were paid in 3020. I could go on listing issues I have seen before, but the problem is that when computer systems allow bad data, bad data creeps in. Analyzing data with more than a smattering of instances with poor quality affects the results of analysis.

Discovering insights by analyzing data is what’s truly important in the long run. Data is useful operationally for minutes, perhaps days, but for analysis for years to come. It all starts with the boring, somewhat time-consuming basics of following proper design patterns.

Louis Davidson (@drsql)

Join the debate, and respond to the editorial on the forums

In this 70 minute livestream recording, I kick the tires of a fresh new Azure DevOps demo environment showing Redgate’s Hybrid Model for SQL Source Control and SQL Change...

Splunk is a search engine for collecting and analyzing all sorts of "machine data", including log data and metrics for SQL Server. SQL Monitor gives you the detailed diagnostic view of all your SQL Server instances, and databases. If you have Splunk to monitor your applications and server infrastructure, and SQL Monitor to help you understand the behavior of a complex database system such as SQL Server, then you have a powerful and capable monitoring tool set.

If you avoid illegal characters and reserved words in your identifiers, you'll rarely need delimiters. Sadly, SSMS applies square bracket delimiters indiscriminately, as a precaution, when generating build scripts. Phil Factor provides a handy function that adds quoted delimiters only where they are really needed and then sits back and lets SQL Prompt strip out any extraneous square brackets, in a flash.

Phil Factor explores the role of table aliases, explaining when they are required, and their general purpose otherwise, the need for sensible naming of aliases, and how SQL Prompt handles them.

We’ve added new capabilities to the Deployment Suite for Oracle, so it now supports automated migrations-based and state-based Oracle deployments on both Linux and Windows. We've introduced Redgate Change Control, a new capability for understanding development changes, generating migration scripts for version control, and getting precise control over the migration path during automated deployments. Our Schema Compare and Data Compare for Oracle command line tools have been extended to Linux as well as Windows, and we’ve added static code analysis that encourages teams to follow good coding practices on Windows or Linux.

Tracing a query is a common task for a DBA. The methods employed are pretty varied.

This post is going to walk through the quick steps to move TempDB through TSQL and the Windows Server UI.

SQL Server 2019 introduced, among other things, two new filesystem-related items:

I wrote a short blog post about the misperception that Profiler was easier than Extended Events when it came to the core concept of “click, connect, BOOM, too much...

Recently I’ve been delving into Chaos Engineerin...

As usual, the scripts in this post can be found HERE on our Git Hub repo If your SQL Server comes across something that it’s not happy about, it... The...

Total: 0 Average: 0An Overview of Traditional Recovery As with all relational database systems, SQL Server guarantees the durability of data by implementing crash recovery. Durability in the acronym...

Niko Neugebauer looks at some techniques for copyi...

In my previous post, I talked about deploying a Big Data Cluster on a single node Kubernetes cluster.

Click to learn more about author Lex Boost. In 2019, global hybrid solutions became the ‘hot topic’ for businesses. Industry experts anticipate this trend to continue this year as...

Free webinar February 12th 4-5pm GMT - The results are in! Join our expert panel, Kendra Little, Kellyn Pot’Vin Gorman and Grant Fritchey, to dig into the key findings from Redgate’s fourth annual State of Database DevOps report and learn how to use these insights to drive improvements in your own software development process.

We’re excited to announce our new ‘hybrid model’ workflow! The model combines the state-based development experience implemented in SQL Source Control and Source Control for Oracle with the customizable power of migrations-based deployments. Join this session on February 20th 4-5pm to find out more.

Remember all that talk in 2018 about the introduction of the GDPR across Europe? For many of us in the US, it didn’t seem real because it wasn’t in...

Data science can be a bit of a lonely job. It’s ...

Foreign keys help to ensure referential integrity ...

I am excited to announce a fairly substantial update to SSIS Catalog Browser! What’s New? In a word, speed. The #1 complaint I received from customers using previous versions...

(2020-Jan-28) This blog post is a followup to my ...

Recently, I was developing a simple SSIS package. The purpose of the SSIS package was extraction of a data from MS SQL server into Oracle database. Extraction data flow...

Total: 2 Average: 4.5In my previous article, I have explained the step-by-step process of installing SQL Server Failover Clustered Instance. In this article, I will explain how to add...

You have excessive WRITELOG waits (or HADR_SYNC_COMMIT waits) and among other things, you want to understand where.

Polygraph Unless you’re looking at an actual execution plan, one must never ever ever ever ever believe what one is…

Last Friday I spent my lunch break going through s...

We know that sorting can be one of the most expensive things in an execution plan as shown below. However,  we continue to do ORDER BYs repeatedly. Yes, I...

I was chatting to my good friend Treb Gatte and one of the things we both discovered is that there was not a good blog post with regards to an introduction into Power BI Premium

This post is part 1 in my series on creating financial statements in Power BI!

When using the cloud-based Power BI Service, powerbi.com, every action that is taken while logged into the portal — whether it is viewing or publishing a report, creating a new workspace, or even signing up for a pro trial license, that activity is logged within the Microsoft servers as part of the Office 365 audit logs.

The Waterfall chart is a good visualization to show you changes on value over a sequence, The sequence can be time, or date or workflow steps, etc. There is...

Send Power BI alerts to users via Teams using Power Automate and format the data using Adaptive Cards

Thanks for watching this week's Power BI news roundup! Last weeks roundup: https://guyinacu.be/roundup164 2 Minute Tuesday: https://guyinacu.be/qandatooling Patrick's tech video: https://guyinacu.be/copyreports Adam's tech video: https://guyinacu.be/pbiworkemail Sources Easily...

Perhaps one of the most important aspects of running a secure SQL Server is auditing login events – essentially capturing who’s logging in, and who’s attempting to login. Being aware of who’s logging in, and who’s trying to login, can help troubleshoot a variety of problems.

Background Fellow Microsoft MVP Troy Hunt (blog | ...

You may recall the transitive property from elementary school math class. It states:

Question: What are Different Methods to Know the R...

If you read the title to this post and your first ...

Say that you’ve got a table with a datetime column in it, like the LastAccessDate column in the Stack Overflow Users table: And let’s say you wanna find the...

SQL Server has a rich set of data types. Some of them were older data types and a number of new ones were introduced in SQL Server 2008. One...