How to Use Microsoft Excel to Perform Trend Analysis
Once you have determined which data you want to analyze and have exported it into Microsoft Excel, your first job is to create a chart as we described earlier in this article. To create a trendline for any of the counters you are tracking (you have to create a separate trendline for each counter), follow these steps:
-
Display the chart containing your historical data. The bigger you can display the chart on the screen, the easier it will be to use.
-
Select the counter you want to create a timeline for by clicking it with your right mouse button, then select “Add trendline” from the dropdown menu.
-
In the “Add Trendline” screen, you have the option of selecting the type of trendline you want to create. Unless you are an expert on statistics and know the meaning of each option, stick with the default choice of “Linear”.
-
Now, click on the “Options” tab of the “Add Trendline” screen. Here, under “Forecast”, you want to enter the number of time periods you want to project into the future. Time periods refers to the time interval for your historical data. For example, if your time period is one hour, and you want to project one month into the future, you would enter 720 (30×24).
-
When you are done, click on “OK”, and the trendline is created for you automatically in your chart. See figure below.
You can repeat these steps for each of the counters in your graph, looking for potential trends. Not all counters will indicate a trend, while others will have very obvious trends. Keep in mind that the quality of your trend analysis projections is based on the quality of the historical data you are feeding it. It is sometimes a little to easy to accept the results created by Microsoft Excel because the results “look” so good. But we all know the old adage, “garbage in, garbage out.”
As you can see, performing trend analysis on Performance Monitor data is relatively easy, once your historical data is imported into Microsoft Excel and it has been put into a chart. As a beginner, you will want to do a lot of experimenting to get charts that look great for presentations. And if you are a statistics expert, you can perform many others types of trend analysis.
Use Pivot Tables to Analyze Performance Monitor Data
Another powerful way to use Microsoft Excel to analyze your Performance Monitor Data is to use pivot tables. A pivot table is an interactive table that allows you to quickly filter, summarize or cross-tabulate data. A pivot table can be created from a Microsoft Excel table, or it can be created directly from a SQL Server table using Microsoft Excel’s ability to access an external data source using a DSN.
In this section, I will introduce you to the basics steps of how you might use pivot tables to analyze Performance Monitor data. What I won’t do is provide you step-by-step details, as this would add several thousand words to this already long article. If you are new to pivot tables, you can learn more about them from Microsoft Excel’s online help.
Use Pivot Tables to Aggregate SQL Server Performance Monitor Data
While pivot tables offer many options for summarizing and analyzing data, one of the most useful features of pivot tables is to aggregate your Performance Monitor counter data. For example, say that you collect Performance Monitor data every minute, but that you want to aggregate the data by the hour to make it easier for you to perform long-term trend analysis. One option would be to aggregate the data when you move it from SQL Server to Microsoft Excel using DTS. But if you don’t want to aggregate your data using DTS, you can perform the same task using a pivot table. Here are the basic steps:
-
The first step is to decide if you want to first export your data to Excel from SQL Server using DTS, then create the pivot table based on this data, or if you want to directly import the data from SQL Server using Microsoft’s data access capability. For this example, I will assume you will export the data from SQL Server to Microsoft Excel using DTS as described earlier in this article. Keep in mind that Microsoft Excel has a finite amount of space to hold data, so don’t send over too much data to Microsoft Excel.
-
When importing the data from SQL Server, I generally only bring over at least two columns of data: the “time” column and at least one counter column. Also, be sure that the “Time” column is brought over in a DATETIME data type, not a VARCHAR data type. If the “time” column is not in a DATETIME format, then the pivot table won’t be able to aggregate the times for you.
-
Once you have imported the data into an Excel spreadsheet, the next step is to run the Pivot Table Wizard, which is started by selecting Data|PivotTable Report from the Microsoft Excel drop-down menu.
-
In screen 1 of the Pivot Table Wizard, you must specify where the data is you want to analyze. Since our example is using data from a Microsoft Excel spreadsheet, select “Microsoft Excel list or database”. Click “Next” to continue.
-
In screen 2 of the Pivot Table Wizard, you must select the data from the Microsoft Excel spreadsheet that will be added to the pivot table. The Wizard will try to guess what data you want in your pivot table. If it guesses incorrectly, you can select it yourself by clicking and dragging the data on the spreadsheet. Click “Next” to continue.
-
Screen 3 of the Pivot Table Wizard is the most difficult to understand by most beginners. What you do here is to drag the column names (at the right of the screen) and place them on the Pivot Table (on the left of the screen). You do this by clicking on the column name, and then dragging the column name to the part of the pivot table where it belongs. Generally, you will want to drag your “time” column to the “row” part of the pivot table, and drag the counter column names to the “data” part of the pivot table. To keep things simple, I only drag one counter column name to the “data” part of the pivot table. Click “Next” to continue.
-
Screen 4 of the Pivot Table Wizard is the last screen, and all you have to do is tell the Wizard where you want to the pivot table to be created. Most often, select “New worksheet”. Then click “Finish”, and the pivot table will be created in a new worksheet.
-
The pivot table is now created, but we are not done yet. Our goal is to aggregate the data into larger time intervals. Once we have done this, we can use our data to create a chart and then perform trend analysis on it, like described earlier in this article. Now let’s take a look at the two steps required to aggregate our data.
-
The first step is to select all of the cells in the pivot table for the counter you want to aggregate. Do this by clicking and dragging. Once all the cells are selected, right-click on any of the selected cells and select “Field” from the menu. This displays the Pivot Table Field screen. From here, click on “Average” from the “Summarize by” list box, then click “OK”. Once you have done this it will appear as if nothing has happened. It has, but you won’t see it until the next step.
-
The second, and final step, is to first select the “Time” column by clicking on the “Time” column heading on the pivot table. Next, select Data|Group and Outline|Group from the drop-down menu. This displays the Grouping screen. Here, select how you want to aggregate your data. You can choose grouping by Seconds, Minutes, Hours (the most useful), Days, Quarters, and Years, then click on “OK”.
Finally, you are done. The pivot table should now show an aggregation of your counter data, averaged over the time period you specified. At this point, you can then chart the data using the Microsoft Excel Chart Wizard, and then add a trendline as described earlier.
This is Just the Beginning
While this article is long, it just barely touches the surface of what you can do with Microsoft Excel. If what you have found here is of interest, I suggest you take some time and experiment. If you are new to Microsoft Excel, then I suggest you get a good book on it, or take a class. While Microsoft Excel is not a perfect tool, it provides a lot of power if you learn how to take advantage of it.
Read Part Four
In the next installment (part four of four parts), we take a look at how to interpret SQL Server Performance counters data. Read part four.
]]>