Adding a Data Source View for Call Center Data (Intermediate Data Mining Tutorial)
The initial data from the call center has been added to the AdventureWorks data warehouse to facilitate analysis. In this task, you add a new data source view that will be used to access the call center data.
Procedures
To add a data source view
In Solution Explorer, right-click Data Source Views, and select New Data Source View.
On the Welcome to the Data Source View Wizard page, click Next.
On the Select a Data Source page, under Relational data sources, select the Adventure Works DW2008 data source. If you do not have this data source, see Basic Data Mining Tutorial. Click Next.
On the Select Tables and Views page, select the following table and then click the right arrow to add it to the data source view:
- FactCallCenter (dbo)
Click Next.
On the Completing the Wizard page, by default the data source view is named AdventureWorks DW 2008. Change this to CallCenter, and then click Finish.
Data Source View Designer opens to display the CallCenter data source view.
In the Data Source View pane, right click the table FactCallCenter, and select Explore Data.
By browsing the data, you can see that the following columns are available for data mining.
Column name |
Contains |
|---|---|
FactCallCenterID |
An arbitrary key created when the data was imported to the data warehouse. |
DateKey |
The date of call center operation. Dates are not unique because the vendor provides a separate report for each shift in each day of operation. |
WageType |
Indicates whether the day was a weekday, or a weekend or holiday. |
Shift |
Indicates the shift for which calls are recorded. This call center divides the working day into four shifts: AM, PM1, PM2, and Midnight. |
LevelOneOperators |
Indicates the number of Level 1 operators on duty. Call center employees start at Level 1. |
LevelTwoOperators |
Indicates the number of Level 2 operators on duty. An employee must log a certain number of service hours to qualify as a Level 2 Operator. |
TotalOperators |
The total number of operators present during the shift. |
Calls |
Number of calls received during the shift. |
AutomaticResponses |
The number of calls that were handled entirely by automated call processing (Interactive Voice Response, or IVR). |
Orders |
The number of orders that resulted from calls. |
IssuesRaised |
The number of issues requiring follow-up that were generated by calls. |
AverageTimePerIssue |
The average time required to respond to an incoming call. |
ServiceGrade |
Indicates the abandon rate for the shift. Abandon rate is a metric frequently used by call centers. The higher the abandon rate, the more likely it is that customers are dissatisfied and that potential orders are being lost. The abandon rate is measured per shift. |
Based on your past experience with support centers, you decide that the day of the week might be a factor. The data source view does not contain this information, but you can easily derive the day of week given the date. To do this, you will create a named calculation in the data source view.
This approach is particularly helpful because you do not need to make any changes to the relational data, and you do not require a higher level of access to the database engine. Instead, you can create the calculations or derived columns as you require them. The following procedure describes how to add the calculated column by using the features in the Data Source View designer. In the example, you first convert the integer column, DateKey, to a datetime data type, and then extract the day of week information by using the DATENAME (Transact-SQL) function.
To create a new column in the data source view for the day of the week
From the Data Source View menu, select New Named Calculation.
In the Create Named Calculation dialog box, type DayOfWeek, which is the friendly name for the new day of week column. Type a description.
Copy the following expression into the Expression box.
DATENAME(weekday,CONVERT(DATETIME, CAST([Datekey] AS CHAR(8)),101))Click OK.
Select the data source view and select Explore to verify that the day of week is displayed correctly.
Next Task in Lesson
Creating a Neural Network Structure and Model (Intermediate Data Mining Tutorial)
Change History
Updated content |
|---|
Added procedure that describes how to add a named calculation to the data source view. This named calculation adds a column, DayOfWeek, that provides a better analysis for the mining model. |