Export (0) Print
Expand All

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.

To add a data source view

  1. In Solution Explorer, right-click Data Source Views, and select New Data Source View.

  2. On the Welcome to the Data Source View Wizard page, click Next.

  3. 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.

  4. 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)

  5. Click Next.

  6. 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.

  7. 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

  1. From the Data Source View menu, select New Named Calculation.

  2. In the Create Named Calculation dialog box, type DayOfWeek, which is the friendly name for the new day of week column. Type a description.

  3. Copy the following expression into the Expression box.

    DATENAME(weekday,CONVERT(DATETIME, CAST([Datekey] AS CHAR(8)),101))
    
  4. Click OK.

  5. Select the data source view and select Explore to verify that the day of week is displayed correctly.

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.

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft