Mastering SQL Server 2014 Data Mining

Before going into further detail, it is important to understand the various stages of the data mining life cycle. The data mining life cycle can be broadly classified into the following steps:

Let's look at each of these stages in detail.

First and foremost, the task that needs to be well defined even before beginning the mining process is to identify the goals. This is a crucial part of the data mining exercise and you need to understand the following questions:

Let's define a classic problem and understand more about the preceding questions. Note that for the most part of this book, we will be using the AdventureWorks and AdventureWorksDW databases for our data mining activities as they already have the schema and dimensions predefined. We can use them to discuss how to extract the information rather than spending our time on defining the schema.

The details on how to acquire the AdventureWorks database is already discussed in the Preface of this book.

Consider an instance where you are a salesman for the AdventureWorks Cycles, company, and you need to make predictions that could be used in marketing the products. The problem sounds simple and straightforward, but any serious data miner would immediately come up with many questions. Why? The answer lies in the exactness of the information being searched for. Let's discuss this in detail.

The problem statement comprises the words predictions and marketing. When we talk about predictions, there are several insights that we seek, namely:

From the marketing point of view, several follow-up questions that must be answered are as follows:

We might observe that there are many questions that overlap the two categories and, therefore, there is an opportunity to consolidate the questions and classify them as follows:

Let's throw some light on these aspects based on the AdventureWorks example. We will get answers to the preceding questions and arrive at a more refined problem statement.

What is the population that we are targeting? The target population might be classified according to the following aspects:

What are the factors that we are actually looking at? They might be classified as follows:

What is the time period of the past data that we would be looking at? Usually, the data that we get is quite huge and often consists of the information that we might very adequately label as noise. In order to sieve effective information, we will have to determine exactly how much into the past we should look at; for example, we can look at the data for the past year, past two years, or past five years.

We also need to decide the future data that we will consider the data mining results for. We might be looking at predicting our market strategy for an upcoming festive season or throughout the year. We need to be aware that market trends change and so do people's needs and requirements. So we need to keep a time frame to refresh our findings to an optimal; for example, the predictions from the past five years' data can be valid for the upcoming two or three years depending upon the results that we get.

Now that we have taken a closer look into the problem, let's redefine the problem more accurately. AdventureWorks Cycles has several stores in various locations and, based on the location, we would like to get an insight into the following:

It is not necessary that we receive answers to all the detailed questions but even if we keep looking for the answers to these questions, there would be several insights that we will get, which will help us make better business decisions.

In this phase, we collect data from all the sources and dump them into a common repository, which can be any database system such as SQL Server, Oracle, and so on. Usually, an organization might have various applications to keep track of the data from various departments, and it is quite possible that all these applications might use a different database system to store the data. Thus, the staging phase is characterized by dumping the data from all the other data storage systems into a centralized repository.

Extract, transform, and load

This term is most common when we talk about data warehouse. As it is clear, ETL has the following three parts:

• Extract: The data is extracted from a different source database and other databases that might contain the information that we seek

• Transform: Some transformation is applied to the data to fit the operational needs, such as cleaning, calculation, removing duplicates, reformatting, and so on

• Load: The transformed data is loaded into the destination data store database

We usually believe that the ETL is only required till we load the data onto the data warehouse but this is not true. ETL can be used anywhere that we feel the need to do some transformation of data as shown in the following figure:

[ProductKey]
 [OrderDateKey]
 [DueDateKey]
 [ShipDateKey]
 [CustomerKey]
 [PromotionKey]
 [CurrencyKey]
 [SalesTerritoryKey]
 [SalesOrderNumber]
 [SalesOrderLineNumber]
 [RevisionNumber]
 [OrderQuantity]
 [UnitPrice]
 [ExtendedAmount]
 [UnitPriceDiscountPct]
 [DiscountAmount]
 [ProductStandardCost]
 [TotalProductCost]
 [SalesAmount]
 [TaxAmt]
 [Freight]
 [CarrierTrackingNumber]
 [CustomerPONumber]
 [OrderDate]
 [DueDate]
 [ShipDate]

[BusinessEntityID]
,[NationalIDNumber]
,[LoginID]
,[OrganizationNode]
,[OrganizationLevel]
,[JobTitle]
,[BirthDate]
,[MaritalStatus]
,[Gender]
,[HireDate]
,[SalariedFlag]
,[VacationHours]
,[SickLeaveHours]
,[CurrentFlag]
,[rowguid]
,[ModifiedDate]

Create table employee_load_status(
HireDate   DateTime,
LoadStatus       varchar
);

With employee_loaded_date(HireDate)  as 
(select  ISNULL(Max(HireDate),to_date('01-01-1900','MM-DD-YYYY')) from employee_load_status where LoadStatus='success'
Union All
Select ISNULL(min(HireDate),to_date('01-01-1900','MM-DD-YYYY')) from employee_load_status where LoadStatus='failed'
)
Insert into DimEmployee  select * from employee where HireDate >=(select Min(HireDate) from employee_loaded_date);

