As a follow up to our previous article on monitoring SQL Server on a budget, we will now discuss how to present the collected data to management using Microsoft Excel. While this topic is more focused on Excel rather than SQL Server, it is a useful skill to quickly create graphs from SQL Server data or any external data source. In this article, we will explore a technique to present SQL Server data in Excel and discuss its benefits.
The Technique
Here are the steps to present SQL Server data in Excel:
- Open Excel and create a new sheet or use an existing one.
- Click on “Data” in the menu bar.
- Select “PivotTable and PivotChart Report…”
- Choose the radio button for “External data source and PivotChart report (with PivotTable report)”
- Click “Next”
- Click “Get Data…”
- Highlight “
” and click “OK” - Enter a new source name on line 1.
- Select “SQL Server” on line 2.
- Click “Connect…” on line 3.
- Enter the server name and appropriate connection credentials.
- Click “Options >>” and select the database that hosts your statistics.
- Click “OK”
- Select one of the views defined in the original article.
- Click “OK”
- Click “OK”
- Select all columns in the view by clicking “>”
- Click “Next”
- Click “Next”
- Sort the data by SrvName in ascending order, then by SampDate in ascending order.
- Click “Next”
- Select the radio button for “Return Data to Microsoft Office Excel”
- Click “Finish”
- Click “Next”
- Select the radio button for “Existing worksheet”
- Select a cell as the starting point for inserting the data into the sheet.
- Click “Finish”
- Drag and drop the SrvName field from the PivotTable Field List to “Drop Page Fields Here”.
- Drag and drop the SampDate field from the PivotTable Field List to “Drop Category Fields Here”.
- Drag and drop any or all of the FACT columns to “Drop Data Items Here”.
- Repeat the previous step for each FACT column to create separate charts for each metric.
- Double click on labels and tags to enhance the appearance.
- Replace column and tab names with meaningful, unabbreviated words and add commas to the numeric scale.
- Save the spreadsheet.
Benefits of this Technique
This technique offers several benefits when presenting SQL Server data:
- Easy to view: The data is presented in a graphical format, making it easier to interpret and analyze.
- Accessible to multiple users: The Excel file can be shared with multiple users, allowing them to view and interact with the data.
- Filtering capabilities: Users can filter the data by selecting specific servers and dates using drop-down boxes.
- Observing trends and spikes: The graphs allow users to observe performance metrics over time, identifying trends and spikes in the data.
While there are many other ways to present SQL Server data, this technique is relatively simple and inexpensive. It provides a quick and effective way to visualize and analyze the collected data without the need for complex tools or software.
We hope you find this technique useful in presenting your SQL Server data to management. Feel free to share your ideas and examples on alternative methods of presenting data.