Monitoring on a Budget - Part 2

Introduction

As a follow up to my first article "Monitoring on a Budget", here's how we present the fact data to management using Microsoft Excel. This has little to do with SQL Server and much to do with Excel but is certainly useful in quickly creating graphs from SQL Server data (or any external data). There are many other ways to present this data. I'd welcome your ideas and/or examples.

Technique

1. Open Excel (you can add this data to an existing sheet or use a blank one)
2. Click Data on the menu bar.

    

   
3. Select PivotTable and PivotChart Report...
4. Click the radio button for External data source and PivotChart report (with PivotTable report)
5. Click Next
6. Click Get Data...

    

   
7. Highlight <New Data Source> and click OK

    

   
8. Enter a NEW source name on line 1.
9. Select SQL Server on line 2.

    

   
10. Click Connect... on line 3.
11. Enter a server name and the appropriate connection credentials. Since I'm always the person generating these charts, I use a trusted connection. If many people will be viewing your charts, you may want to use a native SQL Server login.
12. Click Options >> and select the database that host's your statistics.
13. Click OK

     

    
14. Select one of the views defined in the original article
15. Click OK

     

    
16. Click OK
17. Click > to select all columns in the view
18. Click Next

     

    
19. Click Next (I'm not sure why you'd want to, but you can add your own filters here if you'd like)
20. Sort by: SrvName Ascending, Then by: SampDate Ascending
21. Click Next

     

    
22. Select the radio button for Return Data to Microsoft Office Excel
23. Click Finish

     

    
24. Click Next

     

    
25. Select the radio button for Existing worksheet
26. Select a cell which will be the starting point for inserting data into the sheet.
27. Click Finish

     

    

     

    
28. Highlight then drag SrvName from PivotTable Field List to Drop Page Fields Here

     

    
29. Highlight then drag SampDate from PivotTable Field List to Drop Category Fields Here
30. Highlight then drag any or all (one at a time) of the FACT columns to Drop Data Items Here. We created separate charts for each fact (repeating each step 2-30 above for each fact) for a cleaner presentation.

     

    
31. You can double click any of the labels and tags to enhance the appearance. We replaced all of the column and tab names with meaningful, unabbreviated words and added commas to the numeric scale.

     

    
32. Finally, you need to save the spread sheet.

     

    

Show me the money

Obviously, we have so much data that the dates across the bottom are no longer readable. If you pass your cursor across a point on the graph, it will pop up with the date and the value for that date.

You'll notice drop down boxes so you can select a specific server and/or dates. You'll need to click the exclamation point (!) to refresh the data if the database has changed since your last viewing. After a period of time, refreshing the data will take longer.

Conclusion

As I stated in the beginning, there are many ways to present this data. This technique is relatively simple and inexpensive. It is easy to view, allows access by multiple users, and provides the ability to filter the data. Immediately, you will be able to observe spikes/dips and over time, trends in performance metrics.