[BusinessEntityID]
,[TerritoryID]
,[SalesQuota]
,[Bonus]
,[CommissionPct]
,[SalesYTD]
,[SalesLastYear]
,[rowguid]
,[ModifiedDate]

Before entering the warehouse, the data should go through several cleaning operations. These involve shaping up the raw data through transformation, null value removals, duplicate removals, and so on. In this section, we will discuss the techniques and methodologies pertaining to the understanding and cleansing of the data, we will see how we can identify the data that we need to cleanse, and how we will set a benchmark for the data sanity for further data refresh.

Data cleansing is the act of detecting the data that is either out of sync, not accurate, or incomplete, and we either correct, delete, or synchronize the data so that the chances of any ambiguity or inaccuracy in any prediction that is based on this data is minimized.

When we talk about data cleansing, we definitely need to have a basic data benchmark in place—a dataset against which the incoming data can be compared. Or, there needs to be a set of conditions or criteria against which the incoming data needs to be compared. Once the data is compared and we have a list of deviation of the data from the criteria for data sanity, we can very easily correct the deviation or delete the data with the deviation.

We will now get more specific on the meaning of the benchmark data or the set of conditions. The following are some of the situations that would help us to get a deeper understanding of what criteria a benchmark data will possess:

Thus, as we see from the preceding discussion, a benchmark data would have the following aspects:

The preceding task of ensuring high quality data might seem to be an uphill task and might not be feasible for a human being; therefore, we have many tools that are at our disposal, which possess the ability to analyze the incoming data, detect any variation from the defined standard, suggest the correction, and even correct the data for us if we so desire. One such tool that we will be using in our scenario is the Data Quality Services (DQS).

DQS is a knowledge-based product that is available with the SQL Server and can be installed as a part of the SQL Server installation.

Rather than going into the discussion of the DQS, we will go through a practical example here and see how the DQS makes our task of maintaining the data quality easier and also ensures a high degree of data quality.

The first thing that we need to do to have a data quality project is to create a knowledge base. We will have to start an instance of Data Quality Client.

Once started, the screen will look this:

Start-up screen of the Data Quality Client

We will now create a new knowledge base by clicking on the New Knowledge Base button, as shown in the following screenshot:

Data Quality Client screen

This opens up the new Knowledge Base Management window, as shown here:

The Knowledge Base Management interface

Now, we will have to decide which columns we are going to have as our reference. In AdventureWorksDW, we take CustomerAlternateKey, MaritalStatus, and Gender from the table DimCustomer as KnowledgeBase.

We will now develop knowledge base as follows:

Selecting the source of the data

Click on the icon, as shown in the following screenshot, to create a domain. We will create a unique domain for every column:

Creating a Domain

Click on the Next button to start the data discovery and we will reach the following screen:

Starting the Data Discovery Process once the Domains for the Fields have been created

In the following screenshot, we can see that CustomerAlternateKey has all the unique values, the MaritalStatus column has 2 unique values and the Gender column has 2 unique values:

Unique values contained in each column

Then, we reach the following screen that gives us detailed data for each column:

Results of the discovery for each column

When we click on the Finish button, we will see the following screen:

We publish this knowledge base to be used for other datasets

We will now click on Publish and we have a base data ready to test the sanity of other datasets.

Let's induce some anomaly in the data by changing the first record of the DimCustomer table as follows:

Now, let's apply the DQS implementation over this new table and see the results. We can click on New Data Quality Project, as shown here:

Starting a New Data Quality Project

We name the project as testSanity, and select the activity as Cleaning, as shown in the following screenshot:

Project is for cleaning the data

We now click on Next and reach the next screen where we need to select the table to be cleansed, as shown here:

The Map tab in Data Quality Project

The remaining fields will be populated automatically. We now click on Next and start the analysis to arrive at the following screenshot:

The CustomerKey details in Data Quality Project

We now highlight the Gender domain to see the suggestion and the confidence for this suggestion, as shown here:

The Gender details in Data Quality Project

The confidence and suggestion for MaritalStatus is as shown in the following screenshot:

The MartialStatus details in Data Quality Project

The Confidence column is the certainty with which the DQS is suggesting the changes in the value. So, we can see that it suggests the change in CustomerAlternateKey with a confidence of 73 percent, but it suggests the addition of the values of MaritalStatus and Gender as N with a confidence of 0 percent, which we can approve or ignore.

In this chapter, we've covered the first three steps of any data mining process. We've considered the reasons why we would want to undertake a data mining activity and identified the goal we have in mind. We then looked to stage the data and cleanse it. In the next chapter, we will look at how the SQL Server Business Intelligence Suite will help us work with this data.