Scenario Analysis-What-If Video Tutorial (Data Mining Add-in For Excel)

Welcome to another SQL Server 2008 Analysis Services (SSAS) screencast from Michele and Mary.

My name is Mary Brennan and I’m a Programmer Writer for Microsoft SQL Server 2008.

In this video I will show you how to use the Scenario Analysis - What-If Table Analysis Tool for data mining.

This tool is just one of many data mining add-ins for Excel and we’ve created screencasts for each one.

The Scenario Analysis tool uses the logistic regression algorithm and allows you to model two types of scenarios and report the effect on either a single row or the entire table of input data.

What If? analysis helps you answer the question "What will happen if I make this change?" This tool can help to determine, for example, the impact ‘decreasing Sales Staff’ will have on Sales based on factors it analyzes in your data.

In this tutorial we’ll use call center data to see how we can decrease Average Time Per Issue. The Excel data I am using is available on codeplex.com. If you use your own spreadsheet, just remember that in order to find meaningful patterns, you must begin with a minimum amount of good data. At least 50 rows of data.

We are interested in Level2Operators and Average time per issue, so for the purposes of this tutorial I’m going to hide some columns to make reviewing the results easier.

1. To begin, select the Table Analysis Tools Sample tab and click anywhere inside the table to activate the Table Analysis Tools.

2. Under the Table Tools menu, select the Analyze tab to open the Table Analysis Tools ribbon.

3. Click Scenario Analysis and then What-If to launch the wizard.

4. Select Average time per issue as the column to change.

5. Select Percentage, and type 80. This means that, on average, we are willing to take a little longer per issue.

6. If the Change column contains continuous numeric values, you can also specify a desired increase or decrease in the value. For example, I could choose Service Average time per issue and specify the change as an exact value.

7. In the What happens to box, select the column that will be impacted by changing Average time per issue. I am going to select Level2Operators. How many Level 2 Operators will I need if I decrease my Average Time Per Issue expectations?

8. If I click Run now, the analysis will be performed on all of the columns. Instead, I open Choose columns to be used for analysis… and deselect FactCallCenterID and TotalOperators. By streamlining my analysis I improve performance and accuracy. But be careful; don’t deselect columns that you will use for either the Target or the Change.

9. I will make predictions on Entire table and click Run.

10. My results are added as new columns to the right of the original data table. These columns show the impact on Level2Operators as a result of changing Average time per issue. The first column shows whether the number of Level 2 Operators would need to be increased or decreased IF we made this change. And the final column displays a level of confidence in the findings for each row.

Now let’s perform a What If? on a single row of data.

1. With a single row, the tool reports in the Results pane of the dialog box. If a successful solution is found, the tool displays the result. For example, the What If tool might tell you that if you increase the Average time per issue, you will need more Level2Operators.

I’ll quickly go through the same steps as I did for an entire table EXCEPT that I will make my predictions for On this row and then click Run.

1. My results display below; along with a level of confidence. This tells me that if I change Average time per issue to 80% of the average, I will need to increase Level 2 Operators from 10 to 11.

2. Here I’ll need to decrease from 8 to 7.

3. If I select this row with an Average time per issue of 81%, What If suggests decreasing Level 2 Operators from 8 to 6. But on this row also with 81% it suggests increasing from 9 to 10.

You’ll notice that the results are not as straightforward as you might have predicted! It isn’t simply a matter of decreasing Level 2 Operators when Average time per issue is less than the average and increasing Level 2 Operators when Average time per issue is longer than the average. Current Average time per issue cannot be used to predict whether the change will result in a need for more or for less Operators – other factors in the data also contribute to the What If results, illustrating the importance of using data mining to guide your business decisions